stored procedure insert statement
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=arch...@jab.org
Re: stored procedure insert statement
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
Re: stored procedure insert statement
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
Re: stored procedure insert statement
Thanks Johnny, In this case I wouldn't be able to insert a completely new row but replace the existent one, so row count would stay the same. This is a storage table with the only unique constraints on: dda_debits_id column. the test data is very small, so I would've noticed any duplicates and they wouldn't make it to the table anyway with or without INSERT IGNORE. +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | dda_debits_id | int(11) | NO | PRI |0 || | created_on| datetime| YES | | NULL || | reference_number | varchar(18) | YES | | NULL || | user_format_debit_ref | varchar(18) | YES | | NULL || | amount| int(11) | YES | | NULL || | debit_date| datetime| YES | | NULL || | status| tinyint(1) | YES | | NULL || | debit_type| tinyint(1) | YES | | NULL || | recharge_for_id | int(11) | YES | | NULL || | processed_on | datetime| YES | | NULL || | service_user_id | int(11) | YES | | NULL || +---+-+--+-+-++ Claudio, good point. Unfortunately, didn't work. I tried it before but no luck. Thanks, Igor On 07/09/2011 02:43 PM, Johnny Withers wrote: 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 mailto: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
