Did you try turning it off and on again?
Recently, I encountered an issue with a MySQL database where my query would just never receive a response.
I would open up MySQL Workbench, start the query, then watch as the loading spinner appeared until 10 minutes later, the request would fail and Workbench would return an error message saying that the connection was lost.
I was upset but it wasn’t totally unexpected. I was trying to perform an INSERT from a joined table created from two tables each with more than a million rows after all.
Maybe my timeout wasn’t long enough. I increased the timeout threshold from 600 to 1200 seconds.
Twenty minutes later, Workbench gave me the same error. Connection lost.
I tried running it directly on the command line.
No good.
Hmmm...
I removed the INSERT section, seeing if simplifying the query and just asking for a SELECT from the joined tables could speed up the response time.
Same result.
I opened up Google and started to look for answers. I started learning about chunking queries, indexing, composite primary keys, and MySQL Workbench settings. Most of all, I learned how much more I had to learn about databases.
I started experimenting with other queries, and saw queries that used to be fast become slow, and I could feel my gradual descent into the place that no engineer wants to be.
This is the place engineers go when they’ve started reading unrelated StackExchange posts from the 12th page of the Google Search Results, and they start thinking, “well, the market for engineers is hot right now...”.
Every engineer knows about “being in the zone” or “flow state”. Well, this is basically the opposite. It’s like The Phantom Zone for engineers.
It’s not a pleasant place to be. But I’ve come to realize it’s a necessary part of learning, because otherwise, you are probably not facing challenging enough tasks.
Here’s a insightful article on why “flow” is actually avoided by the best piano players:
http://www.calnewport.com/blog/2011/12/23/flow-is-the-opiate-of-the-medicore-advice-on-getting-better-from-an-accomplished-piano-player/
“Okay, I’m going to need some help on this.” It was the end of the day, and I made a note to ask for help tomorrow.
I think people like to personify inanimate objects. It’s a way to add flavor and character to what actually is just a really advanced piece of rock. It’s why we say our phones “died”, or the compiler is “yelling” at you. So the next morning, I decided to retry my query. After all, the database had a good night of rest. Very scientific I know.
To my dismay, it seemed the database had pulled an all-nighter binging Netflix or whatever databases do because it spat out the same error message, even for much simpler queries.
Oh wait. What if I...
I sshed into the remote docker instance running MySQL, looked up how to restart MySQL, and restarted it.
When it had restarted, I went back to Workbench and reran the SELECT query. It worked...
Closing Notes and QA:
How did you not think of restarting the database??
- In retrospect, I should have. It’s because the database instance runs in a docker container, and when the docker container is restarted, any changes I’ve made to the database are wiped. I think that prevented me from realizing I could just restart the MySQL process within the docker container without resetting the database itself.
Did the INSERT work too?
- No. That still took too long, but I realized that all the simpler queries that were taking longer and longer was just due to the database process being in poor shape after all those long queries. I’m still figuring that one out.
