`mysql_upgrade` is failing with no real reason given

Question

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 via mysqladmin shutdown and restarting mysql via service 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 :

Also probably worth nothing, `mysqld` is running, with `--skip-grant-tables` option. I can connect via `mysql` on the terminal with no credentials, and I get no errors via syslog or anywhere else I can think to look when I run `mysql_upgrade` — Jim Rubenstein, Jul 30 '13 at 20:59
The [MySQL Reference Manual](http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html) 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... — Aaron Copley, Sep 16 '13 at 19:45
If your mysql root user doesn't have a password, don't include ` -p` in ` mysql_upgrade -u root -p` — Jeferex, Jan 08 '15 at 16:33

Riccardo Galli · Answer 1 · 2016-09-28T08:55:58.450

97

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)

edited Sep 28 '16 at 08:55

answered Aug 14 '13 at 10:01

Riccardo Galli

1,070
8
10

24

This was exactly the problem I had - why the hell couldn't it just say "Could not authenticate" or "Connection error" or something? So angry ... – les2 Sep 30 '13 at 20:26
3

Guys, you get the same error if your password is wrong too. so be informed. – Yoosaf Abdulla Dec 04 '13 at 15:25
3

And you get the same error if the server isn't running, even though it appears to accept the password. – Raman Dec 19 '13 at 03:25
1

just 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! – Henning May 05 '14 at 14:52
+1 for this. Yet another reason I hate MySQL – Excalibur Oct 08 '14 at 19:33
Thanks! You are a genius! What an idiot failed to output something like 'Authentication error'? – wrzasa Feb 21 '15 at 22:23
`Table 'mysql.plugin' doesn't exist` – tread Sep 27 '16 at 13:24

score 10 · Answer 2 · answered Aug 19 '13 at 19:33

10

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.

answered Aug 19 '13 at 19:33

Aubrey Falconer

313
1
2
6

that was exactly my problem because I run mysqd like this: mysqld --skip-grant-tables --user=mysql – Rodo Feb 13 '14 at 13:28

score 9 · Answer 3 · edited Oct 09 '15 at 09:13

