Ошибка lost connection to mysql server during query

I got the Error Code: 2013. Lost connection to MySQL server during query error when I tried to add an index to a table using MySQL Workbench.
I noticed also that it appears whenever I run long query.

Is there away to increase the timeout value?

asked May 12, 2012 at 12:14

user836026user836026

10.2k13 gold badges72 silver badges124 bronze badges

New versions of MySQL WorkBench have an option to change specific timeouts.

For me it was under Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600

Changed the value to 6000.

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.

Marko

20.2k13 gold badges48 silver badges64 bronze badges

answered Oct 8, 2012 at 22:49

If your query has blob data, this issue can be fixed by applying a my.ini change as proposed in this answer:

[mysqld]
max_allowed_packet=16M

By default, this will be 1M (the allowed maximum value is 1024M). If the supplied value is not a multiple of 1024K, it will automatically be rounded to the nearest multiple of 1024K.

While the referenced thread is about the MySQL error 2006, setting the max_allowed_packet from 1M to 16M did fix the 2013 error that showed up for me when running a long query.

For WAMP users: you’ll find the flag in the [wampmysqld] section.

answered Jul 3, 2014 at 13:36

Start the DB server with the comandline option net_read_timeout / wait_timeout and a suitable value (in seconds) — for example: --net_read_timeout=100.

For reference see here and here.

answered May 12, 2012 at 12:17

YahiaYahia

69k9 gold badges113 silver badges143 bronze badges

SET @@local.net_read_timeout=360;

Warning: The following will not work when you are applying it in remote connection:

SET @@global.net_read_timeout=360;

Edit: 360 is the number of seconds

Vince V.

3,1192 gold badges29 silver badges45 bronze badges

answered Apr 20, 2015 at 3:58

user1313024user1313024

2594 silver badges3 bronze badges

Add the following into /etc/mysql/cnf file:

innodb_buffer_pool_size = 64M

example:

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
innodb_buffer_pool_size = 64M

Sam

1,1851 gold badge23 silver badges39 bronze badges

answered Apr 17, 2015 at 15:11

MysqlManMysqlMan

2172 silver badges2 bronze badges

In my case, setting the connection timeout interval to 6000 or something higher didn’t work.

I just did what the workbench says I can do.

The maximum amount of time the query can take to return data from the DBMS.Set 0 to skip the read timeout.

On Mac
Preferences -> SQL Editor -> Go to MySQL Session -> set connection read timeout interval to 0.

And it works 😄

answered Nov 26, 2019 at 3:55

Thet HtunThet Htun

4414 silver badges13 bronze badges

There are three likely causes for this error message

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently
Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries.
More rarely, it can happen when the client is attempting the initial connection to the server

For more detail read >>

Cause 2 :

SET GLOBAL interactive_timeout=60;

from its default of 30 seconds to 60 seconds or longer

Cause 3 :

SET GLOBAL connect_timeout=60;

answered Dec 8, 2016 at 6:30

Nanhe KumarNanhe Kumar

15.1k5 gold badges78 silver badges70 bronze badges

You should set the ‘interactive_timeout’ and ‘wait_timeout’ properties in the mysql config file to the values you need.

answered May 12, 2012 at 12:19

Maksym PolshchaMaksym Polshcha

17.8k8 gold badges50 silver badges77 bronze badges

Just perform a MySQL upgrade that will re-build innoDB engine along with rebuilding of many tables required for proper functioning of MySQL such as performance_schema, information_schema, etc.

Issue the below command from your shell:

sudo mysql_upgrade -u root -p

Jamal

7587 gold badges22 silver badges31 bronze badges

answered May 19, 2014 at 20:16

Shoaib KhanShoaib Khan

89114 silver badges25 bronze badges

If you experience this problem during the restore of a big dump-file and can rule out the problem that it has anything to do with network (e.g. execution on localhost) than my solution could be helpful.

My mysqldump held at least one INSERT that was too big for mysql to compute. You can view this variable by typing show variables like "net_buffer_length"; inside your mysql-cli.
You have three possibilities:

increase net_buffer_length inside mysql -> this would need a server restart
create dump with --skip-extended-insert, per insert one line is used -> although these dumps are much nicer to read this is not suitable for big dumps > 1GB because it tends to be very slow
create dump with extended inserts (which is the default) but limit the net-buffer_length e.g. with --net-buffer_length NR_OF_BYTES where NR_OF_BYTES is smaller than the server’s net_buffer_length -> I think this is the best solution, although slower no server restart is needed.

I used following mysqldump command:
mysqldump --skip-comments --set-charset --default-character-set=utf8 --single-transaction --net-buffer_length 4096 DBX > dumpfile

answered Jan 8, 2016 at 11:07

Matt VMatt V

931 silver badge5 bronze badges

On the basis of what I have understood this error was caused due to read timeout and max allowed packet default is 4M. if your query file more than 4Mb then you get an error. this worked for me

change the read timeout. For changing go to Workbench Edit → Preferences → SQL Editor

2. change the max_allowed_packet manually by editing the file my.ini. for editing go to "C:ProgramDataMySQLMySQL Server 8.0my.ini". The folder ProgramData folder is hidden so if you did not see then select show hidden file in view settings. set the max_allowed_packet = 16M in my.ini file.
3. Restart MySQL. for restarting go to win+ R -> services.msc and restart MySQL.

answered Mar 24, 2022 at 6:15

AvinashAvinash

1412 silver badges4 bronze badges

I know its old but on mac

1. Control-click your connection and choose Connection Properties.
2. Under Advanced tab, set the Socket Timeout (sec) to a larger value.

answered Mar 27, 2015 at 6:53

Aamir MahmoodAamir Mahmood

2,6843 gold badges27 silver badges47 bronze badges

Sometimes your SQL-Server gets into deadlocks, I’ve been in to this problem like 100 times. You can either restart your computer/laptop to restart server (easy way) OR you can go to task-manager>services>YOUR-SERVER-NAME(for me , it was MySQL785 something like this). And right-click > restart.
Try executing query again.

answered Feb 10, 2021 at 13:28

Try please to uncheck limit rows in in Edit → Preferences →SQL Queries

because You should set the ‘interactive_timeout’ and ‘wait_timeout’ properties in the mysql config file to the values you need.

answered Jul 24, 2014 at 9:59

user2586714user2586714

1491 gold badge1 silver badge7 bronze badges

Change «read time out» time in Edit->Preferences->SQL editor->MySQL session

answered Apr 21, 2016 at 9:25

I got the same issue when loading a .csv file.
Converted the file to .sql.

Using below command I manage to work around this issue.

mysql -u <user> -p -D <DB name> < file.sql

Hope this would help.

answered Sep 8, 2016 at 6:19

VinRockaVinRocka

3074 silver badges15 bronze badges

Go to Workbench Edit → Preferences → SQL Editor → DBMS connections read time out : Up to 3000.
The error no longer occurred.

answered Sep 1, 2018 at 2:50

I faced this same issue. I believe it happens when you have foreign keys to larger tables (which takes time).

I tried to run the create table statement again without the foreign key declarations and found it worked.

Then after creating the table, I added the foreign key constrains using ALTER TABLE query.

Hope this will help someone.

answered Dec 23, 2016 at 7:22

Nimeshka SrimalNimeshka Srimal

7,5845 gold badges44 silver badges56 bronze badges

This happened to me because my innodb_buffer_pool_size was set to be larger than the RAM size available on the server. Things were getting interrupted because of this and it issues this error. The fix is to update my.cnf with the correct setting for innodb_buffer_pool_size.

answered Feb 26, 2017 at 15:35

Go to:

Edit -> Preferences -> SQL Editor

In there you can see three fields in the «MySQL Session» group, where you can now set the new connection intervals (in seconds).

answered May 5, 2017 at 13:23

Turns out our firewall rule was blocking my connection to MYSQL. After the firewall policy is lifted to allow the connection i was able to import the schema successfully.

answered May 11, 2017 at 15:38

I had the same problem — but for me the solution was a DB user with too strict permissions.
I had to allow the Execute ability on the mysql table. After allowing that I had no dropping connections anymore

answered Aug 31, 2017 at 17:35

naabsternaabster

1,47612 silver badges14 bronze badges

Check if the indexes are in place first.

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = '<schema>'

answered Sep 22, 2017 at 3:58

Gayan DasanayakeGayan Dasanayake

1,8932 gold badges17 silver badges21 bronze badges

I ran into this while running a stored proc- which was creating lots of rows into a table in the database.
I could see the error come right after the time crossed the 30 sec boundary.

I tried all the suggestions in the other answers. I am sure some of it helped , however- what really made it work for me was switching to SequelPro from Workbench.

I am guessing it was some client side connection that I could not spot in Workbench.
Maybe this will help someone else as well ?

answered Dec 19, 2017 at 21:19

RN.RN.

9974 gold badges14 silver badges30 bronze badges

If you are using SQL Work Bench, you can try using Indexing, by adding an index to your tables, to add an index, click on the wrench(spanner) symbol on the table, it should open up the setup for the table, below, click on the index view, type an index name and set the type to index, In the index columns, select the primary column in your table.

Do the same step for other primary keys on other tables.

answered Jun 25, 2018 at 8:21

Matthew EMatthew E

5855 silver badges6 bronze badges

There seems to be an answer missing here for those using SSH to connect to their MySQL database. You need to check two places not 1 as suggested by other answers:

Workbench Edit → Preferences → SQL Editor → DBMS

Workbench Edit → Preferences → SSH → Timeouts

My default SSH Timeouts were set very low and causing some (but apparently not all) of my timeout issues. After, don’t forget to restart MySQL Workbench!

