October 2014 - developers - lists.mariadb.org

[Maria-developers] DisconnectEx() problem
by Delveri chick 28 Oct '14

28 Oct '14

Hi i was tracing why mysql_socket_shutdown(...) won't call DisconnectEx(...) as reported in the TODO list and realised DisconnectEx(...) cannot be called in include/mysql/psi/mysql_socket.h. Possible solution would be to add it here or include the *.h where it it defined. Where is it defined please? Regards Delveri

2 1

Re: [Maria-developers] [Commits] 18a7470: MDEV-6939 : Dots in file names of configuration files
by Sergei Golubchik 28 Oct '14

28 Oct '14

Hi, Nirbhay! 1. Why 10.1? 1.0 would be fine too, may be even 5.5 2. In cases like this I usually make two commits, one for a cleanup and one for a bug fix. Anyway, the fix is ok to push, of course. On Oct 24, nirbhay(a)mariadb.com wrote: > revision-id: 18a7470a563c30bffbfe787e4e5406b687597a44 > parent(s): cc656e4d8538326d85e84adbe48d0a3739d55674 > committer: Nirbhay Choubey > branch nick: server > timestamp: 2014-10-24 16:34:54 -0400 > message: > > MDEV-6939 : Dots in file names of configuration files > > Use fn_ext2() to get the file extension from last occurrence > of FN_EXTCHAR ('.') instead. Also made some cosmetic changes > in mysys/mf_fn_ext.c. > > --- > mysys/mf_fn_ext.c | 11 ++++++----- > mysys/my_default.c | 2 +- > 2 files changed, 7 insertions(+), 6 deletions(-) > Regards, Sergei

1 0

[Maria-developers] DATE and GROUP_MIN_MAX optimization
by Alexander Barkov 28 Oct '14

28 Oct '14

Hello Sergey, I'm working on pluggable data types and moving some pieces of the code into new classes for data type handlers. I noticed that GROUP_MIN_MAX optimization works fine with a TIME column with temporal and string literals but does not work for an integer literal: # This script creates demo data: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT, b TIME, KEY(id,b)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,10),(1,11),(1,12),(1,13),(1,14),(1,15),(1,16),(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16); SELECT * FROM t1; # Now three EXPLAIN queries: explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<TIME'10:10:10' GROUP BY id; explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<'10:10:10' GROUP BY id; explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<10 GROUP BY id; The first and the second EXPLAIN queries tell that 8 rows are to be examined; tracing the code in debugger shows that group_min_max optimization is active: > mysql> explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<TIME'10:10:10' GROUP BY id; > +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | > +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ > | 1 | SIMPLE | t1 | range | NULL | id | 9 | NULL | 8 | Using where; Using index for group-by | > +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ > 1 row in set (0.00 sec) > > mysql> explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<'10:10:10' GROUP BY id; > +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | > +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ > | 1 | SIMPLE | t1 | range | NULL | id | 9 | NULL | 8 | Using where; Using index for group-by | > +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ > 1 row in set (0.00 sec) > The third EXPLAIN query tells that all 14 rows is to be examined, and tracing in debugging shows that group_min_max is not enabled: > mysql> explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<10 GROUP BY id; > +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | > +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > | 1 | SIMPLE | t1 | index | NULL | id | 9 | NULL | 14 | Using where; Using index | > +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ > 1 row in set (0.01 sec) Any reasons not to use group_min_max for TIME column in a combination with an integer literal? I have a feeling that it should work. What about decimal and real literals? Thanks.

1 0

[Maria-developers] Problem with KVM version in Buildbot?
by Kristian Nielsen 24 Oct '14

24 Oct '14

Hi Daniel, We have a problem with a build in Buildbot: https://internal.askmonty.org/buildbot/builders/kvm-rpm-centos7_0-x86_64/bu… + qemu-img create -b /kvm/vms/vm-centos7_0-x86_64-build.qcow2 -f qcow2 /dev/shm/vm-tmp-2302.qcow2 qemu-img: '' uses a qcow2 feature which is not supported by this qemu version: QCOW version 3 qemu-img: Could not open '/kvm/vms/vm-centos7_0-x86_64-build.qcow2' Failed to clone base image, aborting This failure happens on the build host `terrier'. I assume the problem is that the image was created on a machine with a newer KVM? The host terrier seems to have a rather old one: knielsen@terrier:~> kvm --version QEMU emulator version 0.13.0 (qemu-kvm-devel), Copyright (c) 2003-2008 Fabrice Bellard How do you think we should solve the problem? Should we upgrade terrier? Or should we alternatively make sure to create new images in a backward-compatible version (and re-create affected images) ? Also, another issue related to VM images. Our old images were created with rather small disk size. I noticed that some new images are created much larger, for example: $ qemu-img info /kvm/vms/vm-sid-amd64-build.qcow2 image: /kvm/vms/vm-sid-amd64-build.qcow2 file format: qcow2 virtual size: 40G (42949672960 bytes) disk size: 3.1G cluster_size: 65536 Format specific information: compat: 0.10 That is fine, I think, it is indeed annoying when we run out of space in a build VM. However, the reason I noticed this is because host bb02 ran out of space in /run/shm/ due to a 16 GB copy of this image. So when we use larger VM images, we cannot use the optimisation to have the temporary copies in ramdisk, lest we get disk full errors. If the virtual size is larger to (ram disk size) / (max_builds), then we need to disable using ramdisk for buildbot for that image. I will try to fix the config to not use ramdisk for existing images that have too large virtual size. But it is something to keep in mind for the future. - Kristian.

3 3

Re: [Maria-developers] SQL Accelerator FPGA/ASIC
by Nasser Ghoseiri 24 Oct '14

24 Oct '14

