1

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

migrated from superuser.com Mar 3 '15 at 16:08

This question came from our site for computer enthusiasts and power users.

  • 1
    What happens if you dpkg-reconfigure mysql-server? – Xyon Feb 6 '15 at 17:09
  • @Xyon: It says /usr/sbin/dpkg-reconfigure: mysql-server is broken or not fully installed. Doesn't sound too nice. – Kaivosukeltaja Feb 7 '15 at 8:30
  • Please run this query : select count(1) ColumnCount from information_schema.columns where table_schema='mysql' and table_name='user'; What is the ColumnCount ? – RolandoMySQLDBA Feb 9 '15 at 5:17
  • @RolandoMySQLDBA: 37. – Kaivosukeltaja Feb 9 '15 at 7:37
1

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

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

  • Thanks for the suggestion, tried that but... xxx@yyy:~$ sudo mysql_upgrade --upgrade-system-tables Looking for 'mysql' as: mysql The --upgrade-system-tables option was used, databases won't be touched. FATAL ERROR: Upgrade failed – Kaivosukeltaja Feb 10 '15 at 14:15
  • Finally got it working with your instructions, many thanks! TEXT columns don't apparently accept defaults so I left the DEFAULT 'N' part out but MySQL didn't seem to mind. After that I could grant the correct privileges and was finally able to upgrade the MySQL binaries. – Kaivosukeltaja Mar 7 '15 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.