I got the Error Code: 2013. Lost connection lớn MySQL server during query error when I tried lớn add an index lớn a table using MySQL Workbench. I also noticed that it appears whenever I lập cập long query.

Is there a way lớn increase the timeout value?

asked May 12, 2012 at 12:14

New versions of MySQL WorkBench have an option lớn change specific timeouts.

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

Changed the value lớn 6000.

Also unchecked limit rows as putting a limit in every time I want lớn tìm kiếm the whole data mix gets tiresome.

Marko

20.5k13 gold badges50 silver badges64 bronze badges

answered Oct 8, 2012 at 22:49

15

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 mặc định, 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 lớn the nearest multiple of 1024K.

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

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

answered Jul 3, năm trước at 13:36

4

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

2

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,1433 gold badges32 silver badges47 bronze badges

answered Apr trăng tròn, năm ngoái at 3:58

2

Add the following into /etc/mysql/my.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

Note that the configuration tệp tin may have a different name. Other examples:

  • /etc/mysql/mariadb.conf.d/50-server.cnf (recent Debian-based)
  • /etc/my.cnf

answered Apr 17, năm ngoái at 15:11

3

In my case, setting the connection timeout interval lớn 6000 or something higher didn't work.

I just did what the workbench says I can tự.

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

On Mac Preferences -> SQL Editor -> Go lớn MySQL Session -> mix connection read timeout interval lớn 0.

And it works 😄

answered Nov 26, 2019 at 3:55

On the basis of what I have understood this error was caused due lớn read timeout and max allowed packet mặc định is 4M. if your query tệp tin more phàn nàn 4Mb then you get an error. this worked for me

  1. change the read timeout. For changing go lớn Workbench Edit → Preferences → SQL Editor

2. change the max_allowed_packet manually by editing the tệp tin my.ini. for editing go lớn "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini". The thư mục ProgramData thư mục is hidden so sánh if you did not see then select show hidden tệp tin in view settings. mix the max_allowed_packet = 16M in my.ini tệp tin. 3. Restart MySQL. for restarting go lớn win+ R -> services.msc and restart MySQL.

answered Mar 24, 2022 at 6:15

There are three likely causes for this error message

  1. Usually it indicates network connectivity trouble and you should kiểm tra the condition of your network if this error occurs frequently
  2. Sometimes the “during query” khuông happens when millions of rows are being sent as part of one or more queries.
  3. More rarely, it can happen when the client is attempting the initial connection lớn the server

For more detail read >>

Cause 2 : 

SET GLOBAL interactive_timeout=60;

from its mặc định of 30 seconds lớn 60 seconds or longer

Cause 3 :

SET GLOBAL connect_timeout=60;

answered Dec 8, năm 2016 at 6:30

1

You should mix the 'interactive_timeout' and 'wait_timeout' properties in the mysql config tệp tin lớn the values you need.

answered May 12, 2012 at 12:19

1

If you experience this problem during the restore of a big dump-file and can rule out the problem that it has anything lớn tự with network (e.g. execution on localhost) phàn nàn my solution could be helpful.

My mysqldump held at least one INSERT that was too big for mysql lớn compute. You can view this variable by typing show variables lượt thích "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 lớn read this is not suitable for big dumps > 1GB because it tends lớn 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 phàn nàn 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, năm 2016 at 11:07

Sometimes your SQL-Server gets into deadlocks, I've been in lớn this problem lượt thích 100 times. You can either restart your computer/laptop lớn restart server (easy way) OR you can go lớn task-manager>services>YOUR-SERVER-NAME(for má , it was MySQL785 something lượt thích this). And right-click > restart. Try executing query again.

answered Feb 10, 2021 at 13:28

1

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

7717 gold badges22 silver badges32 bronze badges

answered May 19, năm trước at 20:16

2

I know its old but on mac 

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

answered Mar 27, năm ngoái at 6:53

1

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

answered Apr 21, năm 2016 at 9:25

Try please lớn uncheck limit rows in in Edit → Preferences →SQL Queries

because You should mix the 'interactive_timeout' and 'wait_timeout' properties in the mysql config tệp tin lớn the values you need.

answered Jul 24, năm trước at 9:59

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

answered Sep 1, 2018 at 2:50

I got the same issue when loading a .csv tệp tin. Converted the tệp tin lớn .sql.

Using below command I manage lớn work around this issue.

mysql -u  -p -D  < tệp tin.sql

Hope this would help.

answered Sep 8, năm 2016 at 6:19

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

I tried lớn lập cập 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, năm 2016 at 7:22

This happened lớn má because my innodb_buffer_pool_size was mix lớn be larger phàn nàn the RAM size available on the server. Things were getting interrupted because of this and it issues this error. The fix is lớn 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 mix the new connection intervals (in seconds).

answered May 5, 2017 at 13:23

There seems lớn be an answer missing here for those using SSH lớn connect lớn their MySQL database. You need lớn kiểm tra two places not 1 as suggested by other answers:

Workbench Edit → Preferences → SQL Editor → DBMS

Workbench Edit → Preferences → SSH → Timeouts

My mặc định SSH Timeouts were mix very low and causing some (but apparently not all) of my timeout issues. After, don't forget lớn restart MySQL Workbench!

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

Hope this helps!

answered May 6, 2019 at 17:36

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

answered May 11, 2017 at 15:38

I had the same problem - but for má the solution was a DB user with too strict permissions. I had lớn allow the Execute ability on the mysql table. After allowing that I had no dropping connections anymore

answered Aug 31, 2017 at 17:35

Check if the indexes are in place first.

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = ''

answered Sep 22, 2017 at 3:58

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 má was switching lớn 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

If you are using SQL Work Bench, you can try using Indexing, by adding an index lớn your tables, lớn 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 mix the type lớn 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

Three things lớn be followed and make sure:

  1. Whether multiple queries show lost connection?
  2. how you use mix query in MySQL?
  3. how delete + update query simultaneously?

Answers:

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

RalfFriedl

1,1303 gold badges11 silver badges13 bronze badges

answered Sep 22, 2019 at 16:10

I had this error message due lớn a problem after of upgrade Mysql. The error appeared immediately after I tried lớn tự any query

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

In my case reassigning Mysql owner lớn the Mysql system thư mục worked for me

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

answered Jan 23, 2021 at 19:29

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