From: Kirill Shcherbatov <kshcherbatov@tarantool.org>
To: tarantool-patches@freelists.org
Cc: v.shpilevoy@tarantool.org, korablev@tarantool.org,
Kirill Shcherbatov <kshcherbatov@tarantool.org>
Subject: [tarantool-patches] [PATCH v1 2/2] sql: prevent executing crossengine sql
Date: Tue, 24 Jul 2018 14:05:13 +0300 [thread overview]
Message-ID: <a1bf1a61c86205718ca465c5fa189bbbcf273188.1532430181.git.kshcherbatov@tarantool.org> (raw)
In-Reply-To: <aa9d1ac8d749bd0dc5e1f189d8480bdd907d1701.1532430181.git.kshcherbatov@tarantool.org>
In-Reply-To: <cover.1532430181.git.kshcherbatov@tarantool.org>
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 dissallow such requests. This mean
that we shouldn't create triggers on memtex spaces writing
something in vinyl and so on.
Closes #3551
---
src/box/sql.c | 11 +++++
test/sql/triggers.result | 120 +++++++++++++++++++++++++++++++++++++++++++++
test/sql/triggers.test.lua | 53 ++++++++++++++++++++
3 files changed, 184 insertions(+)
diff --git a/src/box/sql.c b/src/box/sql.c
index d48c3cf..a964fcb 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -1118,12 +1118,23 @@ cursor_seek(BtCursor *pCur, int *pRes)
return SQL_TARANTOOL_ITERATOR_FAIL;
}
+ struct space *space = pCur->space;
+ struct txn *txn = NULL;
+
+ 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) {
+ if (txn != NULL)
+ txn_rollback_stmt();
pCur->eState = CURSOR_INVALID;
return SQL_TARANTOOL_ITERATOR_FAIL;
}
+ if (txn != NULL)
+ 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..a692b89 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -246,3 +246,123 @@ 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)")
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+function f()
+ box.sql.execute("START TRANSACTION")
+ box.sql.execute("INSERT INTO test VALUES (1)")
+ box.sql.execute("SELECT * FROM test2")
+ box.sql.execute("COMMIT")
+end;
+---
+...
+f();
+---
+- 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;");
+---
+...
+test_run:cmd("setopt delimiter ''");
+---
+- true
+...
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index e019c00..a9df278 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -95,3 +95,56 @@ 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)")
+test_run:cmd("setopt delimiter ';'")
+function f()
+ box.sql.execute("START TRANSACTION")
+ box.sql.execute("INSERT INTO test VALUES (1)")
+ box.sql.execute("SELECT * FROM test2")
+ box.sql.execute("COMMIT")
+end;
+f();
+box.sql.execute("ROLLBACK;");
+box.sql.execute("DROP TABLE test;");
+box.sql.execute("DROP TABLE test2;");
+test_run:cmd("setopt delimiter ''");
--
2.7.4
next prev parent reply other threads:[~2018-07-24 11:05 UTC|newest]
Thread overview: 27+ messages / expand[flat|nested] mbox.gz Atom feed top
2018-07-20 13:52 [tarantool-patches] [PATCH v1 1/1] " Kirill Shcherbatov
2018-07-20 15:03 ` [tarantool-patches] " Vladislav Shpilevoy
2018-07-20 17:50 ` Kirill Shcherbatov
2018-07-23 11:50 ` Vladislav Shpilevoy
2018-07-23 16:20 ` n.pettik
2018-07-23 16:39 ` Vladislav Shpilevoy
2018-07-23 17:09 ` n.pettik
2018-07-23 17:21 ` Vladislav Shpilevoy
2018-07-23 18:06 ` n.pettik
2018-07-23 18:29 ` Vladislav Shpilevoy
2018-07-24 11:05 ` [tarantool-patches] [PATCH v1 1/2] sql: use schema API to get index info in analyze Kirill Shcherbatov
[not found] ` <cover.1532430181.git.kshcherbatov@tarantool.org>
2018-07-24 11:05 ` Kirill Shcherbatov [this message]
2018-07-25 13:24 ` [tarantool-patches] Re: [PATCH v1 2/2] sql: prevent executing crossengine sql n.pettik
2018-07-25 17:07 ` Kirill Shcherbatov
2018-07-25 21:05 ` Vladislav Shpilevoy
2018-07-26 7:08 ` Kirill Shcherbatov
2018-07-26 8:54 ` Vladislav Shpilevoy
2018-07-26 11:22 ` Kirill Shcherbatov
2018-07-26 21:26 ` Vladislav Shpilevoy
2018-07-27 7:13 ` Kirill Shcherbatov
2018-07-27 8:55 ` Vladislav Shpilevoy
2018-07-27 10:02 ` Kirill Shcherbatov
2018-07-27 10:14 ` Vladislav Shpilevoy
2018-07-31 7:54 ` Kirill Yukhin
2018-07-25 13:22 ` [tarantool-patches] Re: [PATCH v1 1/2] sql: use schema API to get index info in analyze n.pettik
2018-07-25 17:07 ` Kirill Shcherbatov
2018-07-25 20:52 ` Vladislav Shpilevoy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=a1bf1a61c86205718ca465c5fa189bbbcf273188.1532430181.git.kshcherbatov@tarantool.org \
--to=kshcherbatov@tarantool.org \
--cc=korablev@tarantool.org \
--cc=tarantool-patches@freelists.org \
--cc=v.shpilevoy@tarantool.org \
--subject='Re: [tarantool-patches] [PATCH v1 2/2] sql: prevent executing crossengine sql' \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox