CODEWITHSUNDEEP
google-cloud-sqlmysql-8.0
Nitesh Mandal
Nitesh Mandal

Reputation: 36

Tables got locked when we try to import table data from mysql dump file

I am trying to import a dump file which consists of a table with its data c_emailnotificationtemplate which was generated by this command :-

mysqldump --host=10.88.129.238 --user=root --password client_1002 c_emailnotificationtemplate --single-transaction --set-gtid-purged=OFF > c_emailnotificationtemplate.sql

But when I am trying to import this c_emailnotificationtemplate.sql to my database , my database gets locked , I am not able perform any query also data is not get inserted on the table.

I tried to add --skip-lock-tables on the command but it doesn't work

so is there any way I can skip the lock operation which is happening when I am trying to import the sql file.

some details database:- client_1002 , tablename:- c_emailnotificationtemplate , db instance :- gcp cloud sql

Upvotes: 0

Views: 844

Answers (1)

Vaidehi Jamankar
Vaidehi Jamankar

Reputation: 1356

When importing your data to a Cloud SQL instance it is likely that you encounter long import times, depending on the respective file size of the data you are trying to import.
It's possible for queries to lock the MySQL database causing all subsequent queries to block/timeout.Connect to the database and try to execute this query: SHOW PROCESSLIST The first item in the list may be the one holding the lock, which the subsequent items are waiting on.Try to check if there are any issues with redundancies or data consistency and eliminate those.
Also check for the status logs to understand what is the table data or item which is causing this issue and try fixing this. SHOW ENGINE INNODB STATUS\G

Something you can do in order to avoid the locks and operation stuck issues and also decrease the amount of time it takes to complete each operation is using the Cloud SQL import or export functionality with smaller batches of data. Another way of reducing the amount of time the import operation may take would be by reducing the number of connections the database receives while you’re importing data into your instance. Check for the Best Practices for SQL Import and Export and also a helpful guide Import export document for your reference.

Upvotes: 0

Related Questions