mysql upgrade 5.6.12-147.1 to 5.6.12-150.1 fails :-(

English Applications
1

Hello

Using OS 12.3 x64
I’ve updated mysql from version 5.6.12-147.1 to 5.6.12-150.1 and mysql does not start anymore.
Is the problem that root user has an password set for localhost?
How can I reset the pw without running mysql?
mysqladmin -u root -pCURRENTPASSWORD password ‘’
fails …
How can I do the upgrade using a pasword?

Output of syslog:


Oct 11 09:06:41 gw1 systemd[1]: Starting LSB: Start the MySQL database server...
Oct 11 09:06:42 gw1 mysql[28092]: Will update MySQL now, if you encounter any problems, please read following file:
Oct 11 09:06:42 gw1 mysql[28092]: /usr/share/doc/packages/mysql-community-server/README.SuSE
Oct 11 09:06:42 gw1 mysql[28092]: Running protected MySQL...
Oct 11 09:06:43 gw1 mysql[28092]: Upgrading MySQL...
Oct 11 09:06:49 gw1 mysql[28092]: /usr/bin/mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
Oct 11 09:06:49 gw1 mysql[28092]: FATAL ERROR: Upgrade failed
Oct 11 09:06:49 gw1 mysql[28092]: Looking for 'mysql' as: /usr/bin/mysql
Oct 11 09:06:49 gw1 mysql[28092]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Oct 11 09:06:49 gw1 mysql[28092]: Running 'mysqlcheck' with connection arguments: '--socket=/var/tmp/mysql-protected.dvQooA/mysql.sock'
Oct 11 09:06:49 gw1 mysql: Last message 'Running 'mysqlcheck'' repeated 1 times, suppressed by syslog-ng on gw1.swiss-wireless.com.ar
Oct 11 09:06:49 gw1 mysql[28092]: mysql.columns_priv                                 OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.db                                           OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.event                                        OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.func                                         OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.general_log                                  OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.help_category                                OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.help_keyword                                 OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.help_relation                                OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.help_topic                                   OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.innodb_index_stats                           OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.innodb_table_stats                           OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.ndb_binlog_index                             OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.plugin                                       OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.proc                                         OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.procs_priv                                   OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.proxies_priv                                 OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.servers                                      OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.slave_master_info                            OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.slave_relay_log_info                         OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.slave_worker_info                            OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.slow_log                                     OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.tables_priv                                  OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.time_zone                                    OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.time_zone_leap_second                        OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.time_zone_name                               OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.time_zone_transition                         OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.time_zone_transition_type                    OK
Oct 11 09:06:49 gw1 mysql[28092]: mysql.user                                         OK
Oct 11 09:06:49 gw1 mysql[28092]: Running 'mysql_fix_privilege_tables'...
Oct 11 09:06:49 gw1 mysql[28092]: Running 'mysqlcheck' with connection arguments: '--socket=/var/tmp/mysql-protected.dvQooA/mysql.sock'
Oct 11 09:06:49 gw1 mysql[28092]: ..failed
Oct 11 09:06:51 gw1 systemd[1]: mysql.service: control process exited, code=exited status=1
Oct 11 09:06:51 gw1 systemd[1]: Failed to start LSB: Start the MySQL database server.
Oct 11 09:06:51 gw1 systemd[1]: Unit mysql.service entered failed state

/var/log/mysql/mysqld-upgrade-run.log:


131011 08:09:20 mysqld_safe mysqld from pid file /var/tmp/mysql-protected.xPOpSl/mysqld.pid ended
131011 09:06:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-10-11 09:06:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-10-11 09:06:42 28474 [Note] Plugin 'FEDERATED' is disabled.
2013-10-11 09:06:42 28474 [ERROR] Function 'archive' already exists
2013-10-11 09:06:42 28474 [ERROR] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2013-10-11 09:06:42 28474 [Note] InnoDB: The InnoDB memory heap is disabled
2013-10-11 09:06:42 28474 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-10-11 09:06:42 28474 [Note] InnoDB: Compressed tables use zlib 1.2.7
2013-10-11 09:06:42 28474 [Note] InnoDB: Using Linux native AIO
2013-10-11 09:06:42 28474 [Note] InnoDB: Using CPU crc32 instructions
2013-10-11 09:06:42 28474 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-10-11 09:06:42 28474 [Note] InnoDB: Completed initialization of buffer pool
2013-10-11 09:06:43 28474 [Note] InnoDB: Highest supported file format is Barracuda.
2013-10-11 09:06:43 28474 [Note] InnoDB: 128 rollback segment(s) are active.
2013-10-11 09:06:43 28474 [Note] InnoDB: Waiting for purge to start
2013-10-11 09:06:43 28474 [Note] InnoDB: 5.6.12 started; log sequence number 1626143
2013-10-11 09:06:43 28474 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2013-10-11 09:06:43 28474 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2013-10-11 09:06:43 28474 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.12'  socket: '/var/tmp/mysql-protected.dvQooA/mysql.sock'  port: 0  openSUSE package
2013-10-11 09:06:49 28474 [Note] /usr/sbin/mysqld: Normal shutdown

2013-10-11 09:06:49 28474 [Note] Giving 0 client threads a chance to die gracefully
2013-10-11 09:06:49 28474 [Note] Shutting down slave threads
2013-10-11 09:06:49 28474 [Note] Forcefully disconnecting 0 remaining clients
2013-10-11 09:06:49 28474 [Note] Binlog end
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'partition'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'BLACKHOLE'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'ARCHIVE'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_METRICS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_CMP'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'INNODB_TRX'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'InnoDB'
2013-10-11 09:06:49 28474 [Note] Shutting down plugin 'InnoDB'
2013-10-11 09:06:49 28474 [Note] InnoDB: FTS optimize thread exiting.
2013-10-11 09:06:49 28474 [Note] InnoDB: Starting shutdown...
2013-10-11 09:06:51 28474 [Note] InnoDB: Shutdown completed; log sequence number 1626153
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'MEMORY'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'MyISAM'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'MRG_MYISAM'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'CSV'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'sha256_password'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'mysql_old_password'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'mysql_native_password'
2013-10-11 09:06:51 28474 [Note] Shutting down plugin 'binlog'
2013-10-11 09:06:51 28474 [Note] /usr/sbin/mysqld: Shutdown complete
2

For starters,
You should describe where you’re getting your mysql packages, do you have MariaDB or Oracle MySQL installed and if your packages are installed from an openSUSE repo, which one?

I have mariadb installed on my system and with “stable” packages my current MySQL version is only 5.5.29

TSU

3

Since mysqlcheck failed to run with “Access denied”, maybe try to run it manually with the password supplied:

mysqlcheck -A -r -u root -p password

And please post the output of “systemctl status mysql”.

4

I had installed MariaDB until an upgrade failed from openSuse Build service database.
Afer this I installed mysql mysql-community-server from Build service database from scratch.
And now the same did occurs again. On an other mchine I have mariadb from Build service database.
No upgrade failed from 5.5.31 until 5.5.33 and the mysql db has also a password set…
I need this database urgently so I have to install from scratch again now but I can do tests.
I don’t want that this occures again by the next upgrade, so I will save the whole /var/lib/mysql
Is this the only I have to save/restore to get the same state of the mysql db?

mysqlcheck -A -r -u root -p password
Fails with
mysqlcheck: Got error: 2002: Can’t connect to local MySQL server through socket ‘/var/run/mysql/mysql.sock’ (2) when trying to connect


mysql.service - LSB: Start the MySQL database server
          Loaded: loaded (/etc/init.d/mysql)
          Active: failed (Result: exit-code) since Fri, 2013-10-11 12:41:59 ART; 20h ago
         Process: 4621 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

          CGroup: name=systemd:/system/mysql.serviceOct 11 12:41:58  gw1.swiss-wireless.com.ar mysql[4621]: mysql.time_zone_leap_second                         OK
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: mysql.time_zone_name                               OK
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: mysql.time_zone_transition                         OK
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: mysql.time_zone_transition_type                    OK
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: mysql.user                                         OK
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: Running 'mysql_fix_privilege_tables'...
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: Running  'mysqlcheck' with connection arguments:  '--socket=/var/tmp/mysql-protected.aI5V68/mysql.sock'
Oct 11 12:41:58 gw1.swiss-wireless.com.ar mysql[4621]: ..failed
Oct 11 12:41:59 gw1.swiss-wireless.com.ar systemd[1]: Failed to start LSB: Start the MySQL database server.
Oct 11 12:41:59 gw1.swiss-wireless.com.ar systemd[1]: Unit mysql.service entered failed state
5

Maybe try to run “mysql_fix_privilege_tables --password=root_password” manually then?
IMHO the best thing would be to just use the mysql or mariadb that ships with openSUSE instead of installing it from that OBS repo, but I don’t know if that older version would be able to use the database files from the newer version now.

And you could also have a look in /var/log/mysql/mysqld.log, maybe that gives a better clue as to what’s wrong.

6

I just had a look what changed between 147 and 150 and found this:

  • drop /etc/mysql dir, use upstream /etc/my.cnf.d only

maybe that’s affecting you?
Do you have config files in /etc/mysql/?

7

I was not able to fix the problem, so I installed mariadb from orig repos and installed db’s again from scratch :frowning:
and have disabled db repository.
Hope it will not pass again by the next update.
Anyway thanks for help!

8

I never had a problem with the mysql packages from the standard repos (oss and update) in the last 10 years…
(yes, even those databases created back then are still working :wink: )

9

You can uninstall the database and install it again.
But write down, what you have done and which password you want to use then.
That would be a solution.