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

Converting CONNECT -> InnoDB deletes data

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.0.6
    • 10.0.8
    • None
    • None

    Description

      IMO, this should work, or should be prohibited to prevent disasters:

      MariaDB [test]> CREATE TABLE t (c INT NOT NULL) ENGINE=CONNECT;
      Query OK, 0 rows affected, 2 warnings (0.13 sec)
       
      Warning (Code 1105): No table_type. Will be set to DOS
      Warning (Code 1105): No file name. Table will use t.dos
      MariaDB [test]> INSERT INTO t VALUES (1), (2), (3);
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SELECT * FROM t;
      +---+
      | c |
      +---+
      | 1 |
      | 2 |
      | 3 |
      +---+
      3 rows in set (0.01 sec)
       
      MariaDB [test]> ALTER TABLE t ENGINE = InnoDB;
      Query OK, 1 row affected (0.71 sec)                
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SELECT * FROM t;
      +---+
      | c |
      +---+
      | 0 |
      +---+
      1 row in set (0.01 sec)

      Attachments

        Activity

          This is a difficult and complex issue because ALTER TABLE can be used for a great variety of action. Therefore it will take some time to fix. Meanwhile, as it is almost impossible for a storage engine to know for sure what an ALTER TABLE will do, prohibiting would be completely prohibiting the ALTER TABLE command. It may be too drastic even most of the time it is possible to simply drop and recreate the table (for the CONNECT engine this generally does not erase the data but there is the exception of tables created without giving the file name for which the data file is erased on DROP)
          Therefore, waiting for a fix, just try to avoid using ALTER TABLE with the CONNECT engine. For instance use CREATE or DROP INDEX instead of ALTER, and instead of doing:

          ALTER TABLE t ENGINE = InnoDB;

          do:

          CREATE TABLE t1 ENGINE=InnoDB select * from t; DROP t; RENAME TABLE t1 TO t;

          Normally CONNECT warns users that using ALTER TABLE is unsafe but unfortunately in the above problem the function check_if_incompatible_data was not called.

          bertrandop Olivier Bertrand added a comment - This is a difficult and complex issue because ALTER TABLE can be used for a great variety of action. Therefore it will take some time to fix. Meanwhile, as it is almost impossible for a storage engine to know for sure what an ALTER TABLE will do, prohibiting would be completely prohibiting the ALTER TABLE command. It may be too drastic even most of the time it is possible to simply drop and recreate the table (for the CONNECT engine this generally does not erase the data but there is the exception of tables created without giving the file name for which the data file is erased on DROP) Therefore, waiting for a fix, just try to avoid using ALTER TABLE with the CONNECT engine. For instance use CREATE or DROP INDEX instead of ALTER, and instead of doing: ALTER TABLE t ENGINE = InnoDB; do: CREATE TABLE t1 ENGINE=InnoDB select * from t; DROP t; RENAME TABLE t1 TO t; Normally CONNECT warns users that using ALTER TABLE is unsafe but unfortunately in the above problem the function check_if_incompatible_data was not called.

          Why does this happen at all, why all rows disappear?

          serg Sergei Golubchik added a comment - Why does this happen at all, why all rows disappear?

          It's a pity the SQL language, defined in a hurry by Don Chamberlain while he was developing system R, was finally chosen as the standard language among better ones. In particular the ALTER TABLE command is a terrible mess, the MySQL extensions making it even messier with bad syntax and much too many possibilities. For instance, it should never be used to do things that can be done with specific commands like creating and dropping indexes. How the poor storage engine can guess what will be done when receiving a command just described by its type SQLCOM_ALTER_TABLE ?
          I have debugged the above ALTER TABLE t ENGINE = InnoDB; statement and be able to now exactly where and why the errors occur (there are two errors: 1- the innodb table is not filled and 2- the t.dos file is not erased) Fixing them is easy but the issue is to be sure it will not introduce other errors with other commands or when the ALTER command is used for other purposes.
          Be patient and meanwhile avoid using ALTER with CONNECT tables.

          bertrandop Olivier Bertrand added a comment - It's a pity the SQL language, defined in a hurry by Don Chamberlain while he was developing system R, was finally chosen as the standard language among better ones. In particular the ALTER TABLE command is a terrible mess, the MySQL extensions making it even messier with bad syntax and much too many possibilities. For instance, it should never be used to do things that can be done with specific commands like creating and dropping indexes. How the poor storage engine can guess what will be done when receiving a command just described by its type SQLCOM_ALTER_TABLE ? I have debugged the above ALTER TABLE t ENGINE = InnoDB; statement and be able to now exactly where and why the errors occur (there are two errors: 1- the innodb table is not filled and 2- the t.dos file is not erased) Fixing them is easy but the issue is to be sure it will not introduce other errors with other commands or when the ALTER command is used for other purposes. Be patient and meanwhile avoid using ALTER with CONNECT tables.

          Fixing this bug was in fact completely revisiting the ALTER TABLE handling by the CONNECT engine.

          As it is said in the the MariaDB mysql_alter_table source code comments:
          "This is a veery long function and is everything but the kitchen sink "
          It was not a piece of cake but a first decent version is now implemented.

          In summary, here is what is ALTER TABLE for the CONNECT engine:

          Firstly, remember that CONNECT implements MED (management of external data) This means that the "true" CONNECT tables – outward tables – are based on data that belong to files that can be produced by other applications or data imported from other DBMS's. Therefore, these data are "precious" and should not be modified except by specific commands such as INSERT, UPDATE, or DELETE. This why the behaviour of CONNECT when an ALTER TABLE is issued is to modify accordingly the table definition without changing the data.

          Another thing to know is, because CONNECT builds indexes in a specific way, that all index modifications must be done using "in-place" algorithm.

          ALTER can be used safely to, for instance, to modify options such as MAPPED, READONLY or HUGE but with extreme care when modifying column definitions or order options.

          Translating a CONNECT table to another engine is alright but the opposite is forbidden when the target CONNECT table is not table based or when its data file exists (CONNECT table data cannot changed and the source table being dropped, the table data would lost.) However it can be done to create a file based tables when the file does not exist or is void.

          In the case of "inward" CONNECT tables (whose file name is not specified and defaults to a file within the DB directory) the behaviour is quite different. Actually they are not "MED" tables and therefore they are treated like other engine tables.

          As this is a complex matter, I cannot claim to have tested all the different cases. Please report bug when you find some.

          bertrandop Olivier Bertrand added a comment - Fixing this bug was in fact completely revisiting the ALTER TABLE handling by the CONNECT engine. As it is said in the the MariaDB mysql_alter_table source code comments: "This is a veery long function and is everything but the kitchen sink " It was not a piece of cake but a first decent version is now implemented. In summary, here is what is ALTER TABLE for the CONNECT engine: Firstly, remember that CONNECT implements MED (management of external data) This means that the "true" CONNECT tables – outward tables – are based on data that belong to files that can be produced by other applications or data imported from other DBMS's. Therefore, these data are "precious" and should not be modified except by specific commands such as INSERT, UPDATE, or DELETE. This why the behaviour of CONNECT when an ALTER TABLE is issued is to modify accordingly the table definition without changing the data. Another thing to know is, because CONNECT builds indexes in a specific way, that all index modifications must be done using "in-place" algorithm. ALTER can be used safely to, for instance, to modify options such as MAPPED, READONLY or HUGE but with extreme care when modifying column definitions or order options. Translating a CONNECT table to another engine is alright but the opposite is forbidden when the target CONNECT table is not table based or when its data file exists (CONNECT table data cannot changed and the source table being dropped, the table data would lost.) However it can be done to create a file based tables when the file does not exist or is void. In the case of "inward" CONNECT tables (whose file name is not specified and defaults to a file within the DB directory) the behaviour is quite different. Actually they are not "MED" tables and therefore they are treated like other engine tables. As this is a complex matter, I cannot claim to have tested all the different cases. Please report bug when you find some.

          People

            bertrandop Olivier Bertrand
            f_razzoli Federico Razzoli
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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