71

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 :

3
  • 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 Jul 30, 2013 at 20:59
  • The 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... Sep 16, 2013 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 8, 2015 at 16:33

18 Answers 18

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)
7
  • 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, 2013 at 20:26
  • 3
    Guys, you get the same error if your password is wrong too. so be informed. Dec 4, 2013 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, 2013 at 3: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 5, 2014 at 14:52
  • +1 for this. Yet another reason I hate MySQL
    – Excalibur
    Oct 8, 2014 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.

1
  • that was exactly my problem because I run mysqd like this: mysqld --skip-grant-tables --user=mysql
    – Rodo
    Feb 13, 2014 at 13:28
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`
1
  • This worked perfectly for me
    – xarlymg89
    Jun 4, 2016 at 9:53
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

2
  • 1
    Thought about that, but fix_priv_tables is a script that is generated by mysql_upgrade in order to fixup the privelege tables Jul 30, 2013 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 Jul 30, 2013 at 21:15
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.

3

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.

1
  • 1
    Yeah once that data dir of mysql has been corrupted there is pretty much nothing you can do
    – tread
    Sep 27, 2016 at 13:36
3

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.
2

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
1

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.

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.

1
  • I'd moved my DataDir at some time, I guess that's why I needed the path to the socket
    – zzapper
    Dec 4, 2017 at 15:03
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.

2
  • That's a pretty scary method for upgrading MySQL, but I am glad it worked for you. Sep 16, 2013 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. Sep 18, 2013 at 21:25
0

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.

0

I faced this problem, and I found out that,

  1. it required MySQL Service to be running

  2. it required username and password

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.

0

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.

0

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

0
-1

try

mysql_upgrade --verbose 

or maybe even (or both)

--debug-check --debug-info
7
  • Tried those, no real useful information, I don't think |; Jul 30, 2013 at 20:57
  • restarted and pasted some error log info \; not sure why it would keep looping through those same errors over and over. Jul 30, 2013 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, 2013 at 21:09
  • but before that, try mysql_upgrade --version and provide output for that.
    – alexus
    Jul 30, 2013 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 Jul 30, 2013 at 21:13
-3

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

mysql_upgrade -uadmin -p
1
  • This is absolutely wrong. The root user in MySQL is root.
    – dr_
    Nov 2, 2016 at 8:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .