Comments - INSERT SELECT

6 years, 4 months ago Matt Cole
This comment has the status of 'removed' and can only be seen by you.

I've got an issue where it appears that data that I insert isn't available for an immediate SELECT statement executed by the same thread.

I'm inserting rows into "table_a", that has columns "id", "a", and "b". "id" is an auto_number column, and I have a separate index on "a". For each row, I grab the insert ID, then run "INSERT INTO table_b (table_a_id_1, table_a_id_2) SELECT ?, id FROM table_a WHERE a = ? AND id != ?" (the variables are replaced with the insert ID, the value of "a" I just inserted, and the insert ID again). Both tables are InnoDB tables.

My problem is that the query never inserts anything. It's not throwing an errors -- it just seems that the SELECT part of the query isn't matching anything, even when I know that it should be. I know the data is there -- I can take the query, run it manually through the mysql command line, and get the results I'm expecting. I've tried this a number of different ways -- separating it into two separate queries (a SELECT and an INSERT query), marking the INSERT query as HIGH_PRIORITY, wrapping the whole thing in a transaction...nothing seems to work. All signs point to "the SELECT query isn't returning any rows".

Is this caused by some sort of write caching issue in MariaDB? Is there some way I can work around this?

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.