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
2011/7/9 Johnny Withers <johnny@stripped>
> It seems to me that your insert statement is trying to insert duplicate
> into the storage table. This is why insert ignore and replace work.
> On Jul 9, 2011 3:49 AM, "Igor Shevtsov" <nixofortune@stripped>
> Hi all,
> I can't explain strange behaviour of the INSERT statement in the stored
> 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)
> 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.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1