MySQL Bugs: #69223: Give a warning on CREATE TABLE without PRIMARY KEY

Bug #69223	Give a warning on CREATE TABLE without PRIMARY KEY
Submitted:	14 May 2013 6:22	Modified:	10 Jul 2018 18:02
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.6.10, 5.6.26, 5.7.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	primary key, warning

Description:
It's easy to create a table without primary key by accident. The result can be bad performance for InnoDB and replication and possible other issues. To add a key after the application has been created can be troublesome as it could break backwards compatibility as it might be required to add a surrogate primary key (id (big)int auto_increment).

http://www.fromdual.com/disadvantages-of-explicitly-not-using-innodb-primary-keys
http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/

How to repeat:
Create a table without primary key. Then check if there are any warnings.

Suggested fix:
It would be good if a developer would get a warning when he/she creates a table without primary key. Optionally it could be changed to an error.

SET GLOBAL sql_mode='WARNING_FOR_NON_PK_CREATE_TABLE';

Thank you for the bug report.

updated version, tags

As noted here not having a primary key can have performance implications at enough concurrency:
https://www.percona.com/blog/2013/10/18/innodb-scalability-issues-tables-without-primary-k...

It would be great to have MySQL generate a warning when tables are created without Primary Keys

I think it should be more than that. MySQL should optionally FORCE this.
See: http://blog.wl0.org/2016/05/lossless-rbr-for-mysql-8-0/

So maybe 3 states: do nothing, generate warning , generate error, and by default in 8.0+ make the behaviour generate an error.

One final thought. sql_modes cause all sorts of confusion and pain. It would seem better to me to have a new global variable which controls this behaviour.

In a replicated environment preventing this on a master should prevent it getting downstream to the slaves, so the configuration on the slave should not necessarily _need_ to be as strict.

Note that group replication requires primary keys (or a non-nullable unique key).

http://lefred.be/content/mysql-group-replication-and-table-design/

Related: Bug#69845.

I wrote a MySQL audit plugin to prevent creation of tables without primary key:
https://github.com/dveeden/mysql-enforcepk

Posted by developer:
 
Fixed in 8.0.13.

The new sql_require_primary_key system variable makes it possible to
have statements that create new tables or alter the structure of
existing tables enforce the requirement that tables have a primary
key. Enabling this variable helps avoid performance problems in
row-based replication that can occur when tables have no primary key.
Suppose that a table has no primary key and an update or delete
modifies multiple rows. On the master server, this operation can be
performed using a single table scan but, when replicated using
row-based replication, results in a table scan for each row to be
modified on the slave. With a primary key, these table scans do not
occur.

suggest this feature do more thing.
force the primary key data type must be int or bigint, can not be varchar/char/uuid and so on.

Ye Jinrong: I do not agree with your suggested improvement.

It may be better to have integer type primary keys but they are not necessary for the cases where issues may occur (e.g. RBR replication changing rows where there's no PK on the slave). Having a unique primary key is sufficient. There may be reasons why you need to have a non-id based primary key and making this behaviour as strict as you suggest may cause various issues on many systems.

Could the R&D port this parameter to mysql 5.7?

Porting the parameter to 5.7 is one option, but the other might be to optionally not enforce it on 8.0 if the data is coming from replication of an upstream 5.7 server where this setting does not exist or an 8.0 box where the setting does not apply.

I have been running 5.7 masters with 8.0 slaves prior to upgrading the master (and while testing the 8.0 versions) and during that time multiple 8.0 slaves have had replication break due to the new stricter setting on the master vs the upstream server.

Running a 5.7 + 8.0 combination cluster may not be typical but it often happens during the upgrade process so it's unfortunate in those situations to break the 8.0 slave because it's too strict.

So I'd be tempted to modify 8.0 only to provide a way to optionally exclude the strict behaviour from a laxer upstream master (which might not have the sql_require_primary_key set or that setting might not even exist [5.7]).