Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17401

LOAD DATA from very big file into MyISAM table results in EOF error and corrupt index

Details

    Description

      A LOAD DATA statement is consistently failing with the following error:

      MariaDB [(none)]> LOAD DATA  LOCAL INFILE '/home/ec2-user/tab.txt' INTO TABLE db1.`tab` IGNORE 0 LINES;
      		 
      ERROR 9 (HY000): Unexpected end-of-file found when reading file '/tmp/STWraeLT' (Errcode: 175 "File too short; Expected more data in file")

      The resulting table is also corrupt, and cannot seem to be repaired:

      MariaDB [db1]> CHECK TABLE tab;
      		 
      +-------------------+-------+----------+------------------------------------------------------------------------+
      		 
      | Table             | Op    | Msg_type | Msg_text                                                               |
      		 
      +-------------------+-------+----------+------------------------------------------------------------------------+
      		 
      | db1.tab | check | error    | Can't read indexpage from filepos: -1                                  |
      		 
      | db1.tab | check | Error    | Index for table './db1/tab.MYI' is corrupt; try to repair it |
      		 
      | db1.tab | check | error    | Corrupt                                                                |
      		 
      +-------------------+-------+----------+------------------------------------------------------------------------+
      		 
      3 rows in set (1 min 51.569 sec)
      		 
       
      		 
      MariaDB [db1]> REPAIR TABLE tab;
      		 
      +-------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table             | Op     | Msg_type | Msg_text                                                                                                                   |
      		 
      +-------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------+
      		 
      | db1.tab | repair | Error    | Unexpected end-of-file found when reading file '/tmp/STjk9WiV' (Errcode: 175 "File too short; Expected more data in file") |
      		 
      | db1.tab | repair | Error    | Unexpected end-of-file found when reading file '/tmp/STpQXG9A' (Errcode: 175 "File too short; Expected more data in file") |
      		 
      | db1.tab | repair | status   | OK                                                                                                                         |
      		 
      +-------------------+--------+----------+----------------------------------------------------------------------------------------------------------------------------+
      		 
      3 rows in set (36 min 31.957 sec)
      		 
       
      		 
      MariaDB [db1]> CHECK TABLE tab;
      		 
      +-------------------+-------+----------+-------------------------------------------------------------+
      		 
      | Table             | Op    | Msg_type | Msg_text                                                    |
      		 
      +-------------------+-------+----------+-------------------------------------------------------------+
      		 
      | db1.tab | check | error    | Found key at page -1 that points to record outside datafile |
      		 
      | db1.tab | check | error    | Corrupt                                                     |
      		 
      +-------------------+-------+----------+-------------------------------------------------------------+
      		 
      2 rows in set (15.393 sec)

      The table has the following definition:

      MariaDB [(none)]> SHOW CREATE TABLE db1.tab\G
      		 
      *************************** 1. row ***************************
      		 
             Table: tab
      		 
      Create Table: CREATE TABLE `tab` (
      		 
        `tab_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `col2` int(10) unsigned NOT NULL,
      		 
        `col3` int(10) unsigned NOT NULL,
      		 
        `col4` int(10) unsigned NOT NULL,
      		 
        `col5` int(10) unsigned NOT NULL,
      		 
        `col6` tinyint(1) NOT NULL,
      		 
        `col7` double DEFAULT NULL,
      		 
        `col8` varchar(18) DEFAULT NULL,
      		 
        PRIMARY KEY (`tab_id`),
      		 
        UNIQUE KEY `unique_idx` (`col2`,`col3`,`col4`,`col6`),
      		 
        UNIQUE KEY `col8_idx` (`col8`),
      		 
        KEY `col3_idx` (`col3`,`col4`),
      		 
        KEY `col2_idx` (`col2`)
      		 
      ) ENGINE=MyISAM AUTO_INCREMENT=205163534 DEFAULT CHARSET=latin1
      		 
      1 row in set (0.005 sec)

      The file being loaded into the table is 14 GB large, and it has 205163533 rows in it.

      The only non-default values in the configuration file are:

      log_error=mysqld.err
      		 
      myisam_sort_buffer_size = 4G
      		 
      bulk_insert_buffer_size = 2G

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16461 MyISAM creates defect indexes on varchar, if rowcount is above some threshold between 480,000,000 and 490,000,000

          • Major - Major loss of function.
          • Confirmed

          Activity

            People

              sanja Oleksandr Byelkin
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.