query_script_execution
QueryScript Execution: script invocation
SYNOPSIS
call run('...script...');
DESCRIPTION
A script is a text, interpreted by common_schema's implementation of QueryScript.
Being a text, the code of a script can be provided by a user defined variable, a table column, a file, a function -- any construct which can produce a string.
common_schema provides the following routines to execute a script:
- run(): run a script provided as text, or possibly run script from file
- run_file(): run a script from file.
- script_runtime(): number of seconds elapsed since since execution began.
Script execution is done in two steps:
- Dry run: the script is parsed and verified to be in good structure (so called compilation)
- Wet run: the script is executed
The script does not check the structure of your queries. This is left up for MySQL to parse and validate. So, a "SELECT GROUP WHERE FROM x LIMIT HAVING;" statement is just fine as far as QueryScript is concerned.
NOTES
Limitations
QueryScript statements are:
- Interpreted by a stored routine
- Executed via dynamic SQL
- One must adhere to the limitations of dynamic SQL. Specifically, it is not allowed to issue the PREPARE statement from within QueryScript.
- One must adhere to limitations of stored routines. For example, it is impossible to disable sql_log_bin or change the statement_format from within a stored routine (the latter limitation as of MySQL 5.5).
- QueryScript itself cannot be issued from within QueryScript. Specifically, you may not call run() or run_file() from within a script. The results are unexpected. You must not call code which calls these routines, such as the foreach() routine (as opposed to the perfectly valid foreach statement).
The common_schema installation process breaks any running scripts (to be changed in the future). Avoid re-installing common_schema while QueryScript code executes.
Performance
Current implementation of QueryScript utilizes stored routines. That is, a stored routine (e.g. run()) executes the script. To affirm the immediate conclusion, scripts are being interpreted by common_schema. Moreover, since current implementation of stored routines within MySQL is itself by way of interpretation, it follows that a QueryScript code is interpreted by an interpreted code. Stored routines alone are known to be slow in execution in MySQL.
The above indicates that QueryScript should not be used for you OLTP operations. Not for the standard SELECT/INSERT/DELETE/UPDATE issued by the developer. However, QueryScript well fits the occasional maintenance work of the DBA/developer.
Generally speaking, large operations can benefit from using QueryScript: the overhead of interpreted code is usually neglectable in comparison with operations on large amounts of data. Moreover, QueryScript adds notions such as throttling to ease out on such large operations. General maintenance operations (creation, alteration or destruction of tables, users, processes, etc.) are also good candidates.
EXAMPLES
Create and run a script on the fly:
mysql> call run("
while (DELETE FROM world.Country WHERE Continent = 'Asia' LIMIT 10)
{
do sleep(1);
}
");
Store a script in session variable:
mysql> SET @script := 'foreach($t: table in world) {alter table world.:$t engine=InnoDB}';
mysql> call run(@script);
Run script from /tmp/sample.qs text file:
bash$ cat /tmp/sample.qs create table test.many_numbers (n bigint unsigned auto_increment primary key); insert into test.many_numbers values(NULL); foreach($i: 1:10) insert into test.many_numbers select NULL from test.many_numbers;
mysql> call run_file('/tmp/sample.qs');
mysql> SELECT COUNT(*) FROM test.many_numbers;
+----------+
| COUNT(*) |
+----------+
| 1024 |
+----------+
run() can also load scripts from file, if given input appears to indicate a file name:
mysql> call run('/tmp/sample.qs');
mysql> SELECT COUNT(*) FROM test.many_numbers;
+----------+
| COUNT(*) |
+----------+
| 1024 |
+----------+