MariaDB upgrade gotchas

Due to a recent infrastructure redo, I had to upgrade a stack that I did not update for around two years. I had MariaDB 10.3.4 and decided to try to upgrade it directly to 10.7.1.

The overall process was relatively painless (data volume wasn’t that big), but a few things caught me by surprise.

• somehow, I lacked index on a pretty big table - I have no idea how it worked in the older version, but suddenly, after deploying the whole stack to new servers, all hell broke loose. Rails started throwing ActiveRecord connection pool-related exceptions. The entire service was down - after inspecting CPU usage on database container and taking a look into process list I realized what was going on - I have a ridiculous amount of running queries hanging there

• so as you guessed from the above - MariaDB doesn’t kill long-running queries by default - you can adjust that by setting max_statement_time - it might be a good practice to set a reasonable timeout on that

Update 2021.12.19: I forgot to mention setting that setting max_statement_time affects your db backups - as mysqldump respects that value (which is not that obvious) - that might lead to incomplete sql dumps! See this stackexchange thread for references. You might want to create a dedicated user for mysqldump or workaround the problem by setting the global value of max_statement_time during your backup (and restoring it after it’s done) - both options are less than ideal. This feature request from 2019 lists some reasonable suggestions (that will be implemented one day hopefully).

• when running MariaDB in docker, you can configure it on the CLI level. It’s not documented super clearly, but if you pass something like --max_statement_time=10 --slow_query_log=ON --long_query_time=2 as docker argument it should just work

• I never understood MySQL/MariaDB query planner (that’s why I became Postgres fanboy :P) - sometimes seems it need help, and index hints might come in handy in such cases (in case ANALYZE TABLE was no good)