MySQL 5.7.4 – How to Fix Startup Timeout Issues

MySQLwindows

I recently upgraded the MySQL Server installation on my Windows 8.1 Pro x64 laptop from 5.7.3 to 5.7.4 using the MySQL Installer, and after doing so, attempting to start the MySQL Server service, either using the MySQL Notifier or directly starting it from the services list fails. When the startup fails with the Notifier, it says that the attempted startup timed out, and suggests restarting the MySQL Notifier if the problem continues. I have tried that unsuccessfully and used IOBit uninstaller to uninstall everything on my computer related to MySQL (IOBit uninstaller scans the computer after uninstalling for leftover files. After uninstalling everything, I checked myself for leftover files and found none.). After reinstalling everything, I am still having the issue. What can I do to resolve the issue?

Here's what's generated in the error log when I try to start the server:

2014-04-24T01:06:11.128348Z 0 [Note] Plugin 'FEDERATED' is disabled.
2014-04-24T01:06:11.131351Z 0 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-04-24T01:06:11.132350Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2014-04-24T01:06:11.133350Z 0 [Note] InnoDB: Uses system mutexes
2014-04-24T01:06:11.134350Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-04-24T01:06:11.134350Z 0 [Warning] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2014-04-24T01:06:11.138350Z 0 [Note] InnoDB: Number of pools: 1
2014-04-24T01:06:11.139351Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2014-04-24T01:06:11.142350Z 0 [Note] InnoDB: Initializing buffer pool, total size = 26.0M, instances = 1
2014-04-24T01:06:11.145348Z 0 [Note] InnoDB: Completed initialization of buffer pool
2014-04-24T01:06:11.211309Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2014-04-24T01:06:15.902276Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2014-04-24T01:06:16.087552Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2014-04-24T01:06:16.930884Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2014-04-24T01:06:19.238141Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2014-04-24T01:06:19.242261Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2014-04-24T01:06:20.807291Z 0 [Note] InnoDB: Waiting for purge to start
2014-04-24T01:06:20.869754Z 0 [Note] InnoDB: 5.7.4 started; log sequence number 1232503
2014-04-24T01:06:20.872722Z 0 [ERROR] unknown variable 'innodb_additional_mem_pool_size=2M'
2014-04-24T01:06:20.874714Z 0 [ERROR] Aborting

2014-04-24T01:06:20.876730Z 0 [Note] Binlog end
2014-04-24T01:06:20.878731Z 0 [Note] Shutting down plugin 'partition'
2014-04-24T01:06:20.880729Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2014-04-24T01:06:20.882730Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2014-04-24T01:06:20.884720Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2014-04-24T01:06:20.886721Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2014-04-24T01:06:20.888731Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2014-04-24T01:06:20.890730Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2014-04-24T01:06:20.892730Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2014-04-24T01:06:20.894716Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2014-04-24T01:06:20.896731Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2014-04-24T01:06:20.898731Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2014-04-24T01:06:20.900731Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2014-04-24T01:06:20.902731Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2014-04-24T01:06:20.905733Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2014-04-24T01:06:20.907731Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2014-04-24T01:06:20.909731Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2014-04-24T01:06:20.910731Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2014-04-24T01:06:20.913733Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2014-04-24T01:06:20.915732Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2014-04-24T01:06:20.917732Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2014-04-24T01:06:20.919732Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2014-04-24T01:06:20.921734Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2014-04-24T01:06:20.923732Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2014-04-24T01:06:20.925733Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2014-04-24T01:06:20.928718Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2014-04-24T01:06:20.930732Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2014-04-24T01:06:20.932733Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2014-04-24T01:06:20.934718Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2014-04-24T01:06:20.936718Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2014-04-24T01:06:20.938733Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2014-04-24T01:06:20.940733Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2014-04-24T01:06:20.942733Z 0 [Note] Shutting down plugin 'InnoDB'
2014-04-24T01:06:20.943756Z 0 [Note] InnoDB: FTS optimize thread exiting.
2014-04-24T01:06:20.945246Z 0 [Note] InnoDB: Starting shutdown...
2014-04-24T01:06:24.978930Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1232513
2014-04-24T01:06:24.983930Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2014-04-24T01:06:24.985946Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2014-04-24T01:06:24.987945Z 0 [Note] Shutting down plugin 'ARCHIVE'
2014-04-24T01:06:24.989945Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2014-04-24T01:06:24.990945Z 0 [Note] Shutting down plugin 'MyISAM'
2014-04-24T01:06:24.992944Z 0 [Note] Shutting down plugin 'MEMORY'
2014-04-24T01:06:24.994929Z 0 [Note] Shutting down plugin 'CSV'
2014-04-24T01:06:24.996944Z 0 [Note] Shutting down plugin 'sha256_password'
2014-04-24T01:06:24.997944Z 0 [Note] Shutting down plugin 'mysql_old_password'
2014-04-24T01:06:25.000468Z 0 [Note] Shutting down plugin 'mysql_native_password'
2014-04-24T01:06:25.002470Z 0 [Note] Shutting down plugin 'binlog'
2014-04-24T01:06:25.004954Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: Shutdown complete

Best Answer

Explanation

If you take a look at the manual concerning innodb_additional_mem_pool_size you see that this is a configuration variable, which is relevant in combination with innodb_use_sys_malloc. If you take a look at the manual concerning innodb_use_sys_malloc you see that also this configuration variable was marked as deprecated and removed. If you follow the links further to the detailed explanation you can read:

When InnoDB was developed, the memory allocators supplied with operating systems and run-time libraries were often lacking in performance and scalability. [...]

Today, as multi-core systems have become more widely available, and as operating systems have matured, significant improvements have been made in the memory allocators provided with operating systems. New memory allocators perform better and are more scalable than they were in the past. [...]

You can control whether InnoDB uses its own memory allocator or an allocator of the operating system, by setting the value of the system configuration parameter innodb_use_sys_malloc in the MySQL option file [...]. If set to ON or 1 (the default), InnoDB uses the malloc and free functions of the underlying system rather than manage memory pools itself. [...]

Which basically means that this was a configuration variable which makes just no sense to set anymore in modern environments. Therefore it was deprecated and removed and also the innodb_additional_mem_pool_size, which just existed in support of the own memory management.

Fix

Fix for your problem: Remove the corresponding line with innodb_additional_mem_pool_size and (if it even exists) the line with innodb_use_sys_malloc from your my.cnf.

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

MySQL 5.6 – No Longer Defaulting to /tmp/mysql.sock

The MySQL documentation states that the default path to mysql.sock is /var/lib/mysql/ on RPM distributions. The path is set at compile-time depending on the target OS and can be overridden by configuration or command line, so for CentOS the behavior you are seeing is correct.

This means that the socket path must have originally been set to /tmp by some other mechanism. There are several places this could have been done; likely in the init script /etc/init.d/mysql.server (to set socket=/tmp/mysql.sock on the command line - this can be done in an init script even though it is unsupported) or a local options file. See this page in the documentation for MySQL's precedence order when processing startup options. Entries listed later in the table will override the preceding ones.

It probably isn't possible to trace the sequence of events leading up to the socket path change, but fortunately you can change it via ~/.my.cnf, the user-specific option file.

Best Answer

Explanation

Fix

Related Solutions

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

MySQL 5.6 – No Longer Defaulting to /tmp/mysql.sock

Related Question