Dear Jan, Thank you for your response. I have background in SQL (MS, Sybase, MySQL, Postgre), so luckily I'm not a beginner in query writing, however, I have absolutely no background in how databases work on background (other than hash-table). The idea is not really SIMD here, but a specially designed hardware to perform database functions much faster than the conventional DISK<->RAM<->CACHE<->CPU model. What I'm struggling to figure is that the latency caused by a enterprise-grade SQL script (not very correct and advanced code, but not very low-quality either), is Memory-Related or Arithmetic related or both? Here is what can be made for example: 1) A card with 16 DDR3 Chips on board and a Central FPGA, allowing very fast access to data, and like 100 small CPUs integrated in the FPGA to process a query. (a query that was written to take advantage of these features however) 2) A card with XDR Memory chips and like 32GB of Flash Chips to achieve very high-speed storage, retrieval (SSD<->RAM) and an FPGA that has many cores inside. A query is executed by FPGA pulling large chunk of data from Flashes, putting them in XDR RAM chips and processes them in a parallel manner 3) A card with XDR Memory chips and an FPGA on board, connected to a PCI Express 8x which receives all the data it needs from the host computer, and does the parallel processing inside itself, returning it back to the host. 4) A card with an FPGA inside, having 100 small CPUs and PCI Express to take commands from host computer and process in a parallel manner 5) A card with an FPGA, not having small processors inside, but special circuitry that does either lookup, sorting, etc in a very fast manner These are a few examples that I gave. The difference among them is the notion of having RAM, having a cold-storage, having an FPGA with many CPUs inside or an FPGA with special dedicated circuitry that performs special functions in parallel that is difficult for a CPU to handle (imaging adding 300 numbers to another 300 numbers in a single cycle, where a CPU with SIMD would take much longer, or maybe a string processor that processes many string columns with PATINDEX, etc in parallel where a CPU would be slow as it can't handle them in parallel). So what I'm researching is whether beneath the SQL pyramid, there is a memory intensive operation going on or logic-intensive. What to look for and what to aim for... Thanks in advance for your help, Nasser On Friday, October 24, 2014 7:49 PM, Jan Lindström <jan.lindstrom(a)mariadb.com> wrote: Hi, This idea of SIMD (single instruction multiple data) processing is not totally new one, similarly the idea to perform SQL-operations inside GPU or GPGA is not new. In traditional relational databases problematic is the fact that e.g. in your example TableX contains several columns, picking columns A and B from pages that reside first on disk, then on main-memory and finally on L1-L3 cache is not cheap, and then they are not on continuous memory. This is because page in cache would contain values for other columns that we not even need. In columnar database architecture this would be a lot easier, you just feed column containing values for A and B directly to SIMD operation and every page in main memory would contain a lot more values to process compared to traditional relational database where page would contain also values for columns that we really do not even need to process the result set of query. Anyway, I find the proposal interesting and challenging. R: Jan Lindström On Thu, Oct 23, 2014 at 3:39 PM, Nasser Ghoseiri <cdmcsd(a)yahoo.com> wrote: Dear Serg, > > >Following our little chat in IRC, I'm writing this email to explain in more detail what the idea is. My name is Nasser GHOSEIR (Founder, CTO of Butterfly labs), which I must note that this project is not from Butterfly Labs, >but will be a new company in Europe. > > >Our idea is to find a way to accelerate SQL query processing by either: > > >1) Creating an FPGA solution (Which will later evolve into ASIC), that has like 400 processors in itself, allow distribute calculation of some kind >2) Creating an FPGA/ASIC solution that performs large number of unrelated tasks (such as addition, multiplication, etc) in parallel >3) Creating an FPGA/ASIC solution that allows very high-speed access to data with some PRE-PROCESSING involved to accelerate the calculation >4) Creating a very high-speed storage solution, but no pre-processing. > > >An example to give is for 200,000 rows, imagine: "SELECT A+B, C FROM TableX WHERE C > 0". If there are 200,000 records, the processor has to perform 200,000 additions (A+B). >A CPU will handle these additions one-by-one (or maybe few-by-few if SSE, etc is used). However, an FPGA/ASIC solution can perform 1000 additions in a single-cycle. This results in acceleration. > > >Now, to what extend this can be effective, or what other solutions (maybe string processing?) can be implemented to accelerate the SQL processing is a question to me right now. But we do have experience in >making extremely fast processors (our Butterfly Labs Monarch chip performs 400 billion double-SHA256 hashes in 1 second, around 20,000 times faster than the best intel XEON processor can do only twenty >million hashes per second). > > >The project is in brain-storming phase, and we are aware that this solution will be useful to large enterprises, or companies that have to deal with 200,000 rows in a single "SELECT" query. The idea is to integrate >some features in hardware, and then re-write a portion of MariaDB to take advantage of the new resources. Also, it is possible that users/companies will need to re-write their query to make it compliant. > > >You can reach my by mail at cdmcsd(a)yahoo.com or by phone at +33 6 72 17 26 19 (France). > > > > > > >Best Regards, >Nasser GHOSEIRI > > > > > > > > >_______________________________________________ >Mailing list: https://launchpad.net/~maria-developers >Post to : maria-developers(a)lists.launchpad.net >Unsubscribe : https://launchpad.net/~maria-developers >More help : https://help.launchpad.net/ListHelp > >

1 0

[Maria-developers] Please review: MDEV-5528 Command line variable to choose MariaDB-5.3 vs MySQL-5.6 temporal data formats
by Alexander Barkov 24 Oct '14

24 Oct '14

Hi, Please review my patch for MDEV-5528. It also fixes a bug: MDEV-6649 Different warnings for TIME and TIME(N) when @@old_mode=zero_date_time_cast Thanks.

2 8

[Maria-developers] doubt about mariadb dialog plugin at server and client side
by Roberto Spadim 23 Oct '14

23 Oct '14

Hi guys I'm with a doubt at dialog plugin I created a user at mariadb server: ---SQL INSTALL PLUGIN three_attempts SONAME 'dialog_examples.so'; CREATE USER test_dialog IDENTIFIED VIA three_attempts USING 'SECRET'; FLUSH PRIVILEGES; --- now i'm trying to connect as user test_dialog: ---SHELL $mysql *--plugin-dir=/home/rspadim/mariadb/mariadb-10.0.14/plugin/auth_dialog* --user=test_dialog --host=spd1.spadim.com.br --password Enter password: (i just hit enter) [mariadb] Password, please: (enter again) [mariadb] Password, please: (SECRET enter) Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 49243 Server version: 5.5.5-10.0.14-MariaDB-log Source distribution Copyright (c) 2000, 2012, Oracle, Monty Program Ab and others. Reading history-file /home/beto/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> quit Writing history-file /home/beto/.mysql_history --- ---SHELL $ls /home/rspadim/mariadb/mariadb-10.0.14/plugin/auth_dialog CMakeFiles cmake_install.cmake CMakeLists.txt CTestTestfile.cmake dialog.c *dialog.so* Makefile --- ---MYSQL.USER table || *Host* || *User* || *Password* || *Select_priv* || *Insert_priv* || *Update_priv* || *Delete_priv* || *Create_priv* || *Drop_priv* || *Reload_priv* || *Shutdown_priv* || *Process_priv* || *File_priv* || *Grant_priv* || *References_priv* || *Index_priv* || *Alter_priv* || *Show_db_priv* || *Super_priv* || *Create_tmp_table_priv* || *Lock_tables_priv* || *Execute_priv* || *Repl_slave_priv* || *Repl_client_priv* || *Create_view_priv* || *Show_view_priv* || *Create_routine_priv* || *Alter_routine_priv* || *Create_user_priv* || *Event_priv* || *Trigger_priv* || *Create_tablespace_priv* || *ssl_type* || *ssl_cipher* || *x509_issuer* || *x509_subject* || *max_questions* || *max_updates* || *max_connections* || *max_user_connections* || *plugin* || *auth_string* || || % || test_dialog || >>>CHECK NO PASSWORD!<<< || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || N || || || || || 0 || 0 || 0 || 0 || three_attempts || SECRET || --- the doubts: 1) why it accept my login with password? i *don't* have a password at mysql.user schema 2) why i didn't received a dialog like in heidisql? 3) there's a auth trace or auth verbose option at client side? or any other command to check if i loaded the dialog plugin? -- Roberto Spadim

