Description:
Message ER_TABLE_WITHOUT_PK is same for creation of non-primary key table AND for dropping of primary key from a PK enabled table, but it doesn't give clear information that user is wrongly trying to drop the PK or action is non-PK table.
Error message "ER_TABLE_WITHOUT_PK" is same for:
a) Creation of non-pk table.
b) Dropping of pk from a pk table.
c) Any modification on an existing non-pk table.
====
M1> create table tnopk3 (a int);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
M1> alter table tpk1 drop primary key;
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
M1> alter table tnopk2 add column b int;
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
====
How to repeat:
Steps to repro:-
================
M1> select @@session.sql_require_primary_key;
+-----------------------------------+
| @@session.sql_require_primary_key |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.00 sec)
M1> create table tpk1 (a int primary key);
Query OK, 0 rows affected (0.17 sec)
M1> create table tnopk1 (a int);
Query OK, 0 rows affected (0.07 sec)
M1> create table tnopk2 (a int unique key);
Query OK, 0 rows affected (0.16 sec)
M1> set @@global.sql_require_primary_key=1, @@session.sql_require_primary_key=1;
Query OK, 0 rows affected (0.00 sec)
M1> select @@session.sql_require_primary_key;
+-----------------------------------+
| @@session.sql_require_primary_key |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.00 sec)
## Now check that message is same for all the below mentioned operations:-
M1> create table tnopk3 (a int);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
M1> alter table tpk1 drop primary key;
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
M1> alter table tnopk2 add column b int;
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
M1>
