← Back to team overview

maria-developers team mailing list archive

Re: CREATE USER gotcha


Hi Pavel,

On 12/19/2014 01:13 AM, Pavel Ivanov wrote:
On Thu, Dec 18, 2014 at 12:24 AM, Oleksandr Byelkin
<sanja@xxxxxxxxxxxxxxxx> wrote:

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:

CREATE USER user1 [IDENTIFIER BY 'password'];

so the passwords also gets synchronized.

Follow ups