오류 코드 : 2013. 쿼리 중 MySQL 서버 연결이 끊어졌습니다

오류 코드 : 2013. 쿼리 중 MySQL 서버 연결이 끊어졌습니다

---

내가 가지고 쿼리 동안 MySQL 서버 2013 로스트 연결 : 오류 코드 내가 MySQL의 워크 벤치를 사용하여 테이블에 인덱스를 추가하려고 할 때 오류가 발생했습니다. 또한 긴 쿼리를 실행할 때마다 나타납니다.

시간 종료 값을 늘릴 수 있습니까?

---

새로운 버전의 MySQL WorkBench에는 특정 타임 아웃을 변경하는 옵션이 있습니다.

나를 위해 편집 → 환경 설정 → SQL 편집기 → DBMS 연결 읽기 시간 초과 (초) : 600

값을 6000으로 변경했습니다.

또한 전체 데이터 세트를 검색 할 때마다 제한을두기 때문에 선택하지 않은 제한 행은 번거 롭습니다.

---

comandline 옵션 net_read_timeout/ wait_timeout및 적절한 값 (초)으로 DB 서버를 시작하십시오 (예 :) --net_read_timeout=100.

참조를 위해 여기 와 여기 를 참조하십시오 .

---

쿼리에 BLOB 데이터가 있는 경우이 답변에서 제안한대로my.ini 변경 사항 을 적용하여이 문제를 해결할 수 있습니다 .

[mysqld]
max_allowed_packet=16M

기본적으로이 값은 1M입니다 (허용되는 최대 값은 1024M입니다). 제공된 값이 1024K의 배수가 아닌 경우 가장 가까운 1024K의 배수로 자동 반올림됩니다.

참조 된 스레드는 MySQL 오류 2006 에 관한 것이지만 max_allowed_packet1M에서 16M으로 설정하면 긴 쿼리를 실행할 때 나타나는 2013 오류 가 수정되었습니다.

WAMP 사용자의 경우 : [wampmysqld]섹션 에서 플래그를 찾을 수 있습니다.

---

/ etc / mysql / cnf 파일에 다음을 추가하십시오.

innodb_buffer_pool_size = 64M

예:

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

---

SET @@local.net_read_timeout=360;

경고 : 다음은 원격 연결에서 적용 할 때 작동하지 않습니다.

SET @@global.net_read_timeout=360;

---

이 오류 메시지에는 세 가지 원인이있을 수 있습니다.

1. 일반적으로 네트워크 연결 문제를 나타내며이 오류가 자주 발생하면 네트워크 상태를 확인해야합니다
2. 때때로“쿼리 중”양식은 하나 이상의 쿼리의 일부로 수백만 행이 전송 될 때 발생합니다.
3. 클라이언트가 서버에 초기 연결을 시도 할 때 발생할 수있는 경우는 거의 없습니다.

자세한 내용 은 >>

원인 2 :

SET GLOBAL interactive_timeout=60;

기본 30 초에서 60 초 이상으로

원인 3 :

SET GLOBAL connect_timeout=60;

---

mysql 설정 파일의 'interactive_timeout'및 'wait_timeout'속성을 필요한 값으로 설정해야합니다.

---

고마워요! 그러나 mysqldb 업데이트로 구성이 변경되었습니다.

max_allowed_packet

net_write_timeout

net_read_timeout

MySQL의 의사

---

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

---

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.

---

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

---

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.

---

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

---

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.

---

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.

---

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.

---

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.

---

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

---

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).

---

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.

---

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

---

Check if the indexes are in place first.

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

---

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 ?

---

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.

---

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!

---

Three things to be followed and make sure:

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

Answers:

1. 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
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

---

check about

OOM on /var/log/messages ,
modify innodb_buffer_pool_size value ; when load data , use 50% of os mem ; 

Hope this helps

---

This usually means that you have "incompatibilities with the current version of MySQL Server", see mysql_upgrade. I ran into this same issue and simply had to run:

mysql_upgrade --password The documentation states that, "mysql_upgrade should be executed each time you upgrade MySQL".

참고URL : https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query