1

I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!).

Also not able to grant permissions to users as well. Not able to change in databases using root account, while taking backup of database due to same.

When I looked into MySQL databases using root account, it doesn't show anything. While on other server, I am able to find all entries.

Kindly please help me to resolve this issue as it is critical.

Log of mysql is as below.

130906 18:46:13 [Warning] Found invalid password for user: '[email protected]'; Ignoring user
1843 130906 18:46:13 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
1844 130906 18:46:14 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
1845 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
1846 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
1847 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
1848 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
1849 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
1850 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
1851 130906 18:46:14 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
1852 130906 18:46:14 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
1853 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
1854 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
1855 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
1856 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
1857 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
1858 130906 18:46:14 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
1859 130906 18:46:14 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
1860 130906 18:46:14 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1862 130906 18:46:14 [ERROR] Column count of mysql.db is wrong. Expected 22, found 13. Created with MySQL 50045, now running 50532. Please use mysql_upgrade to fix this error.
1863 130906 18:46:14 [ERROR] mysql.user has no `Event_priv` column at position 29
1864 130906 18:46:14 [ERROR] Cannot open mysql.event
1865 130906 18:46:14 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
1866 130906 18:46:14 [Note] /usr/libexec/mysqld: ready for connections.
1867 Version: '5.5.32-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Atomicorp
0

2 Answers 2

1

Besides not running mysql_upgrade after upgrading MySQL from major subversion to major subversion (E.g 5.1 to 5.5), having the wrong permissions/ownership on the performance_schema folder and files within the data directory can also cause this error.

For example, a fresh install of MySQL 5.5's RPM will set the ownership of that folder and files within it to root:root. To fix this, you will need to change the ownership to mysql:mysql.

E.g.

chown -R mysql:mysql /var/lib/mysql/performance_schema

ls -al /var/lib/mysql

drwx------. 2 mysql mysql     4096 Jun 10 10:29 performance_schema
1

First of all before performing an upgrade you should have taken a backup of all important databases (or simply all databases) using the mysqldump command.

Anyhow, to fix this please run:

shell> mysql_upgrade 

To understand more what mysql_upgrade does see here.

If you get permissions errors:

mysql_upgrade -u root -p (give the root password here)

If still it fails, rename /etc/my.cnf to /etc/my.cnfbku and then rerun the command.

This will fix all the issues regarding the upgrade.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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