What type of errors actually happen with mySQL/PHP

Question

I'm working on a web-based application using mySQL, and I've used it in the past. I've never seen a mySQL error other then something during the development process -- ie. poorly formed selects -- but once its 'working' what kind of errors are there?

I've never seen an insert or select fail - can they? If the syntax is correct, why would they?

I'm mostly asking this with an eye toward figuring out what to do if an error actually does occour. If an insert/update fails, do I sleep a second and try again? How about a select? How many times before just giving up?

EDIT: Maybe I asked this poorly, what I'm looking for is "How should I handle mySQL errors if and when they occour?". In every sample I've ever seen its to just die -- in a public application that seems very ugly. Is it worth retrying the command in a few seconds and then giving a error to the user and emailing the admin? Or just accept that a fatal error won't get fixed.

Answer 1

Could be something as simple as someone shutting down the MySQL server.
There might be limits set on the MySQL server, e.g. MAX_QUERIES_PER_HOUR.
There can be errors or timeouts where the server drops the connection to the client (the popular CR_SERVER_LOST).
The data (file) may be corrupt which you may notice not until a query on the database/table runs.
Your test environment may have never run into a "packet too large" situation but then your production server does.
And many, many more... Have a read of http://dev.mysql.com/doc/refman/5.1/en/problems.html

Answer 2

Here are a few examples of errors that can occur even with SQL statements that are syntactically correct and which may work in your development environment:

• Duplicate key violation from an INSERT or UPDATE. That is, a PRIMARY KEY or UNIQUE KEY prevents you from setting a column to the same value in another row. Sleeping is not likely to fix this, you need to change the values you're inserting/updating.

• Constraint violations such as unsatisfied foreign key references or setting a NOT NULL column to NULL. You need to insert/update using values that do not violate the constraints. Deleting a row that has foreign key dependencies results in an error unless you defined cascading behavior in the FK constraint declaration. Also it's an error to insert or update using a value that's out of range for a column's data type.

• SQL privileges can restrict the operations you can perform.

• SELECT may give an error if you generate queries dynamically with your application code, and the query it generates is flawed.

• A query that worked before may give an error if someone changes the MySQL SQL MODE to be more strict. For example, some implicit data type conversions or permissive enforcement of the Single-Value Rule in GROUP BY queries may fail.