2 3

[Maria-developers] SQL Accelerator FPGA/ASIC
by Nasser Ghoseiri 23 Oct '14

23 Oct '14

Dear Serg, Following our little chat in IRC, I'm writing this email to explain in more detail what the idea is. My name is Nasser GHOSEIR (Founder, CTO of Butterfly labs), which I must note that this project is not from Butterfly Labs, but will be a new company in Europe. Our idea is to find a way to accelerate SQL query processing by either: 1) Creating an FPGA solution (Which will later evolve into ASIC), that has like 400 processors in itself, allow distribute calculation of some kind 2) Creating an FPGA/ASIC solution that performs large number of unrelated tasks (such as addition, multiplication, etc) in parallel 3) Creating an FPGA/ASIC solution that allows very high-speed access to data with some PRE-PROCESSING involved to accelerate the calculation 4) Creating a very high-speed storage solution, but no pre-processing. An example to give is for 200,000 rows, imagine: "SELECT A+B, C FROM TableX WHERE C > 0". If there are 200,000 records, the processor has to perform 200,000 additions (A+B). A CPU will handle these additions one-by-one (or maybe few-by-few if SSE, etc is used). However, an FPGA/ASIC solution can perform 1000 additions in a single-cycle. This results in acceleration. Now, to what extend this can be effective, or what other solutions (maybe string processing?) can be implemented to accelerate the SQL processing is a question to me right now. But we do have experience in making extremely fast processors (our Butterfly Labs Monarch chip performs 400 billion double-SHA256 hashes in 1 second, around 20,000 times faster than the best intel XEON processor can do only twenty million hashes per second). The project is in brain-storming phase, and we are aware that this solution will be useful to large enterprises, or companies that have to deal with 200,000 rows in a single "SELECT" query. The idea is to integrate some features in hardware, and then re-write a portion of MariaDB to take advantage of the new resources. Also, it is possible that users/companies will need to re-write their query to make it compliant. You can reach my by mail at cdmcsd(a)yahoo.com or by phone at +33 6 72 17 26 19 (France). Best Regards, Nasser GHOSEIRI

1 0

[Maria-developers] COMPOUND STATEMENT fails on Windows.
by Peter Laursen 22 Oct '14

22 Oct '14

I tried to comment on this Blog https://blog.mariadb.org/mariadb-10-1-1-compound-statements/, but it won't let me use my (corporate) gmail address for authencticaton. So I send this mail instead: I tried the example provided on Windows (both server and client are Win7 64 bit) using the command line client shipped with MariaDB 10.1: Enter password: ******** Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.1.1-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use test; Database changed MariaDB [test]> IF @have_csv = 'YES' THEN -> CREATE TABLE IF NOT EXISTS general_log ( -> event_time TIMESTAMP(6) NOT NULL, -> user_host MEDIUMTEXT NOT NULL, -> thread_id BIGINT(21) UNSIGNED NOT NULL, -> server_id INTEGER UNSIGNED NOT NULL, -> command_type VARCHAR(64) NOT NULL, -> argument MEDIUMTEXT NOT NULL -> ) engine=CSV CHARACTER SET utf8 comment="General log"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' a t line 9 MariaDB [test]> END IF; Obviously the SEMICOLON after comment="General log" disturbs. But this is no better: MariaDB [test]> IF @have_csv = 'YES' THEN -> CREATE TABLE IF NOT EXISTS general_log ( -> event_time TIMESTAMP(6) NOT NULL, -> user_host MEDIUMTEXT NOT NULL, -> thread_id BIGINT(21) UNSIGNED NOT NULL, -> server_id INTEGER UNSIGNED NOT NULL, -> command_type VARCHAR(64) NOT NULL, -> argument MEDIUMTEXT NOT NULL -> ) engine=CSV CHARACTER SET utf8 comment="General log" -> END IF; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'END IF' at line 10 MariaDB [test]> What is the problem here? Is it someWindows-specific bug/issue? BTW it is the same in SQLyog (compiled with MariaDB C-API) as in commandline. -- Peter -- Webyog

2 2

Re: [Maria-developers] [Commits] 28fcccc: MDEV-5231: Per query variables from Percona Server (rewritten)
by Sergei Golubchik 22 Oct '14

22 Oct '14

