MYSQL database exception error 42000 when posting more than 50 records
Hi Experts,
We have a php app written that posts information to a mysql database. Everything works fine until we try and post more than around 50 records to our mysql db? 50 and less works fine but when we try to start posting 55 or more then it gives an error: Database Exception (#42000). What could be the issue?
We have a php app written that posts information to a mysql database. Everything works fine until we try and post more than around 50 records to our mysql db? 50 and less works fine but when we try to start posting 55 or more then it gives an error: Database Exception (#42000). What could be the issue?
Can you provide an example of your query? Are you bulk inserting records in one query (e.g. INSERT INTO table (X, Y) VALUES ('x1', 'y1'), ('x2', 'y2'), etc..., or are you inserting records one-by-one?
Make sure you dump / view the whole error message, too (http://php.net/mysqli_error) - not just the code. The entire error message will tell you a lot more information about what specifically went wrong.
Make sure you dump / view the whole error message, too (http://php.net/mysqli_error) - not just the code. The entire error message will tell you a lot more information about what specifically went wrong.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looking up exceptions and their meanings in https://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html I see 123 (one hundred twenty-three) occurrences with very different specific errors and it would be very helpful to see which error. In short 42000 just means database exception or error in the statement executed. Any error, any one of 123 different errors.
One plausible error matching to what you tell is a string length limit that happens at about 50 records in some INSERT statement with multiple VALUES tuples somewhere in the middle of the insert statement causing any type of error like syntax errors due to an incomplete list.
Preparing a single record insert and then executing that multiple time can help you get to any number of records, or stay with what you now do, but split it into groups of 50 records.
But more important than to decide which solution to choose may be finding out what exact error you have. You may also have something as simple as an unallowed character, an unescaped single quote within a single quote delimited string you generated instead of using a parameterized query, for example. Something that could also hit you earlier or later than with 55 records.
Bye, Olaf.