List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:July 9 2011 1:43pm
Subject:Re: stored procedure insert statement
View as plain text  
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" <nixofortune@stripped> 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=1

Thread
stored procedure insert statementIgor Shevtsov9 Jul
  • Re: stored procedure insert statementJohnny Withers9 Jul
    • Re: stored procedure insert statementClaudio Nanni9 Jul
    • Re: stored procedure insert statementIgor Shevtsov9 Jul