← Back to team overview

maria-developers team mailing list archive

Re: Binlog handling of faulty DDL query


Sachin, hello.

> Hi Everyone!
> Suppose this case
>   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 ?

Actually as people asked already 5.7 exposes a third behavior to log
with an error like

# at 477
#180903 10:47:40 server id 1  end_log_pos 777 CRC32 0x13d70f7f  Query   thread_id=3     exec_time=0     error_code=1396
SET TIMESTAMP=1535960860/*!*/;
CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BF2598DC3E1E89DF9EBEF90FE77AA0486DABDC06','user2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*B69027D44F6E5EDC07F1AEAD1477967B16F28227'

While rewriting the query to leave only succeeded items makes sense I
think the 5.7 method is not that bad. Actually recording an error
is still a standard for Mariadb when rollback is impossible.
So I think it's better off to stay with that. Hopefully we turn such DDL
and others to atomic in future.