MySQL restarts every 10 minutes exactly

[Alvin Koh]

Dear all,

I'm facing a rather unusual problem whereby the mysql error logs are showing that mysql is restarting every 10 minutes regardless of server load resulting in "Lost Connection -> Server has gone away" mysql query errors every 10 mins for a split second. The result is the "Users Online" drops from e.g. 180 to 0, then climbs back up, resulting in an infinite loop.

There are no other specific errors to be found and I would appreciate any help to obtain more specific error messages and even rectification to this problem.

Configuration:
P4 2.4ghz, 1gb RAM
Forum: http://www.arofanatics.com/forums (http://www.arofanatics.com/forums)
Sysinfo: http://www.arofanatics.com/phpSysInfo/ (http://www.arofanatics.com/phpSysInfo/)
Daily peak hour users: aprx 200 simultanous users
Apache 1.3.27 + mod_php 4.23 + mysql 3.23.52


--- start error logs ---

030118 01:43:59 mysqld restarted
/usr/local/libexec/mysqld: ready for connections
030118 02:04:01 mysqld restarted
/usr/local/libexec/mysqld: ready for connections
030118 02:24:03 mysqld restarted
/usr/local/libexec/mysqld: ready for connections
030118 02:43:56 mysqld restarted
/usr/local/libexec/mysqld: ready for connections
030118 02:44:03 mysqld restarted
/usr/local/libexec/mysqld: ready for connections
......

--- end error logs ---


--- start my.cnf ---

[Client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-innodb
default-character-set=latin1
set-variable = max_connections=500
set-variable = key_buffer=16M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1024
set-variable = sort_buffer=2M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = thread_cache_size=256
set-variable = myisam_sort_buffer_size=64M
set-variable = connect_timeout=10
set-variable = wait_timeout=14400
set-variable = max_connect_errors=10

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
[mysqlhotcopy]
interactive-timeout

--- end my.cnf ---


--- start mysqladmin extended-status ---

+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 328559 |
| Bytes_sent | 8786839 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 510 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 14 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 20 |
| Com_insert_select | 1 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 1761 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 224 |
| Connections | 128 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 24 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 108 |
| Handler_read_key | 25389 |
| Handler_read_next | 42551 |
| Handler_read_prev | 42 |
| Handler_read_rnd | 20826 |
| Handler_read_rnd_next | 548401 |
| Handler_update | 217 |
| Handler_write | 526 |
| Key_blocks_used | 852 |
| Key_read_requests | 73998 |
| Key_reads | 852 |
| Key_write_requests | 31 |
| Key_writes | 28 |
| Max_used_connections | 2 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 35 |
| Open_files | 66 |
| Open_streams | 0 |
| Opened_tables | 41 |
| Questions | 2659 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 204 |
| Select_range_check | 0 |
| Select_scan | 459 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 465 |
| Sort_rows | 334314 |
| Sort_scan | 393 |
| Table_locks_immediate | 2678 |
| Table_locks_waited | 1 |
| Threads_cached | 1 |
| Threads_created | 3 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 135 |
+--------------------------+---------+

--- end mysqladmin extended-status ---

[Steve Machol]

Perhaps you have a cron job that restarts it?

[Alvin Koh]

Originally posted by Steve Machol
Perhaps you have a cron job that restarts it?

Hi Steve, thank you for your prompt response.

There were no cron jobs that matched the timings of the restarts and the restart entries appeared in the *.err log of the mysql directory which lead me to suspect it's not a automation but an error.
However, I think I may have found the problem.

I searched through all the log files that exists on the server and finally found matching errors in the auth.log. It was an error denying an external script which attempts to connect to the database, but currently, all mysql user settings are allowable from "localhost" only. This script auto-refreshed every 20 minutes and was a very old script which didn't react well with the new server.

Regards,
Alvin.