Avatar billede svinth Nybegynder
03. april 2014 - 14:17 Der er 1 løsning

Corrupt page tabel eller lignende!?

Hej eksperter.

Jeg er ikke servermand, men programmør. Mit hostingcenter havde for 1,5 måned siden et strømnedbrud, og dengang fiksede jeg noget table corruption, vha recovery mode, og ved at truncate en tabel i DB A, og smide backup'en tilbage i den. Det virkede ikke denne gang. Tabellen der var tale om, er den der nedenfor i loggen hedder "shop_product".

Denne gang er problemet lidt anderledes, idet det er et databasekald fra en anden DB, DB B, der laver dump af tabellen shop_product, i DB A! Jeg forsøgte at recover den på samme vis, dvs shop_product i DB A, som beskrevet ovenfor, uden effekt. Så jeg lurer på om det er en random tabel-pegepind ( i mangel af viden om jargonen), fra den anden DB B, som er kommet til at pege over på noget random data i DB A's shop_product tabel?

Nogen der har en ide om problemet og gerne dets løsning, så sig til! Log af 2 kald til DB B, og deraf følgende dump i DB A er her:

Apr  3 14:01:32 46247 mysqld: InnoDB: Database page corruption on disk or a failed
Apr  3 14:01:32 46247 mysqld: InnoDB: file read of page 17739.
Apr  3 14:01:32 46247 mysqld: InnoDB: You may have to recover from a backup.
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32  InnoDB: Page dump in ascii and hex (16384 bytes):
Apr  3 14:01:32 46247 mysqld:  len 16384; hex e55f1ed10000454b0000454a0000454c000000007873bb3b45bf000000000000000000000000000b3de98031194d00d33b6f00050000002e00000000000000000000000000000000043400000000000000000000000000000000000000000100020027696e66696d756d0007000b000073757072656d756d00000700260a000000002d200100001001
<snip dump>
Apr  3 14:01:32 46247 mysqld:                                        p p3I/ + %x!0      F c>I  x=  ;InnoDB: End of page dump
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32  InnoDB: Page checksum 153379797, prior-to-4.0.14-form checksum 4209165028
Apr  3 14:01:32 46247 mysqld: InnoDB: stored checksum 3848216273, prior-to-4.0.14-form stored checksum 1045027035
Apr  3 14:01:32 46247 mysqld: InnoDB: Page lsn 0 2020850491, low 4 bytes of lsn at page end 2017309962
Apr  3 14:01:32 46247 mysqld: InnoDB: Page number (if stored to page already) 17739,
Apr  3 14:01:32 46247 mysqld: InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
Apr  3 14:01:32 46247 mysqld: InnoDB: Page may be an index page where index id is 0 1076
Apr  3 14:01:32 46247 mysqld: InnoDB: (index "PRIMARY" of table "<snip tablename>"."shop_product")
Apr  3 14:01:32 46247 mysqld: InnoDB: Database page corruption on disk or a failed
Apr  3 14:01:32 46247 mysqld: InnoDB: file read of page 17739.
Apr  3 14:01:32 46247 mysqld: InnoDB: You may have to recover from a backup.
Apr  3 14:01:32 46247 mysqld: InnoDB: It is also possible that your operating
Apr  3 14:01:32 46247 mysqld: InnoDB: system has corrupted its own file cache
Apr  3 14:01:32 46247 mysqld: InnoDB: and rebooting your computer removes the
Apr  3 14:01:32 46247 mysqld: InnoDB: error.
Apr  3 14:01:32 46247 mysqld: InnoDB: If the corrupt page is an index page
Apr  3 14:01:32 46247 mysqld: InnoDB: you can also try to fix the corruption
Apr  3 14:01:32 46247 mysqld: InnoDB: by dumping, dropping, and reimporting
Apr  3 14:01:32 46247 mysqld: InnoDB: the corrupt table. You can use CHECK
Apr  3 14:01:32 46247 mysqld: InnoDB: TABLE to scan your table for corruption.
Apr  3 14:01:32 46247 mysqld: InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
Apr  3 14:01:32 46247 mysqld: InnoDB: about forcing recovery.
Apr  3 14:01:32 46247 mysqld: InnoDB: Ending processing because of a corrupt database page.
Apr  3 14:01:32 46247 mysqld_safe: Number of processes running now: 0
Apr  3 14:01:32 46247 mysqld_safe: mysqld restarted
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32 [Note] Plugin 'FEDERATED' is disabled.
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32 [Warning] option 'innodb-additional-mem-pool-size': signed value 512000 adjusted to 524288
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32  InnoDB: Initializing buffer pool, size = 2.0M
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32  InnoDB: Completed initialization of buffer pool
Apr  3 14:01:32 46247 mysqld: InnoDB: The log sequence number in ibdata files does not match
Apr  3 14:01:32 46247 mysqld: InnoDB: the log sequence number in the ib_logfiles!
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32  InnoDB: Database was not shut down normally!
Apr  3 14:01:32 46247 mysqld: InnoDB: Starting crash recovery.
Apr  3 14:01:32 46247 mysqld: InnoDB: Reading tablespace information from the .ibd files...
Apr  3 14:01:32 46247 drwebd.real: Loading /var/drweb/bases/drw50008.vdb - Ok, virus records: 14982
Apr  3 14:01:32 46247 drwebd.real: Loading /var/drweb/bases/drw50008.vdb - Ok, virus records: 14982
Apr  3 14:01:32 46247 mysqld: InnoDB: Restoring possible half-written data pages from the doublewrite
Apr  3 14:01:32 46247 mysqld: InnoDB: buffer...
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32  InnoDB: Started; log sequence number 0 2153932977
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32 [Note] Event Scheduler: Loaded 0 events
Apr  3 14:01:32 46247 mysqld: 140403 14:01:32 [Note] /usr/sbin/mysqld: ready for connections.
Apr  3 14:01:32 46247 mysqld: Version: '5.1.66-0+squeeze1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

