2

I have strange behavior on my MySQL server. I'm sure I have an account with all privileges. Here's the proof :

mysql> show grants;

+-------------------------------------------------------------------------+
| Grants for ***@localhost                                   |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO '***'@'localhost' IDENTIFIED BY PASSWORD '***'    |
| GRANT ALL PRIVILEGES ON `***`.* TO '***'@'localhost'                    |
| GRANT ALL PRIVILEGES ON `***`.* TO '***'@'localhost'                    |
| GRANT ALL PRIVILEGES ON `***`.* TO '***'@'localhost'                    |
+-------------------------------------------------------------------------+
4 rows in set (0.04 sec)

but why I always get error message when I'm executing this syntax :

mysql> ALTER TABLE outbox_multipart.ID AUTO_INCREMENT = 12;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    13924219
Current database: ***

ERROR 1142 (42000): ALTER command denied to user '***'@'localhost' for table 'ID'

I can do almost everything (INSERT, UPDATE, DELETE, TRUNCATE, etc) but ALTER with that account. any idea what's wrong with it?

I tried to do same thing with phpmyadmin and php script, but got same result. seems that I can't ALTER that column.

Saint Robson
  • 5,475
  • 18
  • 71
  • 118
  • 2
    error messages don't lie, are you altering on remote machine? – John Woo Sep 27 '12 at 14:02
  • remote machine? no. I'm using phpmyadmin on my cPanel and it failed. then I tried to use SSH and have same result. so basically, I'm not on remote machine. I'm on localhost (from server point-of-view). – Saint Robson Sep 27 '12 at 14:10

2 Answers2

7

User '***'@'localhost' has all privileges on database '***', but you are trying to ALTER table in another database - outbox_multipart. Add privileges to this database, re-connect and try to ALTER.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • you're right. I have wrong syntax to alter. the correct one is this : ALTER TABLE outbox_multipart AUTO_INCREMENT = $rowLastID – Saint Robson Sep 27 '12 at 14:16
0

After granting commands you should run command as root

flush privileges;

after that your grant will be valid.

Alp Altunel
  • 3,324
  • 1
  • 26
  • 27