Column count of mysql.db is wrong upgrading mySQL

[Metro2]

After using WHM to upgrade mySQL from 5.0.45 to 5.1.40 (and then of course running EasyApache to re-compile) on 3 servers, all 3 have been reporting this similar error:

[ERROR] Column count of mysql.db is wrong. Expected 22, found 20.

[ERROR] mysql.user has no `Event_priv` column at position 29

[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

I'm running cPanel 11.25.0-R42404 / WHM 11.25.0 on REDHAT Enterprise 4

I can't tell what I might have done wrong because I didn't do anything from shell - all I did was use WHM to click to upgrade mySQL and click to run EasyApache (same way I always have).

Does anyone know if this is a bug with 5.1.40 and if there is a fix for it that I should somehow apply? Things seem to be running OK so I don't want to mess with it any further if I shouldn't.

Thanks for any thoughts / opinions / info!

[Metro2]

Nobody else has encountered this? Just bumping this to give it one last shot here...

[cpanelkenneth]

[ERROR] Column count of mysql.db is wrong. Expected 22, found 20

That means the upgrade of the mysql.mysql database failed. There are a variety of ways to fix this. Here are a couple:

1. Re-run the mysql_upgrade or mysql_fix_privilege_tables script

2. backup mysql.mysql, shutdown mysql, remove /var/lib/mysql/mysql and use mysql_install_db to recreate it

Please note these are rather destructive. make sure you have adequate, reliable backups of mysql before pursuing.

[Metro2]

Thanks for the reply. I'm a bit nervous about this undertaking. I find it unusual that it failed on all 3 servers, and all 3 upgrades were done by using the upgrade link right in WHM... I mean, what are the odds? I've always run "Release" and I always keep WHM/cPanel updated, and no special custom configuration or anything like that (the only thing outside of the standard cPanel modules running are CSF/LFD and Mail Queues from ConfigServer, which I can't imagine would have any influence).

[cpanelkenneth]

Thanks for the reply. I'm a bit nervous about this undertaking. I find it unusual that it failed on all 3 servers, and all 3 upgrades were done by using the upgrade link right in WHM... I mean, what are the odds? I've always run "Release" and I always keep WHM/cPanel updated, and no special custom configuration or anything like that (the only thing outside of the standard cPanel modules running are CSF/LFD and Mail Queues from ConfigServer, which I can't imagine would have any influence).

Understandably so. For something so crucial as MySQL fixing those problems can be hair raising.

This may sound like 'passing the blame' but the problem is really MySQL's flaky upgrade capabilities. A quick google search on similar terms shows lots of people, including many not running cPanel, that have this same problem ( a few bug reports filed with MySQL on the matter also ).

Another option is forcing a return to MySQL 5.0.

Are you using FreeBSD?

[kernow]

He's using Redhat Enterprise !
Metro2.
You could try running:
/scripts/mysqlup --force

[Metro2]

Correct, I'm running RHEL 4.

Thank you both for the feedback and pointing out the options. Not quite sure which one is the safe bet at this point.

[Metro2]

You could try running:
/scripts/mysqlup --force

Trying that on one server right now and almost right off the bat I get this:

Installation of system tables failed! Examine the logs in
/var/lib/mysql for more information.

You can try to start the mysqld daemon with:

shell> /usr/sbin/mysqld --skip-grant &

and use the command line tool /usr/bin/mysql
to connect to the mysql database and look at the grant tables:

shell> /usr/bin/mysql -u root mysql
mysql> show tables

and also

! YAML::Tiny is not installed
inc_bundling_support....disabled
requires:
! ExtUtils::Install (1.52) is installed, but we need version >= 1.54
! ExtUtils::Installed (1.43) is installed, but we need version >= 1.999
license_creation........disabled
requires:
! Software::License is not installed

ERRORS/WARNINGS FOUND IN PREREQUISITES. You may wish to install the versions
of the modules indicated above before proceeding with this installation

[noimad1]

Did you ever find a solution to this, because I'm now having the exact same problem???

[Metro2]

Did you ever find a solution to this, because I'm now having the exact same problem???

To be honest I cannot remember which part of all the troubleshooting ended up resolving this for me. I do recall running /scripts/mysqlup --force but I don't think that completely did the trick. I'm quite sure that I ended up using EasyApache to re-compile Apache and rebooted my servers after using any of the advice that I found. Sorry that I can't be of more help.. I wish I could remember exactly, and I would have posted a follow-up here, but I was under a lot of pressure and overwhelmed at the time.

[SuperBaby]

I am getting the same problem. Have you solved yours? Thanks.

[SuperBaby]

Have I solved the problems????

This is the earlier problems:

[root@server02 lib]# service mysql start
Starting MySQL..100505 02:11:13 mysqld_safe Starting mysqld daemon with database
s from /var/lib/mysql
...100505 2:11:17 [Note] Plugin 'FEDERATED' is disabled.
..100505 2:11:19 InnoDB: Started; log sequence number 0 271971181
100505 2:11:19 [ERROR] Column count of mysql.db is wrong. Expected 22, found 15
. Created with MySQL 0, now running 50145. Please use mysql_upgrade to fix this
error.
100505 2:11:19 [ERROR] mysql.user has no `Event_priv` column at position 29
100505 2:11:19 [ERROR] Event Scheduler: An error occurred when initializing sys
tem tables. Disabling the Event Scheduler.
100505 2:11:19 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Commun
ity Server (GPL)
[ OK ]
[root@server02 lib]# mysql -V
mysql Ver 14.14 Distrib 5.1.45, for pc-linux-gnu (i686) using readline 5.1

Then I run this under shell:

# mysql_fix_privilege_tables --verbose --password=MYSQLPASSWORD

and I got this:

This script updates all the mysql privilege tables to be usable by
the current version of MySQL

You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!

ERROR 1060 (42S21) at line 102: Duplicate column name 'File_priv'
ERROR 1060 (42S21) at line 108: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 109: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 110: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 121: Duplicate column name 'ssl_type'
ERROR 1061 (42000) at line 131: Duplicate key name 'Grantor'
ERROR 1054 (42S22) at line 158: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 180: Duplicate column name 'type'
ERROR 1060 (42S21) at line 190: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 207: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 217: Duplicate column name 'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 220: Duplicate column name 'Create_tmp_table_priv'
ERROR 1054 (42S22) at line 323: Unknown column 'Create_view_priv' in 'where clause'
ERROR 1054 (42S22) at line 358: Unknown column 'Create_routine_priv' in 'where clause'
ERROR 1054 (42S22) at line 407: Unknown column 'Create_user_priv' in 'where clause'
ERROR 1060 (42S21) at line 431: Duplicate column name 'Routine_type'
ERROR 1054 (42S22) at line 556: Unknown column 'Event_priv' in 'where clause'
ERROR 1060 (42S21) at line 572: Duplicate column name 'sql_mode'
ERROR 1060 (42S21) at line 611: Duplicate column name 'originator'
ERROR 1060 (42S21) at line 615: Duplicate column name 'time_zone'
ERROR 1060 (42S21) at line 618: Duplicate column name 'character_set_client'
ERROR 1060 (42S21) at line 624: Duplicate column name 'collation_connection'
ERROR 1060 (42S21) at line 630: Duplicate column name 'db_collation'
ERROR 1060 (42S21) at line 636: Duplicate column name 'body_utf8'
ERROR 1054 (42S22) at line 646: Unknown column 'Trigger_priv' in 'where clause'
done
[root@server02 ~]# service mysql stop
Shutting down MySQL.....[ OK ]

[root@server02 ~]# service mysql start
Starting MySQL..[ OK ]

[root@server02 ~]# service mysql status
MySQL running (20464)[ OK ]

And I tried to restart MySQL under WHM and I got this:

Waiting for mysql to restart..............finished.

mysqld_safe (/bin/sh /var/lib/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/server02.updinetwork.com.pid) running as root with PID 18221
mysqld (/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/server02.updinetwork.com.pid --socket=/var/lib/mysql/mysql.sock) running as mysql with PID 18294

mysql started ok

Does that mean I have solved the problems???

[noimad1]

Have I solved the problems????

This is the earlier problems:


Then I run this under shell:

# mysql_fix_privilege_tables --verbose --password=MYSQLPASSWORD

and I got this:


And I tried to restart MySQL under WHM and I got this:


Does that mean I have solved the problems???

I dunno, but I know I had to have cpanel go in and fix my mysql. It corrupted quite a few of my customers databases as well, so I had to try to restore some of them, but not all of them worked, so we were kind of stuck...

[SuperBaby]

Yeah, the server company said it should be OK as I have no problem starting MySQL from shell and WHM. Also, phpMyAdmin works fine.

[cpanelkenneth]

Have I solved the problems????

Then I run this under shell:

# mysql_fix_privilege_tables --verbose --password=MYSQLPASSWORD

Does that mean I have solved the problems???

The mysql_fix_privilege_tables will almost always resolve the column count problem. In extreme circumstances you may need to start MySQL with skip-grants set in /etc/my.cnf, then run mysql_fix_privilege_tables (following which you need to remove skip-grants and restart MySQL).