[URL=“Timeline for PERCONA MYSQL ERROR Error Code: 1034. Incorrect key file for table - Stack Overflow”][/URL] im trying to import a table after partial backup or full with percona 8.0 and im having problems with only 1 table, the size of the table = 14 GB. im doing the steps following all oficial forums:
[LIST=1]
[]full backup: xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup/ --parallel=4 --user=‘root’ --password=‘pass’ --no-timestamp
[]create table with same structure no data.
[]SET FOREIGN_KEY_CHECKS=0;
[]alter table mytable discard tablespace;
[]cp mytable.ibd /var/lib/mysql/database/ & chown -R mysql:mysql /var/lib/mysql/db/mytable.ibd
[]SET FOREIGN_KEY_CHECKS=0;
[*]alter table mytable import tablespace;
[/LIST]after all this steps i got this error:
Error Code: 1034. Incorrect key file for table ‘mytable’; try to repair it
and in mysql.log i got:
[MY-012762] [InnoDB] Page offset doesn’t match file offset: page offset: 262144, file offset: 262144
i only get the error some tables, if i restore full backup with --copy-back all tables are OK!
what can i do? i tried increase change directory /tmp in my.cnf but nothing work.
Are you going to use one of the tables from the backup taken in step 1? If so then I believe you are missing a step to execute --prepare --export. See this link for further guidance: https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/restoring_individual_tables.html
Hi mate, i did the same follow --prepare --export and i copy the file ibd to mysql location but after import tablespace i got the same error:
[ERROR] [MY-012762] [InnoDB] Page offset doesn’t match file offset: page offset: 262144, file offset: 262144
and
Error Code: 1034. Incorrect key file for table ‘mytable’; try to repair it
Hi mate, i did the same follow --prepare --export and i copy the file ibd to mysql location but after import tablespace i got the same error:
[ERROR] [MY-012762] [InnoDB] Page offset doesn’t match file offset: page offset: 262144, file offset: 262144
and
Error Code: 1034. Incorrect key file for table ‘mytable’; try to repair it
anyone can help me?
I have the same problem. You have not decided it yet?
To all commenters, I’ve tested the procedure but did not get the same error:
Can you share sample table description with SHOW CREATE TABLE tblname\G
If you can provide terminal output by pasting it in pastebin or similar site that would also help.
Also exact version of Percona Server 8.0 and Percona XtraBackup 8.0 would also help here.
Bug report - [PS-7095] [InnoDB] Page offset doesn't match file offset: page offset: 524288, file offset: 524288 - Percona JIRA subscribe to the bug report to get direct updates.
mysql> SHOW CREATE TABLE contabilidad.conta23\G
*************************** 1. row ***************************
Table: conta23
Create Table: CREATE TABLE conta23 (
codcop char(4) CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
documento char(7) CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
sec int(11) NOT NULL,
cnt char(2) CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
fecha date NOT NULL,
auxiliar char(12) CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
codcen char(6) CHARACTER SET latin1 COLLATE latin1_spanish_ci DEFAULT NULL,
codbod char(5) CHARACTER SET latin1 COLLATE latin1_spanish_ci DEFAULT NULL,
nit char(16) CHARACTER SET latin1 COLLATE latin1_spanish_ci DEFAULT NULL,
descripcion char(250) CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
nat enum(‘D’,‘C’) CHARACTER SET latin1 COLLATE latin1_spanish_ci NOT NULL,
valor decimal(22,2) NOT NULL,
numtra int(11) DEFAULT NULL,
valifrs enum(‘1’,‘2’,‘3’) CHARACTER SET latin1 COLLATE latin1_spanish_ci DEFAULT NULL,
PRIMARY KEY (cnt,codcop,documento,sec),
KEY cnt (cnt,codcen),
KEY cnt_2 (cnt,codbod),
KEY cnt_3 (cnt,auxiliar),
KEY conta23_5 (cnt,nit,auxiliar,fecha),
KEY index6 (cnt,fecha),
KEY index7 (cnt,codcop,fecha),
KEY index8 (cnt,codcop,documento,auxiliar,fecha),
KEY ind23a (cnt,codcop,documento,sec,fecha,auxiliar,nit,nat),
KEY con23b (codcop,documento,cnt),
KEY con23c (cnt,auxiliar,nit),
KEY con23d (cnt,codcop,fecha,auxiliar,nat),
KEY con23qwe (cnt,codcop,documento,sec,auxiliar,nit),
KEY con23cil1 (cnt,codcop,documento,auxiliar,nit),
KEY conta23car (cnt,codcop,documento,auxiliar,fecha),
KEY c23uvt1 (cnt,fecha,auxiliar,nit,nat),
KEY con23e (cnt,codcop,documento,fecha,auxiliar,sec),
KEY decar231 (cnt,codcop,documento,sec,auxiliar,fecha,nat),
KEY nit_cnt (cnt,nit),
CONSTRAINT conta23_ibfk_1 FOREIGN KEY (cnt, codcen) REFERENCES conta06 (cnt, codcen),
CONSTRAINT conta23_ibfk_2 FOREIGN KEY (cnt, codbod) REFERENCES conta08 (cnt, codbod),
CONSTRAINT conta23_ibfk_3 FOREIGN KEY (cnt, auxiliar) REFERENCES conta02 (cnt, auxiliar),
CONSTRAINT conta23_ibfk_4 FOREIGN KEY (cnt, nit) REFERENCES conta04 (cnt, nit),
CONSTRAINT conta23_ibfk_5 FOREIGN KEY (cnt) REFERENCES conta32 (cnt),
CONSTRAINT conta23_ibfk_6 FOREIGN KEY (cnt, codcop, documento) REFERENCES conta41 (cnt, codcop, documento)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
that is the table with the problem
Server version: 8.0.18-9 Percona Server
xtrabackup version 8.0.9
the size of the table conta23 is 12GB
and this is the ouput when i try to import:mysql> alter table conta23 import tablespace;
ERROR 1034 (HY000): Incorrect key file for table ‘conta23’; try to repair it
mysql> show warnings;
±--------±-----±-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening ‘./contabilidad/conta23.cfg’, will attempt to import without schema verification |
| Warning | 1816 | InnoDB: ALTER TABLE contabilidad.conta23 IMPORT TABLESPACE failed with error 39 : ‘Data structure corruption’ |
| Error | 1034 | Incorrect key file for table ‘conta23’; try to repair it |
±--------±-----±-----------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
and if u do the process with any other table of database all is ok check:
mysql> use contabilidad;
Database changed
mysql> alter table conta04 discard tablespace;
Query OK, 0 rows affected (0.16 sec)
mysql> exit
Bye
[root@c0515-02-250 contabilidad]# cp conta04.ibd /var/lib/mysql/contabilidad/
[root@c0515-02-250 contabilidad]# chown -R mysql:mysql /var/lib/mysql/contabilidad/conta04.ibd
[root@c0515-02-250 contabilidad]# mysql -u querix -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use contabilidad;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table conta04 import tablespace;
Query OK, 0 rows affected, 1 warning (4.14 sec)
in mysql.log i can see this error:
[InnoDB] Page offset doesn’t match file offset: page offset: 262144, file offset: 262144
The following mysqlcheck command checks, optimizes, and auto-repairs all corrupted tables in the “customer” database. The auto repair option automatically fixes a checked table if found corrupted. And the following mysqlcheck command optimizes and auto-repairs all tables in all your MySQL databases.
Regards,
Rachel Gomez