Unable to run update_mysql in MySQL 5.5

Question

I have recently taken over managing a MySQL database running on Windows Server 2008 R2. It is running 5.5.37 Community Edition. When it is started, it includes the following lines:

Thread, Type, Details
Column count of mysql.events_waits_current is wrong. Expected 16, found 19. The table is probably corrupted
Column count of mysql.events_waits_history is wrong. Expected 16, found 19. The table is probably corrupted
Column count of mysql.events_waits_history_long is wrong. Expected 16, found 19. The table is probably corrupted
Native table 'performance_schema'.'threads' has the wrong structure
Incorrect definition of table performance_schema.events_waits_summary_by_thread_by_event_name: expected column 'THREAD_ID' at position 0 to have type int(11), found type bigint(20) unsigned.
Column count of mysql.file_summary_by_event_name is wrong. Expected 5, found 23. The table is probably corrupted
Column count of mysql.file_summary_by_instance is wrong. Expected 6, found 25. The table is probably corrupted
Incorrect definition of table performance_schema.mutex_instances: expected column 'LOCKED_BY_THREAD_ID' at position 2 to have type int(11), found type bigint(20) unsigned.
Incorrect definition of table performance_schema.rwlock_instances: expected column 'WRITE_LOCKED_BY_THREAD_ID' at position 2 to have type int(11), found type bigint(20) unsigned.

I tried running mysql_upgrade, but get the following result:

Looking for 'mysql.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe
Looking for 'mysqlcheck.exe' as: C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqlcheck.exe
The system cannot find the file specified.
FATAL ERROR: Upgrade failed

The paths point to the correct locations. I can run mysql.exe and mysqlcheck.exe from those locations. I am running from an Administrator-level command prompt.

I have tried running mysqlcheck.exe with the parameter --all-databases, and everything checks out as OK. But those errors still remain when I restart MySQL.

Any ideas how to get rid of these errors?

• user41564: I got exactly the same error on Windows as Brian. I resolved this by specifying the -u and -p options on the mysql_upgrade command ie c:\Program Files\MySQL\mysql-5.6.19\bin\mysql_upgrade.exe -u root -p

Answer 1

As you mentioned you are tying to run mysql_upgrade and it's looking for 'mysql.exe' and 'mysqlcheck.exe'.

Try this To make it easier to invoke MySQL programs, you can add the path name of the MySQL bin directory to your Windows system PATH environment variable:

Here is the link to set Mysql environment variable in Windows system path.

Hope this will help you.

• Brian: The path already includes C:\Program Files\MySQL\MySQL Server 5.5\bin. I can run mysql or mysqld from any command prompt, and it finds the path. Whether I run mysql_upgrade from that path or elsewhere, it always says it is trying to find those two in the correct place. This is what confuses me.
• lalit: if your database size is small and downtime is possible than take dump of databases and reinstall mysql.It will create default schema (information_schema,mysql,performance_schema) and than recreate the other databases from backup dump file.
• Brian: If I reinstall mysql, would it be worthwhile to upgrade to a different version? Or would that likely introduce new complications?
• lalit: I upgraded one my databases (on Linux)from mysql 5.5 to 5.6 and it's working fine.
• Brian: I upgraded to 5.6 and have had a few hiccups with code that was not written according to strict data rules. So far, we've been able to fix the code. And the other problems have been fixed. Thanks!