# unable to connect to local mysql server

## icefaerie

So, I've been out of the loop, apparently, while there have been massive upgrades to apache, php, and mysql.  I'm now trying to fix the issues with these things.

I have a cron job to automatically emerge sync and world every week, and in retrospect, perhaps this was not the brightest of ideas.  In short, I used to be running mysql 4.0.2x (don't remember exactly), but my server's installed 4.1.19 without my being able to go through the upgrade process.

I've restarted my mysql init script several times.

```
yuri mysql # /etc/init.d/mysql restart

 * Stopping mysql ...

/etc/init.d/mysql: line 332: /var/run/mysqld/mysqld.pid: No such file or directory

 * Stopping mysqld (0)                                                                [ ok ]

 *  ...

 * Starting mysql (/etc/mysql/my.cnf)                                                 [ ok ]
```

So, apparently it starts fine, but when I try to actually access it, I can't.

```
yuri mysql # mysql

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

I tried dumping the data from the tables as suggested in the guide, but got that same error:

```
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect
```

How can I extract my data and get 4.1.19 correctly set up?

----------

## andyrewbobb

after you have started it, try

```
 ps aux | grep -i mysql
```

that will tell you if you are actually running the mysql server.  It might be that you have missed some update to a configuration file, or something trivial like that.

----------

## icefaerie

```
yuri mysql # ps aux | grep -i mysql

root     14663  0.0  0.1   1424   464 pts/1    S+   07:11   0:00 grep -i mysql
```

Indeed, it's not actually running.

However, I'm sure all the config files are up to date because...

```
yuri mysql # etc-update

Scanning Configuration files...

Exiting: Nothing left to do; exiting. :)
```

----------

## bunder

- check /var/run for a pid file, delete it (or run /etc/init.d/mysql zap)

- check /var/log for errors, probably in mysql.log or syslog/messages/daemon.log

let us know if you find anything,

bunder

----------

## Tortue

I've got the same problem and I have Gallery (for showing pictures on the web) that depends on it!

Here is what /var/log/mysql/mysqld.err shows:

```
060512  0:48:18 [Note] /usr/sbin/mysqld: Arrêt normal du serveur

060512  0:48:20  InnoDB: Starting shutdown...

060512  0:48:24  InnoDB: Shutdown completed; log sequence number 0 44507

060512  0:48:24 [Note] /usr/sbin/mysqld: Arrêt du serveur terminé

InnoDB: Error: log file ./ib_logfile0 is of different size 0 8388608 bytes

InnoDB: than specified in the .cnf file 0 5242880 bytes!

060512  0:49:28 [ERROR] Can't init databases

060512  0:49:28 [ERROR] Aborting

060512  0:49:28 [Note] /usr/sbin/mysqld: Arrêt du serveur terminé

InnoDB: Error: log file ./ib_logfile0 is of different size 0 8388608 bytes

InnoDB: than specified in the .cnf file 0 5242880 bytes!

060512  0:59:05 [ERROR] Can't init databases

060512  0:59:05 [ERROR] Aborting

060512  0:59:05 [Note] /usr/sbin/mysqld: Arrêt du serveur terminé

InnoDB: Error: log file ./ib_logfile0 is of different size 0 8388608 bytes

InnoDB: than specified in the .cnf file 0 5242880 bytes!

060512  1:01:32 [ERROR] Can't init databases

060512  1:01:32 [ERROR] Aborting

060512  1:01:32 [Note] /usr/sbin/mysqld: Arrêt du serveur terminé

InnoDB: Error: log file ./ib_logfile0 is of different size 0 8388608 bytes

InnoDB: than specified in the .cnf file 0 5242880 bytes!

060512  1:04:43 [ERROR] Can't init databases

060512  1:04:43 [ERROR] Aborting

060512  1:04:43 [Note] /usr/sbin/mysqld: Arrêt du serveur terminé
```

And here is the message that Gallery gives me (several times) when I try to connect to the web page:

```
Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) in /var/www/securinux.com/htdocs/imagier/lib/adodb/drivers/adodb-mysql.inc.php on line 348
```

I must say the first message is not the clearest thing for me. I don't know much about databases.

J-M

----------

## Tortue

I checked the content of /var/run/mysqld/ after zapping and restarting the server

There was a 0 byte mysqld.sock file, but no .pid file. It seems mysql doesn't create that .pid file

J-M

----------

## magic919

https://forums.gentoo.org/viewtopic-t-460595-highlight-innodb.html

This one gives you some useful clues.

----------

## Tortue

Yessss!

Thanks Magic919, it works! simply changing 

```
innodb_log_file_size=5M
```

 to

```
innodb_log_file_size=8M
```

in /etc/mysql/my.cnf did the trick.

Thanks again, and long live Gentoo and its Forums!

J-M

----------

## icefaerie

 *bunder wrote:*   

> - check /var/run for a pid file, delete it (or run /etc/init.d/mysql zap)
> 
> - check /var/log for errors, probably in mysql.log or syslog/messages/daemon.log
> 
> let us know if you find anything,
> ...

 

There are LOTS of errors... Everything looks ok at the beginning, but then things get worse.

```
050228 14:28:28 /usr/sbin/mysqld: Shutdown Complete

