• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 43
  • Last Modified:

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?
Reinert Wentzel
Reinert Wentzel
1 Solution
Olaf DoschkeSoftware DeveloperCommented:
The exception number is just about the type of exception and not very specific about what error occurred.

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.
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.
Reinert WentzelManaging MemberAuthor Commented:
issue was caused by records being inserted as VARCHAR and it has a limit. Changed to TEXT and sorted

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now