From 99e462ab0b308e629f4604adc3a33e2cf817be82 Mon Sep 17 00:00:00 2001 From: Nirbhay Choubey Date: Tue, 10 Jan 2012 13:33:45 +0530 Subject: BUG#11760384 - 52792: mysqldump in XML mode does not dump routines. mysqldump in xml mode did not dump routines, events or triggers. This patch fixes this issue by fixing the if conditions that disallowed the dump of above mentioned objects in xml mode, and added the required code to enable dump in xml format. client/mysqldump.c: BUG#11760384 - 52792: mysqldump in XML mode does not dump routines. Fixed some if conditions to allow execution of dump methods for xml and further added the relevant code at places to produce the dump in xml format. mysql-test/r/mysqldump.result: Added a test case for Bug#11760384. mysql-test/t/mysqldump.test: Added a test case for Bug#11760384. --- mysql-test/r/mysqldump.result | 439 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 439 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index a337c0384f6..1d4a5783c3f 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -4628,5 +4628,444 @@ ALTER DATABASE `test-database` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; DROP DATABASE `test-database`; USE `test`; # +# BUG#11760384 : 52792: mysqldump in XML mode does not dump routines. +# +CREATE DATABASE BUG52792; +USE BUG52792; +CREATE TABLE t1 (c1 INT, c2 VARCHAR(20)); +CREATE TABLE t2 (c1 INT); +INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); +INSERT INTO t2 VALUES (1),(2),(3); +# Stored Procedures. +CREATE PROCEDURE simpleproc1 (OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t1; +END// +CREATE PROCEDURE simpleproc2 (OUT param1 INT) +BEGIN +SELECT COUNT(*) INTO param1 FROM t2; +END// +# Events. +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792; +CREATE EVENT e2 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792; +# Functions. +CREATE FUNCTION `hello1` (s CHAR(20)) +RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT('Hello, ' ,s ,'!'); +CREATE FUNCTION `hello2` (s CHAR(20)) +RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT(']]>, ' , s ,'!'); +# Triggers. +CREATE TRIGGER trig1 BEFORE INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1); +END; +| +CREATE TRIGGER trig2 AFTER INSERT ON t2 +FOR EACH ROW BEGIN +INSERT INTO t2 VALUES(1, ']]>'); +INSERT INTO t2 VALUES(2, ''); +INSERT INTO t2 VALUES(3, ' & \ " _'); +END; +| +# Views +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t2; + +# Dumping BUG52792 database in xml format. + +# Running 'replace_regex on timestamp' + + + + + + + + + + + 1 + aaa + + + 2 + bbb + + + 3 + ccc + + + + + + + + + 1 + + + 2 + + + 3 + + + + + + + +'); +INSERT INTO t2 VALUES(2, ''); +INSERT INTO t2 VALUES(3, ' & \ " _'); +END +]]> + + + + + + + + + + + + + + + + + + + + + + + + +, ' , s ,'!') +]]> + + + + + + + + + + + +# Dumping BUG52792 database in xml format with comments. + +# Running 'replace_regex on timestamp' + + + + + + + + + + + + + 1 + aaa + + + 2 + bbb + + + 3 + ccc + + + + + + + + + + + 1 + + + 2 + + + 3 + + + + + + + +'); +INSERT INTO t2 VALUES(2, ''); +INSERT INTO t2 VALUES(3, ' & \ " _'); +END +]]> + + + + + + + + + + + + + + + + + + + + + + + + + + + + +, ' , s ,'!') +]]> + + + + + + + + + + + +# Test to check 'Insufficient privileges' error. + +GRANT ALL PRIVILEGES ON BUG52792.* TO user1; +# Running 'replace_regex on timestamp' + + + + + + + + + + + 1 + aaa + + + 2 + bbb + + + 3 + ccc + + + + + + + + + 1 + + + 2 + + + 3 + + + + + + + +'); +INSERT INTO t2 VALUES(2, ''); +INSERT INTO t2 VALUES(3, ' & \ " _'); +END +]]> + + + + + + + + + + + + + + + + + + + + + + +DROP USER user1; +DROP DATABASE BUG52792; +# UTF-8 +CREATE DATABASE BUG52792; +USE BUG52792; +SET NAMES utf8; +CREATE FUNCTION `straße` ( c1 CHAR(20)) +RETURNS CHAR(50) DETERMINISTIC +RETURN CONCAT(']]>, ', s, '!'); + + + + + +, ', s, '!') +]]> + + + + +DROP DATABASE BUG52792; +USE test; +# # End of 5.1 tests # -- cgit v1.2.1 From 1522923949a9ea2a7379f9369b398870a711f79a Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 12 Jan 2012 13:02:51 +0400 Subject: BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIME Fixing the 5.5 part (the 5.6 part will go in a separate commit soon). Problem: Item_direct_ref::get_date() incorrectly calculated its "null_value", which made UNIX_TIMESTAMP(view_column) incorrectly return NULL for a NOT NULL view_column. Fix: Make Item_direct_ref::get_date() calculate null_value in the similar way with the other methods (val_real,val_str,val_int,val_decimal): copy null_value from the referenced Item. modified: mysql-test/r/func_time.result mysql-test/t/func_time.test sql/item.cc --- mysql-test/r/func_time.result | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index bad467b3255..bd591299455 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1433,6 +1433,21 @@ NULL 1 NULL SET storage_engine=NULL; ERROR 42000: Variable 'storage_engine' can't be set to the value of 'NULL' # +# BUG#13354387 - CRASH IN IN MY_DECIMAL::OPERATOR FOR VIEW AND FUNCTION UNIX_TIMESTAMP +# Part1 (5.5) +SET time_zone='+03:00'; +CREATE TABLE t1 (a DATETIME NOT NULL); +INSERT INTO t1 VALUES ('2009-09-20 07:32:39.06'); +INSERT INTO t1 VALUES ('0000-00-00 00:00:00.00'); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT CAST(UNIX_TIMESTAMP(a) AS DECIMAL(25,3)) AS c1 FROM v1 ORDER BY 1; +c1 +0.000 +1253421159.000 +DROP VIEW v1; +DROP TABLE t1; +SET time_zone=DEFAULT; +# # Bug #59686 crash in String::copy() with time data type # SELECT min(timestampadd(month, 1>'', from_days('%Z'))); -- cgit v1.2.1 From 752496999e661074613459c2f12bc73e21540386 Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Fri, 20 Jan 2012 16:03:39 +0600 Subject: Patch for bug#13070308 - VALGRIND failure in XA test. The issue is that xa.test failed sporadically on some platforms. The reason for the test failure is a race condition in xa.test. The race condition occures between connection that executes statement INSERT INTO t2 SELECT FROM t1 and other connection that tries to run statements DELETE FROM t1 and COMMIT. If COMMIT statement had been executed before the statement INSERT INTO t2 SELECT FROM t1 was locked by lock on table t1 (as a result of query from table t1) then the INSERT statement is executed successfully and a following test for deadlock would failed. This patch fixes this race condition by moving COMMIT statement after commit of distributed transaction from concurrent session. --- mysql-test/r/xa.result | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/xa.result b/mysql-test/r/xa.result index 7b580abb19f..6db257eafd5 100644 --- a/mysql-test/r/xa.result +++ b/mysql-test/r/xa.result @@ -206,8 +206,8 @@ DROP TABLE t1; # FAILED # DROP TABLE IF EXISTS t1, t2; -CREATE TABLE t1 (a INT); -CREATE TABLE t2 (a INT); +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +CREATE TABLE t2 (a INT) ENGINE=InnoDB; START TRANSACTION; INSERT INTO t1 VALUES (1); # Connection con2 @@ -217,12 +217,14 @@ INSERT INTO t2 SELECT a FROM t1; # Connection default # Waiting until INSERT ... is blocked DELETE FROM t1; -COMMIT; # Connection con2 # Reaping: INSERT INTO t2 SELECT a FROM t1 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction XA COMMIT 'xid1'; ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected +# Connection default +COMMIT; +# Connection con2 XA START 'xid1'; XA END 'xid1'; XA PREPARE 'xid1'; -- cgit v1.2.1 From cfbe0fe0cdcaf421fae0fee8d690952f94b73db5 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Fri, 20 Jan 2012 14:19:39 +0100 Subject: Bug#13500478 63623: TEST CASE PARTITION_BINLOG_STMT CREATES A FILE IN AN IMPROPER LOCATION. Fixed by using $MYSQLTEST_VARDIR, as proposed by Davi Arnaut. Thank you Davi! --- mysql-test/r/partition_binlog_stmt.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/partition_binlog_stmt.result b/mysql-test/r/partition_binlog_stmt.result index 9be23636ca6..5b9df742f70 100644 --- a/mysql-test/r/partition_binlog_stmt.result +++ b/mysql-test/r/partition_binlog_stmt.result @@ -8,6 +8,6 @@ name TINYBLOB NOT NULL, modified TIMESTAMP DEFAULT '0000-00-00 00:00:00', INDEX namelocs (name(255))) ENGINE = MyISAM PARTITION BY HASH(id) PARTITIONS 2; -LOAD DATA LOCAL INFILE 'init_file.txt' +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/init_file.txt' INTO TABLE t1 (name); DROP TABLE t1; -- cgit v1.2.1 From e449cf48afc34705b601113fd73bce84338c2e37 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 23 Jan 2012 13:07:10 +0400 Subject: Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI Introducing new collations: utf8_general_mysql500_ci and ucs2_general_mysql500_ci, to reproduce behaviour of utf8_general_ci and ucs2_general_ci from mysql-5.1.23 (and earlier). The collations are added to simplify upgrade from mysql-5.1.23 and earlier. Note: The patch does not make new server start over old data automatically. Some manual upgrade procedures are assumed. Paul: please get in touch with me to discuss upgrade procedures when documenting this bug. modified: include/m_ctype.h mysql-test/r/ctype_utf8.result mysql-test/t/ctype_utf8.test mysys/charset-def.c strings/ctype-ucs2.c strings/ctype-utf8.c --- mysql-test/r/ctype_utf8.result | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index b491ce504bf..c540c750f9d 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1928,4 +1928,28 @@ D120 SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)); HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)) D120D18E +# +# Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI +# +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci); +INSERT INTO t1 VALUES ('a'),('r'),('s'),(_latin1 0xDF),(_latin1 0xF7),('t'),('z'); +SELECT * FROM t1 ORDER BY a; +a +a +r +s +t +z +ß +÷ +SELECT a, COUNT(*) FROM t1 GROUP BY a; +a COUNT(*) +a 1 +r 1 +s 1 +t 1 +z 1 +ß 1 +÷ 1 +DROP TABLE t1; End of 5.1 tests -- cgit v1.2.1 From f8e924b467d099b5d99248c160a2aba59a6ea3ce Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Tue, 24 Jan 2012 13:00:13 +0400 Subject: BUG#13458237 - INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY. SIMILAR TO '2009-10-00' - Reverting the patch for Bug # 12584302 The patch will be reverted in 5.1 and 5.5. The patch will not be reverted in 5.6, the change will be properly documented in 5.6. - Backporting DBUG_ASSERT not to crash on '0000-01-00' (already fixed in mysql-trunk (5.6)) --- mysql-test/r/func_time.result | 184 +++++++++++++++++++++++++++++++++++++++++- 1 file changed, 183 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 68c1e667440..ceb9dd4f7ea 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1430,6 +1430,188 @@ NULL # Bug#12584302 AFTER FIX FOR #12403504: ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP > 0, # DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5); +# +# BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY +# SIMILAR TO '2009-10-00' +# +SELECT +DATE('20091000'), +STR_TO_DATE('200910','%Y%m'), +LAST_DAY('2009-10-00'), +LAST_DAY(DATE('2009-10-00')), +LAST_DAY(DATE'2009-10-00'), +LAST_DAY(STR_TO_DATE('200910','%Y%m')), +WEEK('2009-10-00'), +WEEK(DATE('2009-10-00')), +WEEK(DATE'2009-10-00'), +WEEK(STR_TO_DATE('200910','%Y%m')), +WEEKOFYEAR('2009-10-00'), +WEEKOFYEAR(DATE('2009-10-00')), +WEEKOFYEAR(DATE'2009-10-00'), +WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')), +DAYOFYEAR('2009-10-00'), +DAYOFYEAR(DATE('2009-10-00')), +DAYOFYEAR(DATE'2009-10-00'), +DAYOFYEAR(STR_TO_DATE('200910','%Y%m')), +WEEKDAY('2009-10-00'), +WEEKDAY(DATE('2009-10-00')), +WEEKDAY(DATE'2009-10-00'), +WEEKDAY(STR_TO_DATE('200910','%Y%m')), +TO_DAYs('2009-10-00'), +TO_DAYs(DATE('2009-10-00')), +TO_DAYs(DATE'2009-10-00'), +TO_DAYs(STR_TO_DATE('200910','%Y%m')); +DATE('20091000') 2009-10-00 +STR_TO_DATE('200910','%Y%m') 2009-10-00 +LAST_DAY('2009-10-00') NULL +LAST_DAY(DATE('2009-10-00')) 2009-10-31 +LAST_DAY(DATE'2009-10-00') NULL +LAST_DAY(STR_TO_DATE('200910','%Y%m')) 2009-10-31 +WEEK('2009-10-00') NULL +WEEK(DATE('2009-10-00')) 39 +WEEK(DATE'2009-10-00') NULL +WEEK(STR_TO_DATE('200910','%Y%m')) NULL +WEEKOFYEAR('2009-10-00') NULL +WEEKOFYEAR(DATE('2009-10-00')) 40 +WEEKOFYEAR(DATE'2009-10-00') NULL +WEEKOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL +DAYOFYEAR('2009-10-00') NULL +DAYOFYEAR(DATE('2009-10-00')) 273 +DAYOFYEAR(DATE'2009-10-00') NULL +DAYOFYEAR(STR_TO_DATE('200910','%Y%m')) NULL +WEEKDAY('2009-10-00') NULL +WEEKDAY(DATE('2009-10-00')) 2 +WEEKDAY(DATE'2009-10-00') NULL +WEEKDAY(STR_TO_DATE('200910','%Y%m')) NULL +TO_DAYs('2009-10-00') NULL +TO_DAYs(DATE('2009-10-00')) 734045 +TO_DAYs(DATE'2009-10-00') NULL +TO_DAYs(STR_TO_DATE('200910','%Y%m')) NULL +Warnings: +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '2009-10-00' +SELECT +DATE('00000100'), +STR_TO_DATE('000001','%Y%m'), +LAST_DAY('0000-01-00'), +LAST_DAY(DATE('0000-01-00')), +LAST_DAY(DATE'0000-01-00'), +LAST_DAY(STR_TO_DATE('000001','%Y%m')), +WEEK('0000-01-00'), +WEEK(DATE('0000-01-00')), +WEEK(DATE'0000-01-00'), +WEEK(STR_TO_DATE('000001','%Y%m')), +WEEKOFYEAR('0000-01-00'), +WEEKOFYEAR(DATE('0000-01-00')), +WEEKOFYEAR(DATE'0000-01-00'), +WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')), +DAYOFYEAR('0000-01-00'), +DAYOFYEAR(DATE('0000-01-00')), +DAYOFYEAR(DATE'0000-01-00'), +DAYOFYEAR(STR_TO_DATE('000001','%Y%m')), +WEEKDAY('0000-01-00'), +WEEKDAY(DATE('0000-01-00')), +WEEKDAY(DATE'0000-01-00'), +WEEKDAY(STR_TO_DATE('000001','%Y%m')), +TO_DAYs('0000-01-00'), +TO_DAYs(DATE('0000-01-00')), +TO_DAYs(DATE'0000-01-00'), +TO_DAYs(STR_TO_DATE('000001','%Y%m')); +DATE('00000100') 0000-01-00 +STR_TO_DATE('000001','%Y%m') 0000-01-00 +LAST_DAY('0000-01-00') NULL +LAST_DAY(DATE('0000-01-00')) 0000-01-31 +LAST_DAY(DATE'0000-01-00') NULL +LAST_DAY(STR_TO_DATE('000001','%Y%m')) 0000-01-31 +WEEK('0000-01-00') NULL +WEEK(DATE('0000-01-00')) 52 +WEEK(DATE'0000-01-00') NULL +WEEK(STR_TO_DATE('000001','%Y%m')) NULL +WEEKOFYEAR('0000-01-00') NULL +WEEKOFYEAR(DATE('0000-01-00')) 52 +WEEKOFYEAR(DATE'0000-01-00') NULL +WEEKOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL +DAYOFYEAR('0000-01-00') NULL +DAYOFYEAR(DATE('0000-01-00')) 0 +DAYOFYEAR(DATE'0000-01-00') NULL +DAYOFYEAR(STR_TO_DATE('000001','%Y%m')) NULL +WEEKDAY('0000-01-00') NULL +WEEKDAY(DATE('0000-01-00')) 5 +WEEKDAY(DATE'0000-01-00') NULL +WEEKDAY(STR_TO_DATE('000001','%Y%m')) NULL +TO_DAYs('0000-01-00') NULL +TO_DAYs(DATE('0000-01-00')) 0 +TO_DAYs(DATE'0000-01-00') NULL +TO_DAYs(STR_TO_DATE('000001','%Y%m')) NULL Warnings: -Warning 1292 Incorrect datetime value: '0' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' +Level Warning +Code 1292 +Message Incorrect datetime value: '0000-01-00' End of 5.1 tests -- cgit v1.2.1 From 461e039a07062f05082175edb1156725ac43b4c6 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 24 Jan 2012 11:08:57 -0500 Subject: BUG#12969301 : mysql_plugin: enable is ignored if plugin exists This patch changes the mechanism by which the client enables a plugin. Instead of using INSERT IGNORE to reload a plugin library, it now uses REPLACE INTO. This allows users to load a library multiple times replacing the existing values in the mysql.plugin table. This allows users to replace the symbol reference to a different dl name in the table. Thus permitting enabling of multiple versions of the same library without first disabling the old version. A regression test was added to ensure this feature works. --- mysql-test/r/mysql_plugin.result | 14 +++++++++++++- 1 file changed, 13 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysql_plugin.result b/mysql-test/r/mysql_plugin.result index 93567e28c3d..636b039047e 100644 --- a/mysql-test/r/mysql_plugin.result +++ b/mysql-test/r/mysql_plugin.result @@ -23,12 +23,24 @@ wacky libdaemon_example.so wicky libdaemon_example.so wonky libdaemon_example.so # +# Ensure the plugin is loaded. +# +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; +name dl +daemon_example libdaemon_example.so +# +# Ensure the plugin is replaced. +# +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; +name dl +daemon_example liblibdaemon_example.so +# # Disable the plugin... # # # Ensure the plugin isn't loaded. # -SELECT * FROM mysql.plugin WHERE dl like 'libdaemon%' ORDER BY name; +SELECT * FROM mysql.plugin WHERE dl like '%libdaemon%' ORDER BY name; name dl # # Attempt to load non-existant plugin -- cgit v1.2.1 From 97883d3c0498dfaa17f6f7cbefa373b7cd2c72a3 Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Wed, 25 Jan 2012 15:59:30 +0600 Subject: Fixed bug#11753187 (formerly known as bug 44585): SP_CACHE BEHAVES AS MEMORY LEAK. Background: - There are caches for stored functions and stored procedures (SP-cache); - There is no similar cache for events; - Triggers are cached together with TABLE objects; - Those SP-caches are per-session (i.e. specific to each session); - A stored routine is represented by a sp_head-instance internally; - SP-cache basically contains sp_head-objects of stored routines, which have been executed in a session; - sp_head-object is added into the SP-cache before the corresponding stored routine is executed; - SP-cache is flushed in the end of the session. The problem was that SP-cache might grow without any limit. Although this was not a pure memory leak (the SP-cache is flushed when session is closed), this is still a problem, because the user might take much memory by executing many stored routines. The patch fixes this problem in the least-intrusive way. A soft limit (similar to the size of table definition cache) is introduced. To represent such limit the new runtime configuration parameter 'stored_program_cache' is introduced. The value of this parameter is stored in the new global variable stored_program_cache_size that used to control the size of SP-cache to overflow. The parameter 'stored_program_cache' limits number of cached routines for each thread. It has the following min/default/max values given from support: min = 256, default = 256, max = 512 * 1024. Also it should be noted that this parameter limits the size of each cache (for stored procedures and for stored functions) separately. The SP-cache size is checked after top-level statement is parsed. If SP-cache size exceeds the limit specified by parameter 'stored_program_cache' then SP-cache is flushed and memory allocated for cache objects is freed. Such approach allows to flush cache safely when there are dependencies among stored routines. sql/mysqld.cc: Added global variable stored_program_cache_size to store value of configuration parameter 'stored-program-cache'. sql/mysqld.h: Added declaration of global variable stored_program_cache_size. sql/sp_cache.cc: Extended interface for sp_cache by adding helper routine sp_cache_enforce_limit to control size of stored routines cache for overflow. Also added method enforce_limit into class sp_cache that implements control of cache size for overflow. sql/sp_cache.h: Extended interface for sp_cache by adding standalone routine sp_cache_enforce_limit to control size of stored routines cache for overflow. sql/sql_parse.cc: Added flush of sp_cache after processing of next sql-statement received from a client. sql/sql_prepare.cc: Added flush of sp_cache after preparation/execution of next prepared sql-statement received from a client. sql/sys_vars.cc: Added support for configuration parameter stored-program-cache. --- mysql-test/r/mysqld--help-notwin.result | 4 ++++ mysql-test/r/mysqld--help-win.result | 4 ++++ 2 files changed, 8 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index cb727984ec0..8dacb035f36 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -671,6 +671,9 @@ The following options may be given as the first argument: replication. --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual for the complete list of valid sql modes + --stored-program-cache=# + The soft upper limit for number of cached stored routines + for one connection. -s, --symbolic-links Enable symbolic link support. --sync-binlog=# Synchronously flush binary log to disk after every #th @@ -935,6 +938,7 @@ slow-query-log FALSE sort-buffer-size 2097152 sporadic-binlog-dump-fail FALSE sql-mode +stored-program-cache 256 symbolic-links FALSE sync-binlog 0 sync-frm TRUE diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index 2ef52355f90..1e7e877e29f 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -679,6 +679,9 @@ The following options may be given as the first argument: --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual for the complete list of valid sql modes --standalone Dummy option to start as a standalone program (NT). + --stored-program-cache=# + The soft upper limit for number of cached stored routines + for one connection. -s, --symbolic-links Enable symbolic link support. --sync-binlog=# Synchronously flush binary log to disk after every #th @@ -946,6 +949,7 @@ slow-start-timeout 15000 sort-buffer-size 2097152 sporadic-binlog-dump-fail FALSE sql-mode +stored-program-cache 256 symbolic-links FALSE sync-binlog 0 sync-frm TRUE -- cgit v1.2.1 From 042bd1511d855707f2beff65b9fb803d9dc4fb9e Mon Sep 17 00:00:00 2001 From: Tor Didriksen Date: Wed, 25 Jan 2012 10:36:25 +0100 Subject: Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 Expressions of the form BIGINT_COL should be done either as decimal, or float. Currently however, such comparisons are done as int, which means that the constant may be truncated, and yield false positives/negatives for all queries where compare is '>' '<' '>=' '<=' '=' '!='. BIGINT_COL IN and BIGINT_COL BETWEEN AND are also affected. mysql-test/r/bigint.result: New tests. mysql-test/r/func_in.result: BIGINT <=> string comparison should be done as float, so a warning for the value 'abc' is appropriate. mysql-test/t/bigint.test: New tests. sql/item_cmpfunc.cc: In convert_constant_item() we verify that the constant item can be stored in the given field. For BIGINT columns (MYSQL_TYPE_LONGLONG) we must verify that the stored constant value is actually comparable as int, i.e. that the value was not truncated. For between: compare as int only if both arguments convert correctly to int. --- mysql-test/r/bigint.result | 64 +++++++++++++++++++++++++++++++++++++++++++++ mysql-test/r/func_in.result | 2 ++ 2 files changed, 66 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index 47a45efa5fd..652c5b96147 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -438,3 +438,67 @@ Level Code Message Note 1003 select 1 AS `1` from `test`.`t1` where ((`test`.`t1`.`a` = 0) and ('0' = `test`.`t1`.`b`)) DROP TABLE t1; # End of 5.1 tests +# +# Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON +# +CREATE TABLE t_bigint(id BIGINT); +INSERT INTO t_bigint VALUES (1), (2); +SELECT id, id >= 1.1 FROM t_bigint; +id id >= 1.1 +1 0 +2 1 +SELECT id, 1.1 <= id FROM t_bigint; +id 1.1 <= id +1 0 +2 1 +SELECT id, id = 1.1 FROM t_bigint; +id id = 1.1 +1 0 +2 0 +SELECT id, 1.1 = id FROM t_bigint; +id 1.1 = id +1 0 +2 0 +SELECT * from t_bigint WHERE id = 1.1; +id +SELECT * from t_bigint WHERE id = 1.1e0; +id +SELECT * from t_bigint WHERE id = '1.1'; +id +SELECT * from t_bigint WHERE id = '1.1e0'; +id +SELECT * from t_bigint WHERE id IN (1.1, 2.2); +id +SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0); +id +SELECT * from t_bigint WHERE id IN ('1.1', '2.2'); +id +SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0'); +id +SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9; +id +SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0; +id +SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9'; +id +SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0'; +id +DROP TABLE t_bigint; +# +# Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 +# +CREATE TABLE t1 (a BIGINT); +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; +a +SELECT * FROM t1 WHERE coalesce(a)=0.9; +a +SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); +a +SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; +a +SELECT * FROM t1 WHERE a=0.9; +a +SELECT * FROM t1 WHERE a IN (0.8,0.9); +a +DROP TABLE t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 0b6117581f3..279a18e344e 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -469,6 +469,8 @@ SELECT HEX(a) FROM t2 WHERE a IN HEX(a) 7FFFFFFFFFFFFFFE 7FFFFFFFFFFFFFFF +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'abc' CREATE TABLE t3 (a BIGINT UNSIGNED); INSERT INTO t3 VALUES (9223372036854775551); SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); -- cgit v1.2.1 From 4172d5e9a9f1c637914fa99640a30a75b6d517d3 Mon Sep 17 00:00:00 2001 From: Tor Didriksen Date: Wed, 25 Jan 2012 16:11:03 +0100 Subject: Bug#13359121 LARGE NUMBERS, /STRINGS/DTOA.C:662 Bug#12985021 SIMPLE QUERY WITH DECIMAL NUMBERS TAKE AN When parsing the fractional part of a string which is to be converted to double, we can stop after a few digits: the extra digits will not contribute to the actual result anyways. mysql-test/r/func_str.result: New tests. mysql-test/t/func_str.test: New tests. strings/dtoa.c: The problem was s2b() multiplying and adding hundreds-of-thousands of ever smaller fractions. --- mysql-test/r/func_str.result | 36 ++++++++++++++++++++++++++++++++++++ 1 file changed, 36 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index abf76a533b3..adaa2df3a61 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2826,5 +2826,41 @@ SELECT ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)); ((0xf3) * (rpad(1.0,2048,1)) << (0xcc)) 0 # +# Bug#13359121 LARGE NUMBERS, /STRINGS/DTOA.C:662: +# BALLOC: ASSERTION `K <= 15' FAILED. +# Bug#12985021 SIMPLE QUERY WITH DECIMAL NUMBERS TAKE AN +# EXTRAORDINARY LONG TIME TO EXECUTE +SELECT @tmp_max:= @@global.max_allowed_packet; +@tmp_max:= @@global.max_allowed_packet +1048576 +SET @@global.max_allowed_packet=1024*1024*1024; +SELECT @@global.max_allowed_packet; +@@global.max_allowed_packet +1073741824 +do +format(rpad('111111111.1', +1111111, +'999999999999999999999999999999999999999999'),0,'be_BY') +; +DO +round( +concat( ( +coalesce( ( +linefromwkb('2147483648', +-b'1111111111111111111111111111111111111111111')), +( convert('[.DC2.]',decimal(30,30)) ), +bit_count('') +) ), +( lpad( ( elt('01','}:K5')), +sha1('P'), +( ( select '-9223372036854775808.1' > all (select ''))) +) +) +) +); +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '[.DC2.]' +SET @@global.max_allowed_packet:= @tmp_max; +# # End of 5.5 tests # -- cgit v1.2.1 From 2ed05e38edd82cfb1c1fffbafcca84b7adcdf3a1 Mon Sep 17 00:00:00 2001 From: Tor Didriksen Date: Thu, 26 Jan 2012 08:00:37 +0100 Subject: Bug#11753187 post-push fix: fix .result file --- mysql-test/r/mysqld--help-win.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index 1e7e877e29f..d7a06c5b23e 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -679,7 +679,7 @@ The following options may be given as the first argument: --sql-mode=name Syntax: sql-mode=mode[,mode[,mode...]]. See the manual for the complete list of valid sql modes --standalone Dummy option to start as a standalone program (NT). - --stored-program-cache=# + --stored-program-cache=# The soft upper limit for number of cached stored routines for one connection. -s, --symbolic-links -- cgit v1.2.1 From 9e0b69c0b7122d2973aa61a4efd9e8d80ad4a970 Mon Sep 17 00:00:00 2001 From: Guilhem Bichot Date: Thu, 26 Jan 2012 10:25:23 +0100 Subject: Fixes for: BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND WITH/WITHOUT INDEX RANGE SCAN BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG RESULTS WITH DECIMAL CONVERSION BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG RESULT AFTER MYSQL 5.1. Those are all cases where the range optimizer got it wrong with > and >=. mysql-test/r/range.result: Without the code fix for DECIMAL, "select count(val) from t2 where val > 0.1155" (which uses a range scan) returned 127 instead of 128); Moreover, both select * from t1 force index (primary) where a=1 and c>= 2.9; and select * from t1 force index (primary) where a=1 and c> 2.9; would miss "1 1 3". Without the code fix for strings, both SELECT * FROM t1 WHERE F1 >= 'A '; and SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; would miss "A A A". sql/item.cc: Preamble to the explanations below: opt_range.cc:get_mm_leaf() does this (this is not changed by the patch): changes column > value to column OP V where: * V is what is in "column" after we stored "value" in it (such store operation may have done rounding...) * OP is > or >=, depending on what's correct. For example, if c is an INT column, c > 2.9 is changed to c OP 3 where OP is >= ('>' would not be correct). The bugs below are cases where we chose OP wrongly. Note that such transformations are visible in the optimizer trace. 1) Fix for STRING. In the scenario with CHAR(5) in range.test, this happens, in get_mm_tree(), for the condition F1>='A ': * value->save_in_field_no_warnings(field, 1) wants to store the right argument (named 'item') into the CHAR(5) field; this stores 'A ' (the item's value) padded with spaces (which changes nothing: still 'A ') * we come to case Item_func::GE_FUNC: /* Don't use open ranges for partial key_segments */ if ((!(key_part->flag & HA_PART_KEY_SEG)) && (stored_field_cmp_to_item(param->thd, field, value) < 0)) tree->min_flag= NEAR_MIN; tree->max_flag=NO_MAX_RANGE; What this wants to do is: if the field's value is strictly smaller than the item's, then ">=" can be changed to ">" (this is an optimization, it can help pruning one useless partition). * stored_field_cmp_to_item() is called; it compares the field's and item's values: the item's value (Item_string::val_str()) is 'A ') and the field's value (Field_string::val_str()) is 'A' (yes val_str() removes end spaces unless sql_mode='PAD_CHAR_TO_FULL_LENGTH'); and the comparison is done with stringcmp() which considers end spaces as relevant; as end spaces differ, function returns a negative number, and ">='A '" becomes ">'A'" (i.e. the NEAR_MIN flag is turned on). During execution the index range scan code will search for "A", find a match, but exclude it (because of ">"), wrongly. The badness is the string comparison done by stored_field_cmp_to_item(): we use the reply of this function to determine where the index search should start, so it should do comparison like index search does comparisons; index search comparisons are ha_key_cmp() which uses a collation-aware comparison (in our case, my_strnncollsp_simple(), which ignores end spaces); so stored_field_cmp_to_item() needs to do the same. When this is fixed, condition becomes ">='A '". 2) Fix for DECIMAL: just like in other comparisons in stored_field_cmp_to_item(), we must first pass the field and then the item; otherwise expectations on what <0 and >0 mean (inferiority, superiority) get violated. In the test in range.test about c>2.9: c is an INT column, so 2.9 gets stored as 3, then stored_field_cmp_to_item() compares 3 and 2.9; because of the wrong order of arguments passed to my_decimal_cmp(), range optimizer thinks that 3 is < 2.9 and thus changes "c> 2.9" to "c> 3". After fixing the order, it changes to the correct "c>= 3". In the test in range.inc for val > 0.1155, it was changed to val > 0.116, now it is changed to val >= 0.116. --- mysql-test/r/range.result | 117 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 117 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index ae63edf87b9..5a964baf2c2 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1767,4 +1767,121 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; pk i4 pk i4 DROP TABLE t1; +# +# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND +# WITH/WITHOUT INDEX RANGE SCAN +# +create table t1 (id int unsigned not null auto_increment primary key); +insert into t1 values (null); +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +insert into t1 select null from t1; +create table t2 ( +id int unsigned not null auto_increment, +val decimal(5,3) not null, +primary key (id,val), +unique key (val,id), +unique key (id)); +insert into t2 select null,id*0.0009 from t1; +select count(val) from t2 ignore index (val) where val > 0.1155; +count(val) +128 +select count(val) from t2 force index (val) where val > 0.1155; +count(val) +128 +drop table t2, t1; +# +# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG +# RESULTS WITH DECIMAL CONVERSION +# +create table t1 (a int,b int,c int,primary key (a,c)); +insert into t1 values (1,1,2),(1,1,3),(1,1,4); +select convert(3, signed integer) > 2.9; +convert(3, signed integer) > 2.9 +1 +select * from t1 force index (primary) where a=1 and c>= 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 ignore index (primary) where a=1 and c>= 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 force index (primary) where a=1 and c> 2.9; +a b c +1 1 3 +1 1 4 +select * from t1 ignore index (primary) where a=1 and c> 2.9; +a b c +1 1 3 +1 1 4 +drop table t1; +# +# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG +# RESULT AFTER MYSQL 5.1. +# +CREATE TABLE t1( +F1 CHAR(5) NOT NULL, +F2 CHAR(5) NOT NULL, +F3 CHAR(5) NOT NULL, +PRIMARY KEY(F1), +INDEX IDX_F2(F2) +); +INSERT INTO t1 VALUES +('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'), +('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA'); +SELECT * FROM t1 WHERE F1 = 'A '; +F1 F2 F3 +A A A +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A '; +F1 F2 F3 +A A A +SELECT * FROM t1 WHERE F1 >= 'A '; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F1 > 'A '; +F1 F2 F3 +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F1 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F2 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 WHERE F3 BETWEEN 'A ' AND 'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND +'AAAAA'; +F1 F2 F3 +A A A +AA AA AA +AAA AAA AAA +AAAA AAAA AAAA +AAAAA AAAAA AAAAA +DROP TABLE t1; End of 5.1 tests -- cgit v1.2.1