/usr/sbin/mysqld: ready for connections.

Version: '4.0.22'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.22-r2

050228 19:58:00 /usr/sbin/mysqld: Normal shutdown

050228 19:58:00 /usr/sbin/mysqld: Shutdown Complete

/usr/sbin/mysqld: ready for connections.

Version: '4.0.22'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.22-r2

050413 23:42:27 /usr/sbin/mysqld: Normal shutdown

050413 23:42:27 /usr/sbin/mysqld: Shutdown Complete

/usr/sbin/mysqld: ready for connections.

Version: '4.0.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.24

/usr/sbin/mysqld: ready for connections.

Version: '4.0.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.24

/usr/sbin/mysqld: ready for connections.

Version: '4.0.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.24

050508 12:03:05 /usr/sbin/mysqld: Normal shutdown

050508 12:03:05 /usr/sbin/mysqld: Shutdown Complete

/usr/sbin/mysqld: ready for connections.

Version: '4.0.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.24

050623 12:04:16 /usr/sbin/mysqld: Normal shutdown

050623 12:04:16 /usr/sbin/mysqld: Shutdown Complete

/usr/sbin/mysqld: ready for connections.

Version: '4.0.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.24

050623 12:35:10 /usr/sbin/mysqld: Normal shutdown

050623 12:35:10 /usr/sbin/mysqld: Shutdown Complete

/usr/sbin/mysqld: ready for connections.

Version: '4.0.24'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.24

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

/usr/sbin/mysqld: ready for connections.

Version: '4.0.25'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Gentoo Linux mysql-4.0.25-r2

060507 16:27:29 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060507 16:27:29 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060507 16:27:29 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060507 16:27:29 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

060511  3:01:00  InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait...

060511  3:01:03  InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait...

060511  3:01:05  InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

060511  3:01:11  InnoDB: Started; log sequence number 0 0

060511  3:01:11 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  3:01:11 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  3:01:11 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  3:01:11 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

060511  3:32:17  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

060511  3:32:18  InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 36808.

InnoDB: Doing recovery: scanned up to log sequence number 0 43676

060511  3:32:18  InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 

InnoDB: Apply batch completed

InnoDB: Last MySQL binlog file position 0 79, file name ./yuri-bin.000001

060511  3:32:19  InnoDB: Flushing modified pages from the buffer pool...

060511  3:32:19  InnoDB: Started; log sequence number 0 43676

060511  3:32:19 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  3:32:19 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  3:32:19 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  3:32:19 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

060511  7:14:49  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

060511  7:14:49  InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 43676.

InnoDB: Doing recovery: scanned up to log sequence number 0 43708

InnoDB: Last MySQL binlog file position 0 79, file name ./yuri-bin.000002

060511  7:14:49  InnoDB: Flushing modified pages from the buffer pool...

060511  7:14:49  InnoDB: Started; log sequence number 0 43708

060511  7:14:49 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  7:14:49 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  7:14:49 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  7:14:49 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

060511  7:15:35  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

060511  7:15:35  InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 43708.

InnoDB: Doing recovery: scanned up to log sequence number 0 43740

InnoDB: Last MySQL binlog file position 0 79, file name ./yuri-bin.000003

060511  7:15:35  InnoDB: Flushing modified pages from the buffer pool...

060511  7:15:35  InnoDB: Started; log sequence number 0 43740

060511  7:15:35 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  7:15:35 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  7:15:35 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060511  7:15:35 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

060512  4:58:43  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

060512  4:58:44  InnoDB: Starting log scan based on checkpoint at

InnoDB: log sequence number 0 43740.

InnoDB: Doing recovery: scanned up to log sequence number 0 43772

InnoDB: Last MySQL binlog file position 0 79, file name ./yuri-bin.000004

060512  4:58:44  InnoDB: Flushing modified pages from the buffer pool...

