[SOLVED] mysql (mariadb) fails to start after randomly exiting in Ubuntu 16.04

JohnLocke · 02-10-2017, 09:32 PM

At a bit of a loss here. Upgraded to Ubuntu 16.04 from 14.04 in December, which included the change from mysql to mariadb. That hosed a bunch of stuff (upgrade of the database went badly), so I ended up uninstalling both mysql and mariadb, reinstalling, and there was much joy.

The database there is primarily used for mythtv, so I set that up brand new with the new database, and again, there was much joy. The server has been restarted a couple times between then and now.

About ten days ago, I went in and redid some mythtv-setup to add a new channel to the lineup. The setup didn't exit cleanly, but it restarted the mythtv-backend and everything seemed happy.

Suddenly today, we noticed we stopped getting live TV. Investigate and find it's because mysql isn't running. The single line in any mysql log is this:

170210 19:01:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Ok ... so it appears mysql died yesterday. But I go to restart and nothing works.

journalctl -xe reports:

Code:

Feb 10 20:12:07 serenity systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has begun starting up.
Feb 10 20:12:07 serenity mysql[7685]:  * Starting MariaDB database server mysqld
Feb 10 20:12:07 serenity /etc/init.d/mysql[7714]: /etc/init.d/mysql: line 114: /usr/bin/mysqld_safe: No such file or directory
Feb 10 20:12:38 serenity /etc/init.d/mysql[7997]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Feb 10 20:12:38 serenity /etc/init.d/mysql[7997]: [52B blob data]
Feb 10 20:12:38 serenity /etc/init.d/mysql[7997]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Feb 10 20:12:38 serenity /etc/init.d/mysql[7997]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Feb 10 20:12:38 serenity mysql[7685]:    ...fail!
Feb 10 20:12:38 serenity /etc/init.d/mysql[7997]: 
Feb 10 20:12:38 serenity systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 10 20:12:38 serenity systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysql.service has failed.
-- 
-- The result is failed

Similarly, systemctl status mysql.service returns:

Code:

● mysql.service - LSB: Start and stop the mysql database server daemon
   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2017-02-10 20:12:38 MST; 8min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 7685 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Feb 10 20:12:07 serenity systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
Feb 10 20:12:07 serenity mysql[7685]:  * Starting MariaDB database server mysqld
Feb 10 20:12:38 serenity mysql[7685]:    ...fail!
Feb 10 20:12:38 serenity systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 10 20:12:38 serenity systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Feb 10 20:12:38 serenity systemd[1]: mysql.service: Unit entered failed state.
Feb 10 20:12:38 serenity systemd[1]: mysql.service: Failed with result 'exit-code'.

I only have a single my.cnf file (located in /etc/mysql/my.cnf) and there don't appear to be any strange items in there. There's also nothing added to /var/log/mysql/error.log (the only file in that directory) when using any of:
service mysql start
systemctl start mysql
/etc/init.d/mysql start

I know systemctl is the new right way, but tried the others as well in the hope that something might work ... same result all the time. Complaining about the socket not being there so nothing can talk to anything, but of course the socket isn't there while it's not running.

Somewhat interestingly, I can start the service with:
sudo mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --socket=/var/run/mysqld/mysqld.sock

And of course the socket and pid lock all show up fine this way (this is also where the socket is declared to be in my.cnf)

But then I get a bunch of very strange errors in the log file. All kinds of stuff about what appear to be odd definitions in tables. Things like:

Code:

Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(80).
mysql.user has no `Event_priv` column at position 28
[ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).

And all kinds of others. Seems weird, but it starts up just fine and I can log into mysql as root and poke around. I can use mysqladmin to shut it down at that point and that happens gracefully.

So currently I'm at a loss. Not sure why it won't start because I can't find anything in a log to point to why it won't start, no pid lock exists (no files are in /var/run/mysqld and that directory is owned by mysql as is /var/lib/mysql).

This issue is persistent across a reboot as well. Not sure where to go next. Guess at least it waited till /after/ the superbowl to die.

For completeness, I also looked in the mythtv logs, but there's no logs for it that say anything interesting other than unable to connect through socket to mysql, so it's off.

I'd guess mythtv somehow hosed mysql on exiting badly, but it didn't matter until mysql completely died. Not sure how to fix this at this point.

Any thoughts / help much appreciated.

Edit: I should also note that no mysql sockets exist when checking either "sudo find / -type s" or "sudo netstat -tln | grep 3306"

JohnLocke · 02-11-2017, 02:19 PM

Adding some more info.

I start up mysql with:
sudo mysqld --datadir=/var/lib/mysql --user=mysql --socket=/var/run/mysqld/mysqld.sock

