How to fix "ERROR 1577 (HY000) at line 1: Cannot
proceed because system tables used by Event Scheduler were found
damaged at server start"
After migrating databases data from
FreeBSD MySQL 5.0.83
server to a Debian Squeeze Linux
MySQL version 5.1.61, below
is a
mysql --version issued on both the FreeBSD and the
Debian servers
freebsd# mysql --version
mysql Ver 14.12 Distrib 5.0.83, for portbld-freebsd7.2 (i386) using
5.2
debian:~# mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using
readline 6.1
The data SQL dump from the FreeBSD server was dumped with following
command arguments:
freebsd# mysqldump --opt --allow-keywords --add-drop-table
--all-databases -u root -p >
complete_db_dump.sql
Then I used
sftp to transfer
complete_db_dump.sql
dump to the a brand new installed latest
Debian Squeeze
6.0.2. The Debian server was installed using a "clean Debian
install" without graphical environment with CD downloaded from
debian.org's site.
On the Debian machine I imported the dump with command:
debian:~# mysq -u root -p <
complete_db_dump.sql
Right After the dump was imported I re-started SQL server which was
previously installed with:
debian:~# apt-get install mysql-server
The error
I got after restarting the mysql server:
debian:~# #/etc/init.d/mysql restart
was:
ERROR 1577 (HY000) at line 1: Cannot proceed because
system tables used by Event Scheduler were found damaged at server
start
ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong.
Expected 20, found 16. The table is probably
corrupted
This error cost me a lot of nerves and searching in google to
solve. It took me like half an hour of serious googling ,until I
finally found the FIX!!!:
debian:~# mysql_upgrade -u root -h localhost -p --verbose
--force
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306'
'--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
Running 'mysqlcheck' with connection arguments: '--port=3306'
'--socket=/var/run/mysqld/mysqld.sock' '--host=localhost'
bible.holy_bible OK
bible.holybible OK
bible.quotes_meta OK
Afterwards finally I had to restart the mysql server once again in
order to finally get rid of the shitty:
ERROR 1547 (HY000) at line 1: Column count of mysql.proc is
wrong. Expected 20, found 16. The table is probably corrupted
error!
debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing
tables..
This solved the insane
Column count of mysql.proc is wrong.
Expected 20, found 16 once and for all!
Before I came with this fix I tried all kind of forum suggested
fixes like:
debian:~# mysql_upgrade -u root -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use
--force if you still need to run mysql_upgrade
debian:~# mysql_upgrade -p
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.1.61, use
--force if you still need to run mysql_upgrade
And few more, none of them worked the only one that worked
was:
debian:~# #mysql_upgrade -u root -h localhost -p --verbose
--force
I have to say big thanks to
Mats Lindth wonderful blog post which provided
me with the solution.
It seems, since Oracle bought the Community edition of MySQL thinks
with this database server are getting more and more messy and
backwards incompatible day by day.
Lately, I'm experiencing too much hassles with MySQL version
incompitabilities. Maybe I should think for migrating permanently
to Postgre ...
By the way the
ERROR 1547 (HY000) at line 1: Column count of
mysql.proc is wrong. is most probably caused of some kind of
password hashing incompitability between the password hashing
between the BSD and Debian SQL versions, as
mysql -u root -p
< dump.sql, does override default stored user passwords in
the
mysql database tables... Such password, hashing issues
were common in prior MySQL 4 to MySQL 5 migrations I've done,
however since MySQL 5+ is already storing its password strings
encrypted with md5 encryption I wonder why on earth this mess
happens ....