[Maria-developers] CREATE USER gotcha
Hi Ian, I think there is a CREATE USER related gotcha which is not described in the manual. CREATE USER allows to create multiple users at the same time: CREATE USER user1, user2; A reader of https://mariadb.com/kb/en/mariadb/documentation/sql-commands/account-managem... might think that the above query is a short synonym for: CREATE USER user1; CREATE USER user2; But in fact it is not always true. The difference can happen during replication. A "CREATE USER" statement is binlogged as a single shot, but only if at least one user was really created. Suppose (on master) user1 already exists, while user2 does not. "CREATE USER user1, user2" will be binary logged as is, while two independent "CREATE USER user1" and "CREARE USER user2" statements will be logged only as "CREATE USER user2". The CREATE statement for user1 won't be logged, because it failed on master! Now, suppose non of the users exist on slave. In case of "CREATE USER user1, user2;" both will be created on slave. In case of two independent queries, only "user2" will be created on slave.
The same behaviour is observed with DROP USER. On 12/18/2014 11:50 AM, Alexander Barkov wrote:
Hi Ian,
I think there is a CREATE USER related gotcha which is not described in the manual.
CREATE USER allows to create multiple users at the same time:
CREATE USER user1, user2;
A reader of https://mariadb.com/kb/en/mariadb/documentation/sql-commands/account-managem...
might think that the above query is a short synonym for:
CREATE USER user1; CREATE USER user2;
But in fact it is not always true. The difference can happen during replication.
A "CREATE USER" statement is binlogged as a single shot, but only if at least one user was really created.
Suppose (on master) user1 already exists, while user2 does not.
"CREATE USER user1, user2" will be binary logged as is,
while two independent "CREATE USER user1" and "CREARE USER user2" statements will be logged only as "CREATE USER user2". The CREATE statement for user1 won't be logged, because it failed on master!
Now, suppose non of the users exist on slave. In case of "CREATE USER user1, user2;" both will be created on slave. In case of two independent queries, only "user2" will be created on slave.
Hi! On 18.12.14 08:52, Alexander Barkov wrote:
The same behaviour is observed with DROP USER.
IMHO it is general problem for all our "multi-" commands, or it is solved somehow for DROP TABLE or multi row INSERT (for example)? (If yes it is solved that do the same if no then maybe it is not so big problem)
Hi! As I have previously worked on this module, I would like to help with this issue. I can have a look into it tomorrow. Regards, Vicentiu On Thu, 18 Dec 2014 10:24 Oleksandr Byelkin <sanja@montyprogram.com> wrote:
Hi!
On 18.12.14 08:52, Alexander Barkov wrote:
The same behaviour is observed with DROP USER.
IMHO it is general problem for all our "multi-" commands, or it is solved somehow for DROP TABLE or multi row INSERT (for example)?
(If yes it is solved that do the same if no then maybe it is not so big problem)
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
On Thu, Dec 18, 2014 at 12:24 AM, Oleksandr Byelkin <sanja@montyprogram.com> wrote:
Hi!
On 18.12.14 08:52, Alexander Barkov wrote:
The same behaviour is observed with DROP USER.
IMHO it is general problem for all our "multi-" commands, or it is solved somehow for DROP TABLE or multi row INSERT (for example)?
Note that I believe this is a general problem for all multi-row commands changing tables using non-transactional engines (all mysql.* tables use MyISAM). And using non-transactional engines with replication is always prone to problems like these. When mysql.* tables use InnoDB everything works correctly (though such configuration is not supported by stock MySQL/MariaDB code).
Hi Pavel, On 12/19/2014 01:13 AM, Pavel Ivanov wrote:
On Thu, Dec 18, 2014 at 12:24 AM, Oleksandr Byelkin <sanja@montyprogram.com> wrote:
Hi!
On 18.12.14 08:52, Alexander Barkov wrote:
The same behaviour is observed with DROP USER.
IMHO it is general problem for all our "multi-" commands, or it is solved somehow for DROP TABLE or multi row INSERT (for example)?
Note that I believe this is a general problem for all multi-row commands changing tables using non-transactional engines (all mysql.* tables use MyISAM). And using non-transactional engines with replication is always prone to problems like these. When mysql.* tables use InnoDB everything works correctly (though such configuration is not supported by stock MySQL/MariaDB code).
Right. The non-transactional style of the system tables is another side of the problem. This is not something possible to fix quickly. My concern now is how a multi-user statement, like "CREATE USER user1, user2" is binlogged. For binlog there are some options: 1. Always log as the original single statement, no matter what actually happened for user1 and user2. 2. Log as the original single statement as is, but only if the operation for at least one of the users succeeded. 3. Log as two separate statements, but only if the operation for the particular user succeeded. I.e. if user1 already exist on master and user2 does not, then log "CREATE USER user2" only. Currently it uses #2. For me it looks like an unfortunate choice. #1 would be better, as it would result into more synchronized sets of data on master and slave. To make it even better, slave should probably translate the commands from CREATE USER user1 [IDENTIFIER BY 'password']; to something like: DROP USER IF EXISTS user1; CREATE USER user1 [IDENTIFIER BY 'password']; so the passwords also gets synchronized.
On Thu, Dec 18, 2014 at 11:18 PM, Alexander Barkov <bar@mariadb.org> wrote:
The same behaviour is observed with DROP USER.
IMHO it is general problem for all our "multi-" commands, or it is solved somehow for DROP TABLE or multi row INSERT (for example)?
Note that I believe this is a general problem for all multi-row commands changing tables using non-transactional engines (all mysql.* tables use MyISAM). And using non-transactional engines with replication is always prone to problems like these. When mysql.* tables use InnoDB everything works correctly (though such configuration is not supported by stock MySQL/MariaDB code).
Right. The non-transactional style of the system tables is another side of the problem. This is not something possible to fix quickly.
My concern now is how a multi-user statement, like "CREATE USER user1, user2" is binlogged.
For binlog there are some options:
1. Always log as the original single statement, no matter what actually happened for user1 and user2.
2. Log as the original single statement as is, but only if the operation for at least one of the users succeeded.
3. Log as two separate statements, but only if the operation for the particular user succeeded. I.e. if user1 already exist on master and user2 does not, then log "CREATE USER user2" only.
Currently it uses #2. For me it looks like an unfortunate choice. #1 would be better, as it would result into more synchronized sets of data on master and slave.
To make it even better, slave should probably translate the commands from
CREATE USER user1 [IDENTIFIER BY 'password'];
to something like:
DROP USER IF EXISTS user1; CREATE USER user1 [IDENTIFIER BY 'password'];
so the passwords also gets synchronized.
IIRC, CREATE USER doesn't do anything special to write the statement into binlog the way it does. It's done like that just because of the MyISAM properties. So if you want to change that you will have to introduce some special (most probably hackish) processing, which will complicate the code and will be removed eventually anyway (I believe Oracle works hard to deprecate MyISAM). And BTW, some of the tests depend on CREATE USER to work the way it is now (not that they test this particular behavior, but still). So I'd suggest to not change anything. Besides I don't think this will result on more synchronized data sets. E.g. if you execute DROP/CREATE on slave then the user will loose all permissions it had, and that will be different from master if master already had this user and CREATE USER didn't do anything. Pavel
participants (4)
-
Alexander Barkov -
Oleksandr Byelkin -
Pavel Ivanov -
Vicențiu Ciorbaru