Last, it may be worth contacting your DB Admin and asking them to increase wait_timeout & interactive_timeout properties in mysql itself via my.conf + mysql restart or doing a global set if restarting mysql is not an option.

Hope this helps!

answered May 6, 2019 at 17:36

Three things to be followed and make sure:

Whether multiple queries show lost connection?
how you use set query in MySQL?
how delete + update query simultaneously?

Answers:

Always try to remove definer as MySQL creates its own definer and if multiple tables involved for updation try to make a single query as sometimes multiple query shows lost connection
Always SET value at the top but after DELETE if its condition doesn’t involve SET value.
Use DELETE FIRST THEN UPDATE IF BOTH OF THEM OPERATIONS ARE PERFORMED ON DIFFERENT TABLES

RalfFriedl

1,1263 gold badges10 silver badges12 bronze badges

answered Sep 22, 2019 at 16:10

I had this error message due to a problem after of upgrade Mysql. The error appeared immediately after I tried to do any query

Check mysql error log files in path /var/log/mysql (linux)

In my case reassigning Mysql owner to the Mysql system folder worked for me

chown -R mysql:mysql /var/lib/mysql

answered Jan 23, 2021 at 19:29

franciscorodefranciscorode

5451 gold badge7 silver badges15 bronze badges

Establish connection first
mysql --host=host.com --port=3306 -u username -p
then select your db use dbname
then source dumb source C:dumpfile.sql.
After it’s done q

answered Oct 29, 2021 at 5:32

Источник

When you run MySQL queries, sometimes you may encounter an error saying you lost connection to the MySQL server as follows:

Error Code: 2013. Lost connection to MySQL server during query

The error above commonly happens when you run a long or complex MySQL query that runs for more than a few seconds.

To fix the error, you may need to change the timeout-related global settings in your MySQL database server.

Increase the connection timeout from the command line using –connect-timeout option

If you’re accessing MySQL from the command line, then you can increase the number of seconds MySQL will wait for a connection response using the --connect-timeout option.

By default, MySQL will wait for 10 seconds before responding with a connection timeout error.

You can increase the number to 120 seconds to wait for two minutes:

mysql -uroot -proot --connect-timeout 120

You can adjust the number 120 above to the number of seconds you’d like to wait for a connection response.

Once you’re inside the mysql console, try running your query again to see if it’s completed successfully.

Using the --connect-timeout option changes the timeout seconds temporarily. It only works for the current MySQL session you’re running, so you need to use the option each time you want the connection timeout to be longer.

If you want to make a permanent change to the connection timeout variable, then you need to adjust the settings from either your MySQL database server or the GUI tool you used to access your database server.

Let’s see how to change the timeout global variables in your MySQL database server first.

Adjust the timeout global variables in your MySQL database server

MySQL database stores timeout-related global variables that you can access using the following query:

SHOW VARIABLES LIKE "%timeout";

Here’s the result from my local database. The highlighted variables are the ones you need to change to let MySQL run longer queries:

+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| replica_net_timeout               | 60       |
| rpl_stop_replica_timeout          | 31536000 |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+

To change the variable values, you can use the SET GLOBAL query as shown below:

SET GLOBAL connect_timeout = 600;

The above query should adjust the connect_timeout variable value to 600 seconds. You can adjust the numbers as you see fit.

Adjust the timeout variables in your MySQL configuration files

Alternatively, if you’re using a MySQL configuration file to control the settings of your connections, then you can edit the my.cnf file (Mac) or my.ini file (Windows) used by your MySQL connection.

Open that configuration file using the text editor of your choice and try to find the following variables in mysqld :

[mysqld]
connect_timeout = 10
net_read_timeout = 30
wait_timeout = 28800
interactive_timeout = 28800

The wait_timeout and interactive_timeout variables shouldn’t cause any problem because they usually have 28800 seconds (or 8 hours) as their default value.

To prevent the timeout error, you need to increase the connect_timeout and net_read_timeout variable values. I’d suggest setting it to at least 600 seconds (10 minutes)

If you’re using GUI MySQL tools like MySQL Workbench, Sequel Ace, or PHPMyAdmin, then you can also find timeout-related variables that are configured by these tools in their settings or preferences menu.

For example, in MySQL Workbench for Windows, you can find the timeout-related settings in Edit > Preferences > SQL Editor as shown below:

If you’re using Mac, then the menu should be in MySQLWorkbench > Preferences > SQL Editor as shown below:

If you’re using Sequel Ace like me, then you can find the connection timeout option in the Preferences > Network menu.

Here’s a screenshot from Sequel Ace Network settings:

For other GUI tools, you need to find the option yourself. You can try searching the term [tool name] connection timeout settings in Google to find the option.

And those are the four solutions you can try to fix the MySQL connection lost during query issue.

I hope this tutorial has been helpful for you 🙏

Источник

�� 10. Lost connection to MySQL server during query

�� Lost connection to MySQL server �� connect_timeout, �� . � �� .

$php phpconf2009_4.php string(44) "Lost connection to MySQL server during query"

�� error log �� :

Version: '5.1.39' socket: '/tmp/mysql_sandbox5139.sock' port: 5139 MySQL Community Server (GPL) 091002 14:56:54 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8384512 read_buffer_size=131072 max_used_connections=1 max_threads=151 threads_connected=1


It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338301 K

bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

thd: 0x69e1b00

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 0x450890f0 thread_stack 0x40000

/users/ssmirnova/blade12/5.1.39/bin/mysqld(my_print_stacktrace+0x2e)[0x8ac81e]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_segfault+0x322)[0x5df502]

/lib64/libpthread.so.0[0x3429e0dd40]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN6String4copyERKS_+0x16)[0x5d9876]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_cache_str5storeEP4Item+0xc9)[0x52ddd9]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN26select_singlerow_subselect9send_dataER4ListI4ItemE+0x45)[0x5ca145]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x6386d1]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x64236a]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN4execEv+0x949)[0x658869]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN30subselect_single_select_engine4execEv+0x36c)[0x596f3c]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_realEv+0xd)[0x595fbd]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Arg_comparator18compare_real_fixedEv+0x39)[0x561b89]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN12Item_func_ne7val_intEv+0x23)[0x568fb3]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN8optimizeEv+0x12ef)[0x65208f]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xa0)[0x654850]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x16c)[0x65a1cc]

/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x5ecbda]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z21mysql_execute_commandP3THD+0x602)[0x5efdd2]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5f52f7]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe93)[0x5f6193]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f6a56]

/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_one_connection+0x246)[0x5e93f6]

/lib64/libpthread.so.0[0x3429e061b5]

/lib64/libc.so.6(clone+0x6d)[0x34292cd39d]`)

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort...

thd->query at 0x6a39e60 = select 1 from `t1` where `c0` <>  (select geometrycollectionfromwkb(`c3`) from `t1`)

thd->thread_id=2

thd->killed=NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

information that should help you find out what

�� , �� MySQL �� 11 ( mysqld got signal 11). �� MySQL �� (��, �� ), �� 11. �� Segmentation fault — �� .

�� . �� , �� ̣�� Σ�� perror, �� bin �� MySQL. ��, ��, �� perror �� MacOSX:

$perror 11 OS error code 11: Resource deadlock avoided

�� backtrace (�� Attempting backtrace.) �� Σ�� backtace ��.

�ݣ �� , �� :

Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x6a39e60 = select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`)

��

select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`)

�� mysql cli

$./my sql mysql [localhost] {msandbox} ((none)) > use test Database changed mysql [localhost] {msandbox} (test) > select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql [localhost] {msandbox} (test) > q Bye

�� . �� , �� .

�� backtrace

/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96] /users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_realEv+0xd)[0x595fbd] /users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Arg_comparator18compare_real_fixedEv+0x39)[0x561b89] /users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN12Item_func_ne7val_intEv+0x23)[0x568fb3] /users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN8optimizeEv+0x12ef)[0x65208f]

�� : Item_subselect � Item_singlerow_subselect. �� MySQL �� , �� .

��

$./my sql mysql [localhost] {msandbox} (test) > select 1 from `t1` where `c0` <> geometrycollectionfromwkb(`c3`); Empty set (0.00 sec)

MySQL �� ! �� , �� .

��ɣ� 18: �� error log

�� error log ��

�� , �� Mac-�

091002 16:49:48 - mysqld got signal 10 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.


key_buffer_size=8384512

read_buffer_size=131072

max_used_connections=1

max_connections=100

threads_connected=1

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225784 K

�� , �� backtrace, �� error log ��. �� ?

� �� , �� , �� general query log. MySQL �� general query log � �� . �� .

mysql> set global general_log=1; Query OK, 0 rows affected (0.00 sec) mysql> set global log_output='table'; Query OK, 0 rows affected (0.00 sec)

�� . �� general query log:

mysql> select argument from mysql.general_log order by event_time desc limit 10; +--------------------------------------------+ | argument | +--------------------------------------------+ | Access denied for user 'MySQL_Instance_Manager'@'localhost' (using password: YES) | | select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`) |

��, �� , ��!

��ɣ� 19: �� general query log �� error log �� .

�� ɣ�� , �� mysql.general_log �� MySQL ��. � �� .

�� , �� MySQL �� general query log. � �� , �� .

��, �� , ��ۣ� �� MySQL ��. �� MySQL �� .

��, �� RAM

�� :

key_buffer_size=235929600 read_buffer_size=4190208 max_used_connections=17 max_connections=2048 threads_connected=13 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 21193712 K ----- 21193712K ~= 20G

�� MySQL �� 20G RAM! �� , �� 20G RAM.

��ɣ� 20: �� RAM �� .

�� max_connections.

� �� max_connections=2048. �� . ��, �� .

