From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 35890200A0 for ; Thu, 26 Jul 2018 07:22:22 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id EjJzMmlTjZ2i for ; Thu, 26 Jul 2018 07:22:22 -0400 (EDT) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id E2D4C20090 for ; Thu, 26 Jul 2018 07:22:21 -0400 (EDT) Subject: [tarantool-patches] Re: [PATCH v1 2/2] sql: prevent executing crossengine sql References: <281C2B94-5BA3-4868-B3A5-53234E2DCA4D@tarantool.org> <12091997-2a63-951d-9477-86c3429d981d@tarantool.org> <589c2e7f-7701-d075-8c91-6d5707e6a9a5@tarantool.org> From: Kirill Shcherbatov Message-ID: Date: Thu, 26 Jul 2018 14:22:19 +0300 MIME-Version: 1.0 In-Reply-To: <589c2e7f-7701-d075-8c91-6d5707e6a9a5@tarantool.org> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org, Vladislav Shpilevoy > 1. I did not purposed to use single box.sql.execute. > 2. I did not insist on the single line. My proposal was to remove f(). Ok. > 3. Why do you need ';' after execute()? Deleted... =================================== Some sql requests are complex and could contain R/W with multiple spaces. As we have no ability to make such changes transactionaly, we have to disallow such requests. Since now iterators in SQL start transaction so we cant prevent such vicious and dangerous things. Closes #3551 --- src/box/sql.c | 8 ++++ test/sql/triggers.result | 110 +++++++++++++++++++++++++++++++++++++++++++++ test/sql/triggers.test.lua | 47 +++++++++++++++++++ 3 files changed, 165 insertions(+) diff --git a/src/box/sql.c b/src/box/sql.c index d48c3cf..db08fcf 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -1118,12 +1118,20 @@ cursor_seek(BtCursor *pCur, int *pRes) return SQL_TARANTOOL_ITERATOR_FAIL; } + struct space *space = pCur->space; + struct txn *txn = in_txn(); + if (space->def->id != 0) { + if (txn_begin_ro_stmt(space, &txn) != 0) + return SQL_TARANTOOL_ERROR; + } struct iterator *it = index_create_iterator(pCur->index, pCur->iter_type, key, part_count); if (it == NULL) { + txn_rollback_stmt(); pCur->eState = CURSOR_INVALID; return SQL_TARANTOOL_ITERATOR_FAIL; } + txn_commit_ro_stmt(txn); pCur->iter = it; pCur->eState = CURSOR_VALID; diff --git a/test/sql/triggers.result b/test/sql/triggers.result index dc0a2e5..658571b 100644 --- a/test/sql/triggers.result +++ b/test/sql/triggers.result @@ -246,3 +246,113 @@ box.sql.execute("DROP VIEW V1;") box.sql.execute("DROP TABLE T1;") --- ... +-- +-- gh-3531: Assertion with trigger and two storage engines +-- +-- Case 1: Src 'vinyl' table; Dst 'memtx' table +box.sql.execute("PRAGMA sql_default_engine ('vinyl');") +--- +... +box.sql.execute("CREATE TABLE m (s1 SCALAR PRIMARY KEY);") +--- +... +box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;") +--- +... +box.sql.execute("PRAGMA sql_default_engine('memtx');") +--- +... +box.sql.execute("CREATE TABLE n (s1 CHAR PRIMARY KEY, s2 char);") +--- +... +box.sql.execute("INSERT INTO m VALUES ('');") +--- +... +box.sql.execute("INSERT INTO n VALUES ('',null);") +--- +... +box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';") +--- +- error: A multi-statement transaction can not use multiple storage engines +... +-- ANALYZE operates with _sql_stat{1,4} tables should work +box.sql.execute("ANALYZE m;") +--- +... +box.sql.execute("DROP TABLE m;") +--- +... +box.sql.execute("DROP TABLE n;") +--- +... +-- Case 2: Src 'memtx' table; Dst 'vinyl' table +box.sql.execute("PRAGMA sql_default_engine ('memtx');") +--- +... +box.sql.execute("CREATE TABLE m (s1 SCALAR PRIMARY KEY);") +--- +... +box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;") +--- +... +box.sql.execute("PRAGMA sql_default_engine('vinyl');") +--- +... +box.sql.execute("CREATE TABLE n (s1 CHAR PRIMARY KEY, s2 char);") +--- +... +box.sql.execute("INSERT INTO m VALUES ('');") +--- +... +box.sql.execute("INSERT INTO n VALUES ('',null);") +--- +... +box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';") +--- +- error: A multi-statement transaction can not use multiple storage engines +... +-- ANALYZE operates with _sql_stat{1,4} tables should work +box.sql.execute("ANALYZE n;") +--- +... +box.sql.execute("DROP TABLE m;") +--- +... +box.sql.execute("DROP TABLE n;") +--- +... +-- Test SQL Transaction with LUA +box.sql.execute("PRAGMA sql_default_engine ('memtx');") +--- +... +box.sql.execute("CREATE TABLE test (id INT PRIMARY KEY)") +--- +... +box.sql.execute("PRAGMA sql_default_engine='vinyl'") +--- +... +box.sql.execute("CREATE TABLE test2 (id INT PRIMARY KEY)") +--- +... +box.sql.execute("INSERT INTO test2 VALUES (2)") +--- +... +box.sql.execute("START TRANSACTION") +--- +... +box.sql.execute("INSERT INTO test VALUES (1)") +--- +... +box.sql.execute("SELECT * FROM test2") +--- +- error: A multi-statement transaction can not use multiple storage engines +... +box.sql.execute("ROLLBACK;") +--- +... +box.sql.execute("DROP TABLE test;") +--- +... +box.sql.execute("DROP TABLE test2;") +--- +... diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua index e019c00..8fd385c 100644 --- a/test/sql/triggers.test.lua +++ b/test/sql/triggers.test.lua @@ -95,3 +95,50 @@ box.space._trigger:insert(tuple) box.sql.execute("DROP VIEW V1;") box.sql.execute("DROP TABLE T1;") + +-- +-- gh-3531: Assertion with trigger and two storage engines +-- +-- Case 1: Src 'vinyl' table; Dst 'memtx' table +box.sql.execute("PRAGMA sql_default_engine ('vinyl');") +box.sql.execute("CREATE TABLE m (s1 SCALAR PRIMARY KEY);") +box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;") +box.sql.execute("PRAGMA sql_default_engine('memtx');") +box.sql.execute("CREATE TABLE n (s1 CHAR PRIMARY KEY, s2 char);") +box.sql.execute("INSERT INTO m VALUES ('');") +box.sql.execute("INSERT INTO n VALUES ('',null);") +box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';") + +-- ANALYZE operates with _sql_stat{1,4} tables should work +box.sql.execute("ANALYZE m;") +box.sql.execute("DROP TABLE m;") +box.sql.execute("DROP TABLE n;") + + +-- Case 2: Src 'memtx' table; Dst 'vinyl' table +box.sql.execute("PRAGMA sql_default_engine ('memtx');") +box.sql.execute("CREATE TABLE m (s1 SCALAR PRIMARY KEY);") +box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;") +box.sql.execute("PRAGMA sql_default_engine('vinyl');") +box.sql.execute("CREATE TABLE n (s1 CHAR PRIMARY KEY, s2 char);") +box.sql.execute("INSERT INTO m VALUES ('');") +box.sql.execute("INSERT INTO n VALUES ('',null);") +box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';") + +-- ANALYZE operates with _sql_stat{1,4} tables should work +box.sql.execute("ANALYZE n;") +box.sql.execute("DROP TABLE m;") +box.sql.execute("DROP TABLE n;") + +-- Test SQL Transaction with LUA +box.sql.execute("PRAGMA sql_default_engine ('memtx');") +box.sql.execute("CREATE TABLE test (id INT PRIMARY KEY)") +box.sql.execute("PRAGMA sql_default_engine='vinyl'") +box.sql.execute("CREATE TABLE test2 (id INT PRIMARY KEY)") +box.sql.execute("INSERT INTO test2 VALUES (2)") +box.sql.execute("START TRANSACTION") +box.sql.execute("INSERT INTO test VALUES (1)") +box.sql.execute("SELECT * FROM test2") +box.sql.execute("ROLLBACK;") +box.sql.execute("DROP TABLE test;") +box.sql.execute("DROP TABLE test2;") -- 2.7.4