[MDEV-24050] Restoring the database using mysql and .sql file name results in ERROR 1556 (HY000) at line 819: You can't use locks with log tables Created: 2020-10-29  Updated: 2021-01-28  Resolved: 2020-11-07

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Vivek Singh Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

I am facing this issue when try to restore the database that was dumped using mysqldump utility.
This is the command that I am using to take dump of the database 

mysqldump --column-statistics=0 -u root --password=${root_password} -h <host> --single-transaction  --all-databases

now if I try to restore the things using the `.sql` file that is generated by above command, this is the error that I get 

ERROR 1556 (HY000) at line 819: You can't use locks with log tables

so I tried to exclude the log table that are in mysql database, while taking dump and modified the command to 

mysqldump --column-statistics=0 -u root --password=${root_password} -h <host> --single-transaction --ignore-table=mysql.slow_log --ignore-table=mysql.general_log  --all-databases

and even after this command it looks like the logs tables are there in the backup `.sql` and I get the same error while restore.

I have seen at some places that this error can be ignored. So here are some questions
1. Can/Should we ignore the error that we are getting while restore
2. Why are we not able to exclude log tables from the mysqldump command (this is more related to mysqldump than maria I think)

 Comments   
Comment by Alice Sherepa [ 2020-10-29 ]

Please specify which version do you use.

Comment by Vivek Singh [ 2020-10-30 ]

mysqldump version 

# mysqldump  --version 
mysqldump  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

maria db version, it is installed through helm 

» helm list -n justcouch 
NAME      	NAMESPACE	REVISION	UPDATED                                	STATUS  	CHART        APP VERSION
 
my-release	justcouch	1       	2020-10-30 09:57:48.440644336 +0530 IST	deployed	couchdb-3.3.43.1.0

so it looks like I am running 3.1.0

Comment by Sergei Golubchik [ 2020-11-07 ]

you're running mysqldump from MySQL and a counchdb. There's no MariaDB here as far as I can see.

Comment by Vivek Singh [ 2021-01-27 ]

Hey Sergei,
my bad, here are the updated details 

» helm install my-release bitnami/mariadb -n maria-db
 
» helm list -n maria-db 
NAME      	NAMESPACE	REVISION	UPDATED                               	STATUS  	CHART        APP VERSION
 
my-release	maria-db 	1       	2021-01-26 01:08:44.54405339 +0530 IST	deployed	mariadb-9.2.510.5.8

mysqldump version 

# mysqldump --version 
mysqldump  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

and this is the error that we are getting 

ERROR 1556 (HY000) at line 818: You can't use locks with log tables

and even if we try to ignore the log tables using `--ignore-table` those tables are not being ignored as mentioned on this thread.
MDEV-8494

Let me know if there is something else that needs to be shared.

Comment by Sergei Golubchik [ 2021-01-28 ]

That MDEV-8494 is about mysqldump from MariaDB distribution, you're using mysqldump from MySQL.
You need to report the bug, if any, at https://bugs.mysql.com

Comment by Vivek Singh [ 2021-01-28 ]

even though we use the mysqldump from maria distribution we are having same issue, we are not able to ignore the logs table using `--ignore-table` flag.

Generated at Thu Feb 08 09:27:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.