That's what is bad of SP in MySQL, debugging. Just out of the blue, can you try to disable query cache?
*SET GLOBAL query_cache_size = 0;* * SET GLOBAL query_cache_type = 0; * it could be a bug Claudio 2011/7/9 Johnny Withers <joh...@pixelated.net> > 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 > -- Claudio
