Windows 10

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:

Table	Create Table

t0_conn	CREATE TABLE `t0_conn` (

  `id` int(11) NOT NULL,

  `name` varchar(255) DEFAULT NULL

) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='mysql://root@127.0.0.1:16000/test/t0' `TABLE_TYPE`='MYSQL'

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:

query 'create table t0_conn (

id int primary key AUTO_INCREMENT,

name varchar(255)

) engine=CONNECT table_type=MYSQL connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t0'' failed: ER_TABLE_CANT_HANDLE_AUTO_INCREMENT (1164): Storage engine CONNECT doesn't support AUTO_INCREMENT columns

Maybe to avoid confusion, the automatic discovery should either fail or at least produce a warning about losing auto-increment.

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.")

;