Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.22
-
None
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