| Bug #31641 | Recurring InnoDB Crashes | ||
|---|---|---|---|
| Submitted: | 16 Oct 2007 15:16 | Modified: | 17 Oct 2007 22:33 |
| Reporter: | Daniel Khan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.0.45 | OS: | Linux (2.6.18-4-amd64 (debian etch backport)) |
| Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[16 Oct 2007 15:29]
MySQL Verification Team
Thank you for the bug report. Could you please provide the show create table table_name of one of those which doing optimize presents that behavior?. Thanks in advance.
[16 Oct 2007 15:37]
Daniel Khan
By the way I also saw very high cpu loads (100%-120%).
I now powered down the server and started with no luck.
The error log reports corruptions all the time but it somehow recovers and restarts afterwards.
Here is the requested information + I'll also attach my my.cnf
CREATE TABLE `ShpItem` (
`itemID` int(10) unsigned NOT NULL auto_increment,
`shopID` int(11) unsigned NOT NULL default '0',
`itemNr` varchar(64) collate latin1_german2_ci NOT NULL,
`itemNrMD5` varchar(32) collate latin1_german2_ci NOT NULL default '',
`matchcode` varchar(32) collate latin1_german2_ci NOT NULL default '',
`aktiv` enum('-1','1') collate latin1_german2_ci NOT NULL default '-1',
`ean_isbn` varchar(32) collate latin1_german2_ci NOT NULL default '',
`vendor` varchar(256) collate latin1_german2_ci NOT NULL,
`topangebot` enum('-1','1') collate latin1_german2_ci NOT NULL default '-1',
`topangebot_shop` enum('-1','1') collate latin1_german2_ci NOT NULL default '-1',
`gueltig_von` int(10) unsigned NOT NULL default '0',
`gueltig_bis` int(10) unsigned NOT NULL default '0',
`bezeichnung` varchar(128) collate latin1_german2_ci NOT NULL default '',
`bezeichnung2` varchar(128) collate latin1_german2_ci NOT NULL default '',
`kbeschreibung` mediumtext collate latin1_german2_ci NOT NULL,
`lbeschreibung` mediumtext collate latin1_german2_ci NOT NULL,
`preis` decimal(6,2) NOT NULL default '0.00',
`preis_statt` decimal(6,2) NOT NULL default '0.00',
`ab` enum('-1','1') collate latin1_german2_ci NOT NULL default '-1',
`aufanfrage` enum('-1','1') collate latin1_german2_ci NOT NULL default '-1',
`lieferinfo` text collate latin1_german2_ci NOT NULL,
`link` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userCat1` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userCat2` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userCat3` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userCat4` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userCat5` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userImageUrl1` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userImageUrl2` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userImageUrl3` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userImageUrl4` varchar(255) collate latin1_german2_ci NOT NULL default '',
`userImageUrl5` varchar(255) collate latin1_german2_ci NOT NULL default '',
`attributes` text collate latin1_german2_ci NOT NULL,
`datesortkey` int(10) unsigned NOT NULL default '0',
`keywords` mediumtext collate latin1_german2_ci NOT NULL,
`bulkActionID` varchar(32) collate latin1_german2_ci NOT NULL default '',
`rowStatus` enum('N','A','O','D') collate latin1_german2_ci NOT NULL default 'A',
`indexStatus` enum('P','I') collate latin1_german2_ci NOT NULL default 'P',
PRIMARY KEY (`itemID`),
KEY `shopID` (`shopID`),
KEY `bulkActionID` (`bulkActionID`),
KEY `userCat1` (`userCat1`),
KEY `userCat2` (`userCat2`),
KEY `userCat3` (`userCat3`),
KEY `userCat4` (`userCat4`),
KEY `userCat5` (`userCat5`),
KEY `ean_isbn` (`ean_isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci COMMENT='Artikeltabelle' AUTO_INCREMENT=13346375 ;
[16 Oct 2007 15:39]
Daniel Khan
my.cnf
Attachment: my.cnf.txt (text/plain), 5.14 KiB.
[16 Oct 2007 23:36]
Daniel Khan
Here's another error that occurs if everything goes wrong: Error: index `katID` of table `shopping-live/ShpItem2Cat` contains 1762016 entries, should be 18446744073709551615 Error: index `itemID` of table `shopping-live/ShpItem2Cat` contains 1762016 entries, should be 18446744073709551615 Error: index `shopID` of table `shopping-live/ShpItem2Cat` contains 1762016 entries, should be 18446744073709551615
[17 Oct 2007 12:36]
Heikki Tuuri
This is probably file corruption.
[17 Oct 2007 12:39]
Heikki Tuuri
071016 16:29:37 InnoDB: Page checksum 1775654167, prior-to-4.0.14-form checksum 215858221 InnoDB: stored checksum 997421925, prior-to-4.0.14-form stored checksum 666180653 InnoDB: Page lsn 544695660 1953981801, low 4 bytes of lsn at page end 2885491102 InnoDB: Page number (if stored to page already) 1847621221, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 1684628768 InnoDB stamps the checksums just prior writing the page to the file. It is unlikely that InnoDB could corrupt them. Looks like the page number and the space id in the page header are garbage. Please check your disk subsystem.
[17 Oct 2007 22:33]
Daniel Khan
Thank's for the reply. Meanwhile I migrated the data to another host and it runs painfree now. So although disks, ram and raid controller were changed the hardware was obviously still faulty. I'll close this report. Greetings -- Daniel Khan
Description: Hello, I have database with large InnoDB Tables (> 1.500.000 rows). Those tables sometimes went corrupt when I restart the server. It is reproducable if I try to do an optimize table on one of the large ones. Then the error log shows: 071016 16:29:33 InnoDB: Started; log sequence number 3 196810722 071016 16:29:34 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.45-Debian_1~bpo.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian etch distribution InnoDB: Error: page n:o stored in the page read in is 1847621221, should be 11843! InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 11843. InnoDB: You may have to recover from a backup. 071016 16:29:37 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 3b7373656e207665727a6175626572742077656c74776569742042616e6765722c20 [..] ch >>Kunst & Kultur>>ArchitekturF bac46fb72ab64252 p+I% U c' - ;InnoDB: End of page dump 071016 16:29:37 InnoDB: Page checksum 1775654167, prior-to-4.0.14-form checksum 215858221 InnoDB: stored checksum 997421925, prior-to-4.0.14-form stored checksum 666180653 InnoDB: Page lsn 544695660 1953981801, low 4 bytes of lsn at page end 2885491102 InnoDB: Page number (if stored to page already) 1847621221, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 1684628768 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 11843. 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 InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. InnoDB: Warning: we did not need to do crash recovery, but log scan InnoDB: progressed past the checkpoint lsn 3 196810722 up to lsn 3 196810732 071016 16:29:39 InnoDB: Started; log sequence number 3 196810722 071016 16:29:39 [Note] Recovering after a crash using mysql-bin 071016 16:29:39 [Note] Starting crash recovery... 071016 16:29:39 [Note] Crash recovery finished. 071016 16:29:39 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.45-Debian_1~bpo.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Debian etch distribution InnoDB: Error: page n:o stored in the page read in is 1847621221, should be 11843! InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 11843. InnoDB: You may have to recover from a backup. 071016 16:29:41 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 3b7373656e207665727a6175626572742077 [..] Afterwards I get something like this: InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. 071016 16:56:00 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... 071016 16:56:00 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 2313616646. InnoDB: Doing recovery: scanned up to log sequence number 3 2318859264 InnoDB: Doing recovery: scanned up to log sequence number 3 2324102144 InnoDB: Doing recovery: scanned up to log sequence number 3 2329345024 InnoDB: Doing recovery: scanned up to log sequence number 3 2334587904 InnoDB: Doing recovery: scanned up to log sequence number 3 2339830784 InnoDB: Doing recovery: scanned up to log sequence number 3 2345073664 InnoDB: Doing recovery: scanned up to log sequence number 3 2350316544 InnoDB: Doing recovery: scanned up to log sequence number 3 2355559424 InnoDB: Doing recovery: scanned up to log sequence number 3 2360802304 InnoDB: Doing recovery: scanned up to log sequence number 3 2366045184 InnoDB: Doing recovery: scanned up to log sequence number 3 2371288064 InnoDB: Doing recovery: scanned up to log sequence number 3 2376530944 InnoDB: Doing recovery: scanned up to log sequence number 3 2381773824 InnoDB: Doing recovery: scanned up to log sequence number 3 2387016704 InnoDB: Doing recovery: scanned up to log sequence number 3 2392259584 InnoDB: Doing recovery: scanned up to log sequence number 3 2397502464 InnoDB: Doing recovery: scanned up to log sequence number 3 2402745344 InnoDB: Doing recovery: scanned up to log sequence number 3 2407988224 InnoDB: Doing recovery: scanned up to log sequence number 3 2413231104 InnoDB: Doing recovery: scanned up to log sequence number 3 2418473984 InnoDB: Doing recovery: scanned up to log sequence number 3 2423716864 I upgraded mysql from 5.0.32 with no result, I exchanged disks, raid controller and ram. Nothing helped. I am lost. Thanks in advance -- Daniel Khan How to repeat: optimize table foo