Bug #79836 MySQL57 (service) wont start
Submitted: 4 Jan 2016 17:17 Modified: 11 Jan 2016 23:46
Reporter: Patrick Pope Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.7.10 OS:Windows (Microsoft Windows 10 Home)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[4 Jan 2016 17:17] Patrick Pope
Description:
After I have just installed MySQL 5.7.120 - Despite every effort, including 
    1) Manual starting
    2) re-installation 
    3) and a manual inspection of "my.ini" 
I cannot get MySQL57 (service) to start. 

I note that the properties for this service state 
    1) path to executable as:
          "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" 
           --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" 
           MySQL57
       and I find mysqld.exe and "my.ini" in the expected places, and task 
       manager does list MySQL57 as a service; when I attempt a manual start        
       of this service the response message is 
       "service started then stopped".
   2) the properties also has an EMPTY start up parameters box.

If I try MySQL workbench 6.3.6 to start this service i get the following report:
================================================================
2016-01-04 16:59:14 - Workbench will use cmd shell commands to start/stop this instance
Could not open error log file: [Errno 2] No such file or directory: 'C:\\Users\\Patrick\\Documents\\_MYSQL\\LOGS\\MYSQL.ERR.log'
2016-01-04 17:04:22 - Starting server...
2016-01-04 17:04:29 - Server start done.
2016-01-04 17:04:30 - Checking server status...
2016-01-04 17:04:30 - Trying to connect to MySQL...
2016-01-04 17:04:30 - Can't connect to MySQL server on '127.0.0.1' (10061) (2003)
2016-01-04 17:04:30 - Assuming server is not running
2016-01-04 17:04:30 - Checking server status...
2016-01-04 17:04:30 - Trying to connect to MySQL...
2016-01-04 17:04:30 - Can't connect to MySQL server on '127.0.0.1' (10061) (2003)
2016-01-04 17:04:30 - Assuming server is not running
=======================================================

MySQL57 is configured to use port 3306.

SO: 
why wont the service start ?
why does the system not open th error log file; this is defined in my.ini as:
# Error Logging.
log-error="C:\Users\Patrick\Documents\_MYSQL\LOGS\MYSQL.ERR.log"

How to repeat:
MySQL57 NEVER starts
[5 Jan 2016 9:03] Chiranjeevi Battula
Hello  Patrick Pope,

Thank you for the bug report.
I could not see any issue with MySQL 5.7.10 installation and configuration.

Manual for Starting MySQL as a Windows Service:
http://dev.mysql.com/doc/refman/5.7/en/windows-start-service.html

If still if you are having same issue please do completely uninstall MySQL and reinstall it.

Please download MySQL latest version.
https://dev.mysql.com/downloads/mysql/

Thanks,
Chiranjeevi.
[5 Jan 2016 15:31] Patrick Pope
Before trying a re-install i ran the following DOS (admin) command:
>>mysqld.exe --verbose --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

This generated the following output in an arror log:

2016-01-05T15:14:57.845235Z 0 [ERROR] --verbose is for use with --help; did you mean --log-error-verbosity?
...
2016-01-05T15:14:59.345791Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-01-05T15:14:59.363041Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-01-05T15:14:59.443941Z 0 [Note] InnoDB: 5.7.10 started; log sequence number 1210141
2016-01-05T15:14:59.463216Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\Program Files\MySQL\MySQL Server 5.7\data\ib_buffer_pool
2016-01-05T15:14:59.463216Z 0 [Note] InnoDB: not started
2016-01-05T15:14:59.463216Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-01-05T15:14:59.510094Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160105 15:14:59
mysqld: Table 'mysql.plugin' doesn't exist
2016-01-05T15:14:59.525720Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2016-01-05T15:14:59.547363Z 0 [ERROR] unknown variable 'defaults-file=C:\ProgramData\MySQL\MySQL Server 5.7\my.ini'
2016-01-05T15:14:59.578875Z 0 [ERROR] Aborting

