Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"

share|improve this question
add comment

1 Answer

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 !!!

share|improve this answer
add comment

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.