# Mysqldump: Got error: 1160: Got an error writing communication packets when using LOCK TABLES **URL:** \ **Category:** Percona Server for MySQL 8.0\ **Created:** [April 30, 2024, 12:39pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971 "2024-04-30T12:39:39Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [April 30, 2024, 12:39pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/1 "2024-04-30T12:39:39Z")
We get this error almost every second day on our nightly DB dump cron job. Often for two days in a row. This is on Debian 10.13, all recent updates installed. It started around February when using 8.0.34-26. It’s still there with 8.0.36-28. mysqldump is stared locally with the --opt --routines -u -p --hex-blob parameters mysqldump setting are: ```auto [mysqldump] quick quote-names max_allowed_packet = 16M ``` The error arises at the very start of the dump. The resulting file always contains these lines only: ```auto -- MySQL dump 10.13 Distrib 8.0.36-28, for Linux (x86_64) -- -- Host: localhost Database: XXXXXXX -- ------------------------------------------------------ -- Server version 8.0.36-28 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */; /*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=\'rocksdb_bulk_load\'', 'SELECT 0') */; /*!50717 PREPARE s FROM @rocksdb_get_is_supported */; /*!50717 EXECUTE s */; /*!50717 DEALLOCATE PREPARE s */; /*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */; /*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */; /*!50717 EXECUTE s */; /*!50717 DEALLOCATE PREPARE s */; ``` IMHO this can’t be a network issue because mysqldump connects locally i.e. using the file socket. Cheers ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 1, 2024, 4:20am UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/2 "2024-05-01T04:20:03Z")
Since you are on 8.0, I’m guessing all of your tables are InnoDB. If that is true, you should be using `--single-transaction` to execute mysqldump. Table locks are unnecessary. Another, better, option, is to switch to [mydumper](https://github.com/mydumper/mydumper) which is faster, multi-threaded, logical dump solution. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 1, 2024, 12:49pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/3 "2024-05-01T12:49:48Z")
Hi @matthewb thanks for your reply. Yes, all tables are InnoDB. I will give _–single-transaction_ a try. But I wonder why it worked before for years… ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 8, 2024, 2:06pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/4 "2024-05-08T14:06:42Z")
@matthewb: After adding _–single-transaction_ it worked for six days. But last night we again got a similar error: `mysqldump: Couldn't execute 'show create table `sys\_collection`': Got an error writing communication packets (1160)` So this is not solved, unfortunately… ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 8, 2024, 2:17pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/5 "2024-05-08T14:17:09Z")
@bitone That’s a temporary error. Simply try again. In your script you should be checking the exit status of the mysqldump command. If the exit code is not 0, then your script should simple try again. Also, I would edit your script to ignore the `sys` schema. you don’t need to dump that in your backup. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 8, 2024, 2:24pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/6 "2024-05-08T14:24:48Z")
@matthewb But this is only a workaround. If we have to check after each DB operation if it was successful then the DB is not reliable enough for produciton. And the script was working before. So something has changed that affects the reliability. IMHO this is a bug. edited: > [@matthewb](#): > > Also, I would edit your script to ignore the `sys` schema. _sys\_collection_ is a table of the CMS. It’s not related to DB sys. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 8, 2024, 3:30pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/7 "2024-05-08T15:30:28Z")
I just had this error four times in a row: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/5/5531a0d2febad43678ebc4f75e0131351611197b.png) What exactly does the error mean? Maybe it’s misleading? Does this happen when a table is locked by another process and therefore the DB connection times out? As I mentioned in my first post, _mysqldump_ is running locally, so we can rule out network issues. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 8, 2024, 5:16pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/8 "2024-05-08T17:16:51Z")
> [@bitone](#): > > If we have to check after each DB operation if it was successful then the DB is not reliable enough for produciton. That’s not correct. Every application should always check the result code of any database operation. If you don’t check, then you don’t really know if the commit succeeded or not. That’s a design flaw of your code, not a flaw of the database. The database will always tell you if something succeeded or not; it’s your responsibility to act on that response. If the database had a temporary error, it’s not the job of the DB to try again, it’s your code’s job to act accordingly. > [@bitone](#): > > What exactly does the error mean? It means there was some communication error between mysqldump and your local mysql server. What does the mysql error log say? Have you tried running those commands directly yourself? ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 8, 2024, 8:00pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/9 "2024-05-08T20:00:40Z")
> [@matthewb](#): > > That’s not correct. Every application should always check the result code of any database operation. No offense, but this must be a joke. I’m working with different kinds of DBs for over 25 years. We’re not talking about issues on SQL statement level here. Yes we also check for low level errors. To correct them. But when a tested statement on an existing DB regularly returns some communication error, even when we’re connecting through a local file socket, there’s something wrong with the DB engine. If we need to execute instructions more than once for the server to do it, then that server is not reliable enough for production. > [@matthewb](#): > > It means there was some communication error between mysqldump and your local mysql server. What does the mysql error log say? Have you tried running those commands directly yourself? There’s nothing in the log that’s related to this. Yes I tried those commands; see the screenshot in my last reply. This error appeared four times in a row. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 8, 2024, 9:33pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/10 "2024-05-08T21:33:40Z")
> [@bitone](#): > > We’re not talking about issues on SQL statement level here. My mistake. Above you said “check after each DB operation if it was successful” and I took “DB operation” to mean literally any operation which includes things like INSERT, COMMIT, etc and also extends to tools that operate against the database. > [@bitone](#): > > Yes I tried those commands; see the screenshot in my last reply. The screenshot shows using mysqldump. Have you tried those failing SQL manually? Also, please add `--verbose --force` to your command so we can see where exactly the dump is failing. Have you also tried without `--routines` and without `--hex-blob` purely for testing/checking? ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 9, 2024, 10:44pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/11 "2024-05-09T22:44:53Z")
Hi @matthewb Thanks for you understanding, I’m glad you didn’t take my last message the wrong way. Today it was like when you go to the doctor and as soon you’re there the pain has gone… I wrote a script that does the dump every few seconds. And I tried to force it a bit by writing to and reading from a table in the same DB at the same time. But the error showed up only once in the first 5 minutes and then never again. As you suggested, I used the --verbose --force arguments when running it: ```auto -- Retrieving table structure for table tx_eqbericht_domain_model_module2021... -- Sending SELECT query... -- Retrieving rows... -- Rolling back to savepoint sp... -- Retrieving table structure for table tx_eqbericht_domain_model_module2022... mysqldump: Couldn't execute 'show create table `tx_eqbericht_domain_model_module2022`': Got an error writing communication packets (1160) -- Skipping dump data for table 'tx_eqbericht_domain_model_module2022', it has no fields -- Rolling back to savepoint sp... -- Retrieving table structure for table tx_eqbericht_domain_model_navigation... -- Sending SELECT query... -- Retrieving rows... -- Rolling back to savepoint sp... ``` The error occurs at line 486 of 1075. I run the statement` show create tablle ...` a few times without any issues. When the dump script is running in the late evening there’s hardly anything going on on the server. The whole dump takes only about 5 seconds. This is on a physical four core Xeon 3.3GHz server with 16GB RAM and hardware RAID-1. The DB in question has 166 tables and takes 543MB on disk. Therefore I think that we can rule out resource issues. I rather get the suspicion that this error potentially occurs when the DB is in some “calm” state. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 11, 2024, 3:37am UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/12 "2024-05-11T03:37:08Z")
Are you running other DDLs or locking the table in some way? I know you are not looking to make major changes, and “it’s worked before”, but could you give [mydumper](https://github.com/mydumper/mydumper) a try? I’m curious to see if the issue occurs here. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 13, 2024, 4:31pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/13 "2024-05-13T16:31:16Z")
> [@matthewb](#): > > Are you running other DDLs or locking the table in some way? No DDL, but the CMS (TYPO3) seems to use row locking at some points. And transactions on inserts, so there’s more locking… But then we should rather run into a lock timeout than a read error…? > [@matthewb](#): > > I know you are not looking to make major changes, and “it’s worked before”, but could you give [mydumper](https://github.com/mydumper/mydumper) a try? I’m curious to see if the issue occurs here. I guess I have to give this a try. What I don’t like about it is that we have to keep track of one more repository on a production critical server. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 13, 2024, 5:50pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/14 "2024-05-13T17:50:00Z")
Simple transactions should not be causing such an issue, but if you’re doing explicit LOCKs then that might be the problem. There’s really no such reason at all to use LOCKs _because_ transactions exist and are ACID. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 15, 2024, 5:41pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/15 "2024-05-15T17:41:38Z")
TYPO3 uses Doctrine as DB abstraction layer. I grepped the code but couldn’t find any explicit locking. Therefore the record locks I saw must be from update or delete statements. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 15, 2024, 5:48pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/16 "2024-05-15T17:48:35Z")
I just logged into the CMS and got this: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/d/e/de4ebada9539fffdf7624317925b5205ada643df.png) It looks like this is a general issue now. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 15, 2024, 7:56pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/17 "2024-05-15T19:56:22Z")
Verify you’re actually connecting to MySQL via socket. Are you explicitly providing the socket in your connection string? A connection to 127.0.0.1 still uses networking. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 16, 2024, 6:42am UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/18 "2024-05-16T06:42:26Z")
mysqldump connects through the file socket. The CMS uses indeed a network connection via 127.0.1 i.e. the loop device. If we could not rely on the loop device, a lot of other issues would occur on Linux… ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 16, 2024, 2:14pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/19 "2024-05-16T14:14:03Z")
Have you looked over these [MySQL "Got an error reading communication packet"](https://www.percona.com/blog/mysql-got-an-error-reading-communication-packet-errors/) Are you tracking Aborted\_connections stats? Do you see 1:1 increase in aborted cons when you get these errors? Note that the error says “writing communication packets” which means the client application (ie: your code) had an issue talking to mysql. ---
**Author:** ![bitone](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bitone](https://forums.percona.com/u/bitone)\ **Post date:** [May 16, 2024, 11:40pm UTC](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971/20 "2024-05-16T23:40:04Z")
We actually monitor them (Zabbix), but in a rather useless way for this purpose: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/0/6/0651bd9e400694d8d9ace61927840da4f2b7fc82.png) What we have here is the relationship between open and aborted connections. Therefore I can’t say what is causing a spike in the graph. If the ratio increases, it could mean we have fewer open connections or more aborted connections. I have to investigate a bit more. But either way I couldn’t find any correlation with the one error message in the CMS. [Next page](https://forums.percona.com/t/mysqldump-got-error-1160-got-an-error-writing-communication-packets-when-using-lock-tables/29971.md?page=2)