MySQL Bugs: #90199: During update to 5.6.39, check and upgrade database fails- server not running

Bug #90199	During update to 5.6.39, check and upgrade database fails- server not running
Submitted:	23 Mar 2018 14:54	Modified:	29 May 2018 10:20
Reporter:	Marc Long	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Windows	Severity:	S1 (Critical)
Version:	5.6.39	OS:	Windows (2010, 2012 R2 server, 2016 server)
Assigned to:		CPU Architecture:	Any (64 Bit)

Description:
During update from 5.6.36 to 5.6.39, the check and upgrade database process fails- the failure message says that the server is not running, however the server IS running and I can connect via Workbench and query without issue. Restarting the MySQL Server service does not help. This forces me to skip the check and upgrade portion to complete the update. If I try to upgrade database via cmd prompt running as administrator I get:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql_upgrade --protocol=tcp -P 3307
Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

I also noticed these errors in the MySQL Error log:
InnoDB: .\ibdata1 can't be opened in read-write mode
InnoDB: The system tablespace must be writable!
Plugin 'InnoDB' init function returned error.
Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Unknown/unsupported storage engine: INNODB
Aborting

Previous MySQL minor build updates have always gone smoothly, this is the first time I've had issues like these. This is happening on multiple machines. Please assist.

Thanks,
Marc

How to repeat:
Run MySQL Update on a 5.6.36 MySQL Server on Windows Server, update to 5.6.39. Also try to run C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql_upgrade --protocol=tcp -P 3307

Suggested fix:
No clue

Also, restarting the Windows server does not help.

typo'd the vsn in the synopsis and corrected it.

Hello Marc Long,

Thank you for the bug report.
Verified as described on MySQL for Windows with 5.6.39 version.

Thanks,
Chiranjeevi.

Thanks looking into this, Chiranjeevi. What would you advise re: the following:
1) is there a workaround for this? 2) should we roll back servers we have upgraded? 3) is there a way to identify which schema needed to be updated during the configure database part of the upgrade so we can assess the risk of remaining on 5.6.39?

Thanks for your time.
Marc

Hi,

Just looking for an answer to the following:

Thanks looking into this, Chiranjeevi. What would you advise re: the following:
1) is there a workaround for this? 2) should we roll back servers we have upgraded? 3) is there a way to identify which schema needed to be updated during the configure database part of the upgrade so we can assess the risk of remaining on 5.6.39?

Thanks for your time.
Marc

Could I please get a response to the below questions? This problem disrupted our upgrade plan and we are standing still until we have a path to move forward: 

What would you advise re: the following:
1) is there a workaround for this? 2) should we roll back servers we have upgraded? 3) is there a way to identify which schema needed to be updated during the configure database part of the upgrade so we can assess the risk of remaining on 5.6.39?

Can this case be reassigned to someone who will respond?

Posted by developer:
 
Hello Marc,

Apologies for the delay in the response. Am currently looking
into the issue. I will get back to you with the alternatives
after I have analyzed the issue. Meanwhile could you please try the
if any one of the following helps in identifying the schemas
which require an upgrade/repair:
a) Running the mysqlcheck explicitly.
Documentation link: https://dev.mysql.com/doc/refman/5.6/en/mysqlcheck.html
b) Running CHECK TABLE FOR UPGRADE/REPAIR table explicitly.
Documentation link: https://dev.mysql.com/doc/refman/5.6/en/check-table.html

Regards,
Nisha.

Thank you. I picked on one of my upgraded servers, and running mysqlcheck with -A showed OK for all tables. I ran CHECK TABLE [Table1,2,3 etc] FOR UPGRADE; on all user databases as well as mysql, performance_schema and sys System databases and all showed OK.

-Marc

Posted by developer:
 
Hello Marc,

Thanks for your feedback. You can use the above as workaround
for identifying and fixing the tables after upgrade until we
figure out what went wrong in 5.6.39.

Regards,
Nisha.

Posted by developer:
 
Hello Marc,

Can you please check which port your server is running on?
And ensure that you run mysql_upgrade on the same port.
By default, the MySQL server uses 3306 as the port number,
hence  'mysql_upgrade' also needs to use the same port.

I was able to see the problem reported only with
mis-matched ports. Hence can you confirm from your end?

Hi,

We are not using port 3306, however I am upgrading products via MySQL Installer, which I run from:  C:\Program Files (x86)\MySQL\MySQL Installer for Windows\MySQLInstaller.exe.  There is no prompt or opportunity to set the upgrade to connect to a different port.  This is the way we've always done it and never had a problem before.

-Marc

Posted by developer:
 
I am unable to reproduce the problem reported. 
Here is what I have tried:

a) Installed the MySQL-5.6.37 community installer,
   then installed the MySQL server.
b) Then selected MySQL-5.6.39 from the catalog
   on the installer and installed MySQL-5.6.39 server.
c) The check and upgrade process runs successfully 
   and following is the output.
Attempting to run mysql_upgrade.exe
Starting process for MySQL Server 5.6.39...
Starting process with command: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" --console...
Process for mysqld, with ID 1324, has been started successfully and is running.
Successfully started process for MySQL Server 5.6.39.
2018-04-18 10:01:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-04-18 10:01:37 0 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe (mysqld 5.6.39-log) starting as process 1324 ...
Starting process with command: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql_upgrade.exe --force --host=localhost --port=3306 --user=root --password=root --verbose...
Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe
Warning: Using a password on the command line interface can be insecure.
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlcheck.exe
Running 'mysqlcheck' with connection arguments: "--host=localhost" "--port=3306" 
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: "--host=localhost" "--port=3306" 
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: "--host=localhost" "--port=3306" 
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: "--host=localhost" "--port=3306" 
Warning: Using a password on the command line interface can be insecure.
OK
Process for mysql_upgrade, with ID 6784, was run successfully and exited with code 0.
Killing process for MySQL Server 5.6.39, with ID 1324...
Killed process for MySQL Server 5.6.39, with ID 1324.
Finished running mysql_upgrade.exe
Ended configuration step: Updating server

Beginning configuration step: Starting Server
Attempting to start service MySQL56...
Successfully started service MySQL56.
Waiting until a connection to MySQL Server 5.6.39 can be established (with a maximum of 10 attempts)...
  Retry 1: Attempting to connect to Mysql@localhost:3306 with user root with no password...
Successfully connected to MySQL Server 5.6.39.
Ended configuration step: Starting Server

Beginning configuration step: Updating Start Menu Link
Attempting to verify command-line client shortcut.
Verified command-line client shortcut.
Verified command-line client shortcut.
Ended configuration step: Updating Start Menu Link
=========================================================
I have also tried:
a) Downloaded MySQL-5.6.36 code, built the debug server
   and then started the server.
b) Stopped the server, built MySQL-5.6.39 server, started
   the server on the data directory created by MySQL-5.6.36
   server. 
c) Ran mysql_upgrade: mysql_upgrade --protocol=tcp -P 3306
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrade process completed successfully.
Checking if update is needed.

I see the error only when there is a mismatch in the port number
used. i.e Running the MySQL server on a different port and then
attempting to run 'mysql_upgrade' as administrator on a different
port:

mysql_upgrade --protocol=tcp -uroot -P 5999
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed

I'm confused; why did Chiranjeevi reply to my initial post with:

"Hello Marc Long,

Thank you for the bug report.
Verified as described on MySQL for Windows with 5.6.39 version.

Thanks,
Chiranjeevi."

The problem has happened to us on 3 windows server machines as well as 4 user workstations; it is a real problem in our environment.