Mysqldump error 2013 lost connection to mysql server during query when dumping table

A nightly mysqldump script recently started to fail on a customer’s LAMP server with the following error:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_wfHoover` at row: 458317
mysqldump: Got error: 2002: «Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)» when trying to connect
mysqldump: Got error: 2002: «Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)» when trying to connect

max allowed packet size limit

Although «Lost connection to MySQL server» sounds more like a communication problem on a network level or that MySQL was unavailable during the mysqldump process, it is (most likely) rather a «packet size» problem. A much better (and self-explaining) error would actually be something such as «got too big packet».

To retrieve large data from a table, the default «max allowed packet» size may need to be adjusted. The mysqldump (client) command sets a default value of 24 MB for the —max-allowed-packet parameter. For this table (wp_wfHoover) this was not enough.

Note: Check out this article to find out the data size of MariaDB / MySQL tables.

After appending a —max-allowed-packet parameter with a higher value to the mysqldump command, the backup script worked again, without throwing any errors:

root@db:~# mysqldump —routines —events —single-transaction —quick —max-allowed-packet=256m DBNAME

Other potential reasons causing the «Error 2013» could be timeout-related problems. See this article for additional hints.

MySQL is indeed «gone»

The above error can also show up, if during the mysqldump process the database server is indeed gone. Check your MySQL error logs (usually in /var/log/mysql/error.log) and system logs, including dmesg. When you see events with «oom», your system ran out of memory:

root@db:~# dmesg
[…]
[Thu Jan 27 23:02:33 2022] Memory cgroup out of memory: Kill process 12509 (mysqld) score 279 or sacrifice child
[Thu Jan 27 23:02:33 2022] Killed process 12509 (mysqld) total-vm:5527388kB, anon-rss:2048568kB, file-rss:0kB, shmem-rss:0kB
[Thu Jan 27 23:02:33 2022] oom_reaper: reaped process 12509 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
[Fri Jan 28 00:04:38 2022] mysqld invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

Published on January 26th 2022 — last updated on January 28th 2022 — Listed in MySQL MariaDB Database Linux Backup

A nightly mysqldump script recently started to fail on a customer’s LAMP server with the following error:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_wfHoover` at row: 458317
mysqldump: Got error: 2002: «Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)» when trying to connect
mysqldump: Got error: 2002: «Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)» when trying to connect

max allowed packet size limit

Although «Lost connection to MySQL server» sounds more like a communication problem on a network level or that MySQL was unavailable during the mysqldump process, it is (most likely) rather a «packet size» problem. A much better (and self-explaining) error would actually be something such as «got too big packet».

To retrieve large data from a table, the default «max allowed packet» size may need to be adjusted. The mysqldump (client) command sets a default value of 24 MB for the —max-allowed-packet parameter. For this table (wp_wfHoover) this was not enough.

After appending a —max-allowed-packet parameter with a higher value to the mysqldump command, the backup script worked again, without throwing any errors:

# mysqldump —routines —events —single-transaction —quick —max-allowed-packet=256m DBNAME

Other potential reasons causing the «Error 2013» could be timeout-related problems. See this article for additional hints.

MySQL is indeed «gone»

The above error can also show up, if during the mysqldump process the database server is indeed gone. Check your MySQL error logs (usually in /var/log/mysql/error.log) and system logs, including dmesg. When you see events with «oom», your system ran out of memory:

# dmesg
[. ]
[Thu Jan 27 23:02:33 2022] Memory cgroup out of memory: Kill process 12509 (mysqld) score 279 or sacrifice child
[Thu Jan 27 23:02:33 2022] Killed process 12509 (mysqld) total-vm:5527388kB, anon-rss:2048568kB, file-rss:0kB, shmem-rss:0kB
[Thu Jan 27 23:02:33 2022] oom_reaper: reaped process 12509 (mysqld), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
[Fri Jan 28 00:04:38 2022] mysqld invoked oom-killer: gfp_mask=0x6200ca(GFP_HIGHUSER_MOVABLE), nodemask=(null), order=0, oom_score_adj=0

Источник

Solving Error 2013: Lost connection to MySQL server during query when dumping table

Are you getting failed backups with an error similar to: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table .
If so there are several options you can try to resolve:

it is possible that the backup is hitting the MySQL timeout limits.

