0

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

2
  • You appear to have a non-standard setup. Your error is saying it's looking for mysql.sock here '/tmp/mysql.sock' but you're saying that this file exists in /var/mysql - which is not mentioned in your error messages? You're using MySQL Workbench to start the server - can you do this manually - i.e. from the command line? – Vérace Sep 6 '14 at 16:39
  • @Vérace I cannot start the server from the CL. In the CL I create a mysql.sock file but it's gone once I try (and fail) to start the server – user1613163 Sep 6 '14 at 17:01
1

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.)

3
  • Thx, but frankly I don't understand what you're saying. I've commented out the skip-locking option in my.cnf and it's the only such file in /etc. Now the error log shows "Can't open the mysql.plugin table. Please run mysql_upgrade to create it." which is useless since I can't run mysql_upgrade, and "Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist" which I'm just beginning to investigate. Thank you – user1613163 Sep 6 '14 at 18:03
  • If you've commented out the skip-locking option, then you shouldn't be getting the error message concerning the --skip-locking option in the startup messages. (I'm saying that mysqld may be reading in the my.cnf file from a location other than /etc/my.cnf, depends on how mysqld was compiled/built.) Some questions for you to investigate: Does the new version of MySQL you installed have InnoDB support compiled in? Did the previous version of MySQL use a "plug-in" version of InnoDB, rather than a compiled in version? – spencer7593 Sep 6 '14 at 18:13
  • Please see my answer above and thank you both again for your help. – user1613163 Sep 6 '14 at 20:31
1
I've just upgraded MySQL from 5.1.59 to 5.6.20 (OSX 10.7) and now the MySQL server will not start.

This is a major problem, right here.

Tables owned by the MySQL server itself (in the mysql database) can change format between releases, and the mysql_upgrade process will expect tables from the "N-1" release and upgrade them to the "N" release format.

In clear, mysql_upgrade for 5.6 will expect to find tables in 5.5 format already, and will fail to upgrade from a 5.1 database.

You are attempting to perform a 5.1 -> 5.6 upgrade in place, in one shot, which is not supported.

The upgrade path is MySQL 5.1 -> MySQL 5.5 -> MySQL 5.6.

An alternate solution is to perform a full mysql dump (in 5.1) followed by a full install and import (in 5.6)

0

You cannot create mysql.sock manually - you specify it on the command line as a parameter to mysqld.

This is what I do

./bin/mysqld --defaults-file=./my.cnf

And start the client with

./bin/mysql -S ./mysql.sock -u root -pdba

Both mysqld and mysql client are launched from the basedir.

This is my my.cnf - it's a bit of a mess, but hopefully it should help. I stress that this is not a production server!

[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.pid
socket      = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64
datadir     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/logfile.txt

#log_error   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
log-error   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log

slow_query_log_file = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysqld.sock
host            = 127.0.0.1
user        = pol
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1 

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.sock
#err-log        = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#pid-file   = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/mysql.pid
#log_error  = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
#log-error  = /home/pol/Downloads/mysql/mysql5.6.19/mysql-5.6.19-linux-x86_64/error.log
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.

1
  • Post your second question as a new one. See here (and links within) on how to ask good questions. – Vérace Sep 7 '14 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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