Posts Tagged ‘server start’

Fixing MySQL server start up “ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)”

Friday, September 7th, 2012

I'm still busy configuring the new Lenovo (Le-novo) 🙂 ThinkCenter server necessery for migration of old machines. I've done a lot yesterday but really moving all this stuff takes time …

I moved the SQL databases from the old MySQL server host to the new Debian Linux host.

In order to move databsaes, I did the usual SQL dump from current working host with:

mysql:~# mysqldump --opt --allow-keywords --add-drop-table --all-databases -u root > dump.sql

After that did the standard move of dump.sql to the new host with sftp

mysql-new:~# sftp root@mysql-host
Connected to mysql-host.
sftp> get dump.sql
....
sftp> exit

and imported dump:

mysql-new:~# mysql -u root -p < dump.sql
Enter password:

Databases dump grow really a lot (7GB)!, so I had to wait for dump.sql to import about 20 minutes – (the host configuration is Dual core 6Ghz 3MB Cache CPU, 4GB DDR3 RAM, 7200 500 GB Hitachi ExcelStor Techno V32O HDD).

The dumps migration was between identical release Debian Linux – (Squeeze 6.0.5) servers running identical versions of MySQL.

mysql-new:~# mysql --version
mysql Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (i486) using readline 6.1

Because of that the whole db import worked like a charm.

Once moved the SQL started re-started normally but there was an on screen warning:

ERROR 1045 (28000): Access denied for user
'debian-sys-maint'@'localhost' (using password: YES)"

The cause of this warning error is because of way /etc/init.d/mysql script is written and in particular the custom MySQL (Debian specific start-up philosophy).

/etc/init.d/mysql is written in a way that on every restart a check of Database consistency is done. There in the script the user debian-sys-maint (a user with mysql administrator root privileges) is used to do the quick consistency check. The debian-sys-maint password which is used on start-up is stored in /etc/mysql/debian.cnf:

mysql-new:~# less /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = pQFM9RetOHFjewwn
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = pQFM9RetOHFjewwn
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

The whole problem is that during, the old SQL import the password set for user debian-sys-maint is different and once SQL starts the init script reads this pass and fails to login to SQL server.

The warning (error):

ERROR 1045 (28000): Access denied for user
'debian-sys-maint'@'localhost' (using password: YES)
hence appears on every SQL start (including on every system boot). The err is generally harmless and SQL seems to work fine with or without it. However since the consistency check is not done at start up, if there are some CORRUPT tables not initiating the start up check is not a good idea.

There are two options to get rid of the warning one and better one is to check in /etc/mysql/debian.cnf for password string and change the pwd with mysql cli e.g.:

new-mysql:~# grep -i pass /etc/mysql/debian.cnf | uniq
password = pQFM9RetOHFjewwn

GRANT SELECT on `mysql`.`user` to 'debian-sys-maint'@'localhost' identified by 'pQFM9RetOHFjewwn';
Query OK, 1 row affected (0.01 sec)
GRANT SELECT ON mysql.user TO 'debian-sys-main'@'localhost';Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Second option (not recommended in terms of security) is to set user/pass to root values in /etc/mysql/debian.cnf.

That's all. N-joy 🙂

How to fix “ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start”

Saturday, May 12th, 2012

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 ….