Details
-
Type:
Task
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
Could be nice if we could parse queries without executing
Checking if they have errors and what king of queries they are (DELETE, TRUNCATE, SELECT, UPDATE, SET, etc..)
Example:
1st query) SET sandbox_on=1;
2nd query) SELECT * FROM A WHERE B=0;
SELECT * FROM A WHERE B="";
SELEC * F A W B;
3rd query) CREATE TABLE a(B INT NOT NULL)
4rd query) CREATE TABLE b(B INT NOT NULL)
5th query) SET sandbox_on=0;
6th query) SHOW SANDBOX RESULTS
RESULTS:
| ID | QUERY | TYPE | TABLES | ERROR MESSAGE |
|---|---|---|---|---|
| 1 | SELECT * FROM A WHERE B=0 | SELECT | A | NULL |
| 1 | SELECT * FROM A WHERE B="" | SELECT | A | NULL |
| 1 | SELEC * F A W B | ERROR | NULL | you have a error near blablabla |
| 2 | CREATE TABLE a(B INT NOT NULL) | CREATE | a | table a alread exists |
| 3 | CREATE TABLE b(B INT NOT NULL) | CREATE | b | NULL |
the create table could be used creating tables as TEMPORARY just to allow next queries to be executed without errors
after sandbox_on=0 all temporary tables created in sandbox must be droped (automatically), all locks must be reverted too (lock tables)
binary log is turned off in this case
lock aren't executed (but maybe they could show errors of next queries?)
it's very nice to check if we have a sql injection or test a file of queries before executing them, since we can only do it with a running database and changing tables
in a very big file is very difficult if we have a UPDATE or something that could be executed without seeking file query per query
it's nice to build query tools like in JIRA when we don't need to create a parser, just allow user to write in WHERE part of query
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
that's the point of this 'sandbox' and not a profile
Note: often the server does not need to execute SQL, only to parse it, so these classes should allow that too.
since we will have it, why not expose to sql interface
maybe a UDF?
hi guys
maybe we could close this MDEV:
https://github.com/adrpar/mysql_validateSQL/tree/master/src
It looks like the PROFILE function mixed with some kind of transactionnal EXPLAIN.
Just adding a "query status" and an "error message" column (and maybe an "implicated tables" column) to the SHOW PROFILES command would be simpler/clearer than adding a totally new option/function.
ps: EXPLAIN in 10.x is supposed to support CREATE/UPDATE/DELETE, maybe it could suffice to your needs.