Page 1 of 1

MySQL not inserting values when too many queries in a row?

Posted: Tue Jun 30, 2009 10:52 am
by Alcoori
Hi everyone,

I just stumbled on something that is a little bit of a mystery to me.
I have this PHP script which parse a nice little .csv file, get the data written in it and goes on to perform a series of INSERT into a MySQL table.

To make this a litte less obscure, let's say that those files contain IDs that correspond to certain items, and their corresponding price at certain dates. A file usually contains either 1 or 3 different IDs and 50 values at most. The file where there is only 1 item don't cause any trouble, but the ones with three different IDs do.
For example let's say I have in this file, 3 IDs : 10, 20 and 30. I'll have several INSERT queries looking like this one:
INSERT INTO prices(id_item,observation_date,price) VALUES('$id_item','$obs_date','$price'), where $id_item, $obs_date and $price are PHP variables containing the values parsed.
I did check that the queries send to MySQL do have the right format and values put in them (using an echo) but when I check the table, the values for the IDs 10 are here, but not those for the IDs 20 or 30 8O

When I parse those files separetly, the scripts do insert the values in the DB. So I ruled out anything wrong with the PHP script but that still left me so confused. How is this possible? I thought about a saturation of the memory or something like that but I don't think that is the case because the files that are incorrectly parsed are parsed in the middle (ie the first two files are parsed correctly, the 3rd and 4th one are not but the 5th is).
Like I said, I also checked the queries to the DB using the 'echo' function (I just print on the screen the queries passed to MySQL), the result looks like that :

Code: Select all

 
INSERT INTO prices(id_item,observation_date,price) VALUES('164','2009-05-01','12')
INSERT INTO prices(id_item,observation_date,price) VALUES('164','2009-05-31','15')
INSERT INTO prices(id_item,observation_date,price) VALUES('164','2009-06-01','2')
INSERT INTO prices(id_item,observation_date,price) VALUES('164','2009-06-30','5')
 
INSERT INTO prices(id_item,observation_date,price) VALUES('432','2009-05-01','52')
INSERT INTO prices(id_item,observation_date,price) VALUES('432','2009-05-31','45')
INSERT INTO prices(id_item,observation_date,price) VALUES('432','2009-06-01','46')
INSERT INTO prices(id_item,observation_date,price) VALUES('432','2009-06-30','20')
 
INSERT INTO prices(id_item,observation_date,price) VALUES('1223','2009-05-01','32')
INSERT INTO prices(id_item,observation_date,price) VALUES('1223','2009-05-31','35')
INSERT INTO prices(id_item,observation_date,price) VALUES('1223','2009-06-01','29')
INSERT INTO prices(id_item,observation_date,price) VALUES('1223','2009-06-30','25')
[...]
 
I'm using the MDB2 PEAR package to manage my queries to the database and haven't had any problems until now and the result of the query doesn't yield any errors.

Does anyone have any idea of what's going on?

Thank you!

Re: MySQL not inserting values when too many queries in a row?

Posted: Wed Jul 01, 2009 3:47 pm
by Darhazer
May be you hit the execution time limit? By default PHP script is allowed to run for 30 seconds, that it's killed?

And by the way, you will achieve much better performance if you use:

Code: Select all

INSERT INTO prices(id_item,observation_date,price) VALUES('164','2009-05-01','12'),('164','2009-05-31','15'),('164','2009-06-01','2'),('164','2009-06-30','5');

Re: MySQL not inserting values when too many queries in a row?

Posted: Thu Jul 02, 2009 4:23 am
by Alcoori
Well I don't think that's the case. It happened to me on other scripts and an error message was displayed as the script would stop running.

Here the problem seems to be different, the script does parse every .csv file (or at least go through them) but will not insert some values.
Here is an example of the queries made to MySQL (with your tip that does indeed speed up the process, didn't know it :))

Code: Select all

 
INSERT INTO prices(id_item,observation_date,price) VALUES('164','2009-05-01','12'),('164','2009-05-31','15')('164','2009-06-01','2'),('164','2009-06-30','5');
 
INSERT INTO prices(id_item,observation_date,price) VALUES('4179','2009-05-01','52'),('4179','2009-05-31','45'),('4179','2009-06-01','46'),('4179','2009-06-30','20');
 
INSERT INTO prices(id_item,observation_date,price) VALUES('163','2009-05-01','32'),('163','2009-05-31','35'),('163','2009-06-01','29'),('163','2009-06-30','25');
 
As you can see, the prices for the ID 4179 are supposedly inserted before those for the ID 163. However in the DB, I do have entries for the IDs 164 and 163 but nothing for the ID 4179!
I don't get any error message, nothing, that's what is puzzling me.
If I query only the second one, the values will be inserted. It just doesn't work if I insert too many values at once.

Re: MySQL not inserting values when too many queries in a row?

Posted: Thu Jul 02, 2009 4:44 am
by Alcoori
Ok nevermind, it has nothing to do with PHP or MySQL but with me being dumb and deleting the values within my script :D

Thanks for the help though!