� �� , �� max_connections �� , �� . �� MySQL �� -��.

��ɣ� 21: �� max_connections �� .

�� MySQL �� . �� error log. �� .

�� MySQL �� . �� , �� . � �� , �� .

��ɣ� 22: �� , �� MySQL ��.

�� Lost connection to MySQL server �� timeout. �� error log �� , �� log_warnings=2 � �� error log �� .

��ɣ� 23: �� log_warnings=2 �� Σ�� .

�� . �� general log, �� . � �� , �� .

Источник

The misstep depicted in the title of this article is entirely outstanding. A couple of attempts on handling the issue exist in various articles on the web. However, in error 2013 you lost connection to MySQL server during a query, concerning this article, there is a specific condition that is exceptionally novel so in the end, it causes the error to occur. The error occurs with the specific error message. That error message is in the going with yield message:

root@hostname ~# mysql – uroot – p – h 127.0.0.1 – P 4406
Enter secret key:
Error 2013 (HY000): Lost relationship with MySQL server at ‘examining initial correspondence bundle’, system error: 0
root@hostname ~#

The above error message is a result of partner with a MySQL Database Server. It is a standard MySQL Database Server running on a machine. However, the real connection is a substitute one. The connection exists using Worker holder running collaboration. Coming up next is the reliable running course of that Worker holder:

root@hostname ~# netstat – tulpn | grep 4406
tcp6 0:4406: * LISTEN 31814/worker-go-between
root@hostname ~#

There are at this point lots of articles analyze about this mix-up. For a model in this association in the stack overflow or this association and one more in this association, besides in this association. The general issue is truly something almost identical. There is something misguided in the running arrangement of the regular MySQL Database Server.

Why this happens

This misstep appears when the relationship between your MySQL client and database server times out. Essentially, it took unreasonably long for the request to return data so the connection gets dropped.

By far most of my work incorporates content migrations. These activities for the most part incorporate running complex MySQL requests that burn through a huge lump of the day to wrap up. I’ve found the WordPress wp_postmeta table especially hazardous considering the way that a site with countless posts can without a very remarkable stretch have two or three hundred thousand post meta sections. Joins of enormous datasets from such tables can be especially genuine.

Avoid the issue by sanitizing your requests

Generally speaking, you can avoid the issue absolutely by refining your SQL questions. For example, instead of joining all of the substance of two especially immense tables, have a go at filtering through the records you needn’t waste time with. Where possible, have a go at reducing the amount of partakes in a singular inquiry. This should have the extra benefit of simplifying your inquiry to examine. For my inspirations, I’ve found that denormalizing content into working tables can deal with the read execution. This avoids breaks.

Re-making the inquiries isn’t, by and large, another option so you can effort the going with server-side and client-side workarounds.

A server-side course of action

If you’re ahead for your MySQL server, make a pass at changing a couple of characteristics. The MySQL documentation proposes extending the net_read_timeout or connect timeout values on the server.

The client-side course of action

You can extend your MySQL client’s sever regards on the possibility that you don’t have exclusive induction to the MySQL server.

MySQL Worktable

You can adjust the SQL Editor tendencies in MySQL Work Table:

In the application menu, select Edit > Preferences > SQL Editor.
Quest for the MySQL Session portion and augmentation the DBMS connection read break regard.
Save the settings, very MySQL Work Table, and return the connection.

Step for handling the issue

There are a couple of stages for handling the issue above. There are two segments for handling the issue. The underlying portion is for perceiving the principal driver of the issue. Later on, the ensuing part is the genuine plan taken for tending to the fundamental driver of that issue. Thusly, going with the region which is the underlying portion will focus on power to search for the justification behind the issue.

Glancing through the justification behind the issue

Because of this article, coming up next is the means for settling the mix-up

Check whether the MySQL Database Server measure is truly running. Effect it as follows using any request plan open in the working for truly taking a gander at a running connection. Concerning this article, it is ‘systemctl status MySQL. Thusly, coming up next is a model for the execution of the request plan:

root@hostname ~# systemctl status MySQL
service – MySQL Community Server
Stacked: stacked (/lib/systemd/structure/mysql.service; horrible; vendor preset: engaged)
Dynamic: dynamic (running) since Mon 2019-09-16 13:16:12; 40s back
Cycle: 14867 ExecStart=/usr/sbin/mysqld – demonize – pid-file=/run/mysqld/mysqld. Pid (code=exited, status=0/SUCCESS)
Connection: 14804 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Guideline PID: 14869 (mysqld)
Tasks: 31 (limit: 4915)
Group:/system. Slice/mysql.service
└─14869/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
root@hostname ~#

Preceding partner with MySQL Database Server using a substitute port focusing on any moving toward requesting where it is a worker compartment measure dealing with, basically test the regular connection. By the day’s end, the partner using the normal port tuning in the machine for any moving toward a relationship with MySQL Database Server. Normally, it exists in port ‘3306’. Do it as follow:

root@hostname ~# mysql – uroot
Goof 2002 (HY000): Can’t interface with neighborhood MySQL server through connection ‘/var/run/mysqld/mysqld. Sock’ (2)
root@hostname ~#

The above screw-up message is where the genuine root issue is. Check for the genuine report which is tending to the connection record for MySQL daemon measure as follows:

root@hostname ~# disc/var/run/mysqld/
root@hostname ~# ls
Pid MySQL. sock MySQL. sock. Lock
root@hostname ~#

As shown by the above yield, the report doesn’t exist. That is the explanation the relationship with MySQL Database Server is continually failed. Even though the connection cooperation is done through the default port of ‘3306’.

The effort to restart the cooperation and trust that it will handle the issue.

root@hostname ~# systemctl stop MySQL
root@hostname ~# systemctl start MySQL
root@hostname ~# mysql – uroot
Slip-up 2002 (HY000): Can’t interface with neighborhood MySQL server through connection ‘/var/run/mysqld/mysqld. Sock’ (2)
root@hostname ~#

Unfortunately, the above cycle moreover wraps up in disappointment. Progress forward the movement for handling the issue, just check the MySQL Database arrangement record. In the wake of really investigating the report course of action, it doesn’t fit in any way shape, or form. Eventually, going through hours for changing the arrangement records, nothing happens.

For the reason happens above, check the right game plan before to see which MySQL Database Server arrangement is used by the running MySQL Database Server.

How might we fix MySQL Error 2013 (hy000)?

The fix for MySQL Error 2013 (hy000) depends a ton upon the setting off reason. We should now see how our MySQL Engineers help customers with settling it.

1. Changing MySQL limits

Lately, one of our customers pushed toward us saying that he is getting a mix-up as the one showed underneath while he is efforting to interface with the MySQL server.

Along these lines, our Engineers checked thoroughly and found that the connect timeout regard was set to two or three minutes. Thusly, we extended it to 10 in the MySQL arrangement record. For that, we followed the means underneath:

First thing, we opened the MySQL plan archive at, etc/MySQL/my.cnf

Then, we searched for connect timeout and set it as:

connect timeout=10

Then, we had a go at partner with MySQL server and we were viable.

Additionally, it requires the genuine setting of the variable max_allowed_packet in the MySQL arrangement record also. While efforting to restore the landfill record in GB sizes, we increase the value to a higher one.

2. Disabled person Access limits

This slip-up in like manner appears when the host approaches impediments. In such cases, we fix this by adding the client’s IP in, etc/hosts. Allow or license it in the server firewall.

Similarly, the error can happen as a result of the detachment of the server. Lately, in a similar case, the issue was not related to MySQL server or MySQL settings. We did a significant tunnel and found that high association traffic is causing the issue.

Exactly when we checked we found that an unconventional communication running by the Apache customer. Thusly, we killed that, and this good misstep.

3. Growing Server Memory

Last and not least, MySQL memory apportioning furthermore transforms into a basic factor for the slip-up. Here, the server logs will have related segments showing the lacking memory limit.

Subsequently, our Dedicated Engineers decline the innodb_buffer_pool size. This reduces the memory segment on the server and fixes the slip-up.

Checking the MySQL Database Server configuration used by the running MySQL Database Server

In the past fragment or part, there is a need to search for the real plan report used by the running MySQL Database Server. It is essential to guarantee that the plan record used is the right one. Thusly, every change can invite the right impact on dealing with the mix-up issue. Coming up next is the movement for searching for it:

Truly check out the once-over of the assistance first by suggesting the running framework. In the past part, the running framework is the ‘MySQL one. Execute the going with request guide to list the open running cycle:

systemctl list-unit-reports | grep MySQL

The yield of the above request plan for a model is in the going with one:

user hostname: ~$ systemctl list-unit-archives | grep MySQL
service horrendous
Service horrendous
user hostname: ~$

Then, at that point, truly investigate the substance of the help by executing the going with the request. Pick the right help, in this particular circumstance, it is ‘MySQL. service’:

