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

SEQUENCE depends on TEST_SQL_DISCOVERY for discovering tables upon DDL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2
    • Fix Version/s: 10.0.4
    • Component/s: None
    • Labels:
      None

      Description

      When the first SQL to use a SEQUENCE table is DDL, it is only discovered with TEST_SQL_DISCOVERY enabled.

      TEST_SQL_DISCOVERY enabled:

      MariaDB [test]> show engines;
      +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
      | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
      | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
      | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
      | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
      | Aria               | YES     | Crash-safe tables with MyISAM heritage                         | NO           | NO   | NO         |
      | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
      | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
      | TEST_SQL_DISCOVERY | YES     | Minimal engine to test table discovery via sql statements      | NO           | NO   | NO         |
      | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
      | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
      | FEDERATED          | YES     | FederatedX pluggable storage engine                            | YES          | NO   | YES        |
      | SPHINX             | YES     | Sphinx storage engine                                          | NO           | NO   | NO         |
      | CONNECT            | YES     | Direct access to external data, including many file formats    | NO           | NO   | NO         |
      | SEQUENCE           | YES     | Generated tables filled with sequential values                 | YES          | YES  | YES        |
      +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
      14 rows in set (0.00 sec)
       
      MariaDB [test]> drop table seq_1_to_1;
      Query OK, 0 rows affected (0.01 sec)

      TEST_SQL_DISCOVERY disabled:

      MariaDB [test]> show engines;
      +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
      | MRG_MyISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
      | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
      | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
      | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
      | Aria               | YES     | Crash-safe tables with MyISAM heritage                         | NO           | NO   | NO         |
      | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
      | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
      | TEST_SQL_DISCOVERY | NO      | Minimal engine to test table discovery via sql statements      | NULL         | NULL | NULL       |
      | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
      | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
      | FEDERATED          | YES     | FederatedX pluggable storage engine                            | YES          | NO   | YES        |
      | SPHINX             | YES     | Sphinx storage engine                                          | NO           | NO   | NO         |
      | CONNECT            | YES     | Direct access to external data, including many file formats    | NO           | NO   | NO         |
      | SEQUENCE           | YES     | Generated tables filled with sequential values                 | YES          | YES  | YES        |
      +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
      14 rows in set (0.00 sec)
       
      MariaDB [test]> drop table seq_1_to_1;
      ERROR 1051 (42S02): Unknown table 'seq_1_to_1'

      SELECT and SHOW CREATE still work as expected:

      MariaDB [test]> select * from seq_1_to_2;
      +-----+
      | seq |
      +-----+
      |   1 |
      |   2 |
      +-----+
      2 rows in set (0.01 sec)
       
      MariaDB [test]> show create table seq_1_to_3;
      +------------+----------------------------------------------------------------------------------------------------------------------------------+
      | Table      | Create Table                                                                                                                     |
      +------------+----------------------------------------------------------------------------------------------------------------------------------+
      | seq_1_to_3 | CREATE TABLE `seq_1_to_3` (
        `seq` bigint(20) unsigned NOT NULL,
        PRIMARY KEY (`seq`)
      ) ENGINE=SEQUENCE DEFAULT CHARSET=latin1 |
      +------------+----------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      I would actually expect DROP TABLE to cause ER_ILLEGAL_HA, but maybe it's impossible or undesirable for engine purposes; in any case the behavior on DDL is somewhat inconsistent now, but it's out of scope of this report. I will file something about it separately for the record, although it doesn't seem important.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration