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

huge CSV table silently failed to create index on varchar

    XMLWordPrintable

Details

    Description

      I generated a big text file - 740m size

      MariaDB [test]> select uuid() from mysql.help_topic a, mysql.help_topic b, mysql.help_topic c limit 20000000 into outfile '/home/alice/t/xx3';
      Query OK, 20000000 rows affected (6.75 sec)
       
      MariaDB [test]> select * from bigcsv limit 10;
      +----------+------+------+------+--------------+
      | n1       | n2   | c1   | c2   | c3           |
      +----------+------+------+------+--------------+
      | 0d979bdb | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c09 | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c17 | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c25 | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c33 | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c40 | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c4e | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c5b | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c69 | 261c | 11e7 | a6a4 | 7429af1476cb |
      | 0d979c76 | 261c | 11e7 | a6a4 | 7429af1476cb |
      +----------+------+------+------+--------------+
      10 rows in set (0.00 sec)
      

      Then tried to create an index, the query was ok, but indeed there is no index file created on disk. But in table definition index appear. Then I tried to use it and I got an error.

      MariaDB [test]> alter table bigcsv add index(n1,n2);
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table bigcsv;
      | bigcsv | CREATE TABLE `bigcsv` (
        `n1` varchar(75) NOT NULL,
        `n2` varchar(75) NOT NULL,
        `c1` varchar(75) DEFAULT NULL,
        `c2` varchar(75) DEFAULT NULL,
        `c3` varchar(75) DEFAULT NULL,
        KEY `n1` (`n1`,`n2`)
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=CSV `file_name`='/home/alice/t/xx3' `sep_char`='-' |
      1 row in set (0.00 sec)
       
      MariaDB [test]> explain select 1 from bigcsv where (n1,n2) = ('1','2');
      +------+-------------+--------+------+---------------+------+---------+-------------+------+--------------------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref         | rows | Extra                    |
      +------+-------------+--------+------+---------------+------+---------+-------------+------+--------------------------+
      |    1 | SIMPLE      | bigcsv | ref  | n1            | n1   | 154     | const,const |   10 | Using where; Using index |
      +------+-------------+--------+------+---------------+------+---------+-------------+------+--------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select 1 from bigcsv where (n1,n2) = ('1','2');
      ERROR 1296 (HY000): Got error 122 'Wrong index file /home/alice/t/xx3.dnx' from CONNECT
      
      

      It was the same scenario with index on one field, then I tried to create the same table, but instead of defining field (n2) as varchar, it was defined as int and index was created as it expected.

      [Note] may be related to https://jira.mariadb.org/browse/MDEV-11291

      Attachments

        Activity

          People

            bertrandop Olivier Bertrand
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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