user hostname: ~$ systemctl cat myself. service
#/lib/system/structure/Mysql.service
# MySQL systemd organization record
[Unit]
Description=MySQL Community Server
After=network. Target
[Install]
Wanted by=multi-user. Target
[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/run/mysqld/mysqld. Pid
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
Timeouts=600
Restart=on-dissatisfaction
Runtime Directory=mysqld
RuntimeDirectoryMode=755
LimitNOFILE=5000
user hostname: ~$

The record obligated for starting the help is in the archive ‘/usr/share/MySQL/MySQL-systems-start’ according to the yield message above. Coming up next is the substance of that record which is only fundamental for it:

if [! – r, etc/MySQL/my.cnf]; then,
resonation “MySQL arrangement not found at, etc/MySQL/my.in. Assuming no one minds, make one.”
leave 1
fi
…..

Resulting in truly taking a gander at the substance of the report ‘/, etc/MySQL/my.on, obviously, it isn’t the right record. Accordingly, to be more exact, there are other ways to deal with find the planned archive used by the running MySQL Database Server. The reference or the information exists in this association. Hence, according to the information in that association, basically perform the going with request guide to get the right one. It is forgetting the cycle ID and the right MySQL Database Server running collaboration:

root@hostname ~# netstat – tulpn | grep 3306
tcp6 0:3306: * LISTEN 21192/mysqld
root@hostname ~# ps aux | grep 21192
root@hostname ~# ps aux | grep 21192
mysql 21192 0.2 0.1 3031128 22664? Sl Sep16 1:39/usr/sbin/mysqld – daemonize – pid-file=/run/mysqld/mysqld. Pid
root 25442 0.0 23960 1068 pts/20 S+ 01:41 0:00 grep 21192
root@hostname ~#
Ensuing to getting the right running cycle, do the going with the request of ‘trace file_name_process’:
root@hostname ~# album/usr/bin/
root@hostname ~# strace. /mysqld
Coming up next is fundamental for the yield of the request:
detail, etc/my.cnf”, 0x7fff2e917880) = – 1 ENOENT (No such archive or vault)
detail, etc/mysql/my.cnf”, {st_mode=S_IFREG|0644, st_size=839, …}) = 0
openat (AT_FDCWD, “/, etc/mysql/my.cnf”, O_RDONLY) = 3
fstat (3, {st_mode=S_IFREG|0644, st_size=839, …}) = 0
brk(0x35f6000) = 0x35f6000
read (3, “#n# The MySQL data base server co”…, 4096) = 839
openat (AT_FDCWD, “/, etc/mysql/conf. d/”, O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4
fstat (4, {st_mode=S_IFDIR|0755, st_size=4096, …}) = 0
get dents (4, /* 4 entries */, 32768) = 120
get dents (4, /* 0 entries */, 32768) = 0
close (4) = 0
detail, etc/mysql/conf.d/mysql.cnf”, {st_mode=S_IFREG|0644, st_size=629, …}) = 0
openat (AT_FDCWD, “/, etc/mysql/conf.d/mysql.cnf”, O_RDONLY) = 4
fstat (4, {st_mode=S_IFREG|0644, st_size=629, …}) = 0
read (4, “[mysqld]nn# Connection and Three”…, 4096) = 629
read (4, “”, 4096) = 0
close (4) = 0
detail, etc/mysql/conf.d/mysqldump.cnf”, {st_mode=S_IFREG|0644, st_size=55, …}) = 0
openat (AT_FDCWD, “/, etc/mysql/conf.d/mysqldump.cnf”, O_RDONLY) = 4
fstat (4, {st_mode=S_IFREG|0644, st_size=55, …}) = 0
read (4, “[MySQL dump] nquicknquote-namesnma”…, 4096) = 55
read (4, “”, 4096) = 0
close (4) = 0
read (3, “”, 4096) = 0
close (3) = 0
detail (“/root/.my. cnf”, 0x7fff2e917880) = – 1 ENOENT (No such record or list)

The right one is finally in ‘/, etc/MySQL/conf.d/mysql.cf. Resulting in truly investigating the substance of the record, it is an empty archive. This is its essential driver. There has been some update and inverse present type of the MySQL Database Server, it making some disaster area the MySQL Database Server. The plan is essentially to fill that empty plan record with the right arrangement. The reference for the right arrangement of MySQL Database Server exists in this association. Restart the MySQL Server again, the above error issue will be tended to.

Источник

I’m trying to load mysqldump and I keep getting following error:

ERROR 2013 (HY000) at line X: Lost connection to MySQL server during
query

/etc/my.cnf:

[mysqld]
max_allowed_packet = 16M
net_read_timeout = 30
net_write_timeout = 60
...
[mysqldump]
max_allowed_packet = 16M

I tried to increase these values, but I keep getting that error no matter what( What else can I do to overcome this error?

asked Jan 1, 2016 at 0:12

alexusalexus

5854 gold badges13 silver badges28 bronze badges

If all the other solutions here fail — check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query.

Had this issue when innodb_buffer_pool_size was set too close to physical memory without a swapfile configured. MySQL recommends for a database specific server setting innodb_buffer_pool_size at a max of around 80% of physical memory, I had it set to around 90%, the kernel was killing the mysql process. Moved innodb_buffer_pool_size back down to around 80% and that fixed the issue.

answered Jan 5, 2017 at 18:48

A_funsA_funs

2092 silver badges4 bronze badges

The error code ERROR 2013 (HY000)
related with aborted connection. You can run the following command to verify this.

mysql> SHOW GLOBAL STATUS LIKE  'Aborted_connects';

If the counter getting increased one by each attempt to connect, then it is an issue with connection.

One way to solve this issue, you can increase the connection timeout value in your configuration file. You can do that by using the following command.

mysql> SET GLOBAL connect_timeout = 10;

I hope this will help you. Thank you.

answered Jan 1, 2016 at 13:05

Rathish Kumar BRathish Kumar B

2,1245 gold badges20 silver badges34 bronze badges

@A_funs was right, inspecting the system log yields this:

Aug 14 08:04:15 centos-php55 kernel: Killed process 8597 (mysqld) total-vm:7395680kB, anon-rss:3351108kB, file-rss:0kB, shmem-rss:0kB
Aug 14 08:04:15 centos-php55 mysqld_safe[7848]: /usr/bin/mysqld_safe: line 200:  8597 Killed                  LD_PRELOAD=/usr/lib64/libjemalloc.so.1 nohup /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=1024000 --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock < /dev/null > /dev/null 2>&1

So it might very well be possible you’re (like me) running out of memory on the machine. My problem was that MySQL was using too much memory so the scheduler was killing the process. Actually lowering innodb_buffer_pool_size fixed the issue.

answered Aug 14, 2018 at 8:09

adioe3adioe3

1353 bronze badges

What command are you using to load mysqldump?
Is this a production server?
Size of the dump?
Format of the dump (.gz or .sql)?

Check if the error caused due to restart,if yes
1) check mysql memory allocation
2) try to reduce memory allocation by reducing innodb_buffer_pool size

This will help to reduce swap usage.

answered Apr 11, 2017 at 4:46

Источник

Is your database restore stuck with MySQL Error 2013 (hy000)?

Often queries or modifications in large databases result in MySQL errors due to server timeout limits.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix MySQL Error 2013 (hy000) for our customers.

Why this MySQL Error 2013 (hy000) happens?

While dealing with MySQL, we may encounter some errors. Today, we are going to discuss one such error.

This MySQL 2013 error occurs during a restore of databases via mysqldump, in MySQL replication, etc.

This error appears when the connection between MySQL client and database server times out.

In general, this happens in databases with large tables. As a result, it takes too much time for the query to return data and the connection drops with an error.

Other reasons for the error include a large number of aborted connections, insufficient server memory, server restrictions, etc.

How do we fix MySQL Error 2013 (hy000)?

The fix for MySQL Error 2013 (hy000) depends a lot on the triggering reason. Let’s now see how our MySQL Engineers help customers solve it.

1. Changing MySQL limits

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to connect with MySQL server.

So, our Engineers checked in detail and found that the connect_timeout value was set to only a few seconds. So, we increased it to 10 in the MySQL configuration file. For that, we followed the steps below:

Firstly, we opened the MySQL configuration file at /etc/mysql/my.cnf

Then, we searched for connect_timeout and set it as:

connect_timeout=10

Then we tried connecting with MySQL server and we were successful.

Additionally, it requires the proper setting of the variable max_allowed_packet in the MySQL configuration file too. While trying to restore the dump file in GB sizes, we increase the value to a higher one.

2. Disable Access restrictions

Similarly, this error also appears when the host has access restrictions. In such cases, we fix this by adding the client’s IP in /etc/hosts.allow or allow it in the server firewall.

Also, the error can happen due to the unavailability of the server. Recently, in a similar instance, the problem was not related to MySQL server or MySQL settings. We did a deep dig and found that high network traffic is causing the problem.

When we checked we found that a weird process running by the Apache user. So, we killed that and this fixed the error.

3. Increasing Server Memory

Last and not least, MySQL memory allocation also becomes a key factor for the error. Here, the server logs will have related entries showing the insufficient memory limit.

Therefore, our Dedicated Engineers reduce the innodb_buffer_pool size. This reduces the memory allocation on the server and fixes the error.

[Need assistance with MySQL errors – We can help you fix it]

Conclusion

In short, we discussed in detail on the causes for MySQL Error 2013 (hy000) and saw how our Support Engineers fix this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account

Closed

aubweb9 opened this issue

Jan 31, 2022

· 61 comments

· Fixed by #40751

Assignees

Comments

Hello,

Since upgrade CH version 21.4.xxx to 22.1.xx we have the following error when running MySQL selects that takes more than 5 seconds.
In MySQL we see 6 incoming connections but clickhouse is throwing an error.

