[Maria-discuss] transactional sql update patch system ?
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
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
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@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
Hi, launchpad! On Feb 28, launchpad@rupat.de wrote:
Hello,
we want, that a sql patch file can ONLY be applied if all commands in it can applied successful !
If you want that you can only use statements that can be rolled back. Or set up a replication, make sure that the slave is up to date, stop replication, apply your sql patch file and then either start replication - and the slave will catch up - or remove the master and promote the slave to the new master. Or do something similar without replication. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
participants (3)
-
launchpad＠rupat.de -
Markus Mäkelä -
Sergei Golubchik