[Maria-developers] sql_mode=oracle : Rollback on error
Hello Alexander, I've found one another difference between Mariadb and Oracle : oracle always rollback the active transaction if an error is raised, Maria leave the transaction active. Possible workaround in stored procedure: add an exit handler which do a rollback and resignal the error. What do you think about this ? Regards
Hello Jerome, On 03/30/2018 06:52 PM, jerome brauge wrote:
Hello Alexander, I've found one another difference between Mariadb and Oracle : oracle always rollback the active transaction if an error is raised, Maria leave the transaction active. Possible workaround in stored procedure: add an exit handler which do a rollback and resignal the error.
What do you think about this ?
Sorry, this topic is out of my expertise. Please talk to Sergei.
Regards
Hello Sergei, Can you take a glance on this issue. It's a major behavior difference and my workaround only work with stored procedures. Commercial DBs works like Oracle (as least Sybase, Sqlserver (when XACT_ABORT is ON), DB2 UDB and DB2 AS400). Regards
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.com] Envoyé : mardi 3 avril 2018 12:43 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: sql_mode=oracle : Rollback on error
Hello Jerome,
Hello Alexander, I've found one another difference between Mariadb and Oracle : oracle always rollback the active transaction if an error is raised, Maria leave the
On 03/30/2018 06:52 PM, jerome brauge wrote: transaction active.
Possible workaround in stored procedure: add an exit handler which do a rollback and resignal the error.
What do you think about this ?
Sorry, this topic is out of my expertise. Please talk to Sergei.
Regards
On 05.04.2018 12:22, jerome brauge wrote:
Hello Sergei, Can you take a glance on this issue. It's a major behavior difference and my workaround only work with stored procedures. Commercial DBs works like Oracle (as least Sybase, Sqlserver (when XACT_ABORT is ON), DB2 UDB and DB2 AS400).
Not just stored procedures, but also compound statements, as described in https://mariadb.com/resources/blog/atomic-compound-statements. similar to stored procedures in fact, just not "stored".
Regards
-----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.com] Envoyé : mardi 3 avril 2018 12:43 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: sql_mode=oracle : Rollback on error
Hello Jerome,
Hello Alexander, I've found one another difference between Mariadb and Oracle : oracle always rollback the active transaction if an error is raised, Maria leave the
On 03/30/2018 06:52 PM, jerome brauge wrote: transaction active.
Possible workaround in stored procedure: add an exit handler which do a rollback and resignal the error. What do you think about this ? Sorry, this topic is out of my expertise. Please talk to Sergei.
Regards
_______________________________________________ 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
Hi Vladislav, I agree but it's not natural to have to use compound statement and it's require extra work in many place to handle the mariadb behavior. In addition, this workaround need also an handler for "NOT FOUND" else if there is a query in the block that returns no rows, the exit handler is fired. De : Vladislav Vaintroub [mailto:vvaintroub@gmail.com] Envoyé : jeudi 5 avril 2018 13:03 À : jerome brauge; Sergei Golubchik (serg@mariadb.com) Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: [Maria-developers] sql_mode=oracle : Rollback on error On 05.04.2018 12:22, jerome brauge wrote: Hello Sergei, Can you take a glance on this issue. It's a major behavior difference and my workaround only work with stored procedures. Commercial DBs works like Oracle (as least Sybase, Sqlserver (when XACT_ABORT is ON), DB2 UDB and DB2 AS400). Not just stored procedures, but also compound statements, as described in https://mariadb.com/resources/blog/atomic-compound-statements. similar to stored procedures in fact, just not "stored". Regards -----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.com] Envoyé : mardi 3 avril 2018 12:43 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net<mailto:maria-developers@lists.launchpad.net>) Objet : Re: sql_mode=oracle : Rollback on error Hello Jerome, On 03/30/2018 06:52 PM, jerome brauge wrote: Hello Alexander, I've found one another difference between Mariadb and Oracle : oracle always rollback the active transaction if an error is raised, Maria leave the transaction active. Possible workaround in stored procedure: add an exit handler which do a rollback and resignal the error. What do you think about this ? Sorry, this topic is out of my expertise. Please talk to Sergei. Regards _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net<mailto:maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Finally, I think that handlers are not a good workaround. Some statement throws note with an sqlstate beginning with 42 and stop the code. See attached file with a create temporary table. The second call produce a wrong result. De : Maria-developers [mailto:maria-developers-bounces+j.brauge=qualiac.com@lists.launchpad.net] De la part de jerome brauge Envoyé : jeudi 5 avril 2018 14:14 À : 'Vladislav Vaintroub'; Sergei Golubchik (serg@mariadb.com) Cc : MariaDB Developers (maria-developers@lists.launchpad.net) Objet : Re: [Maria-developers] sql_mode=oracle : Rollback on error Hi Vladislav, I agree but it's not natural to have to use compound statement and it's require extra work in many place to handle the mariadb behavior. In addition, this workaround need also an handler for "NOT FOUND" else if there is a query in the block that returns no rows, the exit handler is fired. De : Vladislav Vaintroub [mailto:vvaintroub@gmail.com] Envoyé : jeudi 5 avril 2018 13:03 À : jerome brauge; Sergei Golubchik (serg@mariadb.com<mailto:serg@mariadb.com>) Cc : MariaDB Developers (maria-developers@lists.launchpad.net<mailto:maria-developers@lists.launchpad.net>) Objet : Re: [Maria-developers] sql_mode=oracle : Rollback on error On 05.04.2018 12:22, jerome brauge wrote: Hello Sergei, Can you take a glance on this issue. It's a major behavior difference and my workaround only work with stored procedures. Commercial DBs works like Oracle (as least Sybase, Sqlserver (when XACT_ABORT is ON), DB2 UDB and DB2 AS400). Not just stored procedures, but also compound statements, as described in https://mariadb.com/resources/blog/atomic-compound-statements. similar to stored procedures in fact, just not "stored". Regards -----Message d'origine----- De : Alexander Barkov [mailto:bar@mariadb.com] Envoyé : mardi 3 avril 2018 12:43 À : jerome brauge Cc : MariaDB Developers (maria-developers@lists.launchpad.net<mailto:maria-developers@lists.launchpad.net>) Objet : Re: sql_mode=oracle : Rollback on error Hello Jerome, On 03/30/2018 06:52 PM, jerome brauge wrote: Hello Alexander, I've found one another difference between Mariadb and Oracle : oracle always rollback the active transaction if an error is raised, Maria leave the transaction active. Possible workaround in stored procedure: add an exit handler which do a rollback and resignal the error. What do you think about this ? Sorry, this topic is out of my expertise. Please talk to Sergei. Regards _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net<mailto:maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi, Jerome! On Apr 05, jerome brauge wrote:
Hello Sergei, Can you take a glance on this issue. It's a major behavior difference and my workaround only work with stored procedures. Commercial DBs works like Oracle (as least Sybase, Sqlserver (when XACT_ABORT is ON), DB2 UDB and DB2 AS400).
I couldn't find what SQL standard says about it. It says an implicit rollback should happen in case of the "unrecoverable error", but doesn't clarify what errors are unrecoverable. Do you say that we should abort the whole transaction in sql_mode=ORACLE? I think it's not even difficult to do. But may be a too big change for 10.3 at this point? Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hello Sergei,
-----Message d'origine----- De : Sergei Golubchik [mailto:serg@mariadb.org] Envoyé : mercredi 11 avril 2018 20:37 À : jerome brauge Cc : maria-developers@lists.launchpad.net Objet : Re: sql_mode=oracle : Rollback on error
Hi, Jerome!
On Apr 05, jerome brauge wrote:
Hello Sergei, Can you take a glance on this issue. It's a major behavior difference and my workaround only work with stored procedures. Commercial DBs works like Oracle (as least Sybase, Sqlserver (when XACT_ABORT is ON), DB2 UDB and DB2 AS400).
I couldn't find what SQL standard says about it. It says an implicit rollback should happen in case of the "unrecoverable error", but doesn't clarify what errors are unrecoverable.
Do you say that we should abort the whole transaction in sql_mode=ORACLE?
Yes, this is right.
I think it's not even difficult to do. But may be a too big change for 10.3 at this point?
I let you decide of the right target release but 10.3 will be fine for us. Regards, Jérôme.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
participants (4)
-
Alexander Barkov -
jerome brauge -
Sergei Golubchik -
Vladislav Vaintroub