← Back to team overview

maria-developers team mailing list archive

Re: sql_mode=oracle : Rollback on error


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@xxxxxxxxxxxxxxxxxxx] De la part de jerome brauge
Envoyé : jeudi 5 avril 2018 14:14
À : 'Vladislav Vaintroub'; Sergei Golubchik (serg@xxxxxxxxxxx)
Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx)
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@xxxxxxxxx]
Envoyé : jeudi 5 avril 2018 13:03
À : jerome brauge; Sergei Golubchik (serg@xxxxxxxxxxx<mailto:serg@xxxxxxxxxxx>)
Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx<mailto:maria-developers@xxxxxxxxxxxxxxxxxxx>)
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".


-----Message d'origine-----

De : Alexander Barkov [mailto:bar@xxxxxxxxxxx]

Envoyé : mardi 3 avril 2018 12:43

À : jerome brauge

Cc : MariaDB Developers (maria-developers@xxxxxxxxxxxxxxxxxxx<mailto:maria-developers@xxxxxxxxxxxxxxxxxxx>)

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.



Mailing list: https://launchpad.net/~maria-developers

Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx<mailto:maria-developers@xxxxxxxxxxxxxxxxxxx>

Unsubscribe : https://launchpad.net/~maria-developers

More help   : https://help.launchpad.net/ListHelp

Attachment: bad_exit_handler.sql
Description: bad_exit_handler.sql