060512  4:58:44  InnoDB: Started; log sequence number 0 43772

060512  4:58:45 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060512  4:58:45 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060512  4:58:45 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060512  4:58:45 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

InnoDB: than specified in the .cnf file 0 8388608 bytes!

060512  5:01:19 [ERROR] Can't init databases

060512  5:01:19 [ERROR] Aborting

060512  5:01:19 [Note] /usr/sbin/mysqld: Shutdown complete
```

I tried changing the log size to 8MB as recommended but that doesn't appear to have helped.

----------

## MasquedAvenger

I've changed it to 8M too (before even starting it for the first time.)  That didn't solve the problem for me either.

James

----------

## csjean

Hi,

I've experienced the same problems emerging from mysql-4.1.14-r1 to mysql-4.1.19:

 1- Problem with 5M logfiles (which was soved by changing logfile size to 8M).

 2- After problem 1 was solved, problem 2:  "damaged" mysql.user table.

Can anybody see a solution? Or a way to unload the data and reload it?

Thanks.

----------

## csjean

After continuing to search for solutions,

I came across a post about charset problems.  The new 4.1.19 version defaults to multibyte charsets (UTF8) and the old one was a Latin1 charset.

So the simple solution is:

 1- reemerge mysql-4.1.19 and add latin1 USE flag.

 2- etc-update

 3- don't forget to change 5M logfiles to 8M.

 4- restart mysql

Everythin works.

Hope this helps.

----------

## MasquedAvenger

I just went ahead and wiped away my previous MySQL databases (I only had one that I had used for a test installation of MythTV; I didn't need it anymore).  It's now working great  :Smile:   I'm guessing that if I had re-merged it using latin1, that probably would have fixed it, although I decided to allow it to remain UTF-8.

James

----------

## flakzeus

I re-emerged mysql with latin1 and it did not help. I still get the same error: 

```

060516 12:45:25 [Warning] './mysql/host' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060516 12:45:25 [Warning] './mysql/user' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060516 12:45:25 [Warning] './mysql/db' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

060516 12:45:25 [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format.

```

I have been doing some research and it looks like the script mysql_fix_privilege_tables will fix the problem, but I can't get mysql to start so that I can run the script.

I have some databases that I would rather not lose, so if anyone has any more ideas please speak up.

thanks

----------

## flakzeus

/bump

Anyone at all?

----------

## lxg

Just out of curiousity, no offense intended: What did you do when /etc-update asked you about /etc/mysql-xxx/my.cnf?

Btw, did you eselect your new MySQL version?

----------

## yamakawa

 *Tortue wrote:*   

> Yessss!
> 
> Thanks Magic919, it works! simply changing 
> 
> ```
> ...

 

Thanks a lot! This solved my problem too!   :Very Happy: 

----------

## khantheripper

I get the same message as icefarie no matter what I do:

```
localhost init.d # mysql zap

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

localhost init.d # mysql restart

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

localhost init.d # mysql

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

localhost init.d #
```

I've tried cahnging the log file from 5M to 8M, but that did nothing.  I did not try deleting.pid files in /var/run, because I didn't know what that would do.  Here are the files I have in that directory:

```
console  cron.pid  dhcpcd-eth0.pid  gdm.pid  metalog.pid  mysqld  random-seed  utmp
```

Should I delete these files?  I don't know what a PID file really is.  I also don't know how to eselect my version.  Yah, I know.  I'm a noob.  Theversion I have installed is 4.1.21.

----------

## khantheripper

 :Very Happy:    As it turns out, I just had to execute the following, to set the (MySQL) root password and start the server:

```
emerge --config =dev-db/mysql-4.1.19 
```

Now the problem is solved for me.

----------

## m4chine

 *csjean wrote:*   

> After continuing to search for solutions,
> 
> I came across a post about charset problems.  The new 4.1.19 version defaults to multibyte charsets (UTF8) and the old one was a Latin1 charset.
> 
> So the simple solution is:
> ...

 

what a life saver. these instructions solved my problem after upgrading as well. thanks again   :Razz: 

----------

## n0n

 *flakzeus wrote:*   

> I have been doing some research and it looks like the script mysql_fix_privilege_tables will fix the problem, but I can't get mysql to start so that I can run the script.

 

Just FYI in the future, you can get around this by starting mysqld temporarily with:

```
mysqld --skip-grant-tables
```

And then running the script.  Then shutdown mysql and start it back up regularly (via the init script).  Note that while mysqld is running w/ that option, anyone who can connect to the TCP port / socket would be able to have free reign of the database.

----------