Error message and/or stacktrace
2022.01.31 06:42:35.336335 [ 22718 ] {70d2138d-f044-46e9-b1b0-70d6c8879c62} DynamicQueryHandler: Poco::Exception. Code: 1000, e.code() = 2013, mysqlxx::ConnectionLost: Lost connection to MySQL server during query (xx.xx.xx.xx:3306), Stack trace (when copying this message, always include the lines below):

Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, int) @ 0x1759fe2c in /usr/bin/clickhouse
mysqlxx::Query::executeImpl() @ 0x17353ab1 in /usr/bin/clickhouse
DB::MySQLWithFailoverSource::onStart() @ 0x14a7d4f7 in /usr/bin/clickhouse
DB::MySQLWithFailoverSource::generate() @ 0x14a7dd5b in /usr/bin/clickhouse
DB::ISource::tryGenerate() @ 0x1483f655 in /usr/bin/clickhouse
DB::ISource::work() @ 0x1483f21a in /usr/bin/clickhouse
DB::SourceWithProgress::work() @ 0x14a8a802 in /usr/bin/clickhouse
DB::ExecutionThreadContext::executeTask() @ 0x1485ecc3 in /usr/bin/clickhouse
DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic*) @ 0x1485353e in /usr/bin/clickhouse
DB::PipelineExecutor::executeImpl(unsigned long) @ 0x14852389 in /usr/bin/clickhouse
DB::PipelineExecutor::execute(unsigned long) @ 0x14852098 in /usr/bin/clickhouse
DB::CompletedPipelineExecutor::execute() @ 0x14850ac4 in /usr/bin/clickhouse
DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::__1::shared_ptrDB::Context, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&, std::__1::basic_string<char, std::__1::char_traits, std::__1::allocator > const&)>, std::__1::optionalDB::FormatSettings const&) @ 0x13d16ef3 in /usr/bin/clickhouse
DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optionalDB::CurrentThread::QueryScope&) @ 0x145a4cda in /usr/bin/clickhouse
DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x145a93e7 in /usr/bin/clickhouse
DB::HTTPServerConnection::run() @ 0x1480c73d in /usr/bin/clickhouse
Poco::Net::TCPServerConnection::start() @ 0x1745b96f in /usr/bin/clickhouse
Poco::Net::TCPServerDispatcher::run() @ 0x1745ddc1 in /usr/bin/clickhouse
Poco::PooledThread::run() @ 0x1760ea49 in /usr/bin/clickhouse
Poco::ThreadImpl::runnableEntry(void*) @ 0x1760c140 in /usr/bin/clickhouse
start_thread @ 0x7fa3 in /usr/lib/x86_64-linux-gnu/libpthread-2.28.so
__clone @ 0xf94cf in /usr/lib/x86_64-linux-gnu/libc-2.28.so
(version 22.1.2.2 (official build))

Is there any settings that have changed regarding MySQL connection since 21.4 that might cause this ?

I can reproduce it on different machines.

Thanks.

downgrading to clickhouse 21.4.4.30 solves it 👎

Hi.
Same problem. Even after downgrade to 22.1.3 version

Even in cli basic small query return error. But not all time. Some time query result successful. Random error )))

SELECT
[few fields]
FROM [mysql_db.table]
WHERE [some condition]
LIMIT 10

And result:
Received exception from server (version 22.1.3):
Code: 1000. DB::Exception: Received from clickhouse.service.consul:9000. DB::Exception: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (192.168.xx.xx:3306).

Hi @vistarsvo,

How big is your mysql table ?
Is it well indexed ?
How long does your query take directly in MySQL ?

Thanks,
Aubry

Hi.
I have a big DB in MySQL and many queries to different tables. Some tables with a more then 1kk records.
Query execution time very different. I can see error after 0.1 sec or after 4-5sec. But also i can see success execution after 1-7 sec. It is really random )))

Quite similar here…
usually I get the ClickHouse error after 5-6 seconds but some queries longer than that succeeds

@aubweb9 are you using a mysql db connection or table engine mysql?
In my case: mysql connected as db.
Cluster architecture: 3 shards with 2 reprlicas on each shard.
All shards has same problem. From cli and from code (

Downgrading to 21.x for cluster is not possible for us (

No shard no replicas using database engine as follow example :

CREATE DATABASE mysql_db ENGINE = MySQL(‘127.0.0.1:3306′,’datasources’, ‘user’, ‘password’) ;

Encountered the same problem today with version 22.3.2.1 migrating from 21.10.3.9.
MySQL engine database query will throw an error after around only 6 seconds.

First I thought it was an issue with the engine timeout setting as per here
so I tried to increase these values by :

CREATE DATABASE IF NOT EXISTS my_mysql_db ON CLUSTER xxx ENGINE = MySQL(
    'my_sql_host:3306',
    'mydb',
    'user',
    'passwd'
) SETTINGS
    connect_timeout=800,
    read_write_timeout=800,
    connection_wait_timeout=800
;

the same error after 6 seconds

  Code: 1000.
  DB::Exception: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306). Stack trace:    0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x165ff3cc in /usr/bin/clickhouse
  1. mysqlxx::Query::executeImpl() @ 0x16380dee in /usr/bin/clickhouse
  2. DB::MySQLWithFailoverSource::onStart() @ 0x156d5e77 in /usr/bin/clickhouse  3. DB::MySQLWithFailoverSource::generate() @ 0x156d66db in /usr/bin/clickhouse
  4. DB::ISource::tryGenerate() @ 0x1548cdf5 in /usr/bin/clickhouse
  5. DB::ISource::work() @ 0x1548c9ba in /usr/bin/clickhouse
  6. DB::SourceWithProgress::work() @ 0x156e4282 in /usr/bin/clickhouse
  7. DB::ExecutionThreadContext::executeTask() @ 0x154ad143 in /usr/bin/clickhouse
  8. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) @ 0x154a0b9e in /usr/bin/clickhouse  9. ? @ 0x154a2504 in /usr/bin/clickhouse  10. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xa584c97 in /usr/bin/clickhouse
  11. ? @ 0xa58881d in /usr/bin/clickhouse
  12. ? @ 0x7ffaf5a4c609 in ?
  13. clone @ 0x7ffaf5971163 in ?

after digging into logs, It seems the connection to MySQL was recognized as lost during the query and clickhouse will try to reconnect for a default of 5 times, then the error above will be thrown, guess that is why always around 6 seconds.

here is the full chain of warning+error logs:

2022.04.02 10:31:32.534562 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Warning> MySQLBlockInputStream: Failed connection (0/5). Trying to reconnect... (Info: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306))
2022.04.02 10:31:33.534575 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Warning> MySQLBlockInputStream: Failed connection (1/5). Trying to reconnect... (Info: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306))
2022.04.02 10:31:34.534572 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Warning> MySQLBlockInputStream: Failed connection (2/5). Trying to reconnect... (Info: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306))
2022.04.02 10:31:35.534550 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Warning> MySQLBlockInputStream: Failed connection (3/5). Trying to reconnect... (Info: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306))
2022.04.02 10:31:36.534521 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Warning> MySQLBlockInputStream: Failed connection (4/5). Trying to reconnect... (Info: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306))
2022.04.02 10:31:37.534536 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Warning> MySQLBlockInputStream: Failed connection (5/5). Trying to reconnect... (Info: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306))
2022.04.02 10:31:37.534574 [ 509 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Error> MySQLBlockInputStream: Failed to create connection to MySQL. (6/5)
2022.04.02 10:31:37.534814 [ 1704 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Error> executeQuery: Poco::Exception. Code: 1000, e.code() = 2013, mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306) (version 22.3.2.1) (from 172.19.35.115:41836) (in query:  SELECT XXXXXXX), Stack trace (when copying this message, always include the lines below):
0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x165ff3cc in /usr/bin/clickhouse
1. mysqlxx::Query::executeImpl() @ 0x16380dee in /usr/bin/clickhouse
2. DB::MySQLWithFailoverSource::onStart() @ 0x156d5e77 in /usr/bin/clickhouse
3. DB::MySQLWithFailoverSource::generate() @ 0x156d66db in /usr/bin/clickhouse
4. DB::ISource::tryGenerate() @ 0x1548cdf5 in /usr/bin/clickhouse
5. DB::ISource::work() @ 0x1548c9ba in /usr/bin/clickhouse
6. DB::SourceWithProgress::work() @ 0x156e4282 in /usr/bin/clickhouse
7. DB::ExecutionThreadContext::executeTask() @ 0x154ad143 in /usr/bin/clickhouse
8. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) @ 0x154a0b9e in /usr/bin/clickhouse
9. DB::PipelineExecutor::executeImpl(unsigned long) @ 0x1549feeb in /usr/bin/clickhouse
10. DB::PipelineExecutor::execute(unsigned long) @ 0x1549f7b8 in /usr/bin/clickhouse
11. ? @ 0x154b0f8d in /usr/bin/clickhouse
12. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xa584c97 in /usr/bin/clickhouse
13. ? @ 0xa58881d in /usr/bin/clickhouse
14. ? @ 0x7fd5e0294609 in ?
15. clone @ 0x7fd5e01b9163 in ?
2022.04.02 10:31:37.534949 [ 1704 ] {b40f0320-de92-4565-9e31-a8b06b48d48b} <Error> TCPHandler: Code: 1000. DB::Exception: mysqlxx::ConnectionLost: Lost connection to MySQL server during query (my_sql_host:3306). (POCO_EXCEPTION), Stack trace (when copying this message, always include the lines below):
0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x165ff3cc in /usr/bin/clickhouse
1. mysqlxx::Query::executeImpl() @ 0x16380dee in /usr/bin/clickhouse
2. DB::MySQLWithFailoverSource::onStart() @ 0x156d5e77 in /usr/bin/clickhouse
3. DB::MySQLWithFailoverSource::generate() @ 0x156d66db in /usr/bin/clickhouse
4. DB::ISource::tryGenerate() @ 0x1548cdf5 in /usr/bin/clickhouse
5. DB::ISource::work() @ 0x1548c9ba in /usr/bin/clickhouse
6. DB::SourceWithProgress::work() @ 0x156e4282 in /usr/bin/clickhouse
7. DB::ExecutionThreadContext::executeTask() @ 0x154ad143 in /usr/bin/clickhouse
8. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) @ 0x154a0b9e in /usr/bin/clickhouse
9. DB::PipelineExecutor::executeImpl(unsigned long) @ 0x1549feeb in /usr/bin/clickhouse
10. DB::PipelineExecutor::execute(unsigned long) @ 0x1549f7b8 in /usr/bin/clickhouse
11. ? @ 0x154b0f8d in /usr/bin/clickhouse
12. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xa584c97 in /usr/bin/clickhouse
13. ? @ 0xa58881d in /usr/bin/clickhouse
14. ? @ 0x7fd5e0294609 in ?
15. clone @ 0x7fd5e01b9163 in ?

