Mysqldump got error 1556

ISSUE TYPE

• Bug Report

Checklist

• .env file is attached — env.txt
• docker-compose.override.yml is attached (if it exists) — N/A
• Custom configs from cfg/ dir are attached (if customized) — N/A
• docker-compose logs and log/ output is added — No applicable logs
• I’ve looked through the docs: https://devilbox.readthedocs.io/en/latest/ — Yep
• I’ve looked through existing issues: https://github.com/cytopia/devilbox/issues
• I’ve read troubleshooting: https://devilbox.readthedocs.io/en/latest/support/troubleshooting.html — Well….

OS / ENVIRONMENT

1. Host: MacOS 10.15.2
2. N/A, but native.
3. Docker version: 2.1.0.5
4. Docker Compose version: 1.24.1
5. (Linux) Is SELinux enabled?: N/A
6. What git commit hash are you on?: ace5d7c1fc13b28d623b08ac30b44ec3d35aa713

SUMMARY

mysqldump-secure fails on mysql table.

STEPS TO REPRODUCE

1. shell.sh
2. Run mysqldump-secure with no arguments (or -vv to trace)
3. My 7/9 databases export without issue.
   3A. Table 8/9 is mysql. Error thrown. See «Actual Behavior» below.
4. Attempts to continue. Table 9/9 is performance_schema, ignored as expected.

EXPECTED BEHAVIOUR

Export of all tables, each compressed, as stated in the documentation. Or log on error. There are no mysql logs in the logs/ directory.

ACTUAL BEHAVIOUR

Error thrown:

$ mysqldump-secure -vv
... Successful database dumps omitted ...
[TRACE] (RUN): 8/9 Number of total tables:       31
[TRACE] (RUN): 8/9 Number of InnoDB tables:      4
[TRACE] (RUN): 8/9 Number of non-InnoDB tables:  26
[TRACE] (RUN): 8/9 Applying consistency setting: --lock-tables
[INFO]  (SQL): 8/9 Dumping:  mysql (11.68 MB)  (compressed) (--lock-tables) (--skip-quick)  Failed
[FATAL] (RUN): 8/9 Error dumping mysql
[FATAL] mysqldump: mysqldump  Got error  1556  "You can't use locks with log tables" when using LOCK TABLES
[TRACE] (CFG): 9/9 Ignoring DB: "performance_schema" by $IGNORE pattern: "performance_schema"
[INFO]  (SQL): 9/9 Skipping: performance_schema (DB is ignored)
[ERR]   (RUN): Required database: "mysql" has not been dumped.
[TRACE] (RUN): Deleting tmp dir:  /tmp/mysqldump-secure.Sfsmzen2I9
[DEBUG] (RUN): Dumping finished (OK: 6 dbs, IGN: 2 dbs, ERR: 1, TOTAL: 9)
[DEBUG] (RUN): Took 10 seconds
[DEBUG] (RUN): Total size dumped: 94.82 MB
[FATAL] Finished with 2 Failures. 1 failed backups (1 required dbs missing)
$

OTHER INFORMATION

Yes, I should update the devilbox commit, and I need to update Docker as well. This bug came about during the first stage of that process … backing up my data.

Start command

File and user permissions (Linux & MacOS)

No issues here

File permissions of the Devilbox directory

No issues here

$ ls -la
drwxr-xr-x@ 32 username   1.0K Dec 12 17:12 ./

File permissions of your projects

No issues here

../projects $ ll
drwxr-xr-x@ 10 username   320B Jan 12 11:56 ./        # From Host

/shared/http $ ll
drwxr-xr-x 10 devilbox devilbox   320 Jan 12 18:56 ./ . # From shell

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

Last Update:2017-12-27
Source: Internet

Mysqldump:got error:1556:you can ‘ t use the locks with log tables.

Original: http://blog.51cto.com/oldboy/1122867

In the old boy took the student master from the synchronous practice, found that the student practice operation encountered the following problems can not be solved, so the old boy to solve the process summarized as follows:

[[email protected] ~]# mysqldump-uroot-p ‘ Oldboy ‘-s/data/3306/mysql.sock-a-B >a.sql

Mysqldump:got error:1556:you can ‘ t use the locks with log tables. When using LOCK TABLES

Resolution process:

The same operation, the other 10 students are OK, only the student has a problem, after the inquiry, and review the relevant configuration process, contact to the previous period also have students have similar problems, so, found the cause.

[email protected] ~]# which mysqldump

/usr/bin/mysqldump

Find the reason, MySQL installation path for/application/mysql, view mysqldump path should be/application/mysql/bin only, but now the path is RPM package installed MySQL command path, this reason found.

[Email protected] ~]# tail-1/etc/profile

Export path= $PATH:/application/mysql/bin

In the/etc/profile file, the MySQL command path, the student put the end, so when the execution of the mysqldump command, the first to find the RPM package comes with the/usr/bin/mysqldump command, resulting in an error.

Let’s put the MySQL command path to the front of the PATH variable:

[Email protected] ~]# tail-1/etc/profile

Export Path=/application/mysql/bin: $PATH

[Email protected] ~]#. /etc/profile

[Email protected] ~]# echo $PATH

/application/mysql/bin: /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/ Local/mysql/bin:/root/bin:/usr/local/mysql/bin:/application/mysql5.1.65/bin:/application/apache/bin

[email protected] ~]# which mysqldump

/application/mysql/bin/mysqldump

At this point in the export database:

[[Email protected] ~] #mysqldump-uroot-p ‘ Oldboy ‘-s/data/3306/mysql.sock-a-B >a.sql

Below the classmate search, other users of the same error in another article:

mysqldump You can ‘ t use locks with log tables 11-01-13 11:05:02

Http://blog.chinaunix.net/uid-21757535-id-86425.html

Category: mysql/postgresql

When I was doing a database backup recently, I encountered an error like this:

Mysqldump:got error:1556:you can ‘ t use the locks with log tables. When using LOCK TABLES

I put some mysqldump statements in a batch command file (the legendary. sh file) executed, and when I put these
Mysqldump statement separate from one execution, I found that there was no error, so I looked up some information on the Web:
Discovery is the logs table in the MySQL default database and cannot be caused by locking (lock tables).
So I tested and emptied the statement about MySQL, the default database, and the entire file worked.
There is also a workaround:
Is in MySQL this database related to the sentence mysqldump plus—lock-tables=0 This parameter, do not lock the table backup, is also feasible.

(EXT) Mysqldump:got error:1556:you can ‘ t use the locks with log tables.