Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I think we've got a corrupt table in this situation. A website with a Drupal setup we host shows the following error:

General error: 2006 MySQL server has gone away: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => ctools_export:views_view ) in lock_may_be_available() (line 167 of /var/www/vhosts/[HOSTNAME]/httpdocs/includes/lock.inc).

Up front: I'm not an MySQL guru, but I've tried the following:

mysql> show tables;

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: drupal_db

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

I can't select any data whatsoever from the database. I've tried repairing the database:

$ mysqlcheck -B drupal_db -uadmin -p[PASS] -r --auto-repair
drupal_db.actions
note     : The storage engine for the table doesn't support repair
drupal_db.authmap
note     : The storage engine for the table doesn't support repair
drupal_db.batch
note     : The storage engine for the table doesn't support repair
drupal_db.block
note     : The storage engine for the table doesn't support repair
drupal_db.block_custom
note     : The storage engine for the table doesn't support repair
drupal_db.block_node_type
note     : The storage engine for the table doesn't support repair
drupal_db.block_role
note     : The storage engine for the table doesn't support repair
drupal_db.blocked_ips
note     : The storage engine for the table doesn't support repair
drupal_db.cache
note     : The storage engine for the table doesn't support repair
drupal_db.cache_block
note     : The storage engine for the table doesn't support repair
drupal_db.cache_bootstrap
note     : The storage engine for the table doesn't support repair
drupal_db.cache_field
note     : The storage engine for the table doesn't support repair
drupal_db.cache_filter
note     : The storage engine for the table doesn't support repair
drupal_db.cache_form
note     : The storage engine for the table doesn't support repair
drupal_db.cache_image
note     : The storage engine for the table doesn't support repair
drupal_db.cache_media_xml
note     : The storage engine for the table doesn't support repair
drupal_db.cache_menu
note     : The storage engine for the table doesn't support repair
drupal_db.cache_page
note     : The storage engine for the table doesn't support repair
drupal_db.cache_path
note     : The storage engine for the table doesn't support repair
drupal_db.cache_token
note     : The storage engine for the table doesn't support repair
drupal_db.cache_update
note     : The storage engine for the table doesn't support repair
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'REPAIR TABLE ... '

The table that should be on the next to be tried by repair should be cache_views, but it breaks on that table. After running this, the following gets appended in the /var/log/mysqld.log