turned on debug log, found an extra log, seems the reconnect was triggered by the connection id change

<Debug> mysqlxx::Pool: Entry(connection 878364): Reconnected to MySQL server. Connection id changed: 878363 -> 878364

I will try to open up another issue later with potential-bug tag

Yes, I forgot to mention that I also see 6 incoming connections in MySQL logs

clickhouse will try to reconnect for a default of 5 times, then the error above will be thrown, guess that is why always around 6 seconds.

In my case i can get execution error even after 0.x sec or 1-2 sec. May be, because other connection in pool is busy by another threads.
May be somebody try to make 10-20 connections pool?

clickhouse will try to reconnect for a default of 5 times, then the error above will be thrown, guess that is why always around 6 seconds.

In my case i can get execution error even after 0.x sec or 1-2 sec. May be, because other connection in pool is busy by another threads. May be somebody try to make 10-20 connections pool?

I think the default pool size according to the code is 16, check here

Also, I think (forgive my poor C++ if I am wrong) the errors are independent across queries, since the exception was thrown by function MySQLWithFailoverSource::onStart() and each iteration will call pool->get() together with the query string. and if settings were not changed, the default default_num_tries_on_connection_loss is 5 as specified here

void MySQLWithFailoverSource::onStart()
{
    size_t count_connect_attempts = 0;

    /// For recovering from "Lost connection to MySQL server during query" errors
    while (true)
    {
        try
        {
            connection = std::make_unique<Connection>(pool->get(), query_str);
            break;
        }
        catch (const mysqlxx::ConnectionLost & ecl)  /// There are two retriable failures: CR_SERVER_GONE_ERROR, CR_SERVER_LOST
        {
            LOG_WARNING(log, "Failed connection ({}/{}). Trying to reconnect... (Info: {})", count_connect_attempts, settings->default_num_tries_on_connection_loss, ecl.displayText());

            if (++count_connect_attempts > settings->default_num_tries_on_connection_loss)
            {
                LOG_ERROR(log, "Failed to create connection to MySQL. ({}/{})", count_connect_attempts, settings->default_num_tries_on_connection_loss);
                throw;
            }
        }
    }

    initPositionMappingFromQueryResultStructure();
}

I think the question should be why the connection id was changed (i.e. connection was reestablished) every time a ping() was performed. According to this MySQL doc on gone away error and the description on ping() method, I couldn’t see any misuse of MySQL C API in this scenario.

Anyhow, I think @vistarsvo you could try to use ClickHouse JDBC bridge if a downgrade is of too much trouble.
I will stick with version 21.10.3.9 for now because of the bulkiness of my existing ETL jobs.

Have the same problem, any news when it can be fixed?
(currently use 21.8 and cant update to 22.3, because affected by this issue)

Hi, @aubweb9 , @gfunc , @vistarsvo, @oleg-savko
Got same problem after upgrade from 21.8 to 22.3, and solved the problem by increasing external_storage_rw_timeout_sec, external_storage_connect_timeout_sec settings

Hi @alexsubota ,

I changed the settings on our side
external_storage_connect_timeout_sec —> 30
and I will keep you posted if it solves our issue or not.

But usually the error was thrown ~5 seconds after we send the query to mysql and the default timeout setting was set to 10… therefore I’ve some doubts …

Unfortunately the above did not fix our issue, we still see :

HttpCode:500 ; ;Poco::Exception. Code: 1000, e.code() = 2013, mysqlxx::ConnectionLost: Lost connection to MySQL server during query (xxx.xx.xx.xx:3306) (version 22.3.2.1)

Same issue on 22.1.x. The error was thrown ~2 seconds after sending queries. Increasing external_storage_connect_timeout_sec setting doesn’t work, too

Same here since upgrading. When using INSERT into mysql db. Tried with custom settings, but same result:

SETTINGS
    connect_timeout=80000,
    external_storage_rw_timeout_sec=999999999,
    external_storage_connect_timeout_sec=9999999999

2022.05.31 11:49:21.057871 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Pool: Entry(connection 527279109): sending PING to check if it is alive.
2022.05.31 11:49:21.061389 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Pool: Entry(connection 527279109): PING ok.
2022.05.31 11:49:21.061408 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Query: Running MySQL query using connection 527279109
2022.05.31 11:49:21.096549 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Pool: Entry(connection 527279109): sending PING to check if it is alive.
2022.05.31 11:49:21.100005 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Pool: Entry(connection 527279109): PING ok.
2022.05.31 11:49:21.100404 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Query: Running MySQL query using connection 527279109
2022.05.31 11:49:21.699421 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Query: Running MySQL query using connection 527279109
2022.05.31 11:49:21.699671 [ 281 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> mysqlxx::Query: Running MySQL query using connection 527279109
2022.05.31 11:49:21.746157 [ 47 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Error> executeQuery: Poco::Exception. Code: 1000, e.code() = 2006, mysqlxx::ConnectionLost: MySQL server has gone away (db01.x:3306) (version 22.5.1.2079 (official build)) (from x:58890) (in query: INSERT INTO db01.flattened_rates_aggregates SELECT .... SETTINGS connect_timeout=80000, external_storage_rw_timeout_sec=999999999, external_storage_connect_timeout_sec=9999999999), Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x1b2113cc in /usr/bin/clickhouse
1. mysqlxx::Query::executeImpl() @ 0x1af8a94e in /usr/bin/clickhouse
2. mysqlxx::Transaction::rollback() @ 0x16391293 in /usr/bin/clickhouse
3. DB::StorageMySQLSink::consume(DB::Chunk) @ 0x1639042d in /usr/bin/clickhouse
4. DB::SinkToStorage::onConsume(DB::Chunk) @ 0x16f4471d in /usr/bin/clickhouse
5. ? @ 0x16edb3d7 in /usr/bin/clickhouse
6. ? @ 0x16edb189 in /usr/bin/clickhouse
7. DB::ExceptionKeepingTransform::work() @ 0x16edaa3f in /usr/bin/clickhouse
8. DB::ExecutionThreadContext::executeTask() @ 0x16d18ee8 in /usr/bin/clickhouse
9. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) @ 0x16d0cbfe in /usr/bin/clickhouse
10. ? @ 0x16d0e4c4 in /usr/bin/clickhouse
11. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xb53fc47 in /usr/bin/clickhouse
12. ? @ 0xb54367d in /usr/bin/clickhouse
13. ? @ 0x7efd94222609 in ?
14. __clone @ 0x7efd94147133 in ?

2022.05.31 11:49:21.763991 [ 47 ] {4354352b-bd0f-4d25-917d-2cce48524527} <Trace> Aggregator: Destroying aggregate states

had the same problem. Turns out that the culprit was the MySQL (MariaDB) server. Editing my.cnf fixed this:

max_allowed_packet = 4G
wait_timeout = 3600

Hi, the issue persists here.
I also noticed that every time the error occurs the mysql aborted clients counter is incremented.
Another proof that the issue is on Clickhouse side.

Issue still persists in ClickHouse server version 22.6.3.35 (official build).
EDIT: it’s definitely a Clickhosue issue, because the same query, executed from mysql console directly, executes just fine. Also, If in the same query I change the filtering condition to use an ID field that has an index on it, the query executes just fine from Clickhouse. So depending on which filter I use, the query that returns the same data (1 row with a fixed ID), might fail or might not, depending on how long this query is executed.
EDIT 2: I just confirmed: this bug is present starting Clickhouse-server v 22.1. The same query works in v 21.12.4.1
@kssenii most probably related to f0d0714
EDIT 3: After going through MySQL query log, it shows that indeed Clickhouse makes 5 identical queries to MySQL each exactly 1 second apart before signalling that MySQL Connection is lost. It seems like it’s trying to get first bite, doesn’t get it after 1 second, and decides MySQL is lost.
EDIT 4: It seems to be related to MySQL version. With MySQL v8+ it seems to work Ok, but with MYSQL 5 it does this weird bug. Can anyone confirm if the bug happens on MySQL 8+ for them?

same issue on Clickhouse 22.3.2.1, mysql version 8.0.21

Increasing mysqlx_max_connections from 100 to 3000 in MySQL settings solved the problem in my case

same issue on Clickhouse 22.6.2.12 MySQL 8.0.21
when i insert rows to mysql table engine with a large batch, a small batch of inserts works

works after downgrade to 21.12.4.1

After years of loading large data sets from MySQL into ClickHouse using AS SELECT * FROM mysql(...) style queries, I also inexplicably began receiving the same «Lost connection to MySQL server during query» error with the latest stable release available as a Debian package according to apt: 22.2.2 revision 54455.

Based on my experience and this rollback successes outlined in this thread, this certainly appears to be a regression introduced with f0d0714. @kssenii, do you have any thoughts on what may be causing this?

Hey Guys, any news about this problem, or does anyone have a workaround to solve it?!

Hey Guys, any news about this problem, or does anyone have a workaround to solve it?!

AFAIK the are two known workarounds:

Revert clickhouse-server to [21.12] or lower
Load everything in smaller chunks, i.e. do filtering in the query to the MySQL table. With default options, this filter will be propagated as-is to the query to MySQL. Then combine chunks on Clickhouse side.

In my case we connect DB as ENGINE = MySQL(…)
Try change external_storage_rw_timeout and external_storage_connect_timeout.
We allow more then 200 connections in MySQL
Up mix package size for mysql
allow 100 connections from ClickHouse (if not mistakes)

But this is not help. Very often have this Lost Connection problem.

—
When we refactor code and move some tables from mysql to clickhouse, and our load to this engine became minimal (some grafana reports and 1-2 crontab processes), this problem was solved. We have big connections pool limit and always can found free connection pool.

as for table engine
@phantom943 please try MySQL table engine setting connection_wait_timeout to very big value. I am almost sure it should help.
(create table … engine=MySQL(…) SETTINGS connection_wait_timeout=10000000 )

as for table engine @phantom943 please try MySQL table engine setting connection_wait_timeout to very big value. I am almost sure it should help. (create table … engine=MySQL(…) SETTINGS connection_wait_timeout=10000000 )

@kssenii just tried. Created a new table with the same MySQL source, added SETTINGS connection_wait_timeout=10000000
However, same result: I get connection lost after 5 seconds.
It seems if Clickhouse doesn’t receive the first byte of the response from MySQL in 5 seconds — it thinks the connection is lost and tries to reconnect. However, MySQL is just busy executing the query and doesn’t respond with anything (or CH doesn’t acknowledge the responce). After 3 reconnects, it fails with the error mentioned above.
Basically, to reproduce this you just need a big table in MySQL with lots of data, and some complex computation that is directly executed on MySQL, and a filter on a column without an index, so that the lookup is longer.

Hey guys any news — I just need the feature of table list in materializedMySQL and the version that has this feature also has the bug of connection loss! 🙁

Hey guys any news — I just need the feature of table list in materializedMySQL and the version that has this feature also has the bug of connection loss! 🙁

For table list, you can always create a Clickhouse table with MySQL table engine sourcing from information_schema.tables in MySQL, and read from that inside Clickhouse 🙂

Hey guys any news — I just need the feature of table list in materializedMySQL and the version that has this feature also has the bug of connection loss! 🙁

For table list, you can always create a Clickhouse table with MySQL table engine sourcing from information_schema.tables in MySQL, and read from that inside Clickhouse 🙂

noo, I meant to specify which tables to include in MaterializedMySQL — no to list tables of MySQL database

I get similar errors after 30-600 seconds of query runtime. Clickhouse manages to pull up to ~20-25GB from MySQL according to docker network stats. I’ve tried increasing MySQL timeouts like @gauvins2 suggested earlier in the thread with no luck.

Error stacktrace:

2022.08.26 16:00:48.632815 [ 48 ] {0d2f2b92-b4f4-4467-9ade-b62a485ed580} <Error> DynamicQueryHandler: Poco::Exception. Code: 1000, e.code() = 2013, mysqlxx::Exception: Lost connection to MySQL server during query (mysql57:3306), Stack trace (when copying this message, always include the lines below):

0. mysqlxx::checkError(st_mysql*) in /usr/bin/clickhouse
1. mysqlxx::UseQueryResult::fetch() in /usr/bin/clickhouse
2. DB::MySQLSource::generate() in /usr/bin/clickhouse
3. DB::MySQLWithFailoverSource::generate() in /usr/bin/clickhouse
4. DB::ISource::tryGenerate() in /usr/bin/clickhouse
5. DB::ISource::work() in /usr/bin/clickhouse
6. DB::ExecutionThreadContext::executeTask() in /usr/bin/clickhouse
7. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) in /usr/bin/clickhouse
8. DB::PipelineExecutor::executeImpl(unsigned long) in /usr/bin/clickhouse
9. DB::PipelineExecutor::execute(unsigned long) in /usr/bin/clickhouse
10. DB::CompletedPipelineExecutor::execute() in /usr/bin/clickhouse
11. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::__1::shared_ptr<DB::Context>, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&)>, std::__1::optional<DB::FormatSettings> const&) in /usr/bin/clickhouse
12. DB::HTTPHandler::processQuery(DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) in /usr/bin/clickhouse
13. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) in /usr/bin/clickhouse
14. DB::HTTPServerConnection::run() in /usr/bin/clickhouse
15. Poco::Net::TCPServerConnection::start() in /usr/bin/clickhouse
16. Poco::Net::TCPServerDispatcher::run() in /usr/bin/clickhouse
17. Poco::PooledThread::run() in /usr/bin/clickhouse
18. Poco::ThreadImpl::runnableEntry(void*) in /usr/bin/clickhouse
19. ? in ?
20. clone in ?
 (version 22.8.2.11 (official build))

