Mysql – Cannot start MySQL server following upgrade

MySQL

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:30:57 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2014-09-06 11:30:57 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 11:30:57 2575 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2014-09-06 11:30:57 2575 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2014-09-06 11:30:57 2575 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2014-09-06 11:30:57 2575 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-06 11:30:57 2575 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-06 11:30:57 2575 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-06 11:30:57 2575 [Note] InnoDB: Memory barrier is not used
2014-09-06 11:30:57 2575 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-06 11:30:57 2575 [Note] InnoDB: Using CPU crc32 instructions
2014-09-06 11:30:57 2575 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-06 11:30:57 2575 [Note] InnoDB: Completed initialization of buffer pool
2014-09-06 11:30:57 2575 [Note] InnoDB: Highest supported file format is Barracuda.
2014-09-06 11:30:57 2575 [Note] InnoDB: 128 rollback segment(s) are active.
2014-09-06 11:30:57 2575 [Note] InnoDB: Waiting for purge to start
2014-09-06 11:30:57 2575 [Note] InnoDB: 5.6.20 started; log sequence number 1600927
2014-09-06 11:30:57 2575 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'
2014-09-06 11:30:57 2575 [ERROR] Aborting

2014-09-06 11:30:57 2575 [Note] Binlog end
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'partition'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_METRICS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_CMPMEM'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_CMP'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_LOCKS'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'INNODB_TRX'
2014-09-06 11:30:57 2575 [Note] Shutting down plugin 'InnoDB'
2014-09-06 11:30:57 2575 [Note] InnoDB: FTS optimize thread exiting.
2014-09-06 11:30:57 2575 [Note] InnoDB: Starting shutdown...
2014-09-06 11:30:59 2575 [Note] InnoDB: Shutdown completed; log sequence number 1600937
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'BLACKHOLE'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'ARCHIVE'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'MRG_MYISAM'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'MyISAM'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'MEMORY'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'CSV'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'sha256_password'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'mysql_old_password'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'mysql_native_password'
2014-09-06 11:30:59 2575 [Note] Shutting down plugin 'binlog'
2014-09-06 11:30:59 2575 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

140906 11:30:59 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

Best Answer

You have an unsupported option specified, either in the my.cnf file, or in the startup script. Likely, this option was valid with the previous version of MySQL you were running, and this option was deprecated in a later release (which would likely have produced a [WARNING] during startup), and then later was made invalid in the release you are now trying to run.

Given that this error message is the last one before the "Aborting" message line, this may be the problem.

Excerpt from the startup messsages:

[ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'
[ERROR] Aborting

Likely, the existing my.cnf configuration file was retained; the upgrade didn't make any changes to this file.

The "fix" would be to locate that option in the my.cnf file, and remove it, replace it with whatever the newer replacement syntax is.

There's a sequence of directories where mysqld looks for the my.cnf file, depending on release and OS ( /etc/my.cnf, /etc/mysql/my.cnf, mysql installation directory, etc. (We run MySQL on Linux, not familiar with differences on OSX).

Note that options can also be specified on the command line that starts mysqld; it's much less likely, but it's possible that this option is being supplied from mysqld_safe script.)

Related Question