Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 \:

share|improve this question
    
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 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

10 Answers 10

I think that it needs username and password

mysql_upgrade -u root -p

If I don't pass them I get your error

share|improve this answer
5  
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
    
Guys, you get the same error if your password is wrong too. so be informed. –  yoosafinpace Dec 4 '13 at 15:25
    
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 3:25
    
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 at 14:52
    
+1 for this. Yet another reason I hate MySQL –  Excalibur Oct 8 at 19:33

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.

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

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
share|improve this answer

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.

share|improve this answer
up vote 1 down vote accepted

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.

share|improve this answer

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.
share|improve this answer

try

mysql_upgrade --verbose 

or maybe even (or both)

--debug-check --debug-info
share|improve this answer
    
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

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

share|improve this answer
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

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.

share|improve this answer
    
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.