2016-01-05T15:14:59.578875Z 0 [Note] Binlog end
2016-01-05T15:14:59.594502Z 0 [Note] Shutting down plugin 'ngram'
2016-01-05T15:14:59.610128Z 0 [Note] Shutting down plugin 'partition'
2016-01-05T15:14:59.625755Z 0 [Note] Shutting down plugin 'BLACKHOLE'
...
2016-01-05T15:14:59.910492Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-01-05T15:14:59.926119Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-01-05T15:14:59.946763Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2016-01-05T15:14:59.948263Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2016-01-05T15:14:59.963898Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-01-05T15:14:59.979524Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-01-05T15:15:00.026401Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
...
2016-01-05T15:15:00.163794Z 0 [Note] Shutting down plugin 'InnoDB'
2016-01-05T15:15:00.179421Z 0 [Note] InnoDB: FTS optimize thread exiting.
2016-01-05T15:15:00.195048Z 0 [Note] InnoDB: Starting shutdown...
2016-01-05T15:15:00.312652Z 0 [Note] InnoDB: Dumping buffer pool(s) to C:\Program Files\MySQL\MySQL Server 5.7\data\ib_buffer_pool
2016-01-05T15:15:00.364013Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 160105 15:15:00
2016-01-05T15:15:01.047594Z 0 [Note] InnoDB: Shutdown completed; log sequence number 1210160
2016-01-05T15:15:01.054102Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016-01-05T15:15:01.100988Z 0 [Note] Shutting down plugin 'MEMORY'
2016-01-05T15:15:01.116614Z 0 [Note] Shutting down plugin 'CSV'
2016-01-05T15:15:01.132239Z 0 [Note] Shutting down plugin 'sha256_password'
2016-01-05T15:15:01.132239Z 0 [Note] Shutting down plugin 'mysql_native_password'
2016-01-05T15:15:01.154387Z 0 [Note] Shutting down plugin 'binlog'
2016-01-05T15:15:01.170019Z 0 [Note] mysqld.exe: Shutdown complete

