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=gcdmg-mysql-2@m.gmane.org

Re: stored procedure insert statement

--001517592bd0d5355904a7a3239c
Content-Type: text/plain; charset=ISO-8859-1

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" 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=johnny@pixelated.net

--001517592bd0d5355904a7a3239c--

Re: stored procedure insert statement

--bcaec5431e90708de304a7a4de05
Content-Type: text/plain; charset=ISO-8859-1

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

> 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"
> 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=johnny@pixelated.net
>



--
Claudio

--bcaec5431e90708de304a7a4de05--

Re: stored procedure insert statement

--------------050804090603020707060301
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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" >> > 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=johnny@pixelated.net
>>

--------------050804090603020707060301--