Version: '5.5.28-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1677.
InnoDB: You may have to recover from a backup.
130408 17:19:31  InnoDB: Page dump in ascii and hex (16384 bytes):
REALLY LONG HEX
InnoDB: End of page dump
130408 17:19:31  InnoDB: Page checksum 519432539, prior-to-4.0.14-form checksum 2411398916
InnoDB: stored checksum 1927538235, prior-to-4.0.14-form stored checksum 2411398916
InnoDB: Page lsn 7 3847827059, low 4 bytes of lsn at page end 3847827059
InnoDB: Page number (if stored to page already) 1677,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 46249
InnoDB: (index "PRIMARY" of table "drupal_db"."cache_views")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1677.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
130408 17:19:31  InnoDB: Assertion failure in thread 2824203152 in file buf0buf.c line 3602
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
15:19:31 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337908 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xab064f8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = ffffffffa855e378 thread_stack 0x30000
/usr/libexec/mysqld(my_print_stacktrace+0x33)[0x8424cf3]
/usr/libexec/mysqld(handle_fatal_signal+0x42b)[0x82d6dfb]
[0xca5420]
[0xca5402]
/lib/libc.so.6(gsignal+0x50)[0x767e30]
/lib/libc.so.6(abort+0x101)[0x769741]
/usr/libexec/mysqld[0x8537201]
/usr/libexec/mysqld[0x8543034]
/usr/libexec/mysqld[0x8543dc1]
/usr/libexec/mysqld[0x8533b40]
/usr/libexec/mysqld[0x851a1c4]
/usr/libexec/mysqld[0x8520bac]
/usr/libexec/mysqld[0x85560e8]
/usr/libexec/mysqld[0x8556c61]
/usr/libexec/mysqld[0x84b1bab]
/usr/libexec/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3d)[0x82d8f3d]
/usr/libexec/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x5af)[0x82303bf]
/usr/libexec/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0xa57)[0x816c797]
/usr/libexec/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x2e3)[0x816e203]
/usr/libexec/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x57)[0x816ebe7]
/usr/libexec/mysqld[0x82651c0]
/usr/libexec/mysqld(_ZN22Repair_table_statement7executeEP3THD+0xed)[0x8266aad]
/usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x493e)[0x81aaf1e]
/usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x471)[0x81afe51]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1a9b)[0x81b193b]
/usr/libexec/mysqld(_Z10do_commandP3THD+0x136)[0x81b1e46]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x145)[0x825a325]
/usr/libexec/mysqld(handle_one_connection+0x4f)[0x825a40f]
/lib/libpthread.so.0[0x8aa912]
/lib/libc.so.6(clone+0x5e)[0x81460e]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (ab8a9b0): REPAIR TABLE `cache_views`
Connection ID (thread ID): 3
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
130408 17:19:31 mysqld_safe Number of processes running now: 0
130408 17:19:31 mysqld_safe mysqld restarted
130408 17:19:31 [Note] libgovernor.so not found
130408 17:19:31 [Note] Plugin 'FEDERATED' is disabled.
130408 17:19:31 InnoDB: The InnoDB memory heap is disabled
130408 17:19:31 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
130408 17:19:31 InnoDB: Compressed tables use zlib 1.2.3
130408 17:19:31 InnoDB: Using Linux native AIO
130408 17:19:31 InnoDB: Initializing buffer pool, size = 128.0M
130408 17:19:31 InnoDB: Completed initialization of buffer pool
130408 17:19:31 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130408 17:19:31  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...
130408 17:19:31  InnoDB: Waiting for the background threads to start
130408 17:19:32 InnoDB: 1.1.8 started; log sequence number 34134973312
130408 17:19:32 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130408 17:19:32 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
130408 17:19:32 [Note] Server socket created on IP: '0.0.0.0'.
130408 17:19:32 [Note] Event Scheduler: Loaded 0 events
130408 17:19:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.28-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp

So I tried dropping it, but also that one gets me an connection problem...

What else can I try?

my.conf (already tried upping the max_allowed_packet):

[mysqld]
local-infile=0
max_allowed_packet=128M
#set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# Forced OLD_PASSWORD format is turned OFF by Plesk
#old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

MySQL version: mysql Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1

It's a PLESK 11.0.9 server with CentOS 5.9 (Final)

edit: let me add that the loss of the cache_views is acceptable collateral damage if needed.

share|improve this question

migrated from serverfault.com Apr 10 '13 at 20:36

This question came from our site for professional system and network administrators.

    
Is there anything in that error log? (/var/log/mysqld.log according to your config.) It sounds like the MySQL server is crashing when trying to open the tables. –  Ladadadada Apr 8 '13 at 11:52
    
chances are your cache_views table is of type InnoDB which you can't repair like myisam –  Mike Apr 8 '13 at 12:00
    
@Ladadadada I've now edited my original question to include the log. Edit: oh wait, my terminal did not show the total log, I'll edit again in a sec. –  Ambidex Apr 8 '13 at 15:13
    
If you have a backup, restoring from that will be the easiest method. A quick read of this should give you an idea of what's required to recover from InnoDB table corruption. Fixing this is beyond my knowledge but I've recommended a migration to DBA.SE where the question is more likely to find an answer. –  Ladadadada Apr 8 '13 at 15:21
    
I do not have ANY CLUE how this happend, but this morning, arriving at the office and opening that website again to review the error again it just magically disappeared and showed the website again. O_O –  Ambidex Apr 9 '13 at 7:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.