-2

Cannot start MySQL server following upgrade

I've just upgraded MySQL from 5.1.59 to 5.6.20 (OSX 10.7) and now the MySQL server will not start.

In MySQL Workbench (v6.1.7), in the startup/shutdown view, upon trying to start the server, the Startup Message Log reads as follows:

Could not open error log file: [Errno 2] The indicated path does not exist
2014-09-06 11:21:52 - Starting server...
2014-09-06 11:21:52 - Executing '/usr/local/mysql/support-files/mysql.server start'
2014-09-06 11:21:52 - Start server: Starting MySQL

2014-09-06 11:21:52 - Start server: .. ERROR! The server quit without updati
2014-09-06 11:21:52 - Start server: ng PID file (/usr/local/mysql/data/new-host-3.home.pid).

2014-09-06 11:21:52 - Server start done.
2014-09-06 11:21:52 - Checking server status...
2014-09-06 11:21:52 - Trying to connect to MySQL...
2014-09-06 11:21:52 - Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) (2002)
2014-09-06 11:21:52 - Assuming server is not running
2014-09-06 11:21:52 - Checking server status...
2014-09-06 11:21:52 - Trying to connect to MySQL...
2014-09-06 11:21:52 - Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) (2002)
2014-09-06 11:21:52 - Assuming server is not running

And in /usr/local/mysql-5.6.20-osx10.7-x86_64/data/new-host-3.home.err reads as follows:

140906 11:43:58 mysqld_safe mysqld from pid file /usr/local/mysql/data/new-host-3.home.pid ended
140906 12:08:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2014-09-06 12:08:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-09-06 12:08:40 3850 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2014-09-06 12:08:40 3850 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2014-09-06 12:08:40 3850 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2014-09-06 12:08:40 3850 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-06 12:08:40 3850 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-06 12:08:40 3850 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-06 12:08:40 3850 [Note] InnoDB: Memory barrier is not used
2014-09-06 12:08:40 3850 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-06 12:08:40 3850 [Note] InnoDB: Using CPU crc32 instructions
2014-09-06 12:08:40 3850 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-06 12:08:40 3850 [Note] InnoDB: Completed initialization of buffer pool
2014-09-06 12:08:40 3850 [Note] InnoDB: Highest supported file format is Barracuda.
2014-09-06 12:08:40 3850 [Note] InnoDB: 128 rollback segment(s) are active.
2014-09-06 12:08:40 3850 [Note] InnoDB: Waiting for purge to start
2014-09-06 12:08:40 3850 [Note] InnoDB: 5.6.20 started; log sequence number 1600957
2014-09-06 12:08:40 3850 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1164d342-35e0-11e4-a780-9b19ad1a780d.
2014-09-06 12:08:40 3850 [Note] Server hostname (bind-address): '*'; port: 3306
2014-09-06 12:08:40 3850 [Note] IPv6 is available.
2014-09-06 12:08:40 3850 [Note]   - '::' resolves to '::';
2014-09-06 12:08:40 3850 [Note] Server socket created on IP: '::'.
2014-09-06 12:08:40 3850 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
140906 12:08:40 mysqld_safe mysqld from pid file /usr/local/mysql/data/new-host-3.home.pid ended
140906 13:01:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2014-09-06 13:01:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-09-06 13:01:13 4213 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2014-09-06 13:01:13 4213 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2014-09-06 13:01:13 4213 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2014-09-06 13:01:13 4213 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-06 13:01:13 4213 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-06 13:01:13 4213 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-06 13:01:13 4213 [Note] InnoDB: Memory barrier is not used
2014-09-06 13:01:13 4213 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-06 13:01:13 4213 [Note] InnoDB: Using CPU crc32 instructions
2014-09-06 13:01:13 4213 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-06 13:01:13 4213 [Note] InnoDB: Completed initialization of buffer pool
2014-09-06 13:01:13 4213 [Note] InnoDB: Highest supported file format is Barracuda.
2014-09-06 13:01:13 4213 [Note] InnoDB: 128 rollback segment(s) are active.
2014-09-06 13:01:13 4213 [Note] InnoDB: Waiting for purge to start
2014-09-06 13:01:13 4213 [Note] InnoDB: 5.6.20 started; log sequence number 1600957
2014-09-06 13:01:13 4213 [Note] Recovering after a crash using mysql-bin
2014-09-06 13:01:13 4213 [Note] Starting crash recovery...
2014-09-06 13:01:13 4213 [Note] Crash recovery finished.
2014-09-06 13:01:13 4213 [Note] Server hostname (bind-address): '*'; port: 3306
2014-09-06 13:01:13 4213 [Note] IPv6 is available.
2014-09-06 13:01:13 4213 [Note]   - '::' resolves to '::';
2014-09-06 13:01:13 4213 [Note] Server socket created on IP: '::'.
2014-09-06 13:01:13 4213 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
140906 13:01:14 mysqld_safe mysqld from pid file /usr/local/mysql/data/new-host-3.home.pid ended

Btw there is a mysql.sock file in /var/mysql, and there is no mysqld process running.

I have not been able to run mysql_upgrade or anything else I've seen suggested to other users with similar symptoms, as I cannot start the server. This is beyond my MySQL consciousness; can someone please explain what has happened and how I can go about repairing this mess?

Any help will be very much appreciated,

svs

closed as off-topic by Sven, Ward, mdpc, Jim B, MadHatter Sep 9 '14 at 7:47

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must be relevant to professional system administration. Server Fault is dedicated to professional system and network administrators. End user and enthusiast questions are off-topic (contact your system administrator or hire a professional to help you out). Please see the Help Center for more information." – Sven, Ward, mdpc, Jim B, MadHatter
If this question can be reworded to fit the rules in the help center, please edit the question.

  • You should be able to repair the mess from your backup. – Iain Sep 6 '14 at 16:21
  • 1
    @Iain I have to admit that initially I thought your response was snide & useless but in light of how convoluted - not to mention widespread - mysql hell seems to be, I'm beginning to wonder why I shouldn't just roll back to v5.1. Pretty irresponsible installer that can create such a mess IMO – user1613163 Sep 6 '14 at 18:05
  • p.s. Pardon my accent - that's the sound of two days of utter frustration and wild goose chase – user1613163 Sep 6 '14 at 18:06
1

According to http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html the --skip-locking feature/option has been removed.

Remove that option from your mysql startup parameters.

  • Thx. I've commented this out from my.cnf but I still can't start the server. Where else is skip-locking indicated? – user1613163 Sep 6 '14 at 17:02
  • Please update your post then with the new error log – MichelZ Sep 6 '14 at 17:03
  • error log updated – user1613163 Sep 6 '14 at 17:35
  • Please see my answer and thank you again for your help. – user1613163 Sep 6 '14 at 20:33
0

After yet another day of googling error after error ad nauseum, and seeing - once again - that mysql installs are endless hell for so many users with infinitely varying conditions - platform, version & install method among them - I decided to throw in the towel on fixing this (I have a project to work on, and have already lost two days on mysql) and instead uninstall 5.6 and then see if the prev version would run. But before doing that I figured it couldn't hurt to just run the installer again so I did ... and now the damn thing starts without error. With so much variance in environments, errors & other sufferers' suggestions, etc., I have no idea what caused all this. If anyone's still listening, I do have a follow-up question: since none of my data is critical (it's all just test dev stuff) I didn't dump it or otherwise prepare it for resurrection in the new version. However, if it's possible to access it without blowing out any of the schemas that the new mysql install created, I would like to give it a shot. Thanks again.

Not the answer you're looking for? Browse other questions tagged or ask your own question.