MySQL - ERROR 1045 (28000): Access denied for user : permission issue

Question

ex :on first server :

    mysql> show grants for 'test'@'localhost';
    +---------------------------------------------------+
    | Grants for test@localhost                         |
    +---------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' |
    +---------------------------------------------------+

on second server its showing like this :

    mysql> show grants for 'root'@'localhost';


    | Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                               |

    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****************' WITH GRANT OPTION |


1 row in set (0.36 sec)

So problem is when i try to grant all for any user or database then getting "subject line"

Answer 1

The problem is simple: On the second server, the table mysql.user is from a previous version of MySQL. How do I know this? I dealt with that error message back on April 12, 2012 : Cannot GRANT privileges as root

The problem stems from the number of columns in mysql.user. When you run

SELECT COUNT(1) MySQLGrantsCount,VERSION() MySQLVersion
FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You get these numbers from each MySQL version

• 43 in MySQL 5.6
• 42 in MySQL 5.5
• 39 in MySQL 5.1
• 37 in MySQL 5.0

You cannot run that query on MySQL4.x since the information_schema database does not exists prior to MySQL 5.x. You would have to run DESC mysql.user to see it has 31 columns for MySQL 4.x.

I addressed this back on May 01, 2013 : Can I find out what version of MySQL from the data files?

While my posts suggested radical methods for correcting this, the recommended way it to run the utility mysql_upgrade on the second server as follows:

# mysql_upgrade --upgrade-system-tables

This should add the missing grant columns to mysql.user.

Give it a Try !!!