WL#11807: Add sysvar which prohibits pk-less tables

Tables without PK can cause performance problems on the slave when doing
row-based replication. The resulting inconsistencies will generate replication
breakage on any slaves which will exacerbate the problem and require additional
work.

Add a system variable sql_require_primary_key (global & session), which if set,
causes CREATE or ALTER statements that would yield a table without PK, to fail
with ER_TABLE_WITHOUT_PK

Motivation:

"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." 

From BUG#69845.

F1: Have system variable sql_require_primary_key
F2: RPK must be settable @ session level
F3: RPK must be settable @ global level -> new default for new connections
F4: Default is OFF
F5: When RPK is set, create of table without explicit PK must fail
F6: When RPK is set, dropping the PK column from a table must fail
F7: When RPK is set, create like  must fail
F8: When RPK is set, create ... as must fail unless a PK-signature is used (even
select * from 
F9: When RPK is set, import of a PK-less table must fail
F10: RPK must be settable on the command line without interfering with the
creation of PK-less system tables during initialize/bootstrap.
F11: RPK must not prevent the PK from being dropped and a added by a single
ALTER statemnt
F12: RPK must also prevent PK-less temporary tables.
F13: RPK setting must be propagated to the slave applier
F14: RPK can only be set with SUPER or SYSTEM_VARIABLES_ADMIN privilege.

                
- Add new system variable to existing variables in sql/sys_vars.cc

Using the correct arguments when creating the global object representing the
system variable will ensure that requirements F1-F4 is satisfied.

- Specify check_has_super as the check function argument to ensure that
privileges are checked. This will satisfy requirement F14.

- Check the value of the variable in  mysql_prepare_create_table() and return
error if true and table does not have PK.

This function is already called for both CREATE and ALTER, and already sets a
variable to indicate if the created or altered table has a primary key.
Consequently, a check of this variable will satisfy requiremens F5-F8 and
F11-F12. By also testing for thd->is_dd_system_thread() and
thd->is_initialize_system_thread() F10 is satisfied.

- Check the value of the variable in Import_target::load and return error if
true and the deserialized dd::Table object does not have PK. This check will
satisfy requirement F9.

- Add another binlog event to propagate the current setting of the sysvar to the
slave applier. This is needed to satisfy requirement F13.

            



        Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.