List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:July 9 2011 3:47pm
Subject:Re: stored procedure insert statement
View as plain text  
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 <johnny@stripped>

> 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
>



-- 
Claudio

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