Error Code : 2013

Another day and another issue with joining complex views. However where I solved the previous problem of slow queries by instructing MySQL to not spend too much time looking for an optimal execution plan, this latest issue seems much more grave.

When trying to execute a join I immediately get returned an Error 2013, "Lost connection to MySQL server during query". This occurs whether I run it on a client or server. Changing the query slightly to omit part of a where clause (simply an is null check) causes the query to run correctly. I have a hunch that this is related to my complex views once again. The fact that even the EXPLAIN returns this error is pretty worrying.

Checking the syslog on the server, it appears that MySQL is immediately crashing out. Unfortunately I don't have much more to go on since the promised stack backtrace (used to understand where in code MySQL was when it went titsup) doesn't appear in the log.

No answers in the community - although since I suspect that this a unique error I wasn't expecting much. It seems my only real option is to debug MySQL. However since this requires recompiling the whole thing from source, this solution is probably best left for a time when I'm not as tied up developing (so probably never). Again my confidence in MySQL is such that I'm sure I'll have to face this issue sooner rather than later so I'll have to do the recompilation eventually.

In the meantime I've managed to come across some pretty interesting behaviour regarding optimizer_search_depth. Setting this back to 4 (from 0) does allow the same query to complete, albeit with an incorrect result set (I'm left joining a table with one row on its own and end up with zero results). 62 may work better, but since that still makes the query unbearably long, I've not checked this. Recompiling the view also changes the behaviour of the query. It's all terribly inconsistent and bizarre.

So as it stands I have a choice between crashing the server, completing with incorrect results, or spending an hour waiting for correct results. I'm certainly not impressed and for the first time am wondering if MySQL is the best choice for this job (or indeed any other that requires a database!). I'm even considering switching to MSSQL (and so Windows with it), something I never hear other people having to do.

For the time being though, I've decided to become a bit more pragmatic about MySQL and its features. I now accept that the ONLY reason I'm now using it is because it's free: I've certainly never managed to crash MSSQL, and I've definitely found the community support for it to be better than what I've seen of MySQL's.

So now, instead of complaining about how rubbish MySQL is, I'm going to accept its flaws, use the workarounds given to me and create my own. Yes, my database's design may suffer for it, and yes code written against it may have to be ugly, but I'm guessing that this is the MySQL/OSS way of getting things done.

This isn't all as fatal as I'm making it sound though. The way MySQL works has also forced me to reassess the bad parts of my design and so has prompted more positive changes. For example, there doesn't seem to be way to pass around table results in MySQL, be it via selecting the results of a proc or allowing a function to return variables (in fact there's no table variables at all here). I've had to use scalar functions in a more creative manner instead, which in turn highlighted some inefficiencies in my code that once I saw was able to massage out.

In short, I'm hoping that the progress I make will distract me from the irritations the platform is bringing me.