Error Code: 2013. Lost connection to MySQL server during query
To rectify Error Code: 2013, adjust your MySQL timeouts. This includes extending the net_read_timeout value for uninterrupted reading, and the wait_timeout value to prevent timeouts during extended query executions. Update these in your MySQL configuration file:
[mysqld]
net_read_timeout = 600 # up to 10 minutes, long enough for a coffee break
wait_timeout = 28800 # up to 8 hours, perfect for a good night's sleep
Isn't sleep the cousin of coding? Restart your MySQL service to set these changes into motion.
Coping with large datasets and long queries
Sometimes, SQL queries take their sweet time, especially when you're dealing with larger datasets that feel like they took a detour through the Pacific Ocean.
Wake up MySQL Workbench
You can adjust a couple parameters to keep MySQL Workbench from dozing off:
-
Connection read timeout: Head over to Edit → Preferences → SQL Editor and set this to a hefty 6000s. Patience is indeed a virtue.
-
Limit rows: Turn off this option to give your dataset the freedom to sprint without any halt, just like a rabbit discovering an open field.
Keep SQL queries lean
Channel your inner Marie Kondo and declutter your SQL queries. Steamline them by:
- Eradicating redundant indexes and superfluous conditions.
- Making
max_allowed_packetchunkier: Boost this value inmy.iniormy.cnfup to 16M, or for the brave and fearless, increase it all the way to 1024M. As we say, the larger the better.
Dial in perfect timeouts
For setting net_read_timeout according to each MySQL session, execute SET @@local.net_read_timeout=360; to design your timeout as per the weight of your query.
Command-line enhancements
For the command-line gurus out there, --net_read_timeout=100 will bolster your read operations and keep connections healthy. After all, the command line is the treadmill of programming.
Tailoring your MySQL server configurations
To get a well-tuned MySQL server, here are some optimizations that could work wonders:
The heart of InnoDB tables
Boost the performance of your InnoDB tables by correctly setting the innodb_buffer_pool_size. It's like changing the diet of your favorite pet for better health and vitality.
System variables control center
You can tweak some system variables to take control of your station:
wait_timeout&net_read_timeoutadjustments: Your secret weapon to prevent the "Gone Away" horror show from making an unexpected cameo.- Thread settings: Setting the right
thread_stackandthread_cache_sizewill help your server efficiently handle concurrent connections, like an octopus juggling tasks.
If you're using MyISAM storage, adjusting the key_buffer is the magic trick to watch your table indexes perform like a well-rehearsed symphony.
Pro tips and further steps
Best practices
Often times, the culprit could be the system around your MySQL setup:
- Round
max_allowed_packet: Make sure it's in multiples of 1024K. - Check the system health: Don't forget the environment around MySQL, including network components and server hardware.
- MySQL inspection: Tools like MySQLTuner offer a magnifying glass to inspect the health of your MySQL server.
Further troubleshooting
In case you've employed all the above strategies but the error persists:
- Inspect official documentation: MySQL's official "Gone Away" documentation is kind of the godfather of timeout issues.
- Crowdsource wisdom: Platforms like Stack Overflow are flooded with discussions from those who've faced similar problems and found solutions. So you'll know you're not alone in the fight.
Was this article helpful?