WL#11144: Improve test coverage for create user/alter/drop if exists with replication
Affects: Server-8.0
—
Status: Complete
This WL is create to add test coverage for scenarios around create user/alter/drop if exists, this was requested after bugfix of 85733 in 5.7. Suggested areas for tests include: Please cover tests when multiple users are created/dropped/altered, and different users exist on master/slave, e.g.: - all users exist on master, some on slave - all users exist on master, none on slave - some users exist on master, others on slave - some users exist on master, all on slave - some users exist on master, none on slave - no users exist on master, some on slave - no users exist on master, all on slave - When creating/altering/dropping multiple users, and there is an error for some of the users (other than those errors that are ignored by IF [NOT] EXISTS), is the statement atomic or not in 8.0? If atomic, that helps a lot: please just test that it is not logged and does not generate a GTID. Test that if the error happens on the slave, the slave stops with appropriate error message. If not atomic, please test that is is logged, it must be consistent with e.g. how create/drop/alter table works for the analogous cases. E.g. error on master but not on slave, error for different users, etc. - We should not expect any issues with upgrades or cross-version replication. OLD->NEW: nothing to test since the statement was not logged on OLD. NEW->OLD: this will only fail in case OLD is so old that it does not support CREATE USER IF NOT EXISTS syntax.
FR1: Replication must work if CREATE/DROP/ALTER IF [NOT] EXISTS is done for multiple user and different users exists on master and slave. FR2: CREATE/DROP/ALTER IF [NOT] EXISTS for single user will not be logged whenever it results in an ERROR. FR3: Replication must work correctly if CREATE/DROP/ALTER IF [NOT] EXISTS is used for multiple users and results in ERROR for a few of these users specified. FR3.1: If atomic (8.0 server) then check that it shall not be logged in the binary log and does not generate the GTID. FR3.2: If not atomic (5.7 server) then check that it shall be logged in the binary log and works correctly.
The aim of this worklog is to test the following scenarios. 1) Replication works successfully under following conditions. i) All users specified in CREATE/DROP/ALTER IF EXISTS exists on master but only some users exists on slave ii) All users specified in CREATE/DROP/ALTER IF EXISTS exists on master but none of the users exists on slave iii) Some users specified in CREATE/DROP/ALTER IF EXISTS exists on master and some of the other users exists on slave iv) Some users specified in CREATE/DROP/ALTER IF EXISTS exists on master but all user exists on slave v) Some users specified in CREATE/DROP/ALTER IF EXISTS exists on master but none of the users exists on slave vi) No users specified in CREATE/DROP/ALTER IF EXISTS exists on master but some users exists on slave vii) No users specified in CREATE/DROP/ALTER IF EXISTS exists on master but all users exists on slave 2) Do some CREATE/DROP/ALTER IF EXISTS for single user which results in an ERROR and check that it is not logged in the binary log. 3) Do some CREATE/DROP/ALTER IF EXISTS for multiple users which results in an ERROR for some of the users and check that i) On 8.0 it is not logged in the binary log. ii) On 5.7 it is logged in the binary log and replication works correctly.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.