Description:
If user does not explicitly declare a primary key when creating a table, InnoDB will generate an implicit primary key using row_id for the table.
The ipk should have the same keytype as explicit primary key, that is KEYTYPE_PRIMARY. But in DD, the `type` of ipk is set to KEYTYPE_UNIQUE.
enum keytype {
KEYTYPE_PRIMARY,
KEYTYPE_UNIQUE,
KEYTYPE_MULTIPLE,
KEYTYPE_FULLTEXT,
KEYTYPE_SPATIAL,
KEYTYPE_FOREIGN
};
How to repeat:
Using DEBUG mode and run SQLs below:
1. create table test.no_pk(a int);
2. SET SESSION debug='+d,skip_dd_table_access_check';
3. select name, id from mysql.tables where name like '%no_pk%';
4. select name, type from mysql.indexes where table_id=xxx;
You will get result like:
+---------+--------+
| name | type |
+---------+--------+
| PRIMARY | UNIQUE |
+---------+--------+
For an explicit primary key, the result will be:
+---------+--------+
| name | type |
+---------+--------+
| PRIMARY | PRIMARY |
+---------+--------+
===
Another way to check `type` of ipk is using ibd2sdi, you will see that the `type` value in SDI is also wrong.
Description: If user does not explicitly declare a primary key when creating a table, InnoDB will generate an implicit primary key using row_id for the table. The ipk should have the same keytype as explicit primary key, that is KEYTYPE_PRIMARY. But in DD, the `type` of ipk is set to KEYTYPE_UNIQUE. enum keytype { KEYTYPE_PRIMARY, KEYTYPE_UNIQUE, KEYTYPE_MULTIPLE, KEYTYPE_FULLTEXT, KEYTYPE_SPATIAL, KEYTYPE_FOREIGN }; How to repeat: Using DEBUG mode and run SQLs below: 1. create table test.no_pk(a int); 2. SET SESSION debug='+d,skip_dd_table_access_check'; 3. select name, id from mysql.tables where name like '%no_pk%'; 4. select name, type from mysql.indexes where table_id=xxx; You will get result like: +---------+--------+ | name | type | +---------+--------+ | PRIMARY | UNIQUE | +---------+--------+ For an explicit primary key, the result will be: +---------+--------+ | name | type | +---------+--------+ | PRIMARY | PRIMARY | +---------+--------+ === Another way to check `type` of ipk is using ibd2sdi, you will see that the `type` value in SDI is also wrong.