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, launch...@rupat.de 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@lists.launchpad.net >> 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@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