Hi, Sanja! On Oct 13, sanja(a)mariadb.com wrote: > revision-id: 28fccccad32fa8a47dc572b968613e18ff51b634 > parent(s): fec5ab5a56cb9a45c621207620cc85079cddf537 > committer: Oleksandr Byelkin > branch nick: work-maria-10.1-sysvar2 > timestamp: 2014-10-13 22:35:40 +0200 > message: > > MDEV-5231: Per query variables from Percona Server (rewritten) This looks a lot better that the previous patch. Thanks! See my comments below. Some parts of the patch are not used (left-overs from the old implementation?). Some others, I think, are not needed. And some of the old tests don't make much sense, your new tests made them redundant and useless. A couple of comments after reading my previous review: * please add a test for any sysvar with non-trivial implementations of sys_var::session_update(), that is, for a sysvar where old approach doesn't work. Unless, of course, your timestamp tests are exactly that :) * add tests with @@default_engine. For example: SET @@default_engine=MyISAM; SET STATEMENT @@default_engine=MEMORY CREATE TABLE t1 (a int); SHOW CREATE TABLE t1; -- verify the engine SET STATEMENT @@default_engine=MyISAM CREATE TABLE t2 (a int); etc. See other comments below: > diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result > new file mode 100644 > index 0000000..fed937e > --- /dev/null > +++ b/mysql-test/r/set_statement.result > @@ -0,0 +1,941 @@ > +'# SET STATEMENT ..... FOR .... TEST' > +DROP TABLE IF EXISTS t1; > +DROP FUNCTION IF EXISTS myProc; > +DROP PROCEDURE IF EXISTS p1; > +DROP PROCEDURE IF EXISTS p2; > +DROP PROCEDURE IF EXISTS p3; > +DROP PROCEDURE IF EXISTS p4; > +DROP PROCEDURE IF EXISTS p5; > +DROP TABLE IF EXISTS STATEMENT; > +'# Setup database' > +CREATE TABLE t1 (v1 INT, v2 INT); > +INSERT INTO t1 VALUES (1,2); > +INSERT INTO t1 VALUES (3,4); > +'' > +'#------------------ STATEMENT Test 1 -----------------------#' > +'# Initialize variables to known setting' > +SET SESSION sort_buffer_size=100000; > +'' > +'# Pre-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 100000 > +SET STATEMENT sort_buffer_size=150000 FOR SELECT *,@@sort_buffer_size FROM t1; > +v1 v2 @@sort_buffer_size > +1 2 150000 > +3 4 150000 > +'' > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 100000 > +'' > +'#------------------ STATEMENT Test 2 -----------------------#' > +'# Initialize variables to known setting' > +SET SESSION binlog_format=mixed; > +SET SESSION sort_buffer_size=100000; > +'# Pre-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 100000 > +SHOW SESSION VARIABLES LIKE 'binlog_format'; > +Variable_name Value > +binlog_format MIXED > +SET STATEMENT sort_buffer_size=150000, binlog_format=row > +FOR SELECT *,@@sort_buffer_size,@@binlog_format FROM t1; > +v1 v2 @@sort_buffer_size @@binlog_format > +1 2 150000 ROW > +3 4 150000 ROW > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 100000 > +SHOW SESSION VARIABLES LIKE 'binlog_format'; > +Variable_name Value > +binlog_format MIXED > +'' > +'#------------------ STATEMENT Test 3 -----------------------#' > +'# set initial variable value, make prepared statement > +SET SESSION binlog_format=row; > +PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1'; > +'' > +'# Change variable setting' > +SET SESSION binlog_format=mixed; > +'' > +'# Pre-STATEMENT variable value' > +'' > +SHOW SESSION VARIABLES LIKE 'binlog_format'; > +Variable_name Value > +binlog_format MIXED > +'' > +EXECUTE stmt1; > +v1 v2 @@binlog_format > +1 2 ROW > +3 4 ROW > +'' > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'binlog_format'; > +Variable_name Value > +binlog_format MIXED > +'' > +DEALLOCATE PREPARE stmt1; > +'#------------------ STATEMENT Test 4 -----------------------#' > +'# set initial variable value, make prepared statement > +SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; > +'' > +'# Pre-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; > +Variable_name Value > +myisam_sort_buffer_size 500000 > +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; > +Variable_name Value > +myisam_repair_threads 1 > +'' > +SET STATEMENT myisam_sort_buffer_size=800000, > +myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; 1. this is not "make prepared statement" as the comment says 2. what does it actually test? you don't verify whether new values had any effect. > +Table Op Msg_type Msg_text > +test.t1 optimize status OK > +'' > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; > +Variable_name Value > +myisam_sort_buffer_size 500000 > +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; > +Variable_name Value > +myisam_repair_threads 1 > +'' > +'#------------------ STATEMENT Test 5 -----------------------#' > +'# Initialize variables to known setting' > +SET SESSION sort_buffer_size=100000; > +'' > +'# Pre-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 100000 > +'' > +SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; > +ERROR 42S02: Table 'test.t2' doesn't exist > +'' > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 150000 Looks wrong. The value is not restored in case of an error. > +'' > +'#------------------ STATEMENT Test 6 -----------------------#' > +'# Initialize variables to known setting' > +SET SESSION keep_files_on_create=ON; > +'' > +'# Pre-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; > +Variable_name Value > +keep_files_on_create ON > +'' > +SET STATEMENT keep_files_on_create=OFF FOR SELECT *,@@keep_files_on_create FROM t1; > +v1 v2 @@keep_files_on_create > +1 2 0 > +3 4 0 How is this test different from other tests above? > +'' > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; > +Variable_name Value > +keep_files_on_create ON > +'' > +'#------------------ STATEMENT Test 7 -----------------------#' > +'# Initialize variables to known setting' > +SET SESSION max_join_size=2222220000000; > +'' > +'# Pre-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'max_join_size'; > +Variable_name Value > +max_join_size 2222220000000 > +'' > +SET STATEMENT max_join_size=1000000000000 FOR SELECT *,@@max_join_size FROM t1; > +v1 v2 @@max_join_size > +1 2 1000000000000 > +3 4 1000000000000 Same question - how is this test different from other tests above? > +'' > +'# Post-STATEMENT variable value' > +SHOW SESSION VARIABLES LIKE 'max_join_size'; > +Variable_name Value > +max_join_size 2222220000000 > +'' > +'#------------------Test 8-----------------------#' > +'# Initialize test variables' > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size = 200000, > +max_join_size=2222220000000, > +keep_files_on_create=ON; > +'' > +'# LONG ' > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 200000 > +SET STATEMENT sort_buffer_size = 100000 > +FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 100000 > +SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; > +Variable_name Value > +sort_buffer_size 200000 > +'' > +'# MY_BOOL ' > +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; > +Variable_name Value > +keep_files_on_create ON > +SET STATEMENT keep_files_on_create=OFF > +FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; > +Variable_name Value > +keep_files_on_create OFF > +SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; > +Variable_name Value > +keep_files_on_create ON > +'' > +'# INT/LONG ' > +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; > +Variable_name Value > +myisam_repair_threads 1 > +SET STATEMENT myisam_repair_threads=2 > +FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; > +Variable_name Value > +myisam_repair_threads 2 > +SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; > +Variable_name Value > +myisam_repair_threads 1 > +'' > +'# ULONGLONG ' > +SHOW SESSION VARIABLES LIKE 'max_join_size'; > +Variable_name Value > +max_join_size 2222220000000 > +SET STATEMENT max_join_size=2000000000000 > +FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; > +Variable_name Value > +max_join_size 2000000000000 > +SHOW SESSION VARIABLES LIKE 'max_join_size'; > +Variable_name Value > +max_join_size 2222220000000 > +'' > +'#------------------Test 9-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +CREATE FUNCTION myProc (cost DECIMAL(10,2)) > +RETURNS DECIMAL(10,2) > +SQL SECURITY DEFINER > +tax: BEGIN > +DECLARE order_tax DECIMAL(10,2); > +SET order_tax = cost * .05; > +RETURN order_tax; > +END| > +'' > +'# During Execution values > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +SELECT myProc(123.45); > +myProc(123.45) > +6.17 How does this verify that SET STATEMENT had any effect? > +'' > +'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +DROP FUNCTION myProc; How does this verify that SET STATEMENT had any effect? > +'' > +'# Post-STATEMENT No 2 variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'#------------------Test 10-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +PREPARE stmt2 > +FROM 'SELECT * FROM t1'; > +'' > +'Test No 1 Post Value & Test 2 Pre values' > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +EXECUTE stmt2; > +v1 v2 > +1 2 > +3 4 How does this verify that SET STATEMENT had any effect? Add @@binlog_format and @@sort_buffer_size to the SELECT and use different values for @@sort_buffer_size in SET STATEMENT for PREPARE and EXECUTE. Ok, I see that you do it below. Then how is this test useful? > +'' > +'# Post-STATEMENT No 2 > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +DEALLOCATE PREPARE stmt2; > +'' > +'#------------------Test 11-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +PREPARE stmt1 FROM > +'SET STATEMENT binlog_format=row FOR SELECT *,@@binlog_format FROM t1'; please also add @@sort_buffer_size to this SELECT > +'' > +'Test No 1 Post Value & Test 2 Pre values' > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +EXECUTE stmt1; > +v1 v2 @@binlog_format > +1 2 ROW > +3 4 ROW > +'' > +'# Post-STATEMENT No 2 > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'#------------------Test 12-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +CREATE PROCEDURE p1() BEGIN > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +END| > +'' > +'Test No 1 Post Value & Test 2 Pre values' > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR Use different values here, not same as in the previous SET STATEMENT > +CALL p1(); > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +400000 2 200000 ROW 0 4444440000000 > +'' > +'# Post-STATEMENT No 2 > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'#------------------Test 13-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'' > +CREATE PROCEDURE p2() BEGIN > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=3, > +sort_buffer_size=300000, > +binlog_format=mixed, > +keep_files_on_create=OFF, > +max_join_size=3333330000000 FOR > +CALL p1(); > +END| > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +CALL p2(); > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +400000 3 300000 MIXED 0 3333330000000 > +'' > +'# Post-STATEMENT > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'#------------------Test 14-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'' > +CREATE PROCEDURE p3() BEGIN > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +SET STATEMENT myisam_sort_buffer_size=320000, > +myisam_repair_threads=2, > +sort_buffer_size=220022, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2222220000000 FOR > +CALL p2(); > +END| > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +CALL p3(); > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +400000 2 200000 ROW 0 4444440000000 > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +400000 3 300000 MIXED 0 3333330000000 > +'' > +'# Post-STATEMENT > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'' > +'' > +'#------------------Test 15-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'' > +CREATE PROCEDURE p4() BEGIN > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +SET STATEMENT myisam_sort_buffer_size=320000, > +myisam_repair_threads=2, > +sort_buffer_size=220022, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2222220000000 FOR > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +SET STATEMENT myisam_sort_buffer_size=320000, > +myisam_repair_threads=2, > +sort_buffer_size=220022, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2222220000000 FOR > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +SET STATEMENT myisam_sort_buffer_size=320000, > +myisam_repair_threads=2, > +sort_buffer_size=220022, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2222220000000 FOR > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +END| > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +CALL p4(); > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +400000 2 200000 ROW 0 4444440000000 > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +320000 2 220022 ROW 1 2222220000000 > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +320000 2 220022 ROW 1 2222220000000 > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +320000 2 220022 ROW 1 2222220000000 > +'' > +'# Post-STATEMENT > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'#------------------Test 16-----------------------#' > +'' > +'# Pre-STATEMENT variable value > +SELECT @@sql_mode; > +@@sql_mode > + > +'' > +'' > +SET STATEMENT sql_mode='ansi' FOR PREPARE stmt FROM 'SELECT "t1".* FROM t1'; > +execute stmt; > +v1 v2 > +1 2 > +3 4 Ok, that's getting interesting. Now before deallocating, try to trigger automatic reprepare. e.g. ALTER TABLE t1 ADD COLUMN v3 int; and then execute it again. also do the same with SP. I mean, set statement sql_mode=ansi create procedure this procedure uses ansi quotes. verify that the correct sql mode (ansi) is recorded in mysql.proc table. > +deallocate prepare stmt; > +'' > +'# Post-STATEMENT > +SELECT @@sql_mode; > +@@sql_mode > + > +'' > +'' > +'#------------------Test 17-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=320000, > +myisam_repair_threads=2, > +sort_buffer_size=220022, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2222220000000 > +FOR SET SESSION > +myisam_sort_buffer_size=260000, > +myisam_repair_threads=3, > +sort_buffer_size=230013, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2323230000000; Add a couple of other variables to SET SESSION. The point - have some variables in SET SESSION that are *not* present in SET STATEMENT. Verify that their values were correctly set. Ok, I see that you do it below. Then how is this test useful? > +'' > +'# Post-STATEMENT > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'#------------------Test 18-----------------------#' > +'# set initial variable values > +SET SESSION myisam_sort_buffer_size=500000, > +myisam_repair_threads=1, > +sort_buffer_size=100000, > +binlog_format=mixed, > +keep_files_on_create=ON, > +max_join_size=2222220000000; > +'' > +'# Pre-STATEMENT variable value > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +CREATE PROCEDURE p5() BEGIN > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +SET SESSION > +myisam_sort_buffer_size=260000, > +myisam_repair_threads=3, > +sort_buffer_size=230013, > +binlog_format=row, > +keep_files_on_create=ON, > +max_join_size=2323230000000; > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +END| How is this test different from other tests above? > +'' > +'' > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=2, > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +CALL p5(); > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +400000 2 200000 ROW 0 4444440000000 > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +260000 3 230013 ROW 1 2323230000000 > +'' > +'# Post-STATEMENT > +SELECT @@myisam_sort_buffer_size, > +@@myisam_repair_threads, > +@@sort_buffer_size, > +@@binlog_format, > +@@keep_files_on_create, > +@@max_join_size; > +@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size > +500000 1 100000 MIXED 1 2222220000000 > +'' > +'' > +'#------------------Test 19-----------------------#' > +SET STATEMENT max_error_count=100 FOR; > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 > +SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1 > +SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 > +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 > +SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); Same erroneous statement tested twice? > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 > +SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); > +ERROR HY000: Unknown system variable 'GLOBAL' > +SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); > +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1 > +'' > +'' > +'#------------------Test 20-----------------------#' > +SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); > +ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL > +'' > +'' > +'#------------------Test 21-----------------------#' > +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; > +@@myisam_sort_buffer_size @@sort_buffer_size > +500000 100000 > +SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 > +FOR SET STATEMENT myisam_sort_buffer_size=200000 > +FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; > +@@myisam_sort_buffer_size @@sort_buffer_size > +200000 100000 > +SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; > +@@myisam_sort_buffer_size @@sort_buffer_size > +500000 100000 > +'' > +'' > +'#------------------Test 22-----------------------#' > +CREATE TABLE STATEMENT(a INT); > +DROP TABLE STATEMENT; > +'' > +'# Cleanup' > +DROP TABLE t1; > +DROP PROCEDURE p1; > +DROP PROCEDURE p2; > +DROP PROCEDURE p3; > +DROP PROCEDURE p4; > +DROP PROCEDURE p5; > +CREATE TABLE t1 (v1 INT, v2 INT); > +PREPARE stmt > +FROM 'SELECT * FROM t1'; > +SET STATEMENT myisam_sort_buffer_size=400000, > +myisam_repair_threads=(select max(v1) from t1), > +sort_buffer_size=200000, > +binlog_format=row, > +keep_files_on_create=OFF, > +max_join_size=4444440000000 FOR > +EXECUTE stmt; > +ERROR HY000: SET STATEMENT does not support using tables in the assignment variables expressions Why do you need a prepared statement to test that error? > +deallocate prepare stmt; > +drop table t1; > +set session binlog_format=mixed; > +PREPARE stmt1 FROM 'SELECT @@binlog_format'; > +execute stmt1; > +@@binlog_format > +MIXED > +set statement binlog_format=row for execute stmt1; > +@@binlog_format > +ROW > +execute stmt1; > +@@binlog_format > +MIXED > +deallocate prepare stmt1; > +set statement binlog_format=row for PREPARE stmt1 FROM 'SELECT @@binlog_format'; > +execute stmt1; > +@@binlog_format > +MIXED > +execute stmt1; > +@@binlog_format > +MIXED > +deallocate prepare stmt1; > +PREPARE stmt1 FROM 'set statement binlog_format=row for SELECT @@binlog_format'; > +execute stmt1; > +@@binlog_format > +ROW > +execute stmt1; > +@@binlog_format > +ROW > +deallocate prepare stmt1; > +set session binlog_format=default; > +set session binlog_format=mixed; > +SET STATEMENT sort_buffer_size=150000 FOR set session binlog_format=row; > +SELECT @@binlog_format; > +@@binlog_format > +ROW those are good tests, and they make many of the tests above unnecessary > +#Correctly set timestamp > +set session timestamp=4646464; > +select @@timestamp != 4646464; > +@@timestamp != 4646464 > +0 > +select @@timestamp != 4646464; > +@@timestamp != 4646464 > +0 > +#Correctly returned normal behaviour > +set session timestamp=default; > +select @@timestamp != 4646464; > +@@timestamp != 4646464 > +1 > +select @@timestamp != 4646464; > +@@timestamp != 4646464 > +1 > +#here timestamp should be set only for the statement then restored default > +set statement timestamp=4646464 for select @@timestamp; > +@@timestamp > +4646464.000000 > +set @save_tm=@@timestamp; > +select @@timestamp != 4646464; > +@@timestamp != 4646464 > +1 > +select @@timestamp != 4646464; > +@@timestamp != 4646464 > +1 > +select @@timestamp != @save_tm; > +@@timestamp != @save_tm > +1 > diff --git a/sql/set_var.cc b/sql/set_var.cc > index bae6511..20e3040 100644 > --- a/sql/set_var.cc > +++ b/sql/set_var.cc > @@ -147,7 +147,7 @@ void sys_var_end() > flags(flags_arg), show_val_type(show_val_type_arg), > guard(lock), offset(off), on_check(on_check_func), on_update(on_update_func), > deprecation_substitute(substitute), > - is_os_charset(FALSE) > + is_os_charset(FALSE), default_val(TRUE) I've asked below why is that needed. > { > /* > There is a limitation in handle_options() related to short options: > @@ -695,7 +695,8 @@ int sql_set_variables(THD *thd, List<set_var_base> *var_list) > } > > err: > - free_underlaid_joins(thd, &thd->lex->select_lex); > + if (free) > + free_underlaid_joins(thd, &thd->lex->select_lex); Ok, so in normal SET you want to free_underlaid_joins, and in SET STATEMENT you don't. Right? > DBUG_RETURN(error); > } > > diff --git a/sql/item_func.cc b/sql/item_func.cc > index 2b89aa0..651e0b1 100644 > --- a/sql/item_func.cc > +++ b/sql/item_func.cc > @@ -5556,7 +5556,7 @@ longlong Item_func_get_user_var::val_int() > tmp_var_list.push_back(new set_var_user(new Item_func_set_user_var(name, > new Item_null()))); > /* Create the variable */ > - if (sql_set_variables(thd, &tmp_var_list)) > + if (sql_set_variables(thd, &tmp_var_list, true)) Make if 'false', not 'true' here. It doesn't make any practical difference (there are no joins, the variable is set to Item_null, explicitly), but makes the logic a bit clearer (standalone SET statement = free joins, a SET that is part of another statement = don't free joins). Alternatively (and better) approach would be to remove sql_set_variables() here and use the same code that Item_func_set_user_var does. And then sql_set_variables() will be only used in SET variants, where a sysvar can be used. Here sysvars aren't possible, so sql_set_variables() is misleading. > { > thd->lex= sav_lex; > goto err; > diff --git a/sql/set_var.h b/sql/set_var.h > index e48f394..9ee914b 100644 > --- a/sql/set_var.h > +++ b/sql/set_var.h > @@ -118,6 +119,9 @@ class sys_var > */ > bool set_default(THD *thd, set_var *var); > bool update(THD *thd, set_var *var); > + void stmt_update(THD *thd) { > + on_update && on_update(this, thd, OPT_SESSION); > + } Doesn't seem to be used anywhere in the patch > > String *val_str_nolock(String *str, THD *thd, const uchar *value); > longlong val_int(bool *is_null, THD *thd, enum_var_type type, const LEX_STRING *base); > diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt > index 233bb83..4072af4 100644 > --- a/sql/share/errmsg-utf8.txt > +++ b/sql/share/errmsg-utf8.txt > @@ -7110,3 +7110,5 @@ ER_SLAVE_SKIP_NOT_IN_GTID > eng "When using GTID, @@sql_slave_skip_counter can not be used. Instead, setting @@gtid_slave_pos explicitly can be used to skip to after a given GTID position." > ER_STATEMENT_TIMEOUT 70100 > eng "Query execution was interrupted (max_statement_time exceeded)" > +ER_SET_STATEMENT_TABLES_IS_NOT_SUPPORTED > + eng "SET STATEMENT does not support using tables in the assignment variables expressions" Better write it as ER_SUBQUERIES_NOT_SUPPORTED eng "%s does not support subqueries or stored functions." and use it for SET STATEMENT: my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "SET STATEMENT"); and also use it for KILL: - my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored " - "function calls as part of this statement"); + my_error(ER_SUBQUERIES_NOT_SUPPORTED, MYF(0), "KILL"); > diff --git a/sql/sql_class.h b/sql/sql_class.h > index d7bbfc3..0f6b39e 100644 > --- a/sql/sql_class.h > +++ b/sql/sql_class.h > @@ -3711,6 +3711,10 @@ class THD :public Statement, > void rgi_unlock_temporary_tables(); > bool rgi_have_temporary_tables(); > public: > + inline MEM_ROOT *get_execution_mem_root() > + { > + return &main_mem_root; > + } Doesn't seem to be used anywhere in this patch > /* > Flag, mutex and condition for a thread to wait for a signal from another > thread. > diff --git a/sql/sql_lex.h b/sql/sql_lex.h > index 5e9c7b9..03861d1 100644 > --- a/sql/sql_lex.h > +++ b/sql/sql_lex.h > @@ -2379,6 +2381,8 @@ struct LEX: public Query_tables_list > required a local context, the parser pops the top-most context. > */ > List<Name_resolution_context> context_stack; > + /* true if SET STATEMENT ... FOR ... statement is use, false otherwise */ > + bool set_statement; This doesn't seem to be needed, I've checked all places where set_statement is used below. > > SQL_I_List<ORDER> proc_list; > SQL_I_List<TABLE_LIST> auxiliary_table_list, save_list; > diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc > index d8906b2..eff0e08 100644 > --- a/sql/sql_parse.cc > +++ b/sql/sql_parse.cc > @@ -2637,6 +2637,98 @@ static bool do_execute_sp(THD *thd, sp_head *sp) > thd->get_binlog_format(&orig_binlog_format, > &orig_current_stmt_binlog_format); > > + if (lex->set_statement && !lex->stmt_var_list.is_empty()) Why do you check lex->set_statement? I'd think that if (!lex->stmt_var_list.is_empty()) is quite enough. > + { > + DBUG_PRINT("info", ("SET STATEMENT %d vars", lex->stmt_var_list.elements)); > + > + lex->old_var_list.empty(); > + List_iterator_fast<set_var_base> it(lex->stmt_var_list); > + set_var_base *var; > + while ((var=it++)) > + { > + DBUG_ASSERT(var->is_system()); > + set_var *o= NULL, *v= (set_var*)var; > + if (v->var->is_default()) > + o= new set_var(v->type, v->var, &v->base, NULL); Why do you treat default values differently? > + else > + { > + switch (v->var->option.var_type) > + { > + case GET_BOOL: > + case GET_INT: > + case GET_LONG: > + case GET_LL: > + { > + bool null_value; > + longlong val= v->var->val_int(&null_value, thd, v->type, &v->base); > + o= new set_var(v->type, v->var, &v->base, > + (null_value ? > + (Item *)new Item_null() : > + (Item *)new Item_int(val))); > + } > + break; > + case GET_UINT: > + case GET_ULONG: > + case GET_ULL: > + { > + bool null_value; > + ulonglong val= v->var->val_int(&null_value, thd, v->type, &v->base); > + o= new set_var(v->type, v->var, &v->base, > + (null_value ? > + (Item *)new Item_null() : > + (Item *)new Item_uint(val))); > + } > + break; > + case GET_DOUBLE: > + { > + bool null_value; > + double val= v->var->val_real(&null_value, thd, v->type, &v->base); > + o= new set_var(v->type, v->var, &v->base, > + (null_value ? > + (Item *)new Item_null() : > + (Item *)new Item_float(val, 1))); > + } > + break; > + default: > + case GET_NO_ARG: > + case GET_DISABLED: > + DBUG_ASSERT(0); > + case 0: > + case GET_FLAGSET: > + case GET_ASK_ADDR: > + case GET_TYPE_MASK: > + case GET_ENUM: > + case GET_SET: > + case GET_STR: > + case GET_STR_ALLOC: > + { > + char buff[STRING_BUFFER_USUAL_SIZE]; > + String tmp(buff, sizeof(buff), v->var->charset(thd)),*val; > + val= v->var->val_str(&tmp, thd, v->type, &v->base); > + if (val) > + { > + Item_string *str= new Item_string(v->var->charset(thd)); > + str->set_str_with_copy(val->ptr(), val->length()); > + o= new set_var(v->type, v->var, &v->base, str); > + } > + else > + o= new set_var(v->type, v->var, &v->base, new Item_null()); > + } > + break; > + } > + } > + DBUG_ASSERT(o); > + lex->old_var_list.push_back(o); > + } > + if (thd->is_error() || > + (res= sql_set_variables(thd, &lex->stmt_var_list, false))) > + { > + if (!thd->is_error()) > + my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET"); > + goto error; > + } > + } > + > /* > Force statement logging for DDL commands to allow us to update > privilege, system or statistic tables directly without the updates > @@ -2812,6 +2904,12 @@ static bool do_execute_sp(THD *thd, sp_head *sp) > } > case SQLCOM_EXECUTE: > { > + /* > + Clean up free_list here from Items used in setting statement variables > + to present clear item list to Prepared_statement::execute_loop which > + need it empty because switch arena (and so item list) on statement arena. > + */ > + //free_items(thd->free_list); thd->free_list= NULL; Eh? Forgot to remove it? > mysql_sql_stmt_execute(thd); > break; > } > diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc > index 9ae3d79..4b54989 100644 > --- a/sql/sql_plugin.cc > +++ b/sql/sql_plugin.cc > @@ -4078,3 +4078,48 @@ static void restore_ptr_backup(uint n, st_ptr_backup *backup) > (backup++)->restore(); > } > > + > +/** > + Create deep copy of system_variables instance. > +*/ > +struct system_variables * > +copy_system_variables(const struct system_variables *src) Doesn't seem to be used anywhere in the patch > +{ > + struct system_variables *dst; > + > + DBUG_ASSERT(src); > + > + dst= (struct system_variables *) > + sql_alloc(sizeof(struct system_variables)); > + if (!dst) > + return NULL; > + *dst= *src; > + > + if (dst->dynamic_variables_ptr) > + { > + if (!(dst->dynamic_variables_ptr= > + (char *)my_malloc(dst->dynamic_variables_size, MYF(MY_WME | MY_FAE)))) > + return NULL; > + memcpy(dst->dynamic_variables_ptr, > + src->dynamic_variables_ptr, > + src->dynamic_variables_size); > + } > + > + mysql_mutex_lock(&LOCK_plugin); > + dst->table_plugin= > + intern_plugin_lock(NULL, src->table_plugin); > + mysql_mutex_unlock(&LOCK_plugin); > + > + return dst; > +} > + > +void free_system_variables(struct system_variables *v) Doesn't seem to be used anywhere in the patch > +{ > + DBUG_ASSERT(v); > + > + mysql_mutex_lock(&LOCK_plugin); > + intern_plugin_unlock(NULL, v->table_plugin); > + mysql_mutex_unlock(&LOCK_plugin); > + > + my_free(v->dynamic_variables_ptr); > +} > diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy > index e7fcdfb..d140597 100644 > --- a/sql/sql_yacc.yy > +++ b/sql/sql_yacc.yy > @@ -14416,8 +14420,52 @@ set: > } > start_option_value_list > {} > + | SET STATEMENT_SYM > + { > + LEX *lex= Lex; > + mysql_init_select(lex); > + lex->sql_command= SQLCOM_SET_OPTION; > + /* Don't clear var_list in the case of recursive statement */ What's "recursive set statement"? SET STATEMENT a=1 FOR SET STATEMENT ? I'd simply return a syntax error here. Like MYSQL_YYABORT_UNLESS(lex->stmt_var_list.is_empty()); > + if (!lex->set_statement) > + { > + lex->var_list.empty(); > + lex->stmt_var_list.empty(); Why here, and not in lex_start() ? > + } > + lex->autocommit= 0; > + lex->set_statement= true; > + sp_head *sp= lex->sphead; > + if (sp && !sp->is_invoked()) > + { > + sp->m_param_begin= ((yychar == YYEMPTY) ? YYLIP->get_ptr() : YYLIP->get_tok_start()); > + sp->m_param_end= ((yychar == YYEMPTY) ? YYLIP->get_ptr() : YYLIP->get_tok_end()); what's that? > + } > + } > + set_stmt_option_value_following_option_type_list > + { > + LEX *lex= Lex; > + if (lex->query_tables) > + { > + my_error(ER_SET_STATEMENT_TABLES_IS_NOT_SUPPORTED, MYF(0)); why? what about stored functions that use tables internally? > + MYSQL_YYABORT; > + } > + //if (lex->set_statement && lex->stmt_var_list.is_empty()) > + { > + lex->stmt_var_list= lex->var_list; > + lex->var_list.empty(); > + } > + } > + FOR_SYM statement > + {} > ; > > +set_stmt_option_value_following_option_type_list: > + /* > + Only system variables can be used here. If this condition is changed > + please check careful code under lex->option_type == OPT_STATEMENT > + condition on wrong type casts. > + */ > + option_value_following_option_type > + | set_stmt_option_value_following_option_type_list ',' option_value_following_option_type > > // Start of option value list > start_option_value_list: > @@ -14522,6 +14570,14 @@ option_value_following_option_type: > { > LEX *lex= Lex; > > + /* > + Ignore SET STATEMENT variables list on slaves because system > + variables are not replicated except certain variables set the > + values of whose are written to binlog event header and nothing > + additional is required to set them. > + */ > + if (!(thd->slave_thread && lex->set_statement)) > + { Hmm, okay. Alternatively, you can simply make sure that set statement is not written into binlog. Or don't execute assignments in the slave thread. > if ($1.var && $1.var != trg_new_row_fake_var) > { > /* It is a system variable. */ Regards, Sergei

2 3