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

inconsistent behavior on error during index creation in CSV table

Details

    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

      Attachments

        Activity

          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)
          

          elenst Elena Stepanova added a comment - 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)
          bertrandop Olivier Bertrand added a comment - - edited

          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.

          bertrandop Olivier Bertrand added a comment - - edited 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 .
          alice Alice Sherepa added a comment - - edited

          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)

          alice Alice Sherepa added a comment - - edited 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)

          People

            bertrandop Olivier Bertrand
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.