It seems to me that your insert statement is trying to insert duplicate rows into the storage table. This is why insert ignore and replace work.
On Jul 9, 2011 3:49 AM, "Igor Shevtsov" <nixofort...@googlemail.com> wrote: Hi all, I can't explain strange behaviour of the INSERT statement in the stored procedure. The idea is to generate a list based on the output of 3 INNER JOIN of regularly updated tables. Something like : INSERT INTO storage (column list) SELECT column list FROM t1 JOIN t2 ON t1.x=t2.y JOIN t3 ON t2.z=t3.w WHERE CONDITIONS; The procedure runs daily by crontask and it inserts correct number of output rows. But after It runs and populated a storage table, I added new entries and expect to find them in the storage table. Even though they were picked up by SELECT statement, they haven't been INSERTed into the storage table. If I DELETE or TRUNCATE from the storage table and run the procedure all newly added entries and existed entries are their, but if I add new rows and run the procedure again It doesn't update the table. All tables have a unique identifier, so duplicate errors are impossible. I use INNODB engine for all tables. I understand that stored procedure is a precompiled thing and I believe it could be something to do with cache but I couldn't find proper explanation or similar case online. I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive the proper result with newly entries added to the storage table. Any ideas guys? Have a nice weekend ALL. Cheers, Igor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