The particular variables in question here are net_read_timeout and net_write_timeout which are set to 30 and 60 seconds respectivley.
You can change these in the my.cnf config file by setting them as you desire. Here is an example. After making these changes to my.cnf you must restart MySQL

net_read_timeout = 120
net_write_timeout = 900

If you prefer to change these settings without needing to reboot MySQL you can do that with the following SQL statements:

set global net_read_timeout = 120;
set global net_write_timeout = 900;

Again, use the value for these settings that you feel is appropriate for your setup.

2) Increase max_allowed_packet setting

You might try increasing the max_allowed_packet configuration setting. You could try setting it to 256M or even up to 1G

3) You could have a corrupt table

Sometimes «Lost connection» errors are caused by a corrupt table. You could try setting innodb_force_recovery = 1 and restart mysql to see if that helps. If it does, doing the dump might fix the page cache in which you can often turn this back to 0 or remove.

We hope you find this helpful! If you need any further assistance, please contact us and we’ll be more than glad to help you out.

Источник

mysqldump Error 2013

I have a database installed, that I would like to backup in mysql. The problem is mysqldump fails on exporting the ‘maia_mail’ table

It runs for less than 30 seconds and gets error out as above.

The total size of the DB is 1.3GB with the maia_mail table being 1.0GB

In my.cnf I have these set:

Please advise or give some guidance on how to dump the database?

5 Answers 5

I could easily suggest changing InnoDB settings which might be a littel heavy-handed just to get a mysqldump to work. You may not like what I am about the suggest, but I believe it’s your best (only) option. Here it goes:

SUGGESTION #1 : Disable extended inserts

The default setting for mysqldump would include clumping together hundreds or thousands of rows in a single INSERT. This is known as an extended INSERT. It is causing some overrun beyond just max_allowed_packet.

I answered a post back on Sep 01, 2011 (MySQL server has gone away obstructing import of large dumps) where I discussed doing the same thing for importing a large mysqldump. I believe disabling extended INSERT would help with creating a troublesome mysqldump as well.

