Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

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

And per http://stackoverflow.com/a/2032096/308851 READ UNCOMMITTED also needs row logging. And here comes http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

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 http://bugs.mysql.com/bug.php?id=52020

So what now?

share|improve this question
1  
We may be able to assist you more effectively if you will explain what you are actually trying to accomplish. You are introducing a concept with the word MERGE that is not at all clear. Examples of what you are specifically trying to do would be helpful, including what you mean when you say INSERT ... ON DUPLICATE KEY UPDATE isn't versatile enough. What can that construct not do, that you are trying to do? It sounds like that's what you're trying to do -- insert, or update if a key exists. –  Michael - sqlbot Dec 31 '12 at 23:11
    
Not clear???? MERGE is ANSI SQL standard and I am trying to emulate it, as much as I can. –  chx Jan 1 '13 at 2:20
    
I have been drinking nothing but MySQL koolaid for several years (it's so very tasty), so ... I apologize, I completely missed what you were saying. I'm still unclear, though, how INSERT ... ON DUPLICATE KEY UPDATE doesn't take you where you want to go. I make extensive use of it, and can't think of any examples where I've had to fight deadlocks related to it. –  Michael - sqlbot Jan 1 '13 at 3:35
    
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
add comment

1 Answer

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.

share|improve this answer
    
But that's exactly what we need to do -- the Drupal site owner is not always the server owner. –  chx Jan 18 at 2:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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