Posted on 2 Comments

Error handling for MySQL applications

When connecting to MySQL, or executing a query, proper error handling is required. Many take this very seriously, and do a construct like mysql_connect() or die() or the equivalent with mysql_query(). For web apps this generally makes error codes end up on the user page, you can easily see this by doing a Google search for some of the common error texts. Slightly improved apps are nicer to the user and log the error separately.

But both approaches fail, fundamentally, as they don’t take into account that not all errors are the same and, most importantly, many error are not fatal but require other forms of action. So let’s look at that, look at what causes the errors so you truly understand that it’s not fatal stuff, and how you can handle them.

When you get a “not successful” response back from a MySQL API function, you first need find out what the error is with mysql_errno() (and mysql_error() for a corresponding text string, if you want to log it).

So when in the below I write about an API function returning an error, I don’t mean that the function itself literally returns the error code… it’ll return false (or whatever the appropriate return code for non-success is for that function) and you need to call mysql_error() to retrieve the actual error code. Oki? Let’s continue!

  • If mysql_connect() to the local Unix socket comes up with OS (operating system, the <1000 codes come from the OS not MySQL server as such) error code 11 (MySQL error text “Resource temporarily unavailable”) this is actually defined as EAGAIN in your operating system. That’s quite descriptive actually and can be read as “failed now, but do try again”.
    Typically you can get this error when the maximum # of connections has been reached. A possible cause can be that apps keep a connection open even if they don’t need to use it for an extended period, or an app server doing persistent connections even though the infrastructure does not effectively manage them (Apache/PHP is the prime example there). MySQL has a very fast connection handshake so speed-wise persistent connections are not necessary.
    There are some things you can tune on the MySQL server end and some other things to look at on the application server end… I posted a note about the PHP issue and solution 4 years back.

  • If the error code is 1040 (Too many connections) the situation is similar but you were trying to open a TCP/IP connection to the server. The handling is similar though. You will want to retry, but not keep on looping forever of course 😉
    By the way, you can’t just increase max_connections in my.cnf as some buffers are allocated per-connection and thus particularly on 32-bit installations you could get into memory trouble.

  • Error 1046 I’m tossing in here just to make the point about non-fatal-errors… it means “No database selected” and indicates you simply forgot to do mysql_select_db() before running a query that doesn’t explicitly specify the database for each table it uses.
  • Error 2013 (Lost connection to MySQL server during query) is actually a generated inside your client library, and if your environment is using the C client library then simply re-issuing the query will make the library reconnect as well. You can also reconnect explicitly and then re-run the query. Why does it happen?
    It could be a network error that caused the TCP/IP connection to drop, or the wait_timeout was exceeded on the server; the latter can actually be useful in keeping the # of open connections down, but the app will then need to handle errorcode 2013 correctly!!

  • Error 1213 (Deadlock found when trying to get lock; try restarting transaction) can only occur when using InnoDB, since MyISAM is by its design deadlock-free. You just need to re-run the query. If it’s a multi-statement transaction, the entire transaction should be re-run. (I’ll clarify this in a separate blog post immediately after this one!)
  • Error 1614 (Transaction branch was rolled back: deadlock was detected) is XA (distributed transaction, two-phase commit) related, handling essentially the same as for 1213.
  • Error 1205 (Lock wait timeout exceeded; try restarting transaction) can occur with both InnoDB and MyISAM, and again simply reissuing the statement or transaction is the way to go (also see note below).

Next up a related post about deadlocks and lock wait timeouts in both MyISAM and InnoDB, and more specifically about some things to look out for when converting from MyISAM to InnoDB (Migrating MySQL MyISAM apps to the InnoDB storage engine). Also, to be very clear, when I write about “re-run” or “re-issue” the query, I don’t mean “create an endless loop”. Normal coding sense applies.

The error codes mentioned in this post are merely very common ones, there are more! For ref, see Errors, Error Codes, and Common Problems in MySQL’s online reference manual.

Posted on 2 Comments

2 thoughts on “Error handling for MySQL applications

  1. User referenced to your post from Migrating MySQL MyISAM apps to the InnoDB storage engine saying: […] encounter additional error situations when using InnoDB, which it must handle. In my previous post (Error handling for MySQL applications) I already indicated that many apps just don’t handle errors properly at all. Many errors are not … […]

  2. […] encounter additional error situations when using InnoDB, which it must handle. In my previous post (Error handling for MySQL applications) I already indicated that many apps just don’t handle errors properly at all. Many errors are […]

Comments are closed.