[MDEV-12540] inconsistent behavior on error during index creation in CSV table Created: 2017-04-20  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.1.22
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Olivier Bertrand
Resolution: Unresolved Votes: 0
Labels: None


 Description   

For table below I wanted to simulate an error during index file creation (e.g. permission issue). So folder with name ../t/qq1.dnx was created.

MariaDB [test]> show create table test2\G
 
*************************** 1. row ***************************
 
       Table: test2
 
Create Table: CREATE TABLE `test2` (
 
  `num` int(11) NOT NULL,
 
  `name` varchar(255) DEFAULT NULL
 
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=CSV `file_name`='../t/qq1' `sep_char`=';'
 
1 row in set (0.00 sec)

Problem 1: engine doesn't report an error, only warning:

MariaDB [test]> alter table test2 add index(num);
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 1
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
 
| Level   | Code | Message                                                                                                                    |
 
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
 
| Warning | 1105 | Open error 21 in mode wb on /home/alice/dt/./test/../t/qq1.dnx: Is a directory |
 
+---------+------+----------------------------------------------------------------------------------------------------------------------------+

Problem 2: Engine pretends that index exists (while no index file was created):

MariaDB [test]> show create table test2\G
 
*************************** 1. row ***************************
 
       Table: test2
 
Create Table: CREATE TABLE `test2` (
 
  `num` int(11) NOT NULL,
 
  `name` varchar(255) DEFAULT NULL,
 
  KEY `num` (`num`)
 
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=CSV `file_name`='../t/qq1' `sep_char`=';'
 
1 row in set (0.00 sec)

Not sure if this is expected behavior

 Comments   
Comment by Elena Stepanova [ 2017-04-21 ] 

MariaDB [test]> system ls /data/tmp/ | grep qq1
 
qq1
 
qq1.dnx
 
MariaDB [test]> system cat /data/tmp/qq1
 
1;a
 
MariaDB [test]> CREATE TABLE `test2` (   `num` int(11) NOT NULL,   `name` varchar(255) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=CSV `file_name`='/data/tmp/qq1'  `sep_char`=';';
 
Query OK, 0 rows affected (0.04 sec)


MariaDB [test]> alter table test2 add index(num);
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 1
 
 
 
MariaDB [test]> show warnings;
 
+---------+------+---------------------------------------------------------------+
 
| Level   | Code | Message                                                       |
 
+---------+------+---------------------------------------------------------------+
 
| Warning | 1105 | Open error 21 in mode wb on /data/tmp/qq1.dnx: Is a directory |
 
+---------+------+---------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> show create table test2 \G
 
*************************** 1. row ***************************
 
       Table: test2
 
Create Table: CREATE TABLE `test2` (
 
  `num` int(11) NOT NULL,
 
  `name` varchar(255) DEFAULT NULL,
 
  KEY `num` (`num`)
 
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=CSV `file_name`='/data/tmp/qq1' `sep_char`=';'
 
1 row in set (0.00 sec)

Comment by Olivier Bertrand [ 2017-05-09 ]

This is depending on another issue I don't have the solution yet. I am making and droping indexes in the only function that I am sure to get called when doing any action on indexing; external_lock.
Currently, for a reason I did not yet found, an error return from this function causes one of these infamous DBUG_ASSERT MariaDB is full of to crash the system. This is why I issue a warning instead of an error.
Then MariaDB remember somewhere that an index have been made.
Right now, I cannot do much about it.

See MDEV-12761.

Comment by Alice Sherepa [ 2017-05-24 ]

I've met the similar problem with indexes in JSON type, so I paste it just for reference here. 

create table t3(i int NOT NULL, k varchar(10) NOT NULL ) engine=CONNECT table_type=JSON  File_name='t/2.json';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
select * from t3;
 
+---+------+
 
| i | k    |
 
+---+------+
 
| 3 | foo  |
 
| 1 | foo1 |
 
+---+------+
 
2 rows in set (0.00 sec)
 
 
 
alter table t3 add index (k);
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 1
 
Warning (Code 1105): JSON not indexable when pretty = 2
 
 
 
show create table t3;
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                 |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t3    | CREATE TABLE `t3` (
 
  `i` int(11) NOT NULL,
 
  `k` varchar(10) NOT NULL,
 
  KEY `k` (`k`)
 
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=JSON `File_name`='t/2.json' |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
select * from t3 where k="foo1";
 
ERROR 1296 (HY000) at line 14 in file: 't/c.sql': Got error 122 'Open error 2 in mode rb on t/2.dnx: No such file or directory' from CONNECT
 
 
 
explain select * from t3 where k="foo1";
 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
 
|    1 | SIMPLE      | t3    | ref  | k             | k    | 12      | const |    0 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
 
1 row in set (0.00 sec)
 
 
 
select * from t3 ignore index (k) where k="foo1";
 
+---+------+
 
| i | k    |
 
+---+------+
 
| 1 | foo1 |
 
+---+------+
 
1 row in set (0.00 sec)

And the same if I create table with an index, but no warnings

create table t3 
	(i int  not null UNIQUE key, j int, m int, n int) engine=CONNECT table_type=JSON  
	File_name='t/2.json';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
select * from t3 where i = 3;
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
ERROR 1296 (HY000) at line 13 in file: '/home/alice/mariadb-environ-framework-master/_depot/t/c.sql': Got error 122 'Open error 2 in mode rb on t/2.dnx: No such file or directory' from CONNECT

btw I can see that MDEV-12761 has been fixed, so hopefully now it will be possible to implement the fix)

Generated at Thu Feb 08 07:58:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.