Apr  3 14:01:43 46247 mysqld: InnoDB: Database page corruption on disk or a failed
Apr  3 14:01:43 46247 mysqld: InnoDB: file read of page 17739.
Apr  3 14:01:43 46247 mysqld: InnoDB: You may have to recover from a backup.
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43  InnoDB: Page dump in ascii and hex (16384 bytes):
Apr  3 14:01:43 46247 mysqld:  len 16384; hex e55f1ed10000454b0000454a0000454c000000007873bb3b45bf000000000000000000000000000b3de98031194d00d33b6f00050000002e00

Apr  3 14:01:43 46247 mysqld:                                        p p3I/ + %x!0      F c>I  x=  ;InnoDB: End of page dump
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43  InnoDB: Page checksum 153379797, prior-to-4.0.14-form checksum 4209165028
Apr  3 14:01:43 46247 mysqld: InnoDB: stored checksum 3848216273, prior-to-4.0.14-form stored checksum 1045027035
Apr  3 14:01:43 46247 mysqld: InnoDB: Page lsn 0 2020850491, low 4 bytes of lsn at page end 2017309962
Apr  3 14:01:43 46247 mysqld: InnoDB: Page number (if stored to page already) 17739,
Apr  3 14:01:43 46247 mysqld: InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
Apr  3 14:01:43 46247 mysqld: InnoDB: Page may be an index page where index id is 0 1076
Apr  3 14:01:43 46247 mysqld: InnoDB: Database page corruption on disk or a failed
Apr  3 14:01:43 46247 mysqld: InnoDB: file read of page 17739.
Apr  3 14:01:43 46247 mysqld: InnoDB: You may have to recover from a backup.
Apr  3 14:01:43 46247 mysqld: InnoDB: It is also possible that your operating
Apr  3 14:01:43 46247 mysqld: InnoDB: system has corrupted its own file cache
Apr  3 14:01:43 46247 mysqld: InnoDB: and rebooting your computer removes the
Apr  3 14:01:43 46247 mysqld: InnoDB: error.
Apr  3 14:01:43 46247 mysqld: InnoDB: If the corrupt page is an index page
Apr  3 14:01:43 46247 mysqld: InnoDB: you can also try to fix the corruption
Apr  3 14:01:43 46247 mysqld: InnoDB: by dumping, dropping, and reimporting
Apr  3 14:01:43 46247 mysqld: InnoDB: the corrupt table. You can use CHECK
Apr  3 14:01:43 46247 mysqld: InnoDB: TABLE to scan your table for corruption.
Apr  3 14:01:43 46247 mysqld: InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
Apr  3 14:01:43 46247 mysqld: InnoDB: about forcing recovery.
Apr  3 14:01:43 46247 mysqld: InnoDB: Ending processing because of a corrupt database page.
Apr  3 14:01:43 46247 mysqld_safe: Number of processes running now: 0
Apr  3 14:01:43 46247 mysqld_safe: mysqld restarted
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43 [Note] Plugin 'FEDERATED' is disabled.
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43 [Warning] option 'innodb-additional-mem-pool-size': signed value 512000 adjusted to 524288
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43  InnoDB: Initializing buffer pool, size = 2.0M
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43  InnoDB: Completed initialization of buffer pool
Apr  3 14:01:43 46247 mysqld: InnoDB: The log sequence number in ibdata files does not match
Apr  3 14:01:43 46247 mysqld: InnoDB: the log sequence number in the ib_logfiles!
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43  InnoDB: Database was not shut down normally!
Apr  3 14:01:43 46247 mysqld: InnoDB: Starting crash recovery.
Apr  3 14:01:43 46247 mysqld: InnoDB: Reading tablespace information from the .ibd files...
Apr  3 14:01:43 46247 mysqld: InnoDB: Restoring possible half-written data pages from the doublewrite
Apr  3 14:01:43 46247 mysqld: InnoDB: buffer...
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43  InnoDB: Started; log sequence number 0 2153933076
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43 [Note] Event Scheduler: Loaded 0 events
Apr  3 14:01:43 46247 mysqld: 140403 14:01:43 [Note] /usr/sbin/mysqld: ready for connections.
Apr  3 14:01:43 46247 mysqld: Version: '5.1.66-0+squeeze1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
Avatar billede svinth Nybegynder
04. april 2014 - 09:55 #1
Kort fortalt, så lykkedes det vha:

innodb_force_recovery = 1

og recovery af:

ibdata1

filen, samt restore af en tabel der pegede forkert, at få noget der ser ud til at køre stabilt.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester