7

I am trying to implement some parts of MERGE in the MySQL driver in Drupal. Of course, Drupal has something but in truth it only works because the most frequent MERGE issuer just eats exceptions.

So, whatever we try, deadlocks occur. What we do, we start a transaction, then SELECT ... FOR UPDATE, try an INSERT and if it causes an 23xxx integrity error try an UPDATE instead. Deadlocks. We removed the FOR UPDATE cos we decided that for our use, it's OK. Still deadlocks.

I can't just switch isolation levels because READ COMMITTED needs row logging per SET TRANSACTION:

As of MySQL 5.1, if you use READ COMMITTED [...] you must use row-based binary logging.

And per READ UNCOMMITTED also needs row logging. And here comes Binary Log Setting:

To change the global binlog_format value, you must have the SUPER privilege. This is also true for the session value as of MySQL 5.1.29.

I can't require every Drupal setup to have SUPER nor we can say that Drupal is incompatible with statement based binlogs when that's the default and the most widespread.

INSERT ... ON DUPLICATE KEY is neither versatile enough nor is it deadlock free.

So what now?

  • See api.drupal.org/api/drupal/… for our Merge API/Implementation. It does not require that you actually have a unique key and you can specify different behaviors for update/insert. – Berdir Jan 1 '13 at 8:54
  • 4
    MERGE does not free you from deadlocks, (experience from DBMS that have implemented it.) And I can't see what is wrong with dealing with deadlocks. Whenever you have transactions, you have to take into consideration that deadlocks may appear. – ypercubeᵀᴹ May 21 '14 at 22:02
  • 1
    There must be some circular updates for a deadlock condition to emerge. The type of query wouldn't cause that, rather the specific updates being performed. – Andrew Brennan Jul 27 '15 at 15:42
1

If possible, write a Stored Procedure to encapsulate the entire task. And deal with exceptions. Then have Drupal call the SP.

0

Regarding the statement below:

To change the global binlog_format value, you must have the SUPER privilege. This is also true for the session value as of MySQL 5.1.29.

You do not need to give SUPER privilege to Drupal since the above statement only applies if you want to change the binary log format at runtime.

Here is what you need to do:

You can select the binary logging format explicitly by starting the MySQL server with --binlog-format=type.

You can read more about Setting The Binary Log Format in the MySQL manual.

  • But that's exactly what we need to do -- the Drupal site owner is not always the server owner. – chx Jan 18 '14 at 2:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.