WL#11807: Add sysvar which prohibits pk-less tables
Affects: Server-8.0 — Status: Complete
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, 2020, Oracle Corporation and/or its affiliates. All rights reserved.