Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I recently loaded a large (~5GB) database from an SQLdump into a local MySQL server database. Loaded fine after a couple of tries, and initially I could query without a problem, although queries took ~1.5 minutes (which is not ideal, but fine).

Now, however, the queries drop out after a couple of seconds with

ERROR 2013 (HY000): Lost connection to MySQL server during query

and I even had a

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

This is extra weird because it worked fine for the first hour or so (both through mysql-workbench and through the standard command line interface).

I've already upped my max_allowed_packet size in my.conf to 64MB, but that has no impact. Any ideas? I'm on Ubuntu 12.04 (3.3.6-030306-generic kernel).

UGH. So it looks like either a bug or maybe some kind of hardware failure. error.log shows a load of hex, followed by

p c    -> y;

InnoDB: End of page dump

130509 18:25:51  InnoDB: Page checksum 3880653435, prior-to-4.0.14-form checksum 3737975691
InnoDB: stored checksum 386314462, prior-to-4.0.14-form stored checksum 3737975691
InnoDB: Page lsn 4 759090297, low 4 bytes of lsn at page end 759090297
InnoDB: Page number (if stored to page already) 240815,
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 208
InnoDB: (index "PRIMARY" of table "chembl_15"."compound_structures")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 240815.
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.
130509 18:25:51  
InnoDB: Assertion failure in thread 139737190622976 in file buf0buf.c line 3629
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.
23:25:51 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=16777216
read_buffer_size=131072
max_used_connections=2
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346682 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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 = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7f172cfd87b9]
/usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7f172ce9e8f3]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f172bbebcb0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f172b257425]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x17b)[0x7f172b25ab8b]
/usr/sbin/mysqld(+0x5f2b81)[0x7f172d075b81]
/usr/sbin/mysqld(+0x620cc9)[0x7f172d0a3cc9]
/usr/sbin/mysqld(+0x5ad1c0)[0x7f172d0301c0]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7f172bbe3e9a]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f172b314ccd]
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.
130509 18:25:51 [Note] Plugin 'FEDERATED' is disabled.
130509 18:25:51 InnoDB: The InnoDB memory heap is disabled
130509 18:25:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130509 18:25:51 InnoDB: Compressed tables use zlib 1.2.3.4
130509 18:25:51 InnoDB: Initializing buffer pool, size = 128.0M
130509 18:25:52 InnoDB: Completed initialization of buffer pool
130509 18:25:52 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!
130509 18:25:52  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...
130509 18:25:52  InnoDB: Waiting for the background threads to start
130509 18:25:53 InnoDB: 5.5.31 started; log sequence number 21293128389
130509 18:25:53 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130509 18:25:53 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
130509 18:25:53 [Note] Server socket created on IP: '127.0.0.1'.
130509 18:25:53 [Note] Event Scheduler: Loaded 0 events
130509 18:25:53 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.31-0ubuntu0.12.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 240815.
InnoDB: You may have to recover from a backup.

130509 18:27:15  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 

Any thoughts :-(

share|improve this question
1  
Nothing in the mysql logfiles? –  ott-- May 10 '13 at 0:52
3  
I think Database page corruption on disk or a failed file read. You may have to recover from a backup. says it all –  Sathya May 10 '13 at 3:42
    
Start running SMART self-tests. –  Xyon May 10 '13 at 10:18
    
@Sathya - ha, yeah, I got that, what I meant was any ideas what could have caused this? –  Alex May 10 '13 at 13:21
    
What query were you running at the point mysql crashed ??? –  RolandoMySQLDBA May 10 '13 at 16:47

2 Answers 2

up vote 2 down vote accepted

So I have no idea what caused this, but it seems like the line

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.

Was spot on - I rebooted my system and can now query again without failure. I wonder if this is related to the fact that after loading the database I installed MySQL-workbench, although it did still work for an hour or so after that.

One thing that could be relevant - my OS runs on an SSD which is only 20 GBs, while the MySQL data folder is on a HDD (~750 GB). I wonder if there was/is some cache issue caused by this split (there shouldn't be, but who knows).

UPDATE

Three days later and no corruptions yet...

share|improve this answer

I had something like this, and in my case a reboot of the virtual machine that runs the database fixed the problem while restarting mysql did not. My understanding is that this points to a RAM error.

If the hard drives themselves had been powered down and restarted as in rebooting the host environment of our VM, then that would leave more possibilities open regarding hardware failures in the disk system and its caching, and

I didn't think to ask about ECC Ram when I commissioned the server, which I'm now regretting. Still, our hosting provider is happy to swap the Ram out for new DIMMs.

If a server reboot didn't fix the problem, then would most likely not be a RAM error.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.