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
