MySQL Bugs: #69845: Provide an option to prevent creation of tables without a unique/PK

Bug #69845	Provide an option to prevent creation of tables without a unique/PK
Submitted:	26 Jul 2013 7:42	Modified:	10 Jul 2018 17:10
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.5 / 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Provide an option to prevent creating tables without primary keys on masters with RBR enabled.  When changing data on such a table RBR can make the process very expensive.  Additionally it's hard to get
out of the hole as any inconsistencies you create will generate replication breakage on any slaves only making matters worse and requiring more work.

How to repeat:
A recent incident came up due to a largish table being created without a primary key on a RBR based master server. (MySQL 5.5 but 5.6 behaves the same).

Later a query was sent to the server which CHANGE a significant part of the table.  In our case this was a DELETE of several rows, an UPDATE would have had the same result.  On the master this did the equivalent of a _single table scan_ and did not take too long so went unnoticed.

However, given the server was using RBR, this was replicated for each changed row as the function equivalent for each row that was changed:

    UPDATE some_table SET col3=X WHERE col1=A AND col2=B AND col4=C AND ... coln = Z

On the slaves this then triggered the equivalent of N table scans where in our case N was a large number and thus generated a severe replication delay.

While this behaviour is documented and known, it is easy to dig yourself into a hole, and recovering from this requires waiting for changes to propagate through (the delay was too large in our case) or doing other manual work on each slave to resolve the issue.

Again messy and time consuming.

Suggested fix:
Adding a warning is unlikely to get seen, so ideally this behaviour should be prevented.  Once you create the table you are just waiting to be bitten.

I'd suggest one of two options:

(1) a new configuration option disable_create_table_without_unique_key

    disable_create_table_without_unique_key, default: 0,

which has both a global and session level setting.

This would allow us on our RBR servers to set this value to 1, and for those cases where we need to create a table without a unique key we can do:

SET SESSION disable_create_table_without_unique_key = 0;
CREATE TABLE no_unique_key ( id int not null ) engine = MyISAM; -- for example
SET SESSION disable_create_table_without_unique_key = 1;

Alternatively the default setting for the variable could be dependent on the binlog_format (better):

disable_create_table_without_unique_key = IF( binlog_format = 'ROW', 1, 0 );

This then automatically enables the setting on a RBR master which is probably what you would expect. It also requires less 'thinking'.

Creating a table would check the resulting table has a unique or primary key.

Note: this should probably only be applicable to explicitly created tables, not tables like: CREATE TABLE xxx LIKE yyyy, or CREATE TABLE xxx AS SELECT ... WHERE it's more likely the user is aware of what they are doing.

You can use an already available option to accomplish the goal: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sql_safe_update...

No, this does not prevent the table being created in the first place which is my intention.
Using sql_safe_updates on a master server is just "silly", as in the end you'll be disabling it every time (at the session level) and will also disable it on the table that's missing indexes.

The thing to do really, which is why I'm asking for this is an option to prevent the table being created in the first place. Basically it's a warning: " you are going to create a table which if you use UPDATE/DELETE on it will generate table scans: that is not wise".

Once the table is there dormant the problem is waiting to happen.

Note: after discussion with others the restriction should probably be that the table has no indexes, not the one stated in this bug number. That's probably fine as well.

I really want to prevent the table being created, unless you explicitly work around it (because you know what you are doing), so you can't shoot yourself in the foot.  The fact that no feature request exists for this I believe is because most people still use SBR not RBR.  From the servers I manage I think 10% of the replication chains use RBR (for various reasons), and I do not think I am an exception.

> Using sql_safe_updates on a master server is just "silly", as in the end you'll be disabling it every time (at the session level) and will also disable it on the table that's missing indexes.

Which is the right thing to do as a "table scan" is something that is caused by a query.

> The thing to do really, which is why I'm asking for this is an option to prevent the table being created in the first place. Basically it's a warning: " you are going to create a table which if you use UPDATE/DELETE on it will generate table scans: that is not wise".

Table scans can happen even if there are indexes in the table. They are equally bad for replication.

> The fact that no feature request exists for this I believe is because most people still use SBR not RBR. 

or because when people encounter it, they use sql_safe_updates. Which was the case for me, although we had to improve slave status information to highlight the problematic events.

Thank you for reasonable feature request.

Since tables without Primary Key may be big problem for replication in ROW format, I think options like following must be created:

sql_safe_table_definition:
value 1 means MySQL will show a warning if CREATE TABLE / ALTER TABLE / DROP INDEX , etc commands result in table without Primary or Unique index
value 2 and bigger means MySQL will show an error if CREATE TABLE / ALTER TABLE / DROP INDEX , etc commands result in table without Primary or Unique index

binlog_max_update_rows_no_pk=N - shows warning if server tries to log UPDATE or DELETE statement in ROW format on table without unique key, which results in more than N events.

replicate_max_update_rows_no_pk=N - logs warning message into error log if replication IO or SQL thread tries to process UPDATE or DELETE statement in ROW format on table without unique key, which results in more than N events.

Related: Bug#69223.

Can we expect that in 8.0 GA pretty please? :)

I think this is a duplicate of:
Bug #69223 	Give a warning on CREATE TABLE without PRIMARY KEY

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.