3

My website has several PHP+MySQL applications. I noticed my website has been very unstable after I installed Limesurvey. myisam tables crashed every 3-4 days.

I copied mysql logs here:

110812 04:10:47  mysqld started
110812  4:10:47 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
110815 23:11:02  mysqld started
110815 23:11:02 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
110815 23:11:02 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
110815 23:11:02 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 0  Source distribution
110815 23:14:10 [ERROR] /usr/libexec/mysqld: Table './DBXXX/TableXXX' is marked as crashed and should be repaired

As you can see, mysqld was started on port 3306 on 08/12/2011 and it had been running for >3.5. days. Then another mysqd was started on port0 on 08/15.

I suspect both MySQL processes access the same database tables and caused the crash. I never set up multiple mysql processes on my webserver and I don't know why another mysqld was started on port 0.

Here's the part of my.cnf file that may be related:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
skip-locking
skip-name-resolve
local-infile = 0
skip-networking
myisam-recover=backup,force
key_buffer_size = 375M

max_connections = 1200

thread_cache_size=60

interactive_timeout=1200

wait_timeout=1200

max_connect_errors=50

max_allowed_packet = 64M

myisam_sort_buffer_size = 64M

query_cache_limit=1M

query_cache_size=64M

query_cache_type=1

read_buffer_size = 256k

read_rnd_buffer_size = 512k

sort_buffer_size = 512k

join_buffer_size=512K

tmp_table_size = 64M # default is 32M = 33554432      

max_heap_table_size = 64M #recommend same size as tmp_table_size

bulk_insert_buffer_size = 64M     #recommend same size as tmp_table_size

table_cache=512

thread_concurrency=4 

skip-innodb

[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]

quick

quote-names

max_allowed_packet=128M

The myisam setting doesn't seem to be working at all. I had to fix the crashed tables using the Repair DB function in PhpMyadmin after my whole website went down. It would be very helpful if I could make myisam-recover work

another issue I noticed is it takes a very long time to stop mysqld service. and the response always says "Failed" but I can still start mysqld service.

MySQl version is 5.0.77

Looks like I still have a couple of days before this problem happens again. Can anybody please help me?

quanta
  • 50,327
  • 19
  • 152
  • 213
user895701
  • 31
  • 2

2 Answers2

1
socket: '/var/lib/mysql/mysql.sock'  port: 0  Source distribution

It seems that when you use --skip-networking option you will see this message because you must connect via name pipes or Unix socket.

You said you have multiple processes, please show us the output of ps -ef | grep mysql and the my.cnf of remaining processes.

quanta
  • 50,327
  • 19
  • 152
  • 213
  • I did some research and realized port 0 is a result of skip-networking. Right now, ps -ef only returns mysqld and mysql_safe. – user895701 Aug 16 '11 at 04:58
  • I don't know how many mysqld were running when the system crashed. It seems 1) I added skip-networking into my.cnf a long time ago 2) there was a mysqld that had been running on port3306 for three days and today a new mysqld came out of nowhere and used port0. then my system crashed. I cannot SSH into my linux server when the problem happened. I am updating my.cnf now – user895701 Aug 16 '11 at 05:02
  • also does anybody have any idea why --myisam-recover didn't run? I checked mysql setting from PhpMyadmin and I can see the value "backup, force" for the myisam recovery option. – user895701 Aug 16 '11 at 05:03
0

Are too many "accounts" trying to log in for every single time a database is accessed? Do they not carry persistent sessions?

U4iK_HaZe
  • 631
  • 5
  • 13
  • I used limesurvey and enables persistent sessions. All these mess started when some users got strange error msg which says "MySQL server has gone away" but the server is running. Before that, I never needed to reboot my server. – user895701 Aug 16 '11 at 05:10