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"
Best 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 rootThe problem stems from the number of columns in
mysql.user
. When you runYou get these numbers from each MySQL version
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 runDESC 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:
This should add the missing grant columns to
mysql.user
.Give it a Try !!!