[MDEV-5440] Converting CONNECT -> InnoDB deletes data Created: 2013-12-13  Updated: 2014-02-06  Resolved: 2014-02-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.6
Fix Version/s: 10.0.8

Type: Bug Priority: Critical
Reporter: Federico Razzoli Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: 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)



 Comments   
Comment by Olivier Bertrand [ 2013-12-23 ]

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.

Comment by Sergei Golubchik [ 2014-01-19 ]

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

Comment by Olivier Bertrand [ 2014-01-20 ]

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.

Comment by Olivier Bertrand [ 2014-02-04 ]

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.

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