14,953
edits
Troubleshooting of MySQL errors: Difference between revisions
Jump to navigation
Jump to search
→Connection and Network Errors
| Line 399: | Line 399: | ||
Solutions: | Solutions: | ||
* Modify the host of database from {{kbd | key=localhost}} to {{kbd | key=127.0.0.1}} | * Modify the host of database from {{kbd | key=localhost}} to {{kbd | key=127.0.0.1}} | ||
=== ERROR 2013: Lost connection to MySQL server during query === | |||
Message: Error Code: 2013. Lost connection to MySQL server during query | |||
Condition: After executed the following query contains number of rows which exceed 1,000,000 rows, I met the error message 'Error Code: 2013. Lost connection to MySQL server during query'. | |||
<pre> | |||
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`; | |||
</pre> | |||
Solution: | |||
* Increase the settings of (1) '''DBMS connection keep-alive interval (in seconds)''' & (2) '''DBMS connection read time out (in seconds)''' on [https://www.mysql.com/products/workbench/ MySQL Workbench] <ref>[http://stackoverflow.com/questions/16877574/how-can-i-execute-sql-queries-that-take-longer-99-999-seconds-on-mysql-workbench How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow]</ref><ref>[http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow]</ref>. And remember to restart the MySQL Workbench after the settings were modified. e.g. The default setting of '''DBMS connection read time out (in seconds)''' is 30 seconds, you may increase to 6000 seconds (100 minutes). | |||
* Reduce the number of rows to reduce the execution time (1) by using {{kbd | key=LIMIT}} clause (2) or by splitting the query size e.g. {{kbd | key=<nowiki>MOD(column, 2) = 0</nowiki>}} & {{kbd | key=<nowiki>MOD(column, 2) > 0</nowiki>}} if the column is numeric. | |||
<pre> | |||
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000; | |||
</pre> | |||
== Resource and Environmental Errors == | == Resource and Environmental Errors == | ||