On startup, I get a lot of errors like:
[ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure

performance_schema is a database I used to have, but haven't been using, so I dropped it earlier as I was getting tons of startup errors related to it. Now I'm getting fewer of them, but still getting them(?).

I ran mysqlcheck on each database in there (information_schema, mysql, and mythconverg) and got all "OK"s.

I ran a mysqlcheck on performance_schema as well (the one I had dropped) but just got a blank back as expected.

I shut it down with mysqladmin shutdown -u root -p

Then I get:
mysqladmin: query failed; error: 'Table 'performance_schema.session_variables' doesn't exist'

No idea why it's doing anything with that database on shutdown, or why it's doing anything with that database at /all/.

I still get no errors in the error log when trying to start with systemctl start mysql. Just the same errors above from status and journalctl.

To check the my.cnf, I added "--defaults-file=/etc/mysql/my.cnf" as the first argument to start it up, and I get the same stuff. It starts, but with loads of performance_schema errors and then is ready for connections (and everything seems to work).

The only interesting thing here is that each time I connect, it tells me my password has expired and I need to change it. So I do (set password = password('newpass')) and that works again. Not sure if this is an artifact of starting it manually like this or what.

astrogeek · 02-11-2017, 02:23 PM

Quote:

Originally Posted by JohnLocke

At a bit of a loss here. Upgraded to Ubuntu 16.04 from 14.04 in December, which included the change from mysql to mariadb. That hosed a bunch of stuff (upgrade of the database went badly), so I ended up uninstalling both mysql and mariadb, reinstalling, and there was much joy.

The database there is primarily used for mythtv, so I set that up brand new with the new database, and again, there was much joy. The server has been restarted a couple times between then and now.

...

Somewhat interestingly, I can start the service with:
sudo mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --socket=/var/run/mysqld/mysqld.sock

And of course the socket and pid lock all show up fine this way (this is also where the socket is declared to be in my.cnf)

But then I get a bunch of very strange errors in the log file. All kinds of stuff about what appear to be odd definitions in tables. Things like:

Code:

Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(80).
mysql.user has no `Event_priv` column at position 28
[ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).

The fact that the mysql.user table structure does not correspond to the installed version indicates that although you uninstalled and reinstalled the mysql/mariadb package, you (or your package manager) did not perform mysql_upgrade on the existing database tables.

From man mysql_upgrade...

Code:

       mysql_upgrade examines all tables in all databases for incompatibilities with the current version of
       MySQL Server.  mysql_upgrade also upgrades the system tables so that you can take advantage of new
       privileges or capabilities that might have been added.

       mysql_upgrade should be executed each time you upgrade MySQL.

I cannot say how this might relate to the ability to start the server manually but not with systemctl, but that might be down to different start parameters triggering an error.

I would definitely sort out the table structure problems before looking any further.

JohnLocke · 02-11-2017, 02:23 PM

Good grief ... I found at least part of the problem.

mysqld_safe is missing. Just flat out gone. It's nowhere on the system, and now that I've cleared up some of the other issues (somewhat, I suppose), I got this line in journalctl:

Code:

-- Unit mysql.service has begun starting up.
Feb 11 13:17:30 serenity mysql[2170]:  * Starting MariaDB database server mysqld
Feb 11 13:17:30 serenity /etc/init.d/mysql[2199]: /etc/init.d/mysql: line 114: /usr/bin/mysqld_safe: No such file or directory
Feb 11 13:18:00 serenity /etc/init.d/mysql[2500]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Feb 11 13:18:00 serenity /etc/init.d/mysql[2500]: [52B blob data]

This is from trying "systemctl start mysql"

So I check, and sure enough, the lines in /etc/init.d/mysql are:

Code:

if mysqld_status check_alive nowarn; then
   log_progress_msg "already running"
   log_end_msg 0
  else
    # Could be removed during boot
    test -e /var/run/mysqld || install -m 755 -o mysql -g root -d /var/run/mysqld

    # Start MariaDB!
    /usr/bin/mysqld_safe "${@:2}" 2>&1 >/dev/null | $ERR_LOGGER &

So I go looking, and again, mysqld_safe doesn't exist on this system.

Where would it have gone and how do I get it back? I don't want to blow away msyql / mariadb since I would /like/ to recover the tv databases I already have.

astrogeek · 02-11-2017, 02:28 PM

Ah! We were typing at the same time...

Your new information only reinforces that the mysql system table structure does not correspond to the server version.

At this point you may have some data corruption, or be at risk of it. Do you have a complete backup of your data? If not, I would definitely pause and secure a data backup. If you can still start it manually, I would start it and generate a mysqldump before doing anything else.

JohnLocke · 02-11-2017, 02:36 PM

Well:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

So that's not good, but I /can/ explore the database by hand by logging in to mysql. Not sure how to get the data out at this point and no, I was a bad bad admin and didn't make an earlier backup since I got it going

.

If things are this bad, it might just be /necessary/ to blow it all away and start over. But given how much of a PITA it was during the 14.04 > 16.04 upgrade, I'm not sure how best to get rid of everything and then get it installed again. Seems like this should be a relatively easy process, but no

.

astrogeek · 02-11-2017, 02:36 PM

Quote:

Originally Posted by JohnLocke

So I go looking, and again, mysqld_safe doesn't exist on this system.

Where would it have gone and how do I get it back? I don't want to blow away msyql / mariadb since I would /like/ to recover the tv databases I already have.

I take that as a rhetorical question - of course no one here can know where it went.

Obviously, you have a broken package installation, or filesystem corruption, or something rogue running amok on your machine!

Secure that data backup first, then fully consider the options whether to sort it out or simply reinstall.

JohnLocke · 02-11-2017, 02:39 PM

Quote:

Originally Posted by astrogeek

Secure that data backup first, then fully consider the options whether to sort it out or simply reinstall.

Given that the backup isn't likely to work at this point (and not the biggest deal to re-set up everything, I guess) ... any thoughts on how to actually get it removed and installed correctly this time?

astrogeek · 02-11-2017, 02:44 PM

Quote:

Originally Posted by JohnLocke

Well:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

So that's not good, but I /can/ explore the database by hand by logging in to mysql. Not sure how to get the data out at this point and no, I was a bad bad admin and didn't make an earlier backup since I got it going

.

If things are this bad, it might just be /necessary/ to blow it all away and start over. But given how much of a PITA it was during the 14.04 > 16.04 upgrade, I'm not sure how best to get rid of everything and then get it installed again. Seems like this should be a relatively easy process, but no

.

Ugghhh...

I think I would at least make a filesystem backup of /var/lib/mysql, or wherever your data tables are stored.

I think I might also risk running mysql_upgrade at this point (but read the man page and do some online reading first!). I have forgotten in in past and subsequently run it safely long after an upgrade, and have never known it to mess things up.

JohnLocke · 02-11-2017, 02:50 PM

Good thought, but no joy there, either.

It goes through and upgrades the base mysql database and then errors out with:

Upgrading the sys schema.
mysql_upgrade: [ERROR] 1136: Column count doesn't match value count at row 1

That suggests to me that you're right ... upgrade never got run, but now it's corrupted and probably won't run.

As to saving the tables themselves via the files, I'm going to skip that. The only database left in there that I care about is the mythtv one, and while it sucks a /little/ to rebuild it, I'd rather start over and know that I've got something that has no errors clinging to it than keep it just to get around an hour of reconfiguring.

astrogeek · 02-11-2017, 03:30 PM

Sorry to be so slow, had to step away for a bit.

OK, I agree that if setting it all up again would not be a major loss then reinstalling may be the simplest route.

I am not an Ubuntu user so will refrain from commenting on how to go about the reinstall. But with missing files I would certainly consider whether mysql/mariadb are the only things affected.

If you do retain the existing filesystem just locate the DB files and remove those directories before the reinstall, and let the new package build its own from scratch.

I must leave for the afternoon - good luck!

JohnLocke · 02-11-2017, 03:58 PM

Oh no worries on stepping out! I appreciate the help and the confirmation of what I was thinking.

You could definitely be right about more than just mariadb being messed up, but I /suspect/ this all started with mythtv exiting incorrectly. There'd been multiple reboots over the last month prior to this, and to have things just go wonky suddenly is ... unusual. The only weirdness I can point my finger at is myth. Other processes on the box are all working perfectly.

I'll go do a dpkg search and destroy for both maria and mysql just in case somehow old mysql stuff is on there, and like you say, blow away the mysql db files as well.

Much thanks for the quick responses and the assist! This is a weird one!

JohnLocke · 02-11-2017, 05:31 PM

Ok ... an update an in case anyone else comes here looking for answers.

I don't have any answers.

. I've still got no idea why the mysql_safe went away or what got corrupted.

I'm on Ubuntu and I used dpkg --force-all --purge to get rid of every package I found in dpkg --list | grep mysql and dpkg --list | grep maria

Then I did an apt-get -f install

I had enough in there with dependencies (mythtv, php, perl, python, etc) that it went right ahead and installed everything that was originally in the list of dpkg --list | grep mysql. Well, everything that was mysql 5.7 and Ubuntu 16.04 related (some of the older 5.5 and 14.04 packages didn't come back, as they shouldn't have).

I tested it and it was working fine, so I didn't bother installing mariadb on top of it.

I went in and added the mythtv user and database, re-ran all the myth setup, and I'm back up and running.

Astrogeek ... thanks for your help!

astrogeek · 02-11-2017, 11:28 PM

Great! And you are welcome!

When you think this is all finished, please return and mark this thread as SOLVED using the Thread Tools option at top of first post.

Good luck!

gcotnoir · 01-13-2018, 11:13 AM

I had the same problem after an upgrade this morning and I fixed it with apt-get remove --> install --> mysql_secure_installation

Never would of found it without this thread, thanks guys !!