[Maria-developers] Binlog handling of faulty DDL query
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 ? -- Regards sachin
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 <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
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. 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 …]
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.
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
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.
Hi Reindl On Mon, Sep 3, 2018 at 2:48 AM Reindl Harald <h.reindl@thelounge.net> 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@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.
_______________________________________________ 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
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
Sachin, hello.
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 ?
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. Cheers, Andrei
participants (5)
-
andrei.elkin＠pp.inet.fi -
Pavel Ivanov -
Reindl Harald -
Sachin Setiya -
Simon Mudd