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)

On Thu, Dec 18, 2014 at 12:24 AM, Oleksandr Byelkin 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).

On Thu, Dec 18, 2014 at 11:18 PM, 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.

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