mysqldump: Got error: 1556: You can't use locks with log tables when using LOCK TABLES #244

New Issue

BirkhoffLee · 2019-11-13T07:40:59Z

BirkhoffLee commented

2019-11-13 07:40:59 +00:00

What I'm trying to do and why

Backup all MySQL databases.

Steps to reproduce (if a bug)

mysql_databases:
      # Database name (required if using this hook). Or "all" to dump all
      # databases on the host.
    - name: all

      # Database hostname to connect to. Defaults to connecting via local
      # Unix socket.
      hostname: xxx

      # Port to connect to. Defaults to 3306.
      port: 3306

      # Username with which to connect to the database. Defaults to the
      # username of the current user.
      username: root

      # Password with which to connect to the database. Omitting a password
      # will only work if MySQL is configured to trust the configured
      # username without a password.
      password: xxx

      # Additional mysqldump options to pass directly to the dump command,
      # without performing any validation on them. See
      # https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html or
      # https://mariadb.com/kb/en/library/mysqldump/ for details.
      options: --default-character-set=utf8 -N --routines --skip-triggers --skip-comments

Actual behavior (if a bug)

mysqldump: Got error: 1556: You can't use locks with log tables when using LOCK TABLES
Command 'mysqldump --add-drop-database --host main_mysql --port 3306 --protocol tcp --user root --default-character-set=utf8 -N --routines --skip-triggers --skip-comments --all-databases' returned non-zero exit status 2.

Expected behavior (if a bug)

Should dump database without an error

Other notes / implementation ideas

mysqldump version 10.17 fixed the issue. I think there's an mechanism introduced in that version to prevent this from happening. We should probably notify users of this (updating mysqldump).

Environment

borgmatic version: 1.4.9

borgmatic installation method: pip3

Borg version: 1.1.10

Python version: 3.6.8

operating system and version: Ubuntu 18.04.3 LTS

#### What I'm trying to do and why Backup all MySQL databases. #### Steps to reproduce (if a bug) ``` mysql_databases: # Database name (required if using this hook). Or "all" to dump all # databases on the host. - name: all # Database hostname to connect to. Defaults to connecting via local # Unix socket. hostname: xxx # Port to connect to. Defaults to 3306. port: 3306 # Username with which to connect to the database. Defaults to the # username of the current user. username: root # Password with which to connect to the database. Omitting a password # will only work if MySQL is configured to trust the configured # username without a password. password: xxx # Additional mysqldump options to pass directly to the dump command, # without performing any validation on them. See # https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html or # https://mariadb.com/kb/en/library/mysqldump/ for details. options: --default-character-set=utf8 -N --routines --skip-triggers --skip-comments ``` #### Actual behavior (if a bug) ``` mysqldump: Got error: 1556: You can't use locks with log tables when using LOCK TABLES Command 'mysqldump --add-drop-database --host main_mysql --port 3306 --protocol tcp --user root --default-character-set=utf8 -N --routines --skip-triggers --skip-comments --all-databases' returned non-zero exit status 2. ``` #### Expected behavior (if a bug) Should dump database without an error #### Other notes / implementation ideas mysqldump version 10.17 fixed the issue. I think there's an mechanism introduced in that version to prevent this from happening. We should probably notify users of this (updating mysqldump). #### Environment **borgmatic version:** `1.4.9` **borgmatic installation method:** `pip3` **Borg version:** `1.1.10` **Python version:** `3.6.8` **operating system and version:** `Ubuntu 18.04.3 LTS`

witten commented

2019-11-13 16:46:07 +00:00

Thank you for taking the time to file this. Feedback like this on using new features is super helpful.

Do you happen to have a link to the changelog / release notes of the fixed issue? Or a resource with recommendations on the right locking flags to use? I'm only coming up with StackOverflow posts like this one. I'd be happy to add some info or a link to the docs.. I just want to make sure I don't lead folks astray, as I'm not that familiar with MySQL myself!

Thank you for taking the time to file this. Feedback like this on using new features is super helpful. Do you happen to have a link to the changelog / release notes of the fixed issue? Or a resource with recommendations on the right locking flags to use? I'm only coming up with StackOverflow posts [like this one](https://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables). I'd be happy to add some info or a link to the docs.. I just want to make sure I don't lead folks astray, as I'm not that familiar with MySQL myself!

BirkhoffLee commented

2019-11-13 16:56:21 +00:00

I can't find changelog for mysqldump. I tested that 10.16 has the bug and 10.17 doesn't. I don't really think we should disable locking tables because I guess it will lead to an inconsistent dump.

witten commented

2019-11-13 17:08:54 +00:00

Maybe then it would be enough to point users at --single-transaction?

Maybe then it would be enough to point users at [`--single-transaction`](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_single-transaction)?

witten referenced this issue from a commit

2019-11-14 18:35:48 +00:00

Add troubleshooting documentation for MySQL lock table errors (#244).

witten commented

2019-11-14 18:56:31 +00:00

Well, I added some info about this to the docs: https://torsion.org/borgmatic/docs/how-to/backup-your-databases/#troubleshooting .. Hope that does it.

Thanks again!

Well, I added some info about this to the docs: https://torsion.org/borgmatic/docs/how-to/backup-your-databases/#troubleshooting .. Hope that does it. Thanks again!

witten closed this issue

2019-11-14 18:56:31 +00:00

Sign in to join this conversation.