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

[14 May 2013 6:22] Daniël van Eeden
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';
[24 Jun 2013 20:03] MySQL Verification Team
Thank you for the bug report.
[30 Oct 2015 8:35] Daniël van Eeden
updated version, tags
[31 Oct 2015 20:53] Ovais Tariq
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
[6 May 2016 13:07] Simon Mudd
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.
[6 May 2016 13:24] Simon Mudd
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.
[21 Dec 2016 8:27] Daniël van Eeden
Note that group replication requires primary keys (or a non-nullable unique key).

http://lefred.be/content/mysql-group-replication-and-table-design/
[22 Jun 2017 13:54] Jean-François Gagné
Related: Bug#69845.
[12 Apr 2018 10:30] Daniël van Eeden
I wrote a MySQL audit plugin to prevent creation of tables without primary key:
https://github.com/dveeden/mysql-enforcepk
[10 Jul 2018 18:02] Paul DuBois
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.
[5 Nov 2018 8:43] Ye Jinrong
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.
[7 Nov 2018 11:49] Simon Mudd
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.
[2 Sep 2020 15:06] Eddy Ng
Could the R&D port this parameter to mysql 5.7?
[3 Sep 2020 8:43] Simon Mudd
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]).