- developers - lists.mariadb.org

[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

Re: [Maria-developers] [Commits] Rev 4445: MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read() in file:///home/psergey/dev2/10.0/
by Jeremy Cole 20 Oct '14

20 Oct '14

Sergey, Nice test case. Would you mind cleaning up the end-space in your patch? Also, is there a reason to put the entire body of Mrr_ordered_index_reader::resume_read in the if() instead of an early return? Regards, Jeremy On Thu, Oct 16, 2014 at 11:58 AM, Sergey Petrunya <psergey(a)askmonty.org> wrote: > At file:///home/psergey/dev2/10.0/ > > ------------------------------------------------------------ > revno: 4445 > revision-id: psergey(a)askmonty.org-20141016135713-md92qr4g9fzhitxh > parent: bar(a)mnogosearch.org-20141013083155-j3j31hqhfc8i95y1 > committer: Sergey Petrunya <psergey(a)askmonty.org> > branch nick: 10.0 > timestamp: Thu 2014-10-16 17:57:13 +0400 > message: > MDEV-6878: Use of uninitialized saved_primary_key in > Mrr_ordered_index_reader::resume_read() > > - Make Mrr_ordered_index_reader::resume_read() restore index position > only if it was saved before with > Mrr_ordered_index_reader::interrupt_read(). > === modified file 'mysql-test/r/innodb_mrr_cpk.result' > --- a/mysql-test/r/innodb_mrr_cpk.result 2013-09-26 18:20:15 +0000 > +++ b/mysql-test/r/innodb_mrr_cpk.result 2014-10-16 13:57:13 +0000 > @@ -194,3 +194,41 @@ id select_type table type possible_keys > 2 DERIVED t2 ALL NULL NULL NULL NULL # > set join_cache_level= @tmp_mdev5037; > drop table t0,t1,t2; > +# > +# MDEV-6878: Use of uninitialized saved_primary_key in > Mrr_ordered_index_reader::resume_read() > +# > +create table t0(a int); > +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > +create table t1 ( > +pk varchar(32) character set utf8 primary key, > +kp1 char(32) not null, > +col1 varchar(32), > +key (kp1) > +) engine=innodb; > +insert into t1 > +select > +concat('pk-', 1000 +A.a), > +concat('kp1-', 1000 +A.a), > +concat('val-', 1000 +A.a) > +from test.t0 A ; > +create table t2 as select kp1 as a from t1; > +set join_cache_level=8; > +set optimizer_switch='mrr=on,mrr_sort_keys=on'; > +explain > +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; > +id select_type table type possible_keys key key_len > ref rows Extra > +1 SIMPLE t2 ALL NULL NULL NULL NULL 10 > +1 SIMPLE t1 ref kp1 kp1 32 test.t2.a 1 > Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan > +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; > +a pk kp1 col1 > +kp1-1000 pk-1000 kp1-1000 val-1000 > +kp1-1001 pk-1001 kp1-1001 val-1001 > +kp1-1002 pk-1002 kp1-1002 val-1002 > +kp1-1003 pk-1003 kp1-1003 val-1003 > +kp1-1004 pk-1004 kp1-1004 val-1004 > +kp1-1005 pk-1005 kp1-1005 val-1005 > +kp1-1006 pk-1006 kp1-1006 val-1006 > +kp1-1007 pk-1007 kp1-1007 val-1007 > +kp1-1008 pk-1008 kp1-1008 val-1008 > +kp1-1009 pk-1009 kp1-1009 val-1009 > +drop table t0,t1,t2; > > === modified file 'mysql-test/t/innodb_mrr_cpk.test' > --- a/mysql-test/t/innodb_mrr_cpk.test 2013-09-26 18:20:15 +0000 > +++ b/mysql-test/t/innodb_mrr_cpk.test 2014-10-16 13:57:13 +0000 > @@ -192,3 +192,35 @@ explain SELECT 1 FROM (SELECT url, id FR > set join_cache_level= @tmp_mdev5037; > > drop table t0,t1,t2; > + > +--echo # > +--echo # MDEV-6878: Use of uninitialized saved_primary_key in > Mrr_ordered_index_reader::resume_read() > +--echo # > +create table t0(a int); > +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); > + > +create table t1 ( > + pk varchar(32) character set utf8 primary key, > + kp1 char(32) not null, > + col1 varchar(32), > + key (kp1) > +) engine=innodb; > + > +insert into t1 > +select > + concat('pk-', 1000 +A.a), > + concat('kp1-', 1000 +A.a), > + concat('val-', 1000 +A.a) > +from test.t0 A ; > + > +create table t2 as select kp1 as a from t1; > + > +set join_cache_level=8; > +set optimizer_switch='mrr=on,mrr_sort_keys=on'; > +explain > +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; > +select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; > + > +drop table t0,t1,t2; > + > + > > === modified file 'sql/multi_range_read.cc' > --- a/sql/multi_range_read.cc 2014-06-06 17:28:42 +0000 > +++ b/sql/multi_range_read.cc 2014-10-16 13:57:13 +0000 > @@ -426,6 +426,7 @@ bool Mrr_ordered_index_reader::set_inter > *space_start += key_len; > > have_saved_rowid= FALSE; > + read_was_interrupted= FALSE; > return FALSE; > } > > @@ -434,6 +435,7 @@ void Mrr_ordered_index_reader::set_no_in > support_scan_interruptions= FALSE; > saved_key_tuple= saved_rowid= saved_primary_key= NULL; /* safety */ > have_saved_rowid= FALSE; > + read_was_interrupted= FALSE; > } > > void Mrr_ordered_index_reader::interrupt_read() > @@ -451,6 +453,7 @@ void Mrr_ordered_index_reader::interrupt > &table->key_info[table->s->primary_key], > table->key_info[table->s->primary_key].key_length); > } > + read_was_interrupted= TRUE; > > /* Save the last rowid */ > memcpy(saved_rowid, file->ref, file->ref_length); > @@ -468,14 +471,17 @@ void Mrr_ordered_index_reader::position( > void Mrr_ordered_index_reader::resume_read() > { > TABLE *table= file->get_table(); > - KEY *used_index= &table->key_info[file->active_index]; > - key_restore(table->record[0], saved_key_tuple, > - used_index, used_index->key_length); > - if (saved_primary_key) > + if (read_was_interrupted) > { > - key_restore(table->record[0], saved_primary_key, > - &table->key_info[table->s->primary_key], > - table->key_info[table->s->primary_key].key_length); > + KEY *used_index= &table->key_info[file->active_index]; > + key_restore(table->record[0], saved_key_tuple, > + used_index, used_index->key_length); > + if (saved_primary_key) > + { > + key_restore(table->record[0], saved_primary_key, > + &table->key_info[table->s->primary_key], > + table->key_info[table->s->primary_key].key_length); > + } > } > } > > @@ -557,8 +563,7 @@ int Mrr_ordered_index_reader::init(handl > is_mrr_assoc= !MY_TEST(mode & HA_MRR_NO_ASSOCIATION); > mrr_funcs= *seq_funcs; > source_exhausted= FALSE; > - if (support_scan_interruptions) > - bzero(saved_key_tuple, key_info->key_length); > + read_was_interrupted= false; > have_saved_rowid= FALSE; > return 0; > } > > === modified file 'sql/multi_range_read.h' > --- a/sql/multi_range_read.h 2013-09-26 18:20:15 +0000 > +++ b/sql/multi_range_read.h 2014-10-16 13:57:13 +0000 > @@ -339,6 +339,12 @@ class Mrr_ordered_index_reader : public > > uchar *saved_key_tuple; /* Saved current key tuple */ > uchar *saved_primary_key; /* Saved current primary key tuple */ > + > + /* > + TRUE<=> saved_key_tuple (and saved_primary_key when applicable) have > + valid values. > + */ > + bool read_was_interrupted; > > static int compare_keys(void* arg, uchar* key1, uchar* key2); > static int compare_keys_reverse(void* arg, uchar* key1, uchar* key2); > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits >

2 1

Re: [Maria-developers] MariaDB 10.0.14 got rid of replication safety warning without warning in release notes?
by Pavel Ivanov 20 Oct '14

20 Oct '14

> Pavel> Which key is "the first duplicate key". > > The first key that has a duplicate value in the order of keys in the KEY > structure that MariaDB provides for the storage engine. I'm definitely not that familiar with the relevant MariaDB code, but I didn't find KEY structure to be passed to any insert-related function. Can you elaborate (preferably with code references) how this restriction is supposed to work? > This is the same order that keys are presented in SHOW CREATE TABLE Why is it safe to assume that keys have the same order on master and on slave? > To help engine writes we are constantly adding new test in the storage > engine test suite, to make it easier for storage engine vendors to > test that their storage engine follows the interface. > We have now added a test to check for the correct behaviour for the > case of INSERT ... ON DUPLICATE KEY. This is very interesting. Can you say which test it is and what exactly does it test? Is it present in 10.0.14 or it's only at the latest 10.0 branch? > Do you have or use a storage engine that doesn't provide the expected value > for the conflicting key ? I don't have a specific example of storage engine that detects duplicate key in different order, but I'm not convinced and don't have hard proof that such engine cannot exist and that InnoDB itself is not susceptible to this problem. I can easily imagine though that even given the particular order of keys in table definition the key reported as having duplicate can depend on the way storage engine stores those keys. BTW, can you point to MySQL documentation and/or comment in the code that indicates this requirement to storage engine implementation? > Pavel> On the related note Release Notes mention the error log flood > Pavel> protection. Why it's not behind flag? How should I disable the feature > Pavel> and get back to the old behavior? > > We already had in MariaDB flood protection for some error > messages. Can you say exactly which error messages are suppressed already? Is there a documentation link about that? > I am happy to look into adding a flag that is usable for any kind of > suppressed messages. > > A couple of ways to do this: > > a) Not suppress anything if you give --log-warnings a value > 2 > b) make LIMIT_UNSAFE_WARNING_ACTIVATION_TIMEOUT a configurable > variable, where 0 would mean no suppression. > > Which of the above, or both, would you prefer? > Or do you have any other suggestion of how to do this? Reading documentation on --log-warnings I don't see how it can be related to the error suppression. So I think making LIMIT_UNSAFE_WARNING_ACTIVATION_TIMEOUT configurable would be much better, though it should apply to all possible error suppressions. Thank you, Pavel On Fri, Oct 17, 2014 at 1:43 PM, Michael Widenius <michael.widenius(a)gmail.com> wrote: > > Hi! > > First sorry for the delayed reply; I am at a conference in Oxford and > didn't have an reliable internet connection until now. > >>>>>> "Pavel" == Pavel Ivanov <pivanof(a)google.com> writes: > > Pavel> Hi, > Pavel> I see that in MariaDB 10.0.14 Michael disabled replication safety > Pavel> warning for the case of INSERT ON DUPLICATE KEY UPDATE with several > Pavel> unique keys with a comment "We should assume that the store engine > Pavel> will report the first duplicate key for this case." > Pavel> http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/4400.1.3 > > Pavel> What does that mean? > > That we don't anymore give a warning for things that can't happen in > the distributed MariaDB server. > > Pabel> Which key is "the first duplicate key". > > The first key that has a duplicate value in the order of keys in the KEY > structure that MariaDB provides for the storage engine. > > This is the same order that keys are presented in SHOW CREATE TABLE > > Pavel> and why > Pavel> MariaDB relies on an engine behavior which AFAIK is not guaranteed? > > This is guaranteed for all storage engines shipped with MariaDB and > should also be guaranteed for any storage engine that wants to follow > the MariaDB and even the original MySQL storage engine interface. > > The change is also in line with how I designed the part of the storage > engine interface that gives the SQL layer the information about which > key that was conflicted. As we use this for things like INSERT ... ON > DUPLICATE KEY, it's critical that all storage engine provides the same > information in case of conflicts. > > To help engine writes we are constantly adding new test in the storage > engine test suite, to make it easier for storage engine vendors to > test that their storage engine follows the interface. > We have now added a test to check for the correct behaviour for the > case of INSERT ... ON DUPLICATE KEY. > > What is important when it comes to the storage engine interface is > that in all possible cases, a query should work identically against 2 > different storage engines. > > The only 'allowed difference's that I can think of for the moment are: > > - Different storage engines may retrieve rows in different order. > - In case of errors, changes to non transactional tables will not roll > back. > - Automatically generated key values may be different between storage engines. > (but preferably they should not be that). However when things are > replicated the slave, it should get the same values as the master. > > In the case of INSERT ... ON DUPLICATE KEY, it was clear that someone > had broke the storage engine interface, for no good reason. Instead > of users having to depend on 'unsafe, different and undocumented > behavior' for which row as updated, it's better to fix this issue once > and for all, especially as fixing this is trivial. > > Do you have or use a storage engine that doesn't provide the expected value > for the conflicting key ? > > If yes, there is a couple of ways to fix this. I am happy to talk with > you to find a solution that will satisfy your needs and ours. > > I underatand that in many cases it's not critical which unique key the > storage engine reports as duplicate. However in some cases, it's > critical to get the first key. > > I could add a new extra call to inform the engine that we now require > the first duplicate key and not just any conflicting key. This would > be used for example for INSERT ... ON DUPLICATE KEY ... > > I would prefer to have the check for which key was duplicated in the > storage engine. However, if critically needed, and assuming there is more > than one storage engine that needs this, we could add a check on the > handler level that will do a lookup on all unique keys to find the > first duplicate one. > > What solution would you prefer? > > Pavel> And why this serious change in behavior wasn't mentioned in the > Pavel> Release Notes? > > I didn't see it as a a serious change as it's only about removing a > warning that has no value for any normal MariaDB user (who only use > storage engines supplied with MariadB) and which actually caused > serious problems for some MariaDB users. > > That said, I agree it was a mistake from my part to not notice that > this was missing in the release notes. I will try to do better in the > future and check that the release notes correctly reflects any changes > that I have done. > > I appologize for missing this in this case. > > Pavel> On the related note Release Notes mention the error log flood > Pavel> protection. Why it's not behind flag? How should I disable the feature > Pavel> and get back to the old behavior? > > We already had in MariaDB flood protection for some error > messages. What I did was to make it more general and extend it to also > include other error messages, like the one we are discussing. As > there wasn't a flag for the old suppression, I didn't see a need for > adding a flag for the new suppression. > > I am happy to look into adding a flag that is usable for any kind of > suppressed messages. > > A couple of ways to do this: > > a) Not suppress anything if you give --log-warnings a value > 2 > b) make LIMIT_UNSAFE_WARNING_ACTIVATION_TIMEOUT a configurable > variable, where 0 would mean no suppression. > > Which of the above, or both, would you prefer? > Or do you have any other suggestion of how to do this? > > For 10.1 we will probably add a mechanism where you can permanently > suppress any specific error message to be written to the error log. > (They would still be given to the client). > > Any suggestion or wishes regarding this? > > Regards, > Monty

