3 Sep
2018
3 Sep
'18
11:49 a.m.
On Mon, Sep 3, 2018 at 2:35 AM Pavel Ivanov <pivanof@google.com> wrote: > > 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. Right > > Pavel > > On Sun, Sep 2, 2018 at 5:59 AM Simon Mudd <simon.mudd@booking.com> wrote: > > > > Hi, > > > > On 2 Sep 2018, at 11:27, Sachin Setiya <sachin.setiya@mariadb.com> 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 …] Nope, in 5.7 it is like mariadb > > > > 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. Actually if we dont want partial binary log (either with wrong user creation , or trimmed binary log with only correct part), then user creation has to be atomic. > > > > 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. > > Right but this need atomic ddl , which might come in newer versions of mariadb. > > 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. > > Right , it sets error in Query_log_event->error_code. > > In any account management I’ve used I’ve always handled users one by one and thus atomic behaviour is implicitly guaranteed. Right > > 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. > > Right, So untill we have atomic ddl , I think create user with single user is much better option for practical use. > > 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. > > Right it creates less confusion , but we also also modify binlog in some query , for example in the case of "drop table t1(normal table), t2(temporarty table);" In binary log we will have 2 Query_log_event 1. Drop table t1; 2. Drop temporary table t2; So we do some minor changes into binlog. > > Regards, > > > > Simon > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-developers > > Post to : maria-developers@lists.launchpad.net > > Unsubscribe : https://launchpad.net/~maria-developers > > More help : https://help.launchpad.net/ListHelp -- Regards Sachin Setiya Software Engineer at MariaDB