Can't grant MySQL privileges to Debian system maintenance user

Question

I have a dedicated Debian 7 server running multiple websites with MySQL as the database server. When I tried to install the latest updates, MySQL couldn't be updated because the server failed to stop.

I found someone with similar symptoms in this question: https://superuser.com/questions/268053/debian-cant-stop-mysql-permissions

The answers to the above question suggest granting full privileges on all databases to 'debian-sys-maint'@'localhost', but when I try to grant the privileges I get an access denied error.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '...';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Another weird thing is I now have two root users in my mysql.user table. Is this normal or could this be related to my problem? The localhost.localdomain one doesn't even have a password hash. I also have one user for each site I run but I left them out for clarity.

+-----------------------+------------------+
| Host                  | User             |
+-----------------------+------------------+
| localhost             | debian-sys-maint |
| localhost             | root             |
| localhost.localdomain | root             |
+-----------------------+------------------+

Edit: Running dpkg-reconfigure mysql-server says this:

xxx@yyy:~$ sudo dpkg-reconfigure mysql-server
/usr/sbin/dpkg-reconfigure: mysql-server is broken or not fully installed

Searching by that error led me to a forum post suggesting there may be older packages messing things up, but to me it looks like that's not the case here:

xxx@yyy:~$ sudo dpkg --get-selections | grep mysql
dovecot-mysql                                   install
libdbd-mysql-perl                               install
libmysqlclient18:amd64                          install
mysql-client-5.5                                install
mysql-common                                    install
mysql-server                                    install
mysql-server-5.5                                install
mysql-server-core-5.5                           install
php5-mysql                                      install

Answer 1

I have three(3) posts in the StackExchange that quickly explain the root cause

• Apr 24, 2014 : mysql: Restore All privileges to admin user
• Aug 07, 2013 : MySQL - ERROR 1045 (28000): Access denied for user : permission issue
• May 01, 2013 : Can I find out what version of MySQL from the data files?

ROOT CAUSE

• You have 37 columns in mysql.user. That's OK for MySQL 5.0
• You are running MySQL 5.5. You are supposed to have 42 columns.

SUGGESTION

You will have to run MySQL command

# mysql_upgrade --upgrade-system-tables

Keep in mind that you are leaping two versions of MySQL. Backup the mysql.user table before doing so

UPDATE 2015-02-10 10:59 EST

Based on your comment, I can only suggest one thing: Refer to my post Any known issues upgrading from MySQL 5.1.73 to 5.6.21? under the section "ISSUE #1 : Upgrade Path" on how to upgrade two versions of MySQL.

UPDATE 2015-02-11 12:26 EST

Here is your situation

• You have a mysql.user that is MySQL 5.0 compliant
• You have to get mysql.user upgraded to work with MySQL 5.5

I have a crazy idea: Add the missing 5 columns by hand

In my DBA StackExchange post Cannot GRANT privileges as root, I display all the columns in the mysql.user table from MySQL 5.6, 5.5, 5.1, 5.0 and 4.x.

From those displays, we will do the following:

• Make a backup of mysql.user beforehand as mysql.user_backup
• Construct mysql.user to have the same columns as MySQL 5.5, adding the five(5) missing colunms.
• Make sure the columns are in the same order
• Fill in the missing privs for root users with Y
• Deploy new privs

Steps to repair mysql.user

CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
ALTER TABLE mysql.user
    ADD Event_priv enum('N','Y') DEFAULT 'N' AFTER Create_user_priv;
ALTER TABLE mysql.user
    ADD Trigger_priv enum('N','Y') DEFAULT 'N' AFTER Event_priv;
ALTER TABLE mysql.user
    ADD Create_tablespace_priv enum('N','Y') DEFAULT 'N' AFTER Trigger_priv;
ALTER TABLE mysql.user
   ADD plugin CHAR(64) DEFAULT NULL AFTER max_user_connections;
ALTER TABLE mysql.user
    ADD authentication_string text DEFAULT 'N' AFTER plugin;
UPDATE mysql.user SET
    Event_priv='Y',
    Trigger_priv='Y',
    Create_tablespace_priv ='Y'
WHERE user='root' and host='localhost';
FLUSH PRIVILEGES;

If you want to revert it, you can do this

RENAME TABLE mysql.user TO mysql.user_old,mysql.user_backup TO mysql.user;
FLUSH PRIVILEGES;

GIVE IT A TRY !!!