Bad news: What this does in create an INSERT command for each row. This will definitely increase the time it takes to perform the mysqldump. Consequently, it will also increase to time is take to reload (probably by a factor of 10-100.

I have discussed skip-extended-insert before

SUGGESTION #2 : Dump binary data as hex (OPTIONAL)

To make the mysqldump’s binary data more byte portable, dump such data in hexadecimal

Bad News: It will bloat the mysqldump a little more

Источник

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Страниц: 1

• Список
• » Администрирование MySQL
• » Error 2013: Lost connection to MySQL server during query при mysqldump

#1 22.12.2016 12:19:40

Error 2013: Lost connection to MySQL server during query при mysqldump

Добрые день вопрос по базе данных mariadb, перешли с myisam на innodb и теперь при бекапе выдаёт ошибку
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `b_stat_city` at row: 4468018
Пробовал бекап с ключём —single-transaction тоже не помогает всё так же ошибка и на этой же таблице, может таблица с ошибкой и не пропускает дальше?

Отредактированно Austin (22.12.2016 12:20:03)

#2 22.12.2016 12:22:04

Re: Error 2013: Lost connection to MySQL server during query при mysqldump

#3 22.12.2016 12:41:27

Re: Error 2013: Lost connection to MySQL server during query при mysqldump

MariaDB [paci]> check table b_stat_city;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Вот ошибку выдало, база большая достаточно эта таблица (b_stat_city) 840 мб сайт на битриксе раньше когда тип движка был myisam всё нормально было сейчас вот проблемы начались.

#4 22.12.2016 13:27:15

Re: Error 2013: Lost connection to MySQL server during query при mysqldump

А чему равны переменные
wait_timeout
и
connect_timeout
?

Попробуйте увеличить их значения.

#5 22.12.2016 14:54:58

Re: Error 2013: Lost connection to MySQL server during query при mysqldump

wait_timeout = 150 стоит
connect_timeout = 10 хотя пытался увеличить до 30 не получилось почему то, я не спец в этом просто решил в my.cnf дописать эту переменную
[mysqld]
connect_timeout=100
А в консоли mysql gkclt команды MariaDB [paci]> SHOW VARIABLES LIKE «%connect_timeout%» мне выдало значение 10

Отредактированно Austin (23.12.2016 01:41:10)

#6 22.12.2016 15:09:22

Re: Error 2013: Lost connection to MySQL server during query при mysqldump

вот лог
161223 01:18:57 mysqld_safe Number of processes running now: 0
161223 01:18:57 mysqld_safe mysqld restarted
161223 1:18:57 [Warning] ‘THREAD_CONCURRENCY’ is deprecated and will be removed in a future release.
161223 1:18:57 [Note] /usr/libexec/mysqld (mysqld 5.5.44-MariaDB) starting as process 29544 .
161223 1:18:57 InnoDB: The InnoDB memory heap is disabled
161223 1:18:57 InnoDB: Mutexes and rw_locks use GCC atomic builtins
161223 1:18:57 InnoDB: Compressed tables use zlib 1.2.7
161223 1:18:57 InnoDB: Using Linux native AIO
161223 1:18:57 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
161223 1:18:57 InnoDB: Initializing buffer pool, size = 600.0M
161223 1:18:57 InnoDB: Completed initialization of buffer pool
161223 1:18:57 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 16283368995
161223 1:18:57 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files.
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer.
InnoDB: Doing recovery: scanned up to log sequence number 16283371054
161223 1:19:02 InnoDB: Starting an apply batch of log records to the database.
InnoDB: Progress in percents: 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
161223 1:19:02 InnoDB: Waiting for the background threads to start
161223 1:19:03 Percona XtraDB (http://www.percona.com) 5.5.43-MariaDB-37.2 started; log sequence number 16283371054
161223 1:19:03 [Note] Plugin ‘FEEDBACK’ is disabled.
161223 1:19:03 [Note] Server socket created on IP: ‘0.0.0.0’.
161223 1:19:03 [Note] Event Scheduler: Loaded 0 events
161223 1:19:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.5.44-MariaDB’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MariaDB Server

Отредактированно Austin (23.12.2016 01:41:36)

Источник

If you spend time running lots of MySQL queries, you might come across the Error Code: 2013. Lost connection to MySQL server during query . This article offers some suggestions on how to avoid or fix the problem.

Why this happens

This error appears when the connection between your MySQL client and database server times out. Essentially, it took too long for the query to return data so the connection gets dropped.

Most of my work involves content migrations. These projects usually involve running complex MySQL queries that take a long time to complete. I’ve found the WordPress wp_postmeta table especially troublesome because a site with tens of thousands of posts can easily have several hundred thousand postmeta entries. Joins of large datasets from these types of tables can be especially intensive.

Avoid the problem by refining your queries

In many cases, you can avoid the problem entirely by refining your SQL queries. For example, instead of joining all the contents of two very large tables, try filtering out the records you don’t need. Where possible, try reducing the number of joins in a single query. This should have the added benefit of making your query easier to read. For my purposes, I’ve found that denormalizing content into working tables can improve the read performance. This avoids time-outs.

Re-writing the queries isn’t always option so you can try the following server-side and client-side workarounds.

Server-side solution

If you’re an administrator for your MySQL server, try changing some values. The MySQL documentation suggests increasing the net_read_timeout or connect_timeout values on the server.

Client-side solution

You can increase your MySQL client’s timeout values if you don’t have administrator access to the MySQL server.

MySQL Workbench

You can edit the SQL Editor preferences in MySQL Workbench:

1. In the application menu, select Edit > Preferences > SQL Editor.
2. Look for the MySQL Session section and increase the DBMS connection read time out value.
3. Save the settings, quite MySQL Workbench and reopen the connection.

Navicat

How to edit Navicat preferences:

1. Control-click on a connection item and select Connection Properties > Edit Connection.
2. Select the Advanced tab and increase the Socket Timeout value.

Command line

On the command line, use the connect_timeout variable.

Python script

If you’re running a query from a Python script, use the connection argument:
con.query(‘SET GLOBAL connect_timeout=6000’)

Drupal to WordPress migration consulting

Any Drupal version · All content · Custom content types · SEO · Plugins

Migrating a site from Drupal to WordPress and need a specialist? Please contact me for a quotation. Whether you’re a media agency who needs a database expert or a site owner looking for advice, I’ll save you time and ensure accurate content exports.

Источник