Clickhouse query:

create database mysql57_import_database
engine = MySQL('[server]', '[database]', '[user]', '[password]')
settings
    connection_wait_timeout=999999,
    connect_timeout=999999,
    read_write_timeout=999999
;

create table analytics.target_table
engine = MergeTree
order by pk_field
as select *
from mysql57_import_database.target_table;

MySQL log:

2022-08-26T15:25:58.136271Z 0 [Note] mysqld: ready for connections.
Version: '5.7.22-22-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release '22', Revision 'f62d93c'
2022-08-26T16:00:48.427138Z 724 [Note] Aborted connection 724 to db: '[database]' user: '[user]' host: 'clickhouse' (Got an error writing communication packets)

@kssenii after few days seeking result I finally get result. When we try to create a table with mysql enging , we use function below to create a PoolWithFailover which contine a driver to connect to mysql server.

createMySQLPoolWithFailover(const StorageMySQLConfiguration & configuration, const MySQLSettings & mysql_settings)
{
    if (!mysql_settings.connection_pool_size)
        throw Exception(ErrorCodes::BAD_ARGUMENTS, "Connection pool cannot have zero size");

    return mysqlxx::PoolWithFailover(
        configuration.database, configuration.addresses, configuration.username, configuration.password,
        MYSQLXX_POOL_WITH_FAILOVER_DEFAULT_START_CONNECTIONS,
        mysql_settings.connection_pool_size,
        mysql_settings.connection_max_tries,
        mysql_settings.connection_wait_timeout,
        mysql_settings.connect_timeout,
        mysql_settings.read_write_timeout);
}

As we can see in this function, we use mysql_settings.read_write_timeout which is 300 to set our pool. In my test the unit of that 300 value is millisecond. And also, I pretty shure that any change after mysql_driver be instantiated will not influence rw-timeout work. For me, I change mysql_settings.read_write_timeout to 10000 and all problems goes away. @kssenii

@kssenii after few days seeking result I finally get result. When we try to create a table with mysql enging , we use function below to create a PoolWithFailover which contine a driver to connect to mysql server.
createMySQLPoolWithFailover(const StorageMySQLConfiguration & configuration, const MySQLSettings & mysql_settings)
{
    if (!mysql_settings.connection_pool_size)
        throw Exception(ErrorCodes::BAD_ARGUMENTS, "Connection pool cannot have zero size");

    return mysqlxx::PoolWithFailover(
        configuration.database, configuration.addresses, configuration.username, configuration.password,
        MYSQLXX_POOL_WITH_FAILOVER_DEFAULT_START_CONNECTIONS,
        mysql_settings.connection_pool_size,
        mysql_settings.connection_max_tries,
        mysql_settings.connection_wait_timeout,
        mysql_settings.connect_timeout,
        mysql_settings.read_write_timeout);
}
As we can see in this function, we use mysql_settings.read_write_timeout which is 300 to set our pool. In my test the unit of that 300 value is millisecond. And also, I pretty shure that any change after mysql_driver be instantiated will not influence rw-timeout work. For me, I change mysql_settings.read_write_timeout to 10000 and all problems goes away. @kssenii

@danelfedor this seems to be working for me! Thanks a lot!

@kssenii after few days seeking result I finally get result. When we try to create a table with mysql enging , we use function below to create a PoolWithFailover which contine a driver to connect to mysql server.
createMySQLPoolWithFailover(const StorageMySQLConfiguration & configuration, const MySQLSettings & mysql_settings)
{
    if (!mysql_settings.connection_pool_size)
        throw Exception(ErrorCodes::BAD_ARGUMENTS, "Connection pool cannot have zero size");

    return mysqlxx::PoolWithFailover(
        configuration.database, configuration.addresses, configuration.username, configuration.password,
        MYSQLXX_POOL_WITH_FAILOVER_DEFAULT_START_CONNECTIONS,
        mysql_settings.connection_pool_size,
        mysql_settings.connection_max_tries,
        mysql_settings.connection_wait_timeout,
        mysql_settings.connect_timeout,
        mysql_settings.read_write_timeout);
}
As we can see in this function, we use mysql_settings.read_write_timeout which is 300 to set our pool. In my test the unit of that 300 value is millisecond. And also, I pretty shure that any change after mysql_driver be instantiated will not influence rw-timeout work. For me, I change mysql_settings.read_write_timeout to 10000 and all problems goes away. @kssenii

@phantom943
So how can we override the value of rw_timout? is it by passing the value in the settings of MySQL Engine function?
MySQL(Host,......) Settings read_write_timeout = 1000000?

Can you explain more?

Thanks

