Not the answer you need?
Register and ask your own question!

PERCONA MYSQL ERROR Error Code: 1034. Incorrect key file for table

saloaunsaloaun EntrantCurrent User Role Supporter
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:
  1. full backup: xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backup/ --parallel=4 --user='root' --password='pass' --no-timestamp
  2. create table with same structure no data.
  3. SET FOREIGN_KEY_CHECKS=0;
  4. alter table mytable discard tablespace;
  5. cp mytable.ibd /var/lib/mysql/database/ & chown -R mysql:mysql /var/lib/mysql/db/mytable.ibd
  6. SET FOREIGN_KEY_CHECKS=0;
  7. alter table mytable import tablespace;
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.

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role

    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

  • saloaunsaloaun Entrant Current User Role Supporter

    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

  • saloaunsaloaun Entrant Current User Role Supporter
    edited April 20

    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

  • saloaunsaloaun Entrant Current User Role Supporter
    anyone can help me?
  • did1992did1992 Current User Role Novice
    I have the same problem. You have not decided it yet?
  • jriverajrivera Percona Support Engineer Percona Staff Role
    To all commenters, I've tested the procedure but did not get the same error:
    mysql> use test2
    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, 1 warning
    mysql> show warnings;
    +---------+------+------------------------------------------------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                                                                |
    +---------+------+------------------------------------------------------------------------------------------------------------------------+
    | Warning | 1287 | 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead |
    | Warning | 1814 | InnoDB: Tablespace has been discarded for table 'mytable1'                                                             |
    +---------+------+------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

    mysql> alter table mytable1 import tablespace;
    Query OK, 0 rows affected (0.10 sec)

    mysql> show tables;
    +-----------------+
    | Tables_in_test2 |
    +-----------------+
    | mytable1        |
    +-----------------+
    1 row in set (0.00 sec)

    mysql> select * from mytable1;
    +----+--------+
    | id | s      |
    +----+--------+
    |  1 | table1 |
    +----+--------+
    1 row in set (0.00 sec)
    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.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Also exact version of Percona Server 8.0 and Percona XtraBackup 8.0 would also help here.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Please subscribe to the bug report to get direct updates.
  • saloaunsaloaun Entrant Current User Role Supporter
    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


  • saloaunsaloaun Entrant Current User Role Supporter
    that is the table with the problem
  • saloaunsaloaun Entrant Current User Role Supporter
    Server version: 8.0.18-9 Percona Server
    xtrabackup version 8.0.9


  • saloaunsaloaun Entrant Current User Role Supporter
    the size of the table conta23 is 12GB
  • saloaunsaloaun Entrant Current User Role Supporter
    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)


  • saloaunsaloaun Entrant Current User Role Supporter
    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
    [[email protected] contabilidad]# cp conta04.ibd /var/lib/mysql/contabilidad/
    [[email protected] contabilidad]# chown -R mysql:mysql /var/lib/mysql/contabilidad/conta04.ibd
    [[email protected] 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)


  • saloaunsaloaun Entrant Current User Role Supporter
    in mysql.log i can see this error:

     [InnoDB] Page offset doesn't match file offset: page offset: 262144, file offset: 262144
                                                                                                                                                                  

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.