MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root

mac os xMySQLmysql-5.6

I'm having serious problems with a MySQL 5.6 instance on a Mac Server.

We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the /data directory and the my.cnf file as well. However, when setting an init_file to restore the previous password and its permissions. So we created a text file with this content:

UPDATE mysql.user SET Password=PASSWORD('myOriginalPassword') WHERE User='root';
FLUSH PRIVILEGES;

We have double checked the init_file permissions (we even gave it a chmod 777, to make sure it worked), but something is not working. When we run mysqld_safe --init_file=/usr/local/mysql/bin/init_file it tries to start the server, but then it shuts itself down. The log is as follows:

130308 13:23:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-08 13:23:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-08 13:23:53 18979 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2013-03-08 13:23:53 18979 [Note] Plugin 'FEDERATED' is disabled.
2013-03-08 13:23:53 18979 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-08 13:23:53 18979 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-03-08 13:23:53 18979 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-08 13:23:53 18979 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-08 13:23:53 18979 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-03-08 13:23:53 18979 [Note] InnoDB: Completed initialization of buffer pool
2013-03-08 13:23:53 18979 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-08 13:23:53 18979 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-08 13:23:53 18979 [Note] InnoDB: Waiting for purge to start
2013-03-08 13:23:53 18979 [Note] InnoDB: 1.2.10 started; log sequence number 19440939
2013-03-08 13:23:53 18979 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'
2013-03-08 13:23:53 18979 [ERROR] Aborting

2013-03-08 13:23:53 18979 [Note] Binlog end
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'partition'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_METRICS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCKS'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_TRX'
2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'InnoDB'
2013-03-08 13:23:53 18979 [Note] InnoDB: FTS optimize thread exiting.
2013-03-08 13:23:53 18979 [Note] InnoDB: Starting shutdown...
2013-03-08 13:23:54 18979 [Note] InnoDB: Shutdown completed; log sequence number 19440949
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'BLACKHOLE'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'ARCHIVE'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MRG_MYISAM'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MyISAM'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MEMORY'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'CSV'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'sha256_password'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_old_password'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_native_password'
2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'binlog'
2013-03-08 13:23:54 18979 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

130308 13:23:54 mysqld_safe mysqld from pid file /usr/local/mysql/data/server.myServerName.com.pid ended

The problem may definitely come from the [Error] lines, but as we haven't used the –skip-locking, it's just confusing. If we try to log into mysql using mysql -r -p and set the password we used, it can't connect to the server, as it is not started. Any ideas where this problem may come from?

Best Answer

I just noticed you have lower_case_table_names defaulted to 2.

According to the MySQL Documentation on lower_case_table_names

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you set this variable to 0 on such a system and access MyISAM tablenames using different lettercases, index corruption may result. On Windows the default value is 1. On Mac OS X, the default value is 2.

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

Since you do not set anything, MySQL has set lower_case_table_names to 2, which is very bad for InnoDB. This at least explains why InnoDB won't start up.

You will have set lower_case_table_names to 1 and try again.

Give it a Try !!!

Related Question