1 0

[Maria-developers] Igor please review: [Commits] Rev 4446: MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost()
by Sergey Petrunia 20 Oct '14

20 Oct '14

Hello Igor, Could you please review the below: At file:///home/psergey/dev2/10.0-cp2/ ------------------------------------------------------------ revno: 4446 revision-id: psergey at askmonty.org-20141020175620-vodbkx9ugmdhh8c1 parent: psergey at askmonty.org-20141016135713-md92qr4g9fzhitxh committer: Sergey Petrunya <psergey at askmonty.org> branch nick: 10.0-cp2 timestamp: Mon 2014-10-20 21:56:20 +0400 message: MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() (Attempt #2) - Don't attempt to use BKA for materialized derived tables. The table is neither filled nor fully opened yet, so attempt to call handler->multi_range_read_info() causes crash. === modified file 'mysql-test/r/derived_opt.result' --- a/mysql-test/r/derived_opt.result 2013-06-18 15:21:00 +0000 +++ b/mysql-test/r/derived_opt.result 2014-10-20 17:56:20 +0000 @@ -352,4 +352,33 @@ pk pk 72 72 80 80 drop table t1, t2, t3, t4; +# +# MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int, b int, c text); +insert into t2 +select +A.a + B.a* 10, +A.a + B.a* 10, +'blob-data' +from t1 A, t1 B; +set @tmp_jcl= @@join_cache_level; +set @tmp_os= @@optimizer_switch; +set join_cache_level=6; +set @@optimizer_switch='derived_merge=on,derived_with_keys=on,mrr=on'; +explain +select * from +t1 join +(select * from t2 order by a limit 1000) as D1 +where +D1.a= t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 10 +2 DERIVED t2 ALL NULL NULL NULL NULL 100 Using filesort +set join_cache_level=@tmp_jcl; +set optimizer_switch=@tmp_os; +drop table t1, t2; set optimizer_switch=@exit_optimizer_switch; === modified file 'mysql-test/t/derived_opt.test' --- a/mysql-test/t/derived_opt.test 2012-11-19 16:17:46 +0000 +++ b/mysql-test/t/derived_opt.test 2014-10-20 17:56:20 +0000 @@ -272,5 +272,34 @@ limit 10; drop table t1, t2, t3, t4; +--echo # +--echo # MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost() +--echo # +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2 (a int, b int, c text); +insert into t2 +select + A.a + B.a* 10, + A.a + B.a* 10, + 'blob-data' +from t1 A, t1 B; + +set @tmp_jcl= @@join_cache_level; +set @tmp_os= @@optimizer_switch; +set join_cache_level=6; +set @@optimizer_switch='derived_merge=on,derived_with_keys=on,mrr=on'; +explain +select * from + t1 join + (select * from t2 order by a limit 1000) as D1 +where + D1.a= t1.a; + +set join_cache_level=@tmp_jcl; +set optimizer_switch=@tmp_os; +drop table t1, t2; + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2014-10-09 08:30:11 +0000 +++ b/sql/sql_select.cc 2014-10-20 17:56:20 +0000 @@ -10550,6 +10550,19 @@ uint check_join_cache_usage(JOIN_TAB *ta } /* + Don't use BKA for materialized tables. We could actually have a + meaningful use of BKA when linked join buffers are used. + + The problem is, the temp.table is not filled (actually not even opened + properly) yet, and this doesn't let us call + handler->multi_range_read_info(). It is possible to come up with + estimates, etc. without acessing the table, but it seems not to worth the + effort now. + */ + if (tab->table->pos_in_table_list->is_materialized_derived()) + no_bka_cache= true; + + /* Don't use join buffering if we're dictated not to by no_jbuf_after (This is not meaningfully used currently) */ @@ -10615,7 +10628,7 @@ uint check_join_cache_usage(JOIN_TAB *ta if (tab->ref.is_access_triggered()) goto no_join_cache; - if (!tab->is_ref_for_hash_join()) + if (!tab->is_ref_for_hash_join() && !no_bka_cache) { flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; if (tab->table->covering_keys.is_set(tab->ref.key)) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0