`mysql_upgrade` is failing with no real reason given
Solution 1
This question is incredibly generic, and I apologize for that.
I couldn't find a direct cause and solution to the problem I was having, so I resorted to re-installing MySQL to see if that would work. Turns out, re-installing did the trick. That was a lame way to fix it, but it was the only option I had left.
A lot of the other answers on this question are problems I had to work through to get mysql_upgrade to run initially, but for whatever reason - it failed as it was trying to run some automated queries, and I couldn't find the documentation on which queries it was running so I could fix them.
Solution 2
I think that it needs username and password
mysql_upgrade -u root -p
If I don't pass them I get your error
Edit: thanks to the comments now I know that there are other reasons, maybe less frequent but it's best to be aware of them too
So you get that error when
- you didn't pass username and password
- you passed your credentials, but they were wrong
- the MySQL server isn't running
- the permissions' tables are ruined (then you must restart MySQL with
mysqld --skip-grant-table
) - the table mysql.plugin is missing (you'll see an error about that when starting MySQL which suggests to run... mysql_upgrade, and that fails. You probably have some obsolete configuration in my.cnf)
Solution 3
I just encountered these precise symptoms when upgrading from 5.5 to 5.6, and it turned out to be a service reachability issue.
Even though the cli MySQL client could connect to my local DB instance with only a -u and -p provided, I also needed to specify -h 127.0.0.1 for mysql_upgrade as it was attempting a socket file connection and failing miserably in the attempt.
Solution 4
That seems a Plesk server, when using Plesk there is no root for Mysql, but the administrator of Mysql called admin, so this command should work on Plesk as I tried it before:
mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`
Solution 5
Same issue! The solution for me came from http://www.freebsd.org/cgi/query-pr.cgi?pr=180624
Briefly: the error is misleading! run mysql_upgrade -u root -p
with the DB on-line and provide the root password.
Related videos on Youtube
Jim Rubenstein
https://www.twitter.com/jrubsc https://www.jimsc.com
Updated on September 18, 2022Comments
-
Jim Rubenstein almost 2 years
I'm upgrading from MySQL 5.1 to 5.5, running
mysql_upgrade
and getting this output:# mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed
Any ideas on where to look for what's happening (or, not happening?) so I can fix whatever is wrong and actually run
mysql_upgrade
?Thanks!
More output:
# mysql_upgrade --verbose Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed # mysql_upgrade --debug-check --debug-info Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed # mysql_upgrade --debug-info Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed User time 0.00, System time 0.00 Maximum resident set size 1260, Integral resident set size 0 Non-physical pagefaults 447, Physical pagefaults 0, Swaps 0 Blocks in 0 out 16, Messages in 0 out 0, Signals 0 Voluntary context switches 9, Involuntary context switches 5 # mysql_upgrade --debug-check Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed
After shutting down
mysqld --skip-grant-tables
viamysqladmin shutdown
and restarting mysql viaservice mysql start
, the error log loops through this set of errors over and over:130730 21:03:27 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist 130730 21:03:27 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 130730 21:03:27 InnoDB: The InnoDB memory heap is disabled 130730 21:03:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130730 21:03:27 InnoDB: Compressed tables use zlib 1.2.3.4 130730 21:03:27 InnoDB: Initializing buffer pool, size = 20.0G 130730 21:03:29 InnoDB: Completed initialization of buffer pool 130730 21:03:30 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 588190222435 130730 21:03:30 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 588192055067 130730 21:03:30 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 81298895, file name /var/log/mysql/mysql-bin.006008 130730 21:03:33 InnoDB: Waiting for the background threads to start 130730 21:03:34 InnoDB: 5.5.32 started; log sequence number 588192055067 130730 21:03:34 [Note] Recovering after a crash using /var/log/mysql/mysql-bin 130730 21:03:34 [Note] Starting crash recovery... 130730 21:03:34 [Note] Crash recovery finished. 130730 21:03:34 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130730 21:03:34 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 130730 21:03:34 [Note] Server socket created on IP: '0.0.0.0'. 130730 21:03:34 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
MySQL log during start up via
mysqld_safe --skip-grant-tables
130730 21:19:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 130730 21:19:36 [Note] Plugin 'FEDERATED' is disabled. 130730 21:19:36 InnoDB: The InnoDB memory heap is disabled 130730 21:19:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins 130730 21:19:36 InnoDB: Compressed tables use zlib 1.2.3.4 130730 21:19:37 InnoDB: Initializing buffer pool, size = 20.0G 130730 21:19:39 InnoDB: Completed initialization of buffer pool 130730 21:19:39 InnoDB: highest supported file format is Barracuda. 130730 21:19:42 InnoDB: Warning: allocated tablespace 566, old maximum was 0 130730 21:19:42 InnoDB: Waiting for the background threads to start 130730 21:19:43 InnoDB: 5.5.32 started; log sequence number 588192055067 130730 21:19:43 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130730 21:19:43 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 130730 21:19:43 [Note] Server socket created on IP: '0.0.0.0'. 130730 21:19:43 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them 130730 21:19:43 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure 130730 21:19:43 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure 130730 21:19:43 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.32-0ubuntu0.12.04.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
As I understand it, all the table structure/existence issues (as it relates to mysql system tables) should be corrected by running
mysql_upgrade
:-
Jim Rubenstein almost 11 yearsAlso probably worth nothing,
mysqld
is running, with--skip-grant-tables
option. I can connect viamysql
on the terminal with no credentials, and I get no errors via syslog or anywhere else I can think to look when I runmysql_upgrade
-
Aaron Copley almost 11 yearsThe MySQL Reference Manual covers upgrading to 5.5 from 5.1 pretty well. If you have followed all the instructions here, it would be worth mentioning. If you have not, well...
-
Jeferex over 9 yearsIf your mysql root user doesn't have a password, don't include ` -p` in ` mysql_upgrade -u root -p`
-
-
Jim Rubenstein almost 11 yearsTried those, no real useful information, I don't think |;
-
Jim Rubenstein almost 11 yearsrestarted and pasted some error log info \; not sure why it would keep looping through those same errors over and over.
-
alexus almost 11 yearsseems like you have an error there --
130730 21:03:34 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
I think that's what causing whole thing to fail. -
alexus almost 11 yearsbut before that, try mysql_upgrade --version and provide output for that.
-
Jim Rubenstein almost 11 yearsThought about that, but
fix_priv_tables
is a script that is generated bymysql_upgrade
in order to fixup the privelege tables -
Jim Rubenstein almost 11 years
mysql_upgrade --version
produces no version output (just the FATAL ERROR error).mysql --version
is mysql Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.2, and mysqld version is 5.5 -
user16081-JoeT almost 11 yearsgood point, maybe try just the first mysqlcheck line? And try running from the bin folder directly, fwiw,
/usr/bin/mysql_upgrade
-
Jim Rubenstein almost 11 yearsalso, as I understand it;
mysql.host
should be created bymysql_upgrade
\: -
alexus almost 11 yearstemporary disable selinux, try same operation again. i'd probably consider reinstalling mysql, also try myisamchk to run against mysql db.
-
Aaron Copley almost 11 yearsThat's a pretty scary method for upgrading MySQL, but I am glad it worked for you.
-
les2 almost 11 yearsThis was exactly the problem I had - why the hell couldn't it just say "Could not authenticate" or "Connection error" or something? So angry ...
-
Yoosaf Abdulla over 10 yearsGuys, you get the same error if your password is wrong too. so be informed.
-
Raman over 10 yearsAnd you get the same error if the server isn't running, even though it appears to accept the password.
-
Rodo over 10 yearsthat was exactly my problem because I run mysqd like this: mysqld --skip-grant-tables --user=mysql
-
Henning about 10 yearsjust when the database table or the database format is broken too, it doesn't work either, then you need to start the daemon with "mysqld --skip-grant-tables" and run mysql_upgrade in another terminal!
-
wrzasa over 9 yearsThanks! You are a genius! What an idiot failed to output something like 'Authentication error'?
-
xarlymg89 about 8 yearsThis worked perfectly for me
-
tread almost 8 years
Table 'mysql.plugin' doesn't exist
-
tread almost 8 yearsYeah once that data dir of mysql has been corrupted there is pretty much nothing you can do
-
dr_ over 7 yearsThis is absolutely wrong. The root user in MySQL is
root
. -
zzapper over 6 yearsI'd moved my DataDir at some time, I guess that's why I needed the path to the socket