MySQL Bugs: #94134: CREATE TABLE IF NOT EXISTS .. should not always fail on sql_require_primary

Bug #94134	CREATE TABLE IF NOT EXISTS .. should not always fail on sql_require_primary_key
Submitted:	30 Jan 2019 9:09	Modified:	12 Feb 2019 20:19
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.13, 8.0.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	sql_require_primary_key

Description:
I see a replication error on a server where I've added an 8.0 slave with sql_require_primary_key = 1.   However the table was not created. Code in our code base shows this table has been around for a couple of years.

Note: this feature is good and most welcome. The only concern here is the migration of existing systems to 8.0 and during that transition avoiding as many issues as possible. This seems to be one of them.

How to repeat:
The error seen is:

Slave SQL Running: No, SQL Error 3750: Error 'Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.' on query. Default database: 'db'. Query: 'CREATE TABLE IF NOT EXISTS mytable (

Message is truncated due to mysqld truncating it. I have changed the db and table names.

The table _definition_ indeed does not have a primary key, but the table already exists on the server so it is NOT being created.  That is on this server the statement is a no-op.

Suggested fix:
I think that under these circumstances there should be no error.
Should the table need to be created on the server it is correct to give the error.

Hello Simon,

Thank you for the report and feedback.

regards,
Umesh

## On standalone instance

bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> set @@global.sql_require_primary_key=0, @@session.sql_require_primary_key=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_require_primary_key;
+-----------------------------------+
| @@session.sql_require_primary_key |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> create table t1 (col1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> set @@global.sql_require_primary_key=1, @@session.sql_require_primary_key=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_require_primary_key;
+-----------------------------------+
| @@session.sql_require_primary_key |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TABLE IF NOT EXISTS t1(col1 int);
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
mysql>

Posted by developer:
 
Fixed in 8.0.16.

CREATE TABLE IF NOT EXISTS failed even if the table already exists if
the new table definition had no primary key and the
sql_require_primary_key system variable was enabled.