@kssenii after few days seeking result I finally get result. When we try to create a table with mysql enging , we use function below to create a PoolWithFailover which contine a driver to connect to mysql server.
createMySQLPoolWithFailover(const StorageMySQLConfiguration & configuration, const MySQLSettings & mysql_settings)
{
    if (!mysql_settings.connection_pool_size)
        throw Exception(ErrorCodes::BAD_ARGUMENTS, "Connection pool cannot have zero size");

    return mysqlxx::PoolWithFailover(
        configuration.database, configuration.addresses, configuration.username, configuration.password,
        MYSQLXX_POOL_WITH_FAILOVER_DEFAULT_START_CONNECTIONS,
        mysql_settings.connection_pool_size,
        mysql_settings.connection_max_tries,
        mysql_settings.connection_wait_timeout,
        mysql_settings.connect_timeout,
        mysql_settings.read_write_timeout);
}
As we can see in this function, we use mysql_settings.read_write_timeout which is 300 to set our pool. In my test the unit of that 300 value is millisecond. And also, I pretty shure that any change after mysql_driver be instantiated will not influence rw-timeout work. For me, I change mysql_settings.read_write_timeout to 10000 and all problems goes away. @kssenii
@phantom943 So how can we override the value of rw_timout? is it by passing the value in the settings of MySQL Engine function? MySQL(Host,......) Settings read_write_timeout = 1000000?

Can you explain more?

Thanks

For me, what worked was

CREATE or replace TABLE tname
(
    `id` Int32,
    ...
)
ENGINE = MySQL('<host:port>', '<db>', '<table>', '<login>', '<password>')
SETTINGS read_write_timeout=10000

@kssenii after few days seeking result I finally get result. When we try to create a table with mysql enging , we use function below to create a PoolWithFailover which contine a driver to connect to mysql server.
createMySQLPoolWithFailover(const StorageMySQLConfiguration & configuration, const MySQLSettings & mysql_settings)
{
    if (!mysql_settings.connection_pool_size)
        throw Exception(ErrorCodes::BAD_ARGUMENTS, "Connection pool cannot have zero size");

    return mysqlxx::PoolWithFailover(
        configuration.database, configuration.addresses, configuration.username, configuration.password,
        MYSQLXX_POOL_WITH_FAILOVER_DEFAULT_START_CONNECTIONS,
        mysql_settings.connection_pool_size,
        mysql_settings.connection_max_tries,
        mysql_settings.connection_wait_timeout,
        mysql_settings.connect_timeout,
        mysql_settings.read_write_timeout);
}
As we can see in this function, we use mysql_settings.read_write_timeout which is 300 to set our pool. In my test the unit of that 300 value is millisecond. And also, I pretty shure that any change after mysql_driver be instantiated will not influence rw-timeout work. For me, I change mysql_settings.read_write_timeout to 10000 and all problems goes away. @kssenii
@phantom943 So how can we override the value of rw_timout? is it by passing the value in the settings of MySQL Engine function? MySQL(Host,......) Settings read_write_timeout = 1000000?

Can you explain more?

Thanks

@MahmoudElhalwany Simply add a setting can only fix tables with mysql enging, but not be able to fix problem in mysql database. Change mysql_settings.read_write_timeout in source code can fix both of them

I did some quick tests and indeed it’s not working for mysql database engine 🙁

@kasthack did you try to change source code in MySQLSettings.h and have a try?

@kasthack did you try to change source code in MySQLSettings.h and have a try?
@danelfedor
it needs to clone the source code, edit it and build it. not an easy task

@kasthack did you try to change source code in MySQLSettings.h and have a try?

@danelfedor no, I didn’t. I’ve just increased rw timeout by a factor of 1000 in my settings, but it still fails.

It’s working for mysql engine tables with Settings read_write_timeout = 1000000 👍
But ideally it would be great if it could work for database engine as well.

Fixed for database engine and table function here #40751. Example of all possible options to pass timeouts for table engine, database engine, table function can be seen here for now

	node1.query(
	f»»»
	CREATE TABLE {table_name}
	(
	id UInt32,
	name String,
	age UInt32,
	money UInt32
	)
	ENGINE = MySQL(‘mysql57:3306’, ‘clickhouse’, ‘{table_name}‘, ‘root’, ‘clickhouse’)
	SETTINGS connection_wait_timeout={wait_timeout}, connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}, connection_pool_size={connection_pool_size}
	«»»
	)

	node1.query(f»SELECT * FROM {table_name}«)
	assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

	rw_timeout = 20123001
	connect_timeout = 20123002
	node1.query(f»SELECT * FROM mysql(mysql_with_settings)»)
	assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

	rw_timeout = 30123001
	connect_timeout = 30123002
	node1.query(f»»»
	SELECT *
	FROM mysql(‘mysql57:3306’, ‘clickhouse’, ‘{table_name}‘, ‘root’, ‘clickhouse’,
	SETTINGS
	connection_wait_timeout={wait_timeout},
	connect_timeout={connect_timeout},
	read_write_timeout={rw_timeout},
	connection_pool_size={connection_pool_size})
	«»»)
	assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

	rw_timeout = 40123001
	connect_timeout = 40123002
	node1.query(f»»»
	CREATE DATABASE m
	ENGINE = MySQL(mysql_with_settings, connection_wait_timeout={wait_timeout}, connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}, connection_pool_size={connection_pool_size})
	«»»)
	assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

	rw_timeout = 50123001
	connect_timeout = 50123002
	node1.query(f»»»
	CREATE DATABASE mm ENGINE = MySQL(‘mysql57:3306’, ‘clickhouse’, ‘root’, ‘clickhouse’)
	SETTINGS
	connection_wait_timeout={wait_timeout},
	connect_timeout={connect_timeout},
	read_write_timeout={rw_timeout},
	connection_pool_size={connection_pool_size}
	«»»)
	assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

Fixed for database engine and table function here #40751. Example of all possible options to pass timeouts for table engine, database engine, table function can be seen here for now

node1.query(

f»»»

CREATE TABLE {table_name}

(

id UInt32,

name String,

age UInt32,

money UInt32

)

ENGINE = MySQL(‘mysql57:3306’, ‘clickhouse’, ‘{table_name}‘, ‘root’, ‘clickhouse’)

SETTINGS connection_wait_timeout={wait_timeout}, connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}, connection_pool_size={connection_pool_size}

«»»

)

node1.query(f»SELECT * FROM {table_name}«)

assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

rw_timeout = 20123001

connect_timeout = 20123002

node1.query(f»SELECT * FROM mysql(mysql_with_settings)»)

assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

rw_timeout = 30123001

connect_timeout = 30123002

node1.query(f»»»

SELECT *

FROM mysql(‘mysql57:3306’, ‘clickhouse’, ‘{table_name}‘, ‘root’, ‘clickhouse’,

SETTINGS

connection_wait_timeout={wait_timeout},

connect_timeout={connect_timeout},

read_write_timeout={rw_timeout},

connection_pool_size={connection_pool_size})

«»»)

assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

rw_timeout = 40123001

connect_timeout = 40123002

node1.query(f»»»

CREATE DATABASE m

ENGINE = MySQL(mysql_with_settings, connection_wait_timeout={wait_timeout}, connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}, connection_pool_size={connection_pool_size})

«»»)

assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

rw_timeout = 50123001

connect_timeout = 50123002

node1.query(f»»»

CREATE DATABASE mm ENGINE = MySQL(‘mysql57:3306’, ‘clickhouse’, ‘root’, ‘clickhouse’)

SETTINGS

connection_wait_timeout={wait_timeout},

connect_timeout={connect_timeout},

read_write_timeout={rw_timeout},

connection_pool_size={connection_pool_size}

«»»)

assert node1.contains_in_log(f»with settings: connect_timeout={connect_timeout}, read_write_timeout={rw_timeout}«)

.

@kssenii Can you make the default value of these timeout variable bigger, so we can use default value without taking care of timeout problem

Sorry one more question on this :
is the setting read_write_timeout could be applied on a view ?

for example :

create view mysql_view_abc ( id UInt32, sdate Date, status String ) as SELECT id, sdate, status from MySQL('<host:port>', '<db>', '<table>', '<login>', '<password>') SETTINGS read_write_timeout=10000

yes, but a little different:

from mysql('<host:port>', '<db>', '<table>', '<login>', '<password>', SETTINGS read_write_timeout=10000)
using named collections
given a config

<named_collections>
    <mysql_conf>
        <host></host>
        ...
        <read_write_timeout>10000</read_write_timeout>
    </mysql_conf>
</named_collections>

a. from mysql(mysql_conf)
b. from mysql(mysql_conf, read_write_timeout=10000)

the first solution throws an error :

SQL Error [62]: ClickHouse exception, code: 62, host: localhost, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 109 ('read_write_timeout') (line 3, col 95): read_write_timeout=10000) FORMAT TabSeparatedWithNamesAndTypes. Expected one of: token, Comma, Arrow, Dot, UUID, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, end of query. (SYNTAX_ERROR) (version 22.3.2.1)

Because it was added in PR #40751, which is a master version now.

@kssenii Can you make the default value of these timeout variable bigger, so we can use default value without taking care of timeout problem

Ok.

Because it was added in PR #40751, which is a master version now.

Will this PR merged into release version 22.8 in future？

Will this PR merged into release version 22.8 in future？

Backported: #40944.

Источник

Ошибка lost connection to mysql server during query

Increase the connection timeout from the command line using –connect-timeout option

Adjust the timeout global variables in your MySQL database server

Adjust the timeout variables in your MySQL configuration files

����� 10. Lost connection to MySQL server during query

Why this happens

Avoid the issue by sanitizing your requests

A server-side course of action

The client-side course of action

MySQL Worktable

Step for handling the issue

Glancing through the justification behind the issue

How might we fix MySQL Error 2013 (hy000)?

1. Changing MySQL limits

2. Disabled person Access limits

3. Growing Server Memory

Why this MySQL Error 2013 (hy000) happens?

How do we fix MySQL Error 2013 (hy000)?

1. Changing MySQL limits

2. Disable Access restrictions

3. Increasing Server Memory

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

Comments

А вот еще интересные материалы:

�� 10. Lost connection to MySQL server during query