maria-discuss team mailing list archive

[launchpad@xxxxxxxx]

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