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 please don't consider make InnoDB mandatory, here are small mariadb-servers where only loading innodb would multiply the whole footprint
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 …]
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.