maria-discuss team mailing list archive

[launchpad@xxxxxxxx]

I have 
DBMS: MariaDB (ver. 10.1.37-MariaDB-1~bionic) Driver: MariaDB Connector/J (ver. 2.4.1, JDBC4.2

Cant get it to work it simply strikes at the second word "NOT"

BEGIN NOT ATOMIC is not valid :(

any hints ?


Markus Mäkelä schrieb am 28.02.2020 13:57 (GMT +01:00):

> Hi,
> 
> Perhaps this blog post written about Atomic Compound Statements can be 
> of help: https://mariadb.com/resources/blog/atomic-compound-statements/
> 
> Markus
> 
> On 2/28/20 2:53 PM, launchpad@xxxxxxxx wrote:
>> Hello,
>>
>> we want, that a sql patch file can ONLY be applied if all commands in it can
>> applied successful !
>>
>> so our first try was:
>>
>>
>> START TRANSACTION;
>>
>> statement1
>> statement2
>> statement3
>> statement4
>> ....
>>
>> COMMIT;
>>
>> problem: this is not working because an statement can also create table, which
>> is an ddl command and which do an commit !
>> (https://mariadb.com/kb/en/start-transaction/#autocommit)
>>
>> .... DDL statements (CREATE, ALTER, DROP) and administrative statements
>> (FLUSH, RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX), and LOAD DATA
>> INFILE, cause an implicit COMMIT and start a new transaction.
>>
>> the result is that all before an failing create table statement will be
>> applied !
>>
>>
>> so this is no solution.
>>
>> so how can i garantee that only whole files can be applied ?!
>>
>> Here is an sample:
>>
>> CREATE TABLE test (number int);
>>
>> START TRANSACTION;
>> INSERT INTO test (number) VALUES (1);
>> COMMIT;
>>
>> START TRANSACTION;
>> INSERT INTO test (number) VALUES (2);
>> COMMIT;
>>
>> START TRANSACTION;
>> INSERT INTO test (number) VALUES (3);
>> CREATE TABLE test (thiswillfail int);
>> COMMIT;
>>
>> And the logs from mariadb:
>>
>>   8 Connect   root@localhost as anonymous on arcus
>>   8 Query     CREATE TABLE test (number int)
>>   8 Query     START TRANSACTION
>>   8 Query     INSERT INTO test (number) VALUES (1)
>>   8 Query     COMMIT
>>   8 Query     START TRANSACTION
>>   8 Query     INSERT INTO test (number) VALUES (2)
>>   8 Query     COMMIT
>>   8 Query     START TRANSACTION
>>   8 Query     INSERT INTO test (number) VALUES (3)
>>   8 Query     CREATE TABLE test (thiswillfail int)
>>   8 Quit
>>
>> And with another connection the table test
>>
>> SELECT * FROM test;
>> 1
>> 2
>> 3
>>
>> So we can see value 3 were persistet although there was no COMMIT !!!! because
>> of autocommit when do an create table.
>>
>> But i want that nothing is applied from my sql file when something fail !
>>
>>
>>
>> Greetings Robert
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
> 
> -- 
> Markus Mäkelä, Senior Software Engineer
> MariaDB Corporation
> t: +358 40 7740484
> 
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>