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?
LVL 2
Reinert WentzelManaging MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.