I’ve written a small script that pings mysql once a minute and if it does not respond immediately it restarts it.  This way at least we won’t have lengthy outages while I figure it out.

     The version of mysql provided with Ubuntu 16.04 is version 5.7, an upgrade from the previous version 5.6.

     MySQL 5.7 has proven to be unstable and locks up periodically.  I have spent several days trying to troubleshoot this, fixed a number of issues, and optimized the configuration.  Still it locks up and dies from time to time.

     I tried to upgrade to MariaDB 10.  An in place upgrade failed, and dumping the old database and sourcing the resulting sql file in MariaDB also failed.  It completes but the grant tables aren’t recognized which basically makes it useless.  I have yet to find a fix for this.  I apologize for this instability.

     If anyone can find a clean path to migrate from MySQL 5.7 to MariaDB 10.0, please let me know (e-mail: nanook@eskimo.com) or call me at 206-812-0051.

I’ve got connections from the shell servers disabled temporarily until this issue can be resolved as it trips this bug instantly.  If anybody knows anyplace I can get help with this, a good forum for mysql issues etc, please do let me know (e-mail nanook@eskimo.com).

     While investigating where to fix the file descriptor limit in MySQL I ran across quite a few useful tweaks.  The end result, I was able to shave about 300ms off of our page load speed for our WordPress site so that if you aren’t logged in, it is now under 1/2 second which ain’t shabby for WordPress.

     There is still something causing MySQL to lock up.  I am able to reproduce what is causing it, just don’t know how to fix it yet.  I am trying to find a solution.

     The latest and greatest is that MySQL was running out of file descriptors.  The fun thing about that is that just modifying limits.conf doesn’t fix it, one needs to modify the systemd start-up script for MySQL as well as the /etc/mysql/my.cnf.

     The re-install blew away my modified systemd scripts and so what I had in my.cnf was ignored as it was greater than the system default of 1024.

     This is fixed now.  I also did some tuning to hopefully speed it up a bit.  Shaved about 100ms off o the page load time.

     Now I’m waiting to see what blows up next.

     I totally removed and re-installed MySQL.  This was one of many suggested fixes.

     Still not fixed, the web server mysql database is still exploding.

     Different problem now, now is saying bad file descriptor repeatedly.  No errors prior to that so no clue as to why that is happening.  I increased the number of open file descriptors allowed but I don’t think that is the real issue.

     I believe I’ve got the website issues fixed.

     Ubuntu compiled MySQL 5.7 differently than MySQL 5.6.  Specifically, they included four things that were plugins into the base server.

     When the upgrade installed it, it asked me whether I wanted to keep the original configuration or accept a new configuration.  I opted to keep the original as I have a number of site specific things configured in.

     Also the plugins were kept in a table, mysql.plugins, and so in spite of having these functions compiled in, it also tried to load these plugins.  As you can imagine, this did not go over well.

     So I have deleted those plugins which are now hard coded into the server from the plugin table, and fixed the configuration issues.

     Another issue was that 5.7 now has support for encrypted connections but you have to specify a certificate and key to use them.  It is supposed to disable this functionality if none are found, but instead it tried and bitched about it.

     So I’ve now configured in the ability to support encrypted sessions.

     Mysql keeps hanging, it gets in a loop of errors with socket operation on a non-socket but the only socket I know of is the one to connect and it is still a socket.

     I am researching and trying to understand what is happening to correct it.  If you get cannot connect to the database when you go to our website or anything using MySQL here, that is why.

     This is unrelated to the upgrade.  It started happening several days before.  I had hoped the upgrade would resolve it.  The machine in question is quite generously equipped with memory (16GB), so not a memory issue.