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

[26 Jul 2013 7:42] Simon Mudd
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.
[26 Jul 2013 16:50] Davi Arnaut
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...
[27 Jul 2013 8:18] Simon Mudd
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.
[27 Jul 2013 15:02] Davi Arnaut
> 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.
[17 Sep 2013 10:55] Andrii Nikitin
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.
[22 Jun 2017 13:53] Jean-François Gagné
Related: Bug#69223.
[16 Mar 13:36] Przemyslaw Malkowski
Can we expect that in 8.0 GA pretty please? :)
[12 Apr 10:26] Daniël van Eeden
I think this is a duplicate of:
Bug #69223 	Give a warning on CREATE TABLE without PRIMARY KEY