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@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