Month: July 2012

MySQL error handling on server side: a NO GO!

MySQL

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!”

MySQL joins: ON vs. USING vs. Theta-style

MySQL

What is the difference between the following three syntaxes?

SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id)
SELECT * FROM film JOIN film_actor USING (film_id)
SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id

The difference is mostly syntactic sugar, but with a couple interesting notes.

To put names, the first two are called “ANSI-style” while the third is called “Theta-style”.

Theta style

On the FROM clause, tables are listed as if with Cartesian products, and the WHERE clause specifies how the join should take place.

This is considered to be the “old” style. It is somewhat confusing to read. Consider the following query: Continue reading » “MySQL joins: ON vs. USING vs. Theta-style”

Notes on row based replication

MySQL

MySQL’s Row Based Replication (RBR) succeeds (though not replaces) Statement Based Replication (SBR), as of version 5.1.

Anyone who is familiar with replication data drift — the unexplained growing data difference between master & slave — might wish to look into row based replication. On multiple servers I’m handling the change to RBR has eliminated (to the best of my findings) replication data drift.

This is easily explained, as RBR writes particular row IDs into the binary log. You no longer need to hope the statement

DELETE FROM my_table ORDER BY my_column LIMIT 100

acts deterministically on all servers (is my_column UNIQUE?). With row based replication the particular rows deleted on the master are then deleted on the slave.

Here are three notes on RBR: Continue reading » “Notes on row based replication”