SO THE PROBLEM SUMMARY IS:
   [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

I will submitt this comment, then try reinstalling MySQL57 as advised and see if this problem persists.
[5 Jan 2016 15:37] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.
[5 Jan 2016 16:35] Patrick Pope
De-installed mysql server; re=started PC and confirmed no MYSQL service present

Reinstalled mysql server (5.7.10 - x64) using: mysql-installer-web-community-5.7.10.0.msi (downloaded 4-jan-2016)
    >> install directory = C:\Program Files\MySQL\MySQL Server 5.7
	>> data directory = C:\ProgramData\MySQL\MySQL Server 5.7

Configured as 
	>> Type = development machine
	>> TCP/IP (3306)
	>> User account = Patrick (on localHost)
	>> configued as = Wndows service
	>> name = MYSQL57
	>> start at=startup
	>> standard system account
	>> logs directory = "C:\Users\Patrick\Documents\_MYSQL\LOGS" (for all logs)
	>> server id = 1

During the "Apply Server Configuration" step all steps suceeded until starting server step - server would not start so cancelled configuration.
[5 Jan 2016 16:41] Patrick Pope
The oracle developers insist that this is not a bug; however I see it as an installtion bug. For the moment I am going to continue to report it and investigate.

I have had to devote at least a day to trying to sucessfully install and run MySQL server. It should not be this hard. TWICE I have found the installer stops at the "starting server" step reporting that this is taking longer than expected. I have given it more that 109 minutes to start. If it does take longer than this then perhaps the installer should show a progress bar.
[5 Jan 2016 16:42] Patrick Pope
oops I meant to say 10 minutes to start
[5 Jan 2016 16:56] MySQL Verification Team
Please open a command prompt (Run as Administrator) then go to the install bin directory and issue the below command:

mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" --standalone --console

print here eventual warnings/error messages.

Thanks
[6 Jan 2016 13:53] Patrick Pope
Thanks Miguel - starting from an 'Admin' console and using --console --standalone gives the following output to the console screen:
---------------------------------------------------------
2016-01-06T13:42:21.671850Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-06T13:42:21.671850Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2016-01-06T13:42:21.693599Z 0 [Note] mysqld.exe (mysqld 5.7.10-log) starting as process 116 ...
2016-01-06T13:42:21.840755Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2016-01-06T13:42:21.856380Z 0 [Note] InnoDB: Uses event mutexes
2016-01-06T13:42:21.872007Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2016-01-06T13:42:21.872007Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-06T13:42:21.925417Z 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 8 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2016-01-06T13:42:21.941044Z 0 [Note] InnoDB: Number of pools: 1
2016-01-06T13:42:21.956670Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2016-01-06T13:42:22.111135Z 0 [Note] InnoDB: Initializing buffer pool, total size = 8M, instances = 1, chunk size = 8M
2016-01-06T13:42:22.128663Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-01-06T13:42:22.652517Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-06T13:42:22.700095Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2497576
2016-01-06T13:42:22.718123Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2497585
2016-01-06T13:42:22.731145Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2497585
2016-01-06T13:42:22.748672Z 0 [Note] InnoDB: Database was not shutdown normally!
2016-01-06T13:42:22.763196Z 0 [Note] InnoDB: Starting crash recovery.
2016-01-06T13:42:23.039646Z 0 [Note] InnoDB: Last MySQL binlog file position 0 29965, file name MYSQL.000001
2016-01-06T13:42:23.973665Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016-01-06T13:42:23.984683Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-01-06T13:42:24.034765Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-01-06T13:42:24.513260Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2016-01-06T13:42:24.639965Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-01-06T13:42:24.660999Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-01-06T13:42:24.714085Z 0 [Note] InnoDB: Waiting for purge to start
2016-01-06T13:42:24.798222Z 0 [Note] InnoDB: 5.7.10 started; log sequence number 2497585
2016-01-06T13:42:24.818755Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\ProgramData\MySQL\MySQL Server 5.7\Data\ib_buffer_pool
2016-01-06T13:42:24.822262Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-01-06T13:42:24.853813Z 0 [Note] InnoDB: not started
2016-01-06T13:42:25.093203Z 0 [Note] Recovering after a crash using C:/Users/Patrick/Documents/_MYSQL/LOGS/MYSQL
2016-01-06T13:42:25.107726Z 0 [Note] Starting crash recovery...
2016-01-06T13:42:25.132767Z 0 [Note] Crash recovery finished.
2016-01-06T13:42:25.155804Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160106 13:42:25
2016-01-06T13:42:25.602531Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2016-01-06T13:42:25.632581Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016-01-06T13:42:25.646602Z 0 [Note] IPv6 is available.
2016-01-06T13:42:25.664634Z 0 [Note]   - '::' resolves to '::';
2016-01-06T13:42:25.672645Z 0 [Note] Server socket created on IP: '::'.
2016-01-06T13:42:28.149451Z 0 [Note] Event Scheduler: Loaded 0 events
2016-01-06T13:42:28.167481Z 0 [Note] mysqld.exe: ready for connections.
Version: '5.7.10-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
---------------------------------------------------------

The console command has not finished indicating that mysqld is still running AND mysql workbench is able to connect to mysql57.

The MySQL service is still in the state automatic/network service/stopped
[6 Jan 2016 13:57] Patrick Pope
The properties for MySQL57 service are:
     executable path = 
            "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MYSQL57
     start parameters = <empty>
[6 Jan 2016 18:28] Patrick Pope
Miguel,

your workaround (--standalone --console) has allowed me to start mysql server. I have been able to connect to it with mysql.exe and create a table, after I had used mysql workbench to set myself with admin rights.

What we have here is, after installation, a chicken and egg situation:
     >>>> [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
    -- mysql service wont run because it needs the mysql.plugin.table.
    -- mysql_upgrade wont run because it needs mysql server to be running

using your proposed workaround (launching server manually with --standalone)
is the first step to breaking the dependency. The next step will be for me to use mysql_upgrade to create the mysql.plugins table. So I need to learn how to do this. Cheers Patrick
[6 Jan 2016 21:41] MySQL Verification Team
Thank you for the feedback. If the server is stopped and you issue from an administrator command prompt:

NET START mysql57

the service start success?.
[7 Jan 2016 13:38] Patrick Pope
Hi Miguel,

this is how my PC resonds:

C:\Program Files\MySQL\MySQL Server 5.7\bin>NET START MYSQL57
The MYSQL57 service is starting.
The MYSQL57 service could not be started.

The service did not report an error.

More help is available by typing NET HELPMSG 3534.
[7 Jan 2016 14:21] Patrick Pope
Miguel,

I have started MySQL Server 5.7 again as a stand-alone and queried it, with the following response:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sudoku             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
...
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
...
| plugin                    |
...
| proc                      |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM plugin;
ERROR 1146 (42S02): Table 'mysql.plugin' doesn't exist
mysql>
[7 Jan 2016 14:26] Patrick Pope
further investigated the 'mysql' data bases and found

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

***BUT*** when i use the describe command on each table I find:

ERROR 1146 (42S02): Table 'mysql.engine_cost' doesn't exist
ERROR 1146 (42S02): Table 'mysql.gtid_executed' doesn't exist
ERROR 1146 (42S02): Table 'mysql.help_category' doesn't exist
ERROR 1146 (42S02): Table 'mysql.help_keyword' doesn't exist
ERROR 1146 (42S02): Table 'mysql.help_relation' doesn't exist
ERROR 1146 (42S02): Table 'mysql.help_topic' doesn't exist
ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02): Table 'mysql.plugin' doesn't exist
ERROR 1146 (42S02): Table 'mysql.server_cost' doesn't exist
ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist
ERROR 1146 (42S02): Table 'mysql.slave_master_info' doesn't exist
ERROR 1146 (42S02): Table 'mysql.slave_relay_log_info' doesn't exist
ERROR 1146 (42S02): Table 'mysql.slave_worker_info' doesn't exist
ERROR 1146 (42S02): Table 'mysql.time_zone' doesn't exist
ERROR 1146 (42S02): Table 'mysql.time_zone_leap_second' doesn't exist
ERROR 1146 (42S02): Table 'mysql.time_zone_name' doesn't exist
ERROR 1146 (42S02): Table 'mysql.time_zone_transition' doesn't exist
ERROR 1146 (42S02): Table 'mysql.time_zone_transition_type' doesn't exist

HENCE MY ARGUMENT THAT THIS IS AN INSTALLATION BUG.

over to you Miguel.

Cheers,
Patrick
[7 Jan 2016 19:32] MySQL Verification Team
Your install it was a fresh one or you did an upgrade (if yes over which version).?
Also please print the output of sc qc mysql57..similar to:

C:\dbs>sc qc mysql56
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: mysql56
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 3   DEMAND_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : MySQL56
        DEPENDENCIES       :
        SERVICE_START_NAME : NT AUTHORITY\NetworkService

C:\dbs>
[8 Jan 2016 11:21] Patrick Pope
Miguel,

It was a fresh install.

After executing sc qc mysql57 i get:
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: mysql57
        TYPE               : 10  WIN32_OWN_PROCESS 
        START_TYPE         : 2   AUTO_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MYSQL57
        LOAD_ORDER_GROUP   : 
        TAG                : 0
        DISPLAY_NAME       : MYSQL57
        DEPENDENCIES       : 
        SERVICE_START_NAME : NT AUTHORITY\NetworkService

Patrick
[8 Jan 2016 12:07] Patrick Pope
Hi Miguel,
I have just tried starting MYSQL57 from the windows services window. I get a popup message:
    "the service started then stopped"

Patrick
[9 Jan 2016 23:16] MySQL Verification Team
Please attach the host_name.err file which is located generally in data directory i.e:

c:\>dir "ProgramData\MySQL\MySQL Server 5.6\data\URALBR.err"
 Volume in drive C has no label.
 Volume Serial Number is 0C1C-80A8

 Directory of c:\ProgramData\MySQL\MySQL Server 5.6\data

08/01/2016  21:55           190.674 URALBR.err
               1 File(s)        190.674 bytes
               0 Dir(s)  317.926.473.728 bytes free

To check an error message after the fail of service start. Thanks.
[11 Jan 2016 23:43] Patrick Pope
Hi Miguel,
I have just applied a complete CLEAN install (de-installed all MYSQL components, deleted all MYSL directories). Now MySQL57 service does start automatically. 

Thanks for your help and advice,

Patrick
[11 Jan 2016 23:46] MySQL Verification Team
Thank you for the feedback.
[5 Feb 2017 21:00] Mark Anderson
Our experience here is "exactly" as described by Patrick.
[27 Apr 2017 12:58] Han Xiong
I had the same problem and after a fresh installation, the server will start normally but this problem keeps coming back after a while (about one month). Any solutions for this problem?
[27 Jun 2017 7:27] Richard Nilsson
I also have the exact same problem (using default ini file).Service isn't started. Trying to start it manually give the exact same problems. Already re-installed it once, but will do it a second time. Never had this kind of problems with older MySQL installations.