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: | |
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 |
[30 Jan 2019 9:09]
Simon Mudd
[30 Jan 2019 9:21]
MySQL Verification Team
Hello Simon, Thank you for the report and feedback. regards, Umesh
[30 Jan 2019 9:22]
MySQL Verification Team
## 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>
[12 Feb 2019 20:19]
Paul DuBois
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.