9

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`

edited Oct 09 '15 at 09:13

HBruijn

77,029
24
135
201

answered Oct 09 '15 at 08:56

linuxman1

101
1
5

This worked perfectly for me – xarlymg89 Jun 04 '16 at 09:53

score 5 · Answer 4 · answered Jul 30 '13 at 21:10

5

you could try running these one by one to see where it fails:

mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables:

mysqlcheck --all-databases --check-upgrade --auto-repair  
mysql < fix_priv_tables  
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names

from http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

answered Jul 30 '13 at 21:10

user16081-JoeT

1,948
11
18

1

Thought about that, but `fix_priv_tables` is a script that is generated by `mysql_upgrade` in order to fixup the privelege tables – Jim Rubenstein Jul 30 '13 at 21:12
good point, maybe try just the first mysqlcheck line? And try running from the bin folder directly, fwiw, `/usr/bin/mysql_upgrade` – user16081-JoeT Jul 30 '13 at 21:15

score 5 · Answer 5 · answered Nov 30 '13 at 10:23

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.

answered Nov 30 '13 at 10:23

Dr. Gianluigi Zane Zanettini

478
7
22

score 3 · Accepted Answer · answered Nov 30 '13 at 14:00

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.

Yeah once that data dir of mysql has been corrupted there is pretty much nothing you can do — tread, Sep 27 '16 at 13:36

score 3 · Answer 7 · edited Jun 11 '20 at 10:02

Our DBA uninstalled mysql version 5.0.95 instead of just upgrading to 5.5.39. The uninstall backed up the /etc/my.cnf to /etc/my.cnf.rpmsave then removed it, and this prevented MySQL from starting up properly:

140902 15:00:57 [ERROR] Plugin 'InnoDB' init function returned error.
140902 15:00:57 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140902 15:00:57 [ERROR] Unknown/unsupported storage engine: InnoDB
140902 15:00:57 [ERROR] Aborting

You can do any of the following:

Compare the my.cnf files manually and bring over appropriate config settings for InnoDB
Restore the my.cnf.rpmsave back over the original (check first for any new default settings you should add!)
Use a diff tool like vimdiff to compare the my.cnf.rpmsave to the new my.cnf and brought back over the tweaks that had been made to MySQL config, including the InnoDB settings.

[root]# vimdiff /etc/my.cnf /etc/my.cnf.rpmsave

I did the last option, then was able to start MySQL:

root]# service mysqld start
Starting mysqld:                                           [  OK  ]

and now the mysql_upgrade works fine, using mysql_upgrade -uroot -p so it prompted me for root password.

[root]# mysql_upgrade -uroot -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
....

Hope this helps!

and also using mysql_upgrade -uroot -p failed because it needs MySQL to be running!

Lessons learned:

Backup my.cnf before upgrade... And actually do an in-place upgrade instead of uninstall then install of the newer version.
Get MySQL running so you can use mysql_upgrade.
Profit.

score 2 · Answer 8 · answered Nov 07 '13 at 00:12

You must check the permission all files under mysql data. It should be the same owner of mysql PID (mysql or _mysql). This sometime happens because restore data from file without proper permission. For example if your mysql data is under /var/lib/mysql

chown -R mysql /var/lib/mysql

score 1 · Answer 9 · answered Jul 21 '15 at 07:36

Same problem for me, but the source of my problems was the old password format. While mysql can be forced to connect using the old format with "skip-secure-auth", mysql_upgrade has not this option. You need first to update the root password with the new format and then you can upgrade your mysql.

score 1 · Answer 10 · answered Jan 25 '16 at 17:54

1

Had the same problem upgrading from 5.1 to 5.5.

This worked for me: sudo mysql_upgrade -S <path-to-socket> -u <myuser> -p<mypass>

My error was probably caused by permissions to the socket path, but haven't the time to verify it was the cause.

answered Jan 25 '16 at 17:54

Capt

11
1

I'd moved my DataDir at some time, I guess that's why I needed the path to the socket – zzapper Dec 04 '17 at 15:03

score 0 · Answer 11 · answered May 06 '22 at 08:00

0

always check the log /var/log/mysqld.log first, most of the time answers are there

answered May 06 '22 at 08:00

LIU YUE

121
2

score 0 · Answer 12 · answered Sep 16 '13 at 19:33

0

I just ran into this as well after upgrading my system from Mint 12 to Mint 15. I had archived /var/lib/mysql and put it back in place post-upgrade. I ran the first mysqlcheck from user16081's comment, and it complained about mysql.sock.

I started mysqld using /usr/sbin/mysqld & and mysql_upgrade ran fine.

answered Sep 16 '13 at 19:33

Marty Vance

101
1

That's a pretty scary method for upgrading MySQL, but I am glad it worked for you. – Aaron Copley Sep 16 '13 at 19:47
@aaron-copley: actually it didn't completely work. MySQL 5.5.32 is partially ignoring many of my InnoDB tables; they appear in `SHOW TABLES`, but otherwise don't exist. I'm currently trying to get mysql-utilities to work, but that's complaining about missing python modules. – Marty Vance Sep 18 '13 at 21:25

score 0 · Answer 13 · answered Jan 10 '14 at 17:12

I ran across the same problem.
I solved it by including the -S /path/to/mysql.sock

In my particular case the output of mysql_upgrade was:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

That's pretty useless. --verbose made no difference.

Plugging around I settled on the following command and it worked like a charm:
mysql_upgrade -S /var/lib/mysql/mysql.sock -uUSERNAME -p

Hope it helps.

score 0 · Answer 14 · answered Oct 01 '15 at 06:32

0

I faced this problem, and I found out that,

it required MySQL Service to be running
it required username and password

answered Oct 01 '15 at 06:32

Sruit A.Suk

103
5

score 0 · Answer 15 · edited Nov 19 '15 at 11:04

0

I encountered the same issue.

I solved this by installing new database by mysql_install_db --user=mysql as described in the comments of my rc.mysql file in /etc.

Then I was able to start mysql daemon and use 'mysql' or whatever you want connected with mysql package.

I had this problem on slackware arm , but suppose it doesn't matter in this case.

edited Nov 19 '15 at 11:04

HBruijn

77,029
24
135
201

answered Nov 19 '15 at 10:31

user323106

1

score 0 · Answer 16 · answered Mar 21 '16 at 10:41

In my case I had a few versions of mysqld running locally that made mysql_upgrade fail with Error: Failed while fetching Server version! Could be due to unauthorized access. ps aux | grep mysql and make sure mysqld is all shut down. Then brew uninstall all version, reinstall the right version. And after that mysql_upgrade started to work.

score -1 · Answer 17 · answered Jul 30 '13 at 20:46

-1

try

mysql_upgrade --verbose

or maybe even (or both)

--debug-check --debug-info

answered Jul 30 '13 at 20:46

alexus

13,112
32
117
174

Tried those, no real useful information, I don't think |; – Jim Rubenstein Jul 30 '13 at 20:57
restarted and pasted some error log info \; not sure why it would keep looping through those same errors over and over. – Jim Rubenstein Jul 30 '13 at 21:08
seems 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 Jul 30 '13 at 21:09
but before that, try mysql_upgrade --version and provide output for that. – alexus Jul 30 '13 at 21:10
`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 – Jim Rubenstein Jul 30 '13 at 21:13
also, as I understand it; `mysql.host` should be created by `mysql_upgrade` \: – Jim Rubenstein Jul 30 '13 at 21:20
temporary disable selinux, try same operation again. i'd probably consider reinstalling mysql, also try myisamchk to run against mysql db. – alexus Jul 31 '13 at 13:59

score -3 · Answer 18 · answered Mar 17 '15 at 12:24

-3

The root user for MySQL is named "admin", not root. The right command is

mysql_upgrade -uadmin -p

answered Mar 17 '15 at 12:24

Marco Marsala

471
3
7
14

This is absolutely wrong. The root user in MySQL is `root`. – dr_ Nov 02 '16 at 08:28

`mysql_upgrade` is failing with no real reason given

18 Answers18

Linked