MySQL Broken On 13.1

Hi guys,

With a vanilla OpenSUSE 12.3, MySQL worked fine. I just did a system upgrade to 13.1 and, while most things kept working or were easily fixed,
I have spent hours trying to get MySQL to work and it still does not. The problem is similar to the one on this thread. I was and am still using only
the official repos (except for VLC).

MySQL simply wont start and is not being helpful with its messages. It seems that an upgrade was triggered as I have both a mysql-upgrade-run.log
and mysql-upgrade.log file but no mysqld.log file (as I would expect). When starting mysql manually I get:

service mysql start
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

Running either of the suggested comments gives the same thing:

mysql.service - LSB: Start the MySQL database server
   Loaded: loaded (/etc/init.d/mysql)
   Active: failed (Result: exit-code) since Thu 2014-08-14 23:05:55 EDT; 2min 32s ago
  Process: 4106 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Aug 14 23:05:53 quasar mysql[4106]: mysql.time_zone_name                               OK
Aug 14 23:05:53 quasar mysql[4106]: mysql.time_zone_transition                         OK
Aug 14 23:05:53 quasar mysql[4106]: mysql.time_zone_transition_type                    OK
Aug 14 23:05:53 quasar mysql[4106]: mysql.user                                         OK
Aug 14 23:05:53 quasar mysql[4106]: Running 'mysql_fix_privilege_tables'...
Aug 14 23:05:53 quasar mysql[4106]: Running 'mysqlcheck' with connection arguments: '--socket=/var/tmp/mysql-protected.vyJBeM/mysql.sock'
Aug 14 23:05:53 quasar mysql[4106]: ..failed
Aug 14 23:05:55 quasar systemd[1]: mysql.service: control process exited, code=exited status=1
Aug 14 23:05:55 quasar systemd[1]: Failed to start LSB: Start the MySQL database server.
Aug 14 23:05:55 quasar systemd[1]: Unit mysql.service entered failed state.

The upgrade log stops abruptly:

Thu Aug 14 23:05:47 EDT 2014 - upgrading MySQL...

Will update MySQL now, if you encounter any problems, please read following file:
Running protected MySQL... 
Upgrading MySQL... 
   Looking for 'mysql' as: /usr/bin/mysql
   Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
   Running 'mysqlcheck' with connection arguments: '--socket=/var/tmp/mysql-protected.vyJBeM/mysql.sock' 
   Running 'mysqlcheck' with connection arguments: '--socket=/var/tmp/mysql-protected.vyJBeM/mysql.sock' 
   mysql.columns_priv                                 OK
   mysql.db                                           OK
   mysql.event                                        OK
   mysql.func                                         OK
   mysql.general_log                                  OK
   mysql.help_category                                OK
   mysql.help_keyword                                 OK
   mysql.help_relation                                OK
   mysql.help_topic                                   OK                                         OK
   mysql.innodb_index_stats                           OK
   mysql.innodb_table_stats                           OK
   mysql.ndb_binlog_index                             OK
   mysql.plugin                                       OK
   mysql.proc                                         OK
   mysql.procs_priv                                   OK
   mysql.proxies_priv                                 OK
   mysql.servers                                      OK
   mysql.slave_master_info                            OK
   mysql.slave_relay_log_info                         OK
   mysql.slave_worker_info                            OK
   mysql.slow_log                                     OK
   mysql.tables_priv                                  OK
   mysql.time_zone                                    OK
   mysql.time_zone_leap_second                        OK
   mysql.time_zone_name                               OK
   mysql.time_zone_transition                         OK
   mysql.time_zone_transition_type                    OK
   mysql.user                                         OK
   Running 'mysql_fix_privilege_tables'...
   Running 'mysqlcheck' with connection arguments: '--socket=/var/tmp/mysql-protected.vyJBeM/mysql.sock' 

Nothing has helped so far. I have done a forced update, uninstalled and reinstalled and still the same same error occurs.

Any ideas how to solve this? If you need more information to diagnose this, please let me know.

Thanks in advance,

  • Itai

There seems to be problems with the MySQL database upgrade from 12.3 to 13.1.
Try this:

sudo rm /var/lib/mysql/.run-mysql_upgrade

This should skip the upgrade, and it should start then.

Or switch to MariaDB, which is a drop-in replacement for MySQL.

Thank you very much!

Removing the file worked and its a perfect answer: simple, effective and unambiguous!

Not sure how MariaDB is a drop-in replacement though. Does it use the same DB? Do PHP mysql_* functions work with it? Meaning is there
anything to convert or port, even if trivially?

Thanks again!

  • Itai

MariaDB is, as the name says, a drop-in replacement for MySQL - you don’t need to change anything when you install it.

Naturally you need to make backups of your database before switching over (for obvious reasons) - in fact you might be running MariaDB now if you did a straight dup from 12.3 to 13.1.

What does your;
mysql -V



Meaning is there
anything to convert or port, even if trivially?

It is a completely compatible drop-in replacement by the original MySQL author (at least 5.5).
Even the executables have the same names.

It also uses the same database files in the same directories, but you might experience problems there if the versions differ of course.

If you upgraded MySQL to 5.6 now, you probably cannot just “downgrade” to MariaDB 5.5 any more (which is a drop-in replacement for MySQL 5.5 obviously). The same as you cannot downgrade to MySQL 5.5.
I had to remove the files ib_logfile0 and ib_logfile1 in /var/lib/mysql/ to do that, but that posed no problem whatsoever (in particular no dataloss).

But especially when the versions differ (and the latest versions have diverged considerably AFAIK), it’s better to just dump the database into a backup file (with mysqldump e.g.) and import that after the switch.

That doesn’t mean that you should switch though. If mysql works for you, just use it.
I switched because it took too much RAM on my system with the default settings and I didn’t want to bother fiddling around with them.

If you’ve done an upgrade to 5.6, it may be possible to use MariaDB 10.x from the repositories - I run it on a system that currently gets… let’s just say a few hits (48h up, 29 million queries) without issues.

As Wolfi said, it’s better to dump the entire db and reimport it. Not only because it’s safe, it also nicely defragments the db.

I don’t think so.
When I upgraded to 13.1, MySQL was not replaced with MariaDB. I had to do it manually.

If you installed 12.3 fresh, you already got MariaDB though. It was the default on 12.3 already.

Well, this wasn’t available yet when I made the switch… :wink:

But as I said, just deleting /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 worked for me.
If you don’t use InnoDB, this should not even be necessary of course.

Yeah but I completely agree with you that a dump/import is much safer and a better option if you want to switch over to MariaDB especially if you want to switch to one of the alternative DB engines.

mysql Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using EditLine wrapper

Looks like it’s MySQL. It’s working now, so I wont touch anything, but it’s good to know there is something else that could work.