MySQL Bugs: #21851: Mysql keep crashing with page corruption on disk or a failed file read

Bug #21851	Mysql keep crashing with page corruption on disk or a failed file read
Submitted:	26 Aug 2006 19:04	Modified:	28 Dec 2006 12:50
Reporter:	Alex	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.22	OS:	FreeBSD (FreeBSD 6.1)
Assigned to:		CPU Architecture:	Any

Description:
After moving from MySQL 4.x to 5.0.22 i have constant problems with mysql crashing on diffrent tables (all of them are quite big - from 5k up to 1200k rows). Engine used is InnoDB.

MySQL then starts with crash recovery - but after few crashes it dies with signal 11, and recreating whole database from mysqldumps and mysqlbinlogs is necessary.

So i have upgraded mysql to 5.0.24 - with no luck, same errors are occuring again.

So i moved whole database to the another computer (same OS, but maybe it was hardware fault) - and again, the same bug hit again.

Database was rebuild from scratch (with recreating the innodb data files).

Here is some info about tables:

CREATE TABLE `privgallery` (
  `pg_id` int(10) unsigned NOT NULL auto_increment,
  `pg_usid` int(10) unsigned NOT NULL default '1',
  `pg_pcid` int(10) unsigned NOT NULL default '1',
  `pg_psid` int(10) unsigned default '1',
  `pg_created` int(10) unsigned NOT NULL default '0',
  `pg_changed` int(10) unsigned NOT NULL default '0',
  `pg_atid` int(10) unsigned NOT NULL default '0',
  `pg_type` mediumint(8) unsigned NOT NULL default '5',
  `pg_pics` mediumint(8) unsigned NOT NULL default '0',
  `pg_title` varchar(255) NOT NULL default '',
  `pg_descr` text NOT NULL,
  PRIMARY KEY  (`pg_id`),
  KEY `fk_pgusid` (`pg_usid`),
  KEY `fk_pgpcid` (`pg_pcid`),
  KEY `fk_pgpsid` (`pg_psid`),
  KEY `fk_pgatid` (`pg_atid`),
  CONSTRAINT `0_544` FOREIGN KEY (`pg_usid`) REFERENCES `users` (`us_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

CREATE TABLE `forum_msg` (
  `fm_fpid` int(10) unsigned NOT NULL default '0',
  `fm_title` varchar(100) NOT NULL default '',
  `fm_text` text NOT NULL,
  KEY `fk_fmfpid` (`fm_fpid`),
  CONSTRAINT `0_129` FOREIGN KEY (`fm_fpid`) REFERENCES `forum_posts` (`fp_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

How to repeat:
I have no idea - but it happens with diffrent machines.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional Info:
Please verify if you have applied the recommendation for an upgrade from
4.1 to 5.0:

2.11.2. Upgrading from MySQL 4.1 to 5.0

I wouldn't tell name the bug "can't repeat", becouse the same database was running on two diffrent machines and they both crashed. Database was recreated from mysqldump files, so any changes in ibdata* files couldn't be an issue.
Upgrading from 4.1 to 5.0 was done as instructed in manual, and all databases where checked with "mysqlcheck --check-upgrade --all-databases".

Here are queries few seconds before crash (extracted with mysqlbinlog):

BEGIN;
# at 6961300
#060825 13:31:49 server id 1  end_log_pos 319   Query   thread_id=15051 exec_time=0     error_code=0
SET TIMESTAMP=1156505509;
INSERT INTO visitors (vi_ac, vi_usid, vi_time, vi_ip, vi_ipproxy, vi_browser, vi_url) VALUES ('36fad8a5986d09d006ad9ff2ee2d95ee961
a5672', '91675', UNIX_TIMESTAMP(), '1394005271', '0', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)', '/i/home.pl');
# at 6961619
#060825 13:31:49 server id 1  end_log_pos 478   Query   thread_id=15051 exec_time=0     error_code=0
SET TIMESTAMP=1156505509;
UPDATE sessions SET se_ctime = UNIX_TIMESTAMP() WHERE se_id = '0c309f0eec050cdbe5a14e897d64ad40';
# at 6961778
#060825 13:31:49 server id 1  end_log_pos 6961805       Xid = 103803
COMMIT;
# at 6961805
#060825 13:31:52 server id 1  end_log_pos 6961873       Query   thread_id=15051 exec_time=0     error_code=0
SET TIMESTAMP=1156505512;
BEGIN;
# at 6961873
#060825 13:31:52 server id 1  end_log_pos 395   Query   thread_id=15051 exec_time=0     error_code=0
SET TIMESTAMP=1156505512;
UPDATE sessions SET se_usid = '91675', se_ctime = UNIX_TIMESTAMP(), se_data = '^E^G^C\0\0<80>\0\0\0^GIPPROXY\n^E91675\0\0\0^FUSERI
D\n 0c309f0eec050cdbe5a14e897d64ad40\0\0\0^CSID\n^F471335\0\0\0^EPERMS\n\n1394005271\0\0\0^BIP\n^Gadi1979\0\0\0^DNICK\n^Radi79@neo
strada.pl\0\0\0^EEMAIL\n^A0\0\0\0^BPM' WHERE se_id = '0c309f0eec050cdbe5a14e897d64ad40';
# at 6962268
#060825 13:31:52 server id 1  end_log_pos 6962295       Xid = 103820
COMMIT;
# at 6962295
#060825 13:31:52 server id 1  end_log_pos 6962363       Query   thread_id=15053 exec_time=0     error_code=0
SET TIMESTAMP=1156505512;
BEGIN;
# at 6962363
#060825 13:31:52 server id 1  end_log_pos 159   Query   thread_id=15053 exec_time=0     error_code=0
SET TIMESTAMP=1156505512;
UPDATE sessions SET se_ctime = UNIX_TIMESTAMP() WHERE se_id = 'b9e3489eeb06df8b77ff98c4b288fada';
# at 6962522
#060825 13:31:52 server id 1  end_log_pos 6962549       Xid = 103826
COMMIT;
# at 6962549
#060825 13:31:53 server id 1  end_log_pos 6962617       Query   thread_id=15055 exec_time=0     error_code=0
SET TIMESTAMP=1156505513;
BEGIN;
# at 6962617
#060825 13:31:53 server id 1  end_log_pos 400   Query   thread_id=15055 exec_time=0     error_code=0
SET TIMESTAMP=1156505513;
INSERT INTO sessions (se_id, se_time, se_ctime, se_ip, se_ipproxy, se_data) VALUES ('246af68460e1e265333ea60874176e29', UNIX_TIMES
TAMP(), UNIX_TIMESTAMP(), '1345841804', '0', '^E^G^C\0\0\0^<80>\0\0\0^GIPPROX<81>\0\0\0^FUSERID\n 246af68460e1e265333ea60874176e29
\0\0\0^CSID\n^D4097\0\0\0^EPERMS\n\n1345841804\0\0\0^BIP\n\0\0\0\0^DNICK\n\0\0\0\0^EEMAIL');
# at 6963017
#060825 13:31:53 server id 1  end_log_pos 6963044       Xid = 103850
COMMIT;
# at 6963044
#060825 13:31:53 server id 1  end_log_pos 6963112       Query   thread_id=15056 exec_time=0     error_code=0
SET TIMESTAMP=1156505513;
BEGIN;
# at 6963112
#060825 13:31:53 server id 1  end_log_pos 400   Query   thread_id=15056 exec_time=0     error_code=0
SET TIMESTAMP=1156505513;
INSERT INTO sessions (se_id, se_time, se_ctime, se_ip, se_ipproxy, se_data) VALUES ('08d91648ae2389216f28aad99a9d3d1e', UNIX_TIMES
TAMP(), UNIX_TIMESTAMP(), '1425665204', '0', '^E^G^C\0\0\0^<80>\0\0\0^GIPPROX<81>\0\0\0^FUSERID\n 08d91648ae2389216f28aad99a9d3d1e
\0\0\0^CSID\n^D4097\0\0\0^EPERMS\n\n1425665204\0\0\0^BIP\n\0\0\0\0^DNICK\n\0\0\0\0^EEMAIL');
# at 6963512
#060825 13:31:53 server id 1  end_log_pos 6963539       Xid = 103856
COMMIT;
# at 4
#060825 13:31:55 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.22-log created 060825 13:31:55 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK;

If you need more data (i'd like to point that i've already attached the mysql error log from both machines - set as avaible only for mysql developers), just let me know.

Thank you for the feedback. Could you please provide a dump of the tables
involved with insert data enough to run the query and reproduce the crash,
also the my.cnf if it needed. You can upload the compressed file at:

ftp://ftp.mysql.com/pub/mysql/upload

where it will have access only for MySQL's developers.

Thanks in advance.

Yes, I had found it:

mysqldev@production:~> ls -l /supportftp/pub/mysql/upload/bug-data-21851.tgz
--w-rw----  1 ftpuploads myftp 6105459 Sep  7 02:58 /supportftp/pub/mysql/upload
/bug-data-21851.tgz

Is it correct file?

Hi!

Valeriy Kravchuk has changed status to "need feedback" - but without any message what other then already included feedback is needed. I'd like to remind that 5 days ago i've uploaded bug-data-21851.tgz to ftp.mysql.com, but i did not recive any message that that file was checked or read.

Please reply!
Alex

I had asked a question in private comment. Now I made it public. Anyway, I've got your file and will check it. No feedback is needed now.

Please, try to repeat with a newer version, 5.0.27, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".