[ubuntu] mysql headaches

[RabbieBurns]

it stopped working recently and its probably related to an update ive done

 

however i cannot get it to run

 

start mysql 

command either hangs or just says starting

ps aux 

shows no mysql process

mysql -u root

shows the following error:

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

 

spent the obligatory few hours on Google and tried all the fixes and recommendations i could try but cant figure this one out by myself

[glennda]

open a putty session and tail -f /var/log/mysql (i think its that path). In another windows tail -f /var/log/syslog and in another run the start mysql - can you paste up any errors.

[RabbieBurns]

root@ubuntu:~# tail -f /var/log/mysql/error.log
120704 10:53:52 [Note] Plugin 'FEDERATED' is disabled.
120704 10:53:52  InnoDB: Initializing buffer pool, size = 8.0M
120704 10:53:52  InnoDB: Completed initialization of buffer pool
120704 10:53:52  InnoDB: Started; log sequence number 0 44233
120704 10:53:52 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'

 

root@ubuntu:~# tail -f /var/log/messages
Jul  4 10:49:50 ubuntu kernel: [ 1453.669824] type=1505 audit(1341395390.222:51):  operation="profile_replace" pid=4082 name="/usr/sbin/mysqld"
Jul  4 10:50:20 ubuntu kernel: [ 1484.023990] type=1505 audit(1341395420.582:52):  operation="profile_replace" pid=4162 name="/usr/sbin/mysqld"
Jul  4 10:50:50 ubuntu kernel: [ 1514.349155] type=1505 audit(1341395450.902:53):  operation="profile_replace" pid=4242 name="/usr/sbin/mysqld"
Jul  4 10:51:21 ubuntu kernel: [ 1544.721736] type=1505 audit(1341395481.282:54):  operation="profile_replace" pid=4323 name="/usr/sbin/mysqld"
Jul  4 10:51:51 ubuntu kernel: [ 1575.053669] type=1505 audit(1341395511.612:55):  operation="profile_replace" pid=4403 name="/usr/sbin/mysqld"
Jul  4 10:52:21 ubuntu kernel: [ 1605.362550] type=1505 audit(1341395541.922:56):  operation="profile_replace" pid=4483 name="/usr/sbin/mysqld"
Jul  4 10:52:52 ubuntu kernel: [ 1635.679838] type=1505 audit(1341395572.232:57):  operation="profile_replace" pid=4582 name="/usr/sbin/mysqld"
Jul  4 10:53:22 ubuntu kernel: [ 1665.998682] type=1505 audit(1341395602.552:58):  operation="profile_replace" pid=4670 name="/usr/sbin/mysqld"
Jul  4 10:53:52 ubuntu kernel: [ 1696.292778] type=1505 audit(1341395632.852:59):  operation="profile_replace" pid=4750 name="/usr/sbin/mysqld"
Jul  4 10:54:23 ubuntu kernel: [ 1726.648335] type=1505 audit(1341395663.202:60):  operation="profile_replace" pid=4831 name="/usr/sbin/mysqld"

 

root@ubuntu:/var/log# ls -l | grep mysq
drwxr-s--- 2 mysql     adm         4096 2012-03-16 06:25 mysql
-rw-r----- 1 mysql     adm            0 2012-07-04 09:31 mysql.err
-rw-r----- 1 mysql     adm            0 2012-07-04 09:31 mysql.log

 

root@ubuntu:/var/log# tail -f /var/log/syslog
Jul  4 10:53:52 ubuntu init: mysql post-start process (4675) terminated with status 1
Jul  4 10:53:52 ubuntu kernel: [ 1696.292778] type=1505 audit(1341395632.852:59):  operation="profile_replace" pid=4750 name="/usr/sbin/mysqld"
Jul  4 10:53:52 ubuntu init: mysql main process (4754) terminated with status 1
Jul  4 10:53:52 ubuntu init: mysql main process ended, respawning
Jul  4 10:54:23 ubuntu init: mysql post-start process (4755) terminated with status 1
Jul  4 10:54:23 ubuntu kernel: [ 1726.648335] type=1505 audit(1341395663.202:60):  operation="profile_replace" pid=4831 name="/usr/sbin/mysqld"
Jul  4 10:54:23 ubuntu init: mysql main process (4835) terminated with status 1
Jul  4 10:54:23 ubuntu init: mysql main process ended, respawning
Jul  4 10:54:53 ubuntu init: mysql post-start process (4836) terminated with status 1
Jul  4 11:00:01 ubuntu CRON[4937]: (root) CMD (/etc/cron.hourly/gettv.sh)

Edited July 4, 2012 by RabbieBurns

[mats]

Last line in error.log - the priviledge table is broke so mysql won't start.

 

Have you seen the last answer in Answer : Bug#411672: mysql-server-5.0: Incorrect file format 'host' ?

[RabbieBurns]

Last line in error.log - the priviledge table is broke so mysql won't start.

 

Have you seen the last answer in Answer : Bug#411672: mysql-server-5.0: Incorrect file format 'host' ?

 

if I run

mysqld --skip-grant

 

I can login. But im not sure what to do to fix it?

 

I tried a

mysql> repair table columns_priv use_frm;
+--------------------+--------+----------+----------+
| Table              | Op     | Msg_type | Msg_text |
+--------------------+--------+----------+----------+
| mysql.columns_priv | repair | status   | OK       |
+--------------------+--------+----------+----------+

 

and then create a new user like it said, but get

mysql> CREATE USER 'robert'@'localhost' IDENTIFIED BY .... ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-ta       bles option so it cannot execute this statement 

 

any ideas?

[glennda]

This may also help but appears to be after a host crash?

 

MySQL :: Can´t open and lock privilege tables: Incorrect file format 'host'

[RabbieBurns]

ok ive repaired all the tables but struggling to create a new user using Insert..

mysql> insert into user values('localhost','root','password','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

[glennda]

That looks as though there is two many values i.e how many cols are in that table? You need as many Y's as there are columns

[RabbieBurns]

I lost my ssh session and now I cant get back into mysql as the user table is empty and I never managed to make a new one.

 

Is there any way of editing in a user via nano or such?

[RabbieBurns]

ok gave up and uninstalled it and reinstalled it and its fixed it. its kept my databases as well but i cant remember what users went with each database so ill need to fix a lot up but at least im in and its running

[glennda]

Thats a touch annoying but at least its working!