← Back to team overview

maria-developers team mailing list archive

Re: [External] Binlog handling of faulty DDL query

 

Hi Reindl
On Mon, Sep 3, 2018 at 2:48 AM Reindl Harald <h.reindl@xxxxxxxxxxxxx> wrote:
>
>
>
> Am 02.09.18 um 23:04 schrieb Pavel Ivanov:
> > Note that to make CREATE USER atomic mysql.user table will need to be
> > made to use InnoDB storage engine rather than MyISAM. And that would
> > involve pretty significant amount of changes throughout the code base.
> >
> > And yes, MySQL switched mysql.user table (as well as other mysql.*
> > tables) to use InnoDB storage engine only in 8.0. In 5.7 and earlier
> > it was still MyISAM.
>
> accept or refuse the whole query and so log it complete or not at all is
> a different beast then make it atomic
Actually No ,
Suppose this
create user user 1 , user 2 (password length error).
If we want to refuse to log whole query , but when we reach the state
of binary log(Query_log_event::write)
we already have created user 1, So if we chose to ignore the whole
binlog then we have to revert
creation on user 1.
>
> please don't consider make InnoDB mandatory, here are small
> mariadb-servers where only loading innodb would multiply the whole footprint
We need some kind of transnational storage engine for storing ddl.
>
> > On Sun, Sep 2, 2018 at 5:59 AM Simon Mudd <simon.mudd@xxxxxxxxxxx> wrote:
> >>
> >> 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 it seems the atomic part is explicitly mentioned.
> >> 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.
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp



--
Regards
Sachin Setiya
Software Engineer at  MariaDB


References