There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went wrong.
To illustrate, consider the following query:
INSERT INTO my_table (my_column) VALUES (300);
What could go wrong with this query?
- We might hit a UNIQUE KEY violation
- Or a FOREIGN KEY violation
- my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
- Or, similarly, it could be an ENUM (2,3,5,8)
Is that it? Not remotely:
- This could be a read-only MyISAM table
- We may have issued a LOCK TABLES my_table READ — this violates our lock
- Or this could be an InnoDB table, and this INSERT would make for a deadlock
- Or we have read_only=1 configuration
- Or the user is not allowed access to this table
- Or the table does not exist
- Or the column does not exist
Or… I’m pretty sure there could be many other issues.
Now, if I write a Java program, perhaps using Hibernate, I get the error nicely wrapped up in a SQLException object, with easy access to error code and error message.
But can I have the same on server side? No.
Take a look at the following code: Continue reading » “MySQL error handling on server side: a NO GO!”