Description:
[May be a duplicate of Bug #13707]
I have a large table that was corrupted. The server was crashing when I would run insert delayed...
--Table definition
CREATE TABLE `izfc_log_error` (
`izfc_log_error_id` varchar(16) NOT NULL default '',
`create_date` datetime NOT NULL default '0000-00-00 00:00:00',
`script_file` varchar(255) NOT NULL default '',
`script_line` smallint(5) unsigned NOT NULL default '0',
`error_source` varchar(32) NOT NULL default '',
`message` text NOT NULL,
`error` text NOT NULL,
PRIMARY KEY (`izfc_log_error_id`),
KEY `create_date` (`create_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
-- Problem Query:
INSERT DELAYED INTO izfc_log_error SET izfc_log_error_id = 'dfe61905948319fe', create_date = NOW(), script_file = 'foo', script_line = '23', error_source = 'DEVELOPMENT', message = '';
mysql server would crash (and restart) each time this query was run. It did not matter what I changed the data to, it still crashed.
So, I repaired the table, and the problem DID not go away. I thought you may want to know, because, it doesn't seem like the server should die even if a table is corrupt.
I have the *repaired* table sitting on disk (I moved it), and am not sure the cause of this. I can 100% reproduce the error on that table, but not a duplicate table like it.
---------------------------------------------------------------------------------------
Resolved stack dump:
0x8156750 handle_segfault + 416
0x6f68a8 (?)
0x8ac5738 _end + 5643016
0x819150a _Z11fill_recordP3THDR4ListI4ItemES4_b + 122
0x81915a5 _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table_triggers_list14trg_event_type + 53
0x81b46cf _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb + 1199
0x816d414 _Z21mysql_execute_commandP3THD + 8292
0x8172880 _Z11mysql_parseP3THDPcj + 304
0x8169d03 _Z16dispatch_command19enum_server_commandP3THDPcj + 1139
0x816983d _Z10do_commandP3THD + 141
0x8168d48 handle_one_connection + 600
0x6f01d5 (?)
0x60a2da (?)
-----------------------------------------------------
Error message:
mysqld got signal 11;
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=209715200
read_buffer_size=2093056
max_used_connections=1
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 613999 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x8abd828
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...
Cannot determine thread, fp=0xc7f5b46c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8156750
0x6f68a8
0x8ac5738
0x819150a
0x81915a5
0x81b46cf
0x816d414
0x8172880
0x8169d03
0x816983d
0x8168d48
0x6f01d5
0x60a2da
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8ac4c78 = INSERT DELAYED INTO izfc_log_error SET izfc_log_error_id = 'dfe61905948319fe', create_date = NOW(), script_file = 'foo', script_line = '23', error_source = 'DEVELOPMENT', message = ''
thd->thread_id=3
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
051109 16:49:10 mysqld restarted
051109 16:49:11 InnoDB: Started; log sequence number 0 43756
051109 16:49:11 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
051109 16:49:11 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000018' at position 79, relay log '/var/lib/mysql/log_bin/relaylog.000001' position: 180766792
--------------------------------------------------------------------
Check and Repair
[root@beryllium data]# myisamchk -s vos4_2/izfc_log_error.MYI
myisamchk: MyISAM file vos4_2/izfc_log_error.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: warning: Datafile is almost full, 4294967096 of 4294967294 used
myisamchk: error: Unexpected byte: 3 at link: 4294965376
MyISAM-table 'vos4_2/izfc_log_error.MYI' is corrupted
Fix it using switch "-r" or "-o"
[root@beryllium data]# myisamchk -rv vos4_2/izfc_log_error.MYI
- recovering (with sort) MyISAM-table 'vos4_2/izfc_log_error.MYI'
Data records: 183998
- Fixing index 1
- Searching for keys, allocating buffer for 10737391 keys
Wrong bytesec: 215- 0- 0 at 8900; Skipped
Wrong bytesec: 102- 48- 56 at 8920; Skipped
Wrong bytesec: 118- 97-114 at 8940; Skipped
Wrong bytesec: 114-111-106 at 8960; Skipped
Wrong bytesec: 98-105-110 at 8980; Skipped
Wrong bytesec: 80-114-111 at 9000; Skipped
Wrong bytesec: 69- 76- 79 at 9024; Skipped
... (Many lines) ...
Wrong bytesec: 45- 45- 45 at 196616956; Skipped
Wrong bytesec: 46- 46- 47 at 196616976; Skipped
Wrong bytesec: 113-117-105 at 196616996; Skipped
Wrong bytesec: 108-111-103 at 196617016; Skipped
Wrong bytesec: 32-115-116 at 196617036; Skipped
Wrong bytesec: 101- 32-111 at 196617056; Skipped
Wrong bytesec: 0- 0- 0 at 196617076; Skipped
- Dumping 183998 keys
- Fixing index 2
- Searching for keys, allocating buffer for 183999 keys
- Dumping 183998 keys
[root@beryllium data]# myisamchk -s vos4_2/izfc_log_error.MYI
myisamchk: MyISAM file vos4_2/izfc_log_error.MYI
myisamchk: warning: Datafile is almost full, 4294734260 of 4294967294 used
MyISAM-table 'vos4_2/izfc_log_error.MYI' is usable but should be fixed
???
How to repeat:
I can't repeat it except for the table that is on disk:
-----------------------------------------------
A test case THAT works correctly:
-- Copy of table:
CREATE TABLE `xx_izfc_log_error` (
`izfc_log_error_id` varchar(16) NOT NULL default '',
`create_date` datetime NOT NULL default '0000-00-00 00:00:00',
`script_file` varchar(255) NOT NULL default '',
`script_line` smallint(5) unsigned NOT NULL default '0',
`error_source` varchar(32) NOT NULL default '',
`message` text NOT NULL,
`error` text NOT NULL,
PRIMARY KEY (`izfc_log_error_id`),
KEY `create_date` (`create_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- Problem Query:
INSERT DELAYED INTO xx_izfc_log_error SET izfc_log_error_id = 'dfe61905948319fe', create_date = NOW(), script_file = 'foo', script_line = '23', error_source = 'DEVELOPMENT', message = '';
-- It WORKED!
