maria-developers team mailing list archive

[Simon Mudd <simon.mudd@xxxxxxxxxxx>]

Hi,

> On 2 Sep 2018, at 11:27, Sachin Setiya <sachin.setiya@xxxxxxxxxxx> wrote:
> 
> Hi Everyone!
> 
> Suppose this case
> 
> CREATE USER
>  user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85',
>  user2@localhost IDENTIFIED BY 'x';
> 
> user2 has too short passowrd which will give error (if we use security plugin)
> 
> IN the case of *mariadb* we will create user1 and wont create user2
> In the case of *mysql (8.0)* we will return error and we will not
> create any user
> 
> Our approach create a lots of problem for binary log and
> replication(MDEV-14716, MDEV-16728)
> Because in Query_log_event::write() we write the whole query not the
> parts of it.
> So we have 2 options to solve this either write modified query into
> binlog or do what mysql does.
> 
> So what you think , which approach we should use ? Or there is another
> better way ?

I’m a user of MySQL / MariaDB and I hadn’t been aware you can use CREATE USER on more than one user. I guess I need to study the manual more …

Having said that you mention MySQL 8.0 and say the behaviour there is atomic. Is it also atomic in 5.7 and earlier? It would be good to know.
[ I then go to check …]

Also do we want the statement to be atomic? I don’t know if this comes under SQL specifications or not. If it does follow that.

If not as a user that administers mysql account information I’d prefer the statement to be atomic. As you say it causes less confusion
and it either works or does not.  That’s easier to handle when you automatic account creation and maintenance which some of us do.

So looking at https://dev.mysql.com/doc/refman/8.0/en/create-user.html <https://dev.mysql.com/doc/refman/8.0/en/create-user.html> it seems the atomic part is explicitly mentioned.
https://dev.mysql.com/doc/refman/5.7/en/create-user.html <https://dev.mysql.com/doc/refman/5.7/en/create-user.html> does not give this guarantee so behaviour more likely roughly matches current MariaDB behaviour.

In any account management I’ve used I’ve always handled users one by one and thus atomic behaviour is implicitly guaranteed.
However, given it is possible to create more than one user at once I’d certainly prefer to see the behaviour being atomic as otherwise you’re going to be
forced to check each of the accounts on the server after attempting to create them to see if they are there and configured as you expect. That
task is more complex than handing a more simple error.

While this seems to be a dirty word now I would like to see as much compatibility in MariaDB vs [Oracle] MySQL as possible and where this is reasonable.
Behaviour in MySQL has changed and while it doesn’t have to change in MariaDB the change in MySQL seems “reasonable”. I think it would be good
for MariaDB to follow that for the reasons stated above, if possible.

Based on expected behaviour what gets written to the binlog I think becomes easier. You send the statement as given as you only send it if it has succeeded.

Regards,

Simon