The MySQL dump created by drush sql-sync contains LOCK commands (SQL). The standard Drupal MySQL user since Drupal 6 is not allowed to use the LOCK command though - see DRUPAL/INSTALL.mysql.txt. That's makes it impossible for the Drupal MySQL user to import the dump. Wondering if I'm the only one having this problem. May be I do miss something?

My setup is as following:

  • Remote: MySQL 5.0.51a (Debian Lenny)
  • Local: MySQL 5.1.57 (Mac OS X 10.7 + Macports)

BTW: Adding --skip-add-locks to mysqldump will avoid the LOCKS to be added to the dump.

CommentFileSizeAuthor
#9 do-not-skip-add-locks.patch789 bytesgreg.1.anderson
#2 drush-1283978-2.patch751 bytesfuerst

Comments

fuerst’s picture

fuerst commented

Note: This somewhat undescribing error will be shown by drush sql-sync when encountering the problem:
ERROR 1044 (42000) at line 40: Access denied for user 'user'@'localhost' to database 'db'

fuerst’s picture

fuerst commented
Status: Active » Needs review
StatusFileSize
new drush-1283978-2.patch751 bytes

Attached is a patch for 7.x-4.5.

The mysqldump option --opt is responsible for adding the --add-locks option. That's why it is important that --skip-add-locks comes after --opt.

osopolar’s picture

osopolar
Primary language German
Location 🇩🇪 GER 🌐
commented
Status: Needs review » Reviewed & tested by the community

Great work! Using this patch different servers.

greg.1.anderson’s picture

greg.1.anderson commented
Priority: Normal » Major

This looks right to me, but I have not set up a failing case to confirm. (I usually set the perms on my dev sites really loose.) The deceptive error message could make this annoying to debug,

greg.1.anderson’s picture

greg.1.anderson commented

All tests pass with this applied, so I guess it couldn't hurt to commit it.

drewish’s picture

drewish commented

The downside of using --skip-add-locks is that the inserts end up being slower, from the manual:

--add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 7.3.2.1, “Speed of INSERT Statements”.

Personally I'd rather people just grant the permissions is there a reason besides it's not in the default installation instructions?

drewish’s picture

drewish commented

Okay so annoyingly 5.0 already has this... can we get a --not-slow option to counter act this and --ordered-dump?

greg.1.anderson’s picture

greg.1.anderson commented
Status: Reviewed & tested by the community » Needs review

If this is annoying folks, we could back it out if Drush-5. People who wanted to put the --skip-add-locks in could help out with #518184: Allow Users To Set myqldump Arguments In drushrc.php (for drush sql dump).

greg.1.anderson’s picture

greg.1.anderson commented
Title: sql-sync and LOCKs » Do not --skip-add-locks in mysql dump; it is slow.
StatusFileSize
new do-not-skip-add-locks.patch789 bytes

Oh, the patch:

moshe weitzman’s picture

moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

Sure, rtbc. Could we add a line about why this is not in here. maybe link to this issue.

drewish’s picture

drewish commented
Status: Reviewed & tested by the community » Needs review

I'm trying to work on how to improve sql-sync's performance on large databases. I posted some related thought over on #1011008-12: Re-visit automatically setting --ordered-dump option in sql-sync.

drewish’s picture

drewish commented

I cross posted but since Moshe seemed to mention changes and RTBC I'm not going to monkey with the status further.

greg.1.anderson’s picture

greg.1.anderson commented
Status: Needs review » Fixed

Committed with comment per #10.

@drewish: If you have any further thoughts on sql dump performance, it would be great if you could open a new issue with recommendations.

drewish’s picture

drewish commented

Okay I think I was wrong on this. It looks like it makes little to no difference at least on the large dataset I'm working with. I'll open a performance issue and post some results there.

drewish’s picture

drewish commented

Here's the numbers #1380658: Optimize database dump settings for sql-sync

fuerst’s picture

fuerst commented
Title: Do not --skip-add-locks in mysql dump; it is slow. » Parameter --skip-add-locks in mysql dump
Status: Fixed » Active

Drupal 7 is using InnoDB by default which is using transactions. LOCKs are not necessary therefor (see the MySQL manual: Section 7.3.2.1, “Speed of INSERT Statements”.

To obtain faster insertions for transactional tables, you should use START TRANSACTION and COMMIT instead of LOCK TABLES

To do it right (performance wise) any MyISAM table should be surrounded by LOCKS. Although this leads to the MySQL permission problem I initally described in this issue.

For InnoDB tables one START TRANSACTION together with SET autocommit=0; at the beginning and one COMMIT at the end of the dump would be fine. I don't see any option in mysqldump though which adds this. (--single-transaction only affects dumping itself not the resulting dump file.)

BTW: If speed is your concern you may try mysqldump -T and use LOAD DATA INFILE instead of INSERT.

greg.1.anderson’s picture

greg.1.anderson commented
Category: support » feature
Status: Active » Closed (won't fix)

Closing due to lack of activity. Please re-open with more guidance (preferably a patch) to continue.

greg.1.anderson’s picture

greg.1.anderson commented
Issue summary: View changes

typo fixed

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Issue summary: View changes

For anyone looking for a solution to this...I ended up hacking around it by using:

drush @somesite sql-dump -y --tables-list=topsecretstuff | grep -v '^UNLOCK TABLES' | grep -v '^LOCK TABLES' | drush @anothersite sqlc