Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.9.3, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
-
None
-
Windows 10
Description
I have a CONNECT to a MariaDB server table which contains an AUTO_INCREMENT column.
Trying to insert a NULL value into this column always fails, while acording to the specifications I would expect it to use the next auto_incremented value.
Minimal example using a locally installed MariaDB 10.9.3 server: (Problem came up originally when CONNECTing from a MariaDB 10.6.10 server to a MySQL 5.7 server – same effect.)¶
use test; |
create table t0 ( |
id int primary key AUTO_INCREMENT, |
name varchar(255) |
) ENGINE=InnoDB;
|
create table t0_conn engine=CONNECT table_type=MYSQL |
connection='mysql://username:xyzzy@localhost/test/t0'; |
insert into t0 values ( null, 'test1' ); -- works OK |
select * from t0_conn; -- works OK: 1 test1 |
insert int0 t0_conn values ( 2, 'test2' ); -- works OK |
insert into t0_conn values ( null, 'test3'); -- ERROR 1048 (23000): Column 'id' cannot be null |
Since directly inserting into the underlying table (t0) works OK, it seems that the CONNECT engine is over-eager in checking the NOT NULL condition that is implied by the primary key. Unfortunately, this makes the AUTO_INCREMENT feature unusable in the CONNECT engine.
Attachments
Activity
It would certainly help if, for the time being, the documentation stated that automatic discovery will fail if the target table has an auto_increment field, and also taht, under the given circumstances, automatic discovery should probably fail rather than pretend everything is fine.
(Or is it in fact already documented, and I just failed to find it? In which case, I apologize.)
Maybe, however, the CONNECT mechanism is currently trying to be too clever for its own good. Arguably, the CONNECT engine shouldn't bother to check any such restraints, because the engine for the underlying table on the remote host will do so anyway. And that engine knows perfectly well what it can and cannot do. Why duplicate the effort, check the constraints twice, and get inconsistent results?
To illustrate, assume this manual setup:
use test; |
create table t1 ( |
id int not null, |
name varchar(255) |
) ENGINE=InnoDB;
|
create table t1_conn ( |
id int, |
name varchar(255) |
) engine=CONNECT table_type=MYSQL connection='mysql://username:xyzzy@localhost/test/t1' |
Note that in this case the definition of t1_conn is more lenient than that of the underlying table, in not requiring NOT NULL for column id.
Trying to
insert into t1_conn values(null,'test') |
will produce
Got error 122 '(1048) Column 'id' cannot be null [INSERT INTO `t1` (`id`, `name`) VALUES (NULL,'test3')]' from CONNECT
Which is completely correct. Note that this error message comes from the engine for the underlying table, and the CONNECT engine just passes this message on. This I would consider perfect behavour.
The moral of the story is: what the CONNECT engine thinks is correct or not just does not matter. The engine of the underlying engine has the final word.
Side note:
I appreciate that conceivably there might be a wish to define a CONNECT table with tighter restrictions than the underlying table (e.g., no restriction in the underlying table, but a NOT NULL restriction in the CONNECT table). I would assume such requirements to be rather rare compared to the use cases for creating a CONNECT to some underlying table that happens to include an auto_increment column. This would present an argument for, indeed, double-checking restrictions both in the underlying engine and in the CONNECT engine. If this rather special case is a concern, I would appreciate at least to have the automatic discovery (which otherwise is a highly useful feature!) ignore any restraints that will be handled by the underlying table anyway, rather than introduce "impossible" restraints on its own. That would, IMHO, be the best of both worlds: have reasonable default behaviour with automatic detection and have fine-grain control when manually defining a CONNECT table. ("Easy things should be easy, hard things should be possible.")
;
The reason for this is that upon table discovery the auto-increment property gets ignored, while non-null property remains. It is easy to see if you add show create table to the test case:
10.9 5d5735c1
And the reason for that is that Connect engine doesn't support auto-increment. It can be seen if instead of table discovery we try to create an identical table explicitly:
id int primary key AUTO_INCREMENT,name varchar(255)Maybe to avoid confusion, the automatic discovery should either fail or at least produce a warning about losing auto-increment.