From 6b205f93b00e1ede922c680252d64d97a195c343 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 3 Aug 2007 03:50:19 +0500 Subject: BUG#27040 - Incorrect FTS parser name output When a table uses two (or more) fulltext keys with different fulltext parsers, all fulltext keys (excluding those that use built-in parser) were using first parser that was mentioned in .frm. With this fix fulltext keys use proper (assigned during create table) fulltext parser. This code was contributed by Yoshinori Matsunobu ymatsunobu@mysql.com on 2007-03-12 The copyright was assigned and transferred under the terms of the MySQL Contributor License Agreement. sql/table.cc: Shift extra data segment buffer pointer by parser name length to restore next key parser name correctly. --- sql/table.cc | 1 + 1 file changed, 1 insertion(+) diff --git a/sql/table.cc b/sql/table.cc index e6f9b1086c7..1e8a37faaa6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -918,6 +918,7 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, } parser_name.str= (char*) next_chunk; parser_name.length= strlen((char*) next_chunk); + next_chunk+= parser_name.length + 1; keyinfo->parser= my_plugin_lock_by_name(NULL, &parser_name, MYSQL_FTPARSER_PLUGIN); if (! keyinfo->parser) -- cgit v1.2.1 From 7e9e0efd3ae2e1db7db4139097d3e6baaa888fe4 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 16 Aug 2007 14:00:35 -0700 Subject: Bug#30359 "Test federated_bug_25714 issues non-existing shell command" Problem caused by missing '$' symbol in eval statement causing it to always attempt to run test even if the test was not compiled. mysql-test/include/have_bug25714.inc: bug30359 missing '$' in eval statement --- mysql-test/include/have_bug25714.inc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/include/have_bug25714.inc b/mysql-test/include/have_bug25714.inc index 0c995cd0d4c..0c09ae1a035 100644 --- a/mysql-test/include/have_bug25714.inc +++ b/mysql-test/include/have_bug25714.inc @@ -3,5 +3,5 @@ # --require r/have_bug25714.require disable_query_log; -eval select LENGTH("MYSQL_BUG25714") > 0 as "have_bug25714_exe"; +eval select LENGTH("$MYSQL_BUG25714") > 0 as "have_bug25714_exe"; enable_query_log; -- cgit v1.2.1 From cf5762b40262ff7a74b3dba525e81ec0d9a8211b Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 13 Sep 2007 15:39:16 +0500 Subject: BUG#30590 - delete from memory table with composite btree primary key DELETE query against memory table with btree index may remove not all matching rows. This happens only when DELETE uses index read method to find matching rows. E.g. for queries like DELETE FROM t1 WHERE a=1. Fixed by reverting fix for BUG9719 and applying proper solution. heap/hp_delete.c: Reverted fix for BUG9719 as it makes queries like DELETE FROM t1 WHERE a=1 to remove not all matching rows (assuming this is memory table and there is btree key over `a`). This happens because we calculate info->lastkey_len in heap_rkey(). When we enter heap_rnext(), info->lastkey_len is 0 (set by hp_rb_delete_key()). We need to preserve info->lastkey_len in this situation, otherwise tree_search_key() will always return smallest value in a tree. heap/hp_rfirst.c: If we're performing index_first on a table that was taken from table cache, info->lastkey_len is initialized to previous query. Thus we set info->lastkey_len to proper value for subsequent heap_rnext() calls. This is needed for DELETE queries only, otherwise this variable is not used. Note that the same workaround may be needed for heap_rlast(), but for now heap_rlast() is never used for DELETE queries. heap/hp_rnext.c: An optimization for DELETE queries that use index_first()/index_next(). Use faster tree_search_edge() instead of tree_search_key(). mysql-test/r/heap_btree.result: A test case for BUG#30590. mysql-test/t/heap_btree.test: A test case for BUG#30590. --- heap/hp_delete.c | 3 --- heap/hp_rfirst.c | 11 +++++++++++ heap/hp_rnext.c | 29 +++++++++++++++++++++++++++++ mysql-test/r/heap_btree.result | 7 +++++++ mysql-test/t/heap_btree.test | 9 +++++++++ 5 files changed, 56 insertions(+), 3 deletions(-) diff --git a/heap/hp_delete.c b/heap/hp_delete.c index 266a9da6ca3..90e537081d3 100644 --- a/heap/hp_delete.c +++ b/heap/hp_delete.c @@ -73,10 +73,7 @@ int hp_rb_delete_key(HP_INFO *info, register HP_KEYDEF *keyinfo, int res; if (flag) - { info->last_pos= NULL; /* For heap_rnext/heap_rprev */ - info->lastkey_len= 0; - } custom_arg.keyseg= keyinfo->seg; custom_arg.key_length= hp_rb_make_key(keyinfo, info->recbuf, record, recpos); diff --git a/heap/hp_rfirst.c b/heap/hp_rfirst.c index 85548fea212..6e7ee45af5f 100644 --- a/heap/hp_rfirst.c +++ b/heap/hp_rfirst.c @@ -36,6 +36,17 @@ int heap_rfirst(HP_INFO *info, byte *record, int inx) sizeof(byte*)); info->current_ptr = pos; memcpy(record, pos, (size_t)share->reclength); + /* + If we're performing index_first on a table that was taken from + table cache, info->lastkey_len is initialized to previous query. + Thus we set info->lastkey_len to proper value for subsequent + heap_rnext() calls. + This is needed for DELETE queries only, otherwise this variable is + not used. + Note that the same workaround may be needed for heap_rlast(), but + for now heap_rlast() is never used for DELETE queries. + */ + info->lastkey_len= 0; info->update = HA_STATE_AKTIV; } else diff --git a/heap/hp_rnext.c b/heap/hp_rnext.c index a1bc480333e..513e184c743 100644 --- a/heap/hp_rnext.c +++ b/heap/hp_rnext.c @@ -34,11 +34,40 @@ int heap_rnext(HP_INFO *info, byte *record) heap_rb_param custom_arg; if (info->last_pos) + { + /* + We enter this branch for non-DELETE queries after heap_rkey() + or heap_rfirst(). As last key position (info->last_pos) is available, + we only need to climb the tree using tree_search_next(). + */ pos = tree_search_next(&keyinfo->rb_tree, &info->last_pos, offsetof(TREE_ELEMENT, left), offsetof(TREE_ELEMENT, right)); + } + else if (!info->lastkey_len) + { + /* + We enter this branch only for DELETE queries after heap_rfirst(). E.g. + DELETE FROM t1 WHERE a<10. As last key position is not available + (last key is removed by heap_delete()), we must restart search as it + is done in heap_rfirst(). + + It should be safe to handle this situation without this branch. That is + branch below should find smallest element in a tree as lastkey_len is + zero. tree_search_edge() is a kind of optimisation here as it should be + faster than tree_search_key(). + */ + pos= tree_search_edge(&keyinfo->rb_tree, info->parents, + &info->last_pos, offsetof(TREE_ELEMENT, left)); + } else { + /* + We enter this branch only for DELETE queries after heap_rkey(). E.g. + DELETE FROM t1 WHERE a=10. As last key position is not available + (last key is removed by heap_delete()), we must restart search as it + is done in heap_rkey(). + */ custom_arg.keyseg = keyinfo->seg; custom_arg.key_length = info->lastkey_len; custom_arg.search_flag = SEARCH_SAME | SEARCH_FIND; diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 72be6a3e400..639fe1ff897 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -307,4 +307,11 @@ UNIQUE USING BTREE(c1) ) ENGINE= MEMORY DEFAULT CHARSET= utf8; INSERT INTO t1 VALUES('1'), ('2'); DROP TABLE t1; +CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(2),(2); +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1; +a +1 +DROP TABLE t1; End of 4.1 tests diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index f8d6afbad04..849b7e12843 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -213,4 +213,13 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES('1'), ('2'); DROP TABLE t1; +# +# BUG#30590 - delete from memory table with composite btree primary key +# +CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(2),(2); +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1; +DROP TABLE t1; + --echo End of 4.1 tests -- cgit v1.2.1 From fc7a9058f1759fbc574ee1eefb4b892da49a1afb Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 13 Sep 2007 18:33:40 +0500 Subject: BUG#30583 - Partition on DOUBLE key + INNODB + count(*) == crash Issuing SELECT COUNT(*) against partitioned InnoDB table may cause server crash. Fixed that not all required fields were included into read_set. mysql-test/r/partition_innodb.result: A test case for BUG#30583. mysql-test/t/partition_innodb.test: A test case for BUG#30583. sql/ha_partition.cc: Ensure that all fields of current key are included into read_set, as partitioning requires them for sorting (see ha_partition::handle_ordered_index_scan). --- mysql-test/r/partition_innodb.result | 7 +++++++ mysql-test/t/partition_innodb.test | 8 ++++++++ sql/ha_partition.cc | 16 ++++++++++++++++ 3 files changed, 31 insertions(+) diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 8282cfc212a..5b755b6bfd5 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -129,3 +129,10 @@ insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert' SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; id time first_name last_name drop table t1; +CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB +PARTITION BY KEY(a) PARTITIONS 10; +INSERT INTO t1 VALUES(1),(2); +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index f4320c5c56a..4a50332b3df 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -134,3 +134,11 @@ SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; drop table t1; +# +# BUG#30583 - Partition on DOUBLE key + INNODB + count(*) == crash +# +CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB +PARTITION BY KEY(a) PARTITIONS 10; +INSERT INTO t1 VALUES(1),(2); +SELECT COUNT(*) FROM t1; +DROP TABLE t1; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index d46b3a3bb08..2681498417e 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -3324,6 +3324,22 @@ int ha_partition::index_init(uint inx, bool sorted) */ if (m_lock_type == F_WRLCK) bitmap_union(table->read_set, &m_part_info->full_part_field_set); + else if (sorted && m_table_flags & HA_PARTIAL_COLUMN_READ) + { + /* + An ordered scan is requested and necessary fields aren't in read_set. + This may happen e.g. with SELECT COUNT(*) FROM t1. We must ensure + that all fields of current key are included into read_set, as + partitioning requires them for sorting + (see ha_partition::handle_ordered_index_scan). + + TODO: handle COUNT(*) queries via unordered scan. + */ + uint i; + for (i= 0; i < m_curr_key_info->key_parts; i++) + bitmap_set_bit(table->read_set, + m_curr_key_info->key_part[i].field->field_index); + } file= m_file; do { -- cgit v1.2.1 From b4d566a0538f9f4169f3931c064d6c7ced6ba7ea Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 4 Oct 2007 14:56:33 +0200 Subject: Bug #30484: Partitions: crash with self-referencing trigger Two cases in ha_partition::extra() was missing (HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH) which only is currently used by NDB (which not uses ha_partition) mysql-test/r/partition.result: Added result for test of Bug #30484 mysql-test/t/partition.test: Added test for Bug #30484 sql/ha_partition.cc: Added HA_EXTRA_DELETE_CANNOT_BATCH and HA_EXTRA_UPDATE_CANNOT_BATCH in ha_partition::extra to do nothing (Only currently used in NDB, which is not dependent of ha_partition) --- mysql-test/r/partition.result | 4 ++++ mysql-test/t/partition.test | 9 +++++++++ sql/ha_partition.cc | 18 ++++++++++++++++++ 3 files changed, 31 insertions(+) diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 5d985d053fc..f90f3b07d70 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1259,6 +1259,10 @@ INSERT INTO t1 SELECT a + 8, b FROM t1; ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; +create table t (s1 int) engine=myisam partition by key (s1); +create trigger t_ad after delete on t for each row insert into t values (old.s1); +insert into t values (1); +drop table t; USE mysql; SET GLOBAL general_log = 0; ALTER TABLE general_log ENGINE = MyISAM; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 42db23dadef..f391237a4db 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1480,6 +1480,15 @@ ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; +# +# Bug #30484: Partitions: crash with self-referencing trigger +# + +create table t (s1 int) engine=myisam partition by key (s1); +create trigger t_ad after delete on t for each row insert into t values (old.s1); +insert into t values (1); +drop table t; + # # Bug #27816: Log tables ran with partitions crashes the server when logging # is enabled. diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 1ea3bb34514..6c1c89c4a04 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -4546,6 +4546,8 @@ void ha_partition::get_dynamic_partition_info(PARTITION_INFO *stat_info, 4) Parameters only used by temporary tables for query processing 5) Parameters only used by MyISAM internally 6) Parameters not used at all + 7) Parameters only used by federated tables for query processing + 8) Parameters only used by NDB The partition handler need to handle category 1), 2) and 3). @@ -4812,6 +4814,15 @@ void ha_partition::get_dynamic_partition_info(PARTITION_INFO *stat_info, HA_EXTRA_INSERT_WITH_UPDATE: Inform handler that an "INSERT...ON DUPLICATE KEY UPDATE" will be executed. This condition is unset by HA_EXTRA_NO_IGNORE_DUP_KEY. + + 8) Parameters only used by NDB + ------------------------------ + HA_EXTRA_DELETE_CANNOT_BATCH: + HA_EXTRA_UPDATE_CANNOT_BATCH: + Inform handler that delete_row()/update_row() cannot batch deletes/updates + and should perform them immediately. This may be needed when table has + AFTER DELETE/UPDATE triggers which access to subject table. + These flags are reset by the handler::extra(HA_EXTRA_RESET) call. */ int ha_partition::extra(enum ha_extra_function operation) @@ -4896,6 +4907,13 @@ int ha_partition::extra(enum ha_extra_function operation) /* Category 7), used by federated handlers */ case HA_EXTRA_INSERT_WITH_UPDATE: DBUG_RETURN(loop_extra(operation)); + /* Category 8) Parameters only used by NDB */ + case HA_EXTRA_DELETE_CANNOT_BATCH: + case HA_EXTRA_UPDATE_CANNOT_BATCH: + { + /* Currently only NDB use the *_CANNOT_BATCH */ + break; + } default: { /* Temporary crash to discover what is wrong */ -- cgit v1.2.1 From 1c94c8e674735337af6170cc21db3f685fe4e321 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 4 Oct 2007 10:55:08 -0700 Subject: Bug#31382 "Disabled plugin is provoking Valgrind error" If there are any auto-alloced string plug-in options, memory is allocated during the call for handle_options(). We must free this memory if we are not installing the plug-in. include/my_getopt.h: bug31382 new function: my_cleanup_options() mysys/my_getopt.c: bug31382 new function: my_cleanup_options(), fini_one_value() alter init_variables() to take an extra option. forward declare init_one_value() and fini_one_value() sql/sql_plugin.cc: bug31382 after calling handle_options(), make sure to call my_cleanup_options() if we are not installing the plug-in. --- include/my_getopt.h | 1 + mysys/my_getopt.c | 46 +++++++++++++++++++++++++++++++++++++++++++--- sql/sql_plugin.cc | 10 +++++++--- 3 files changed, 51 insertions(+), 6 deletions(-) diff --git a/include/my_getopt.h b/include/my_getopt.h index c74f3ed672e..30c2eb9531a 100644 --- a/include/my_getopt.h +++ b/include/my_getopt.h @@ -68,6 +68,7 @@ extern my_error_reporter my_getopt_error_reporter; extern int handle_options (int *argc, char ***argv, const struct my_option *longopts, my_get_one_option); +extern void my_cleanup_options(const struct my_option *options); extern void my_print_help(const struct my_option *options); extern void my_print_variables(const struct my_option *options); extern void my_getopt_register_get_addr(uchar ** (*func_addr)(const char *, uint, diff --git a/mysys/my_getopt.c b/mysys/my_getopt.c index 3a5b130e067..6ed03067d60 100644 --- a/mysys/my_getopt.c +++ b/mysys/my_getopt.c @@ -20,6 +20,9 @@ #include #include +typedef void (*init_func_p)(const struct my_option *option, uchar* *variable, + longlong value); + static void default_reporter(enum loglevel level, const char *format, ...); my_error_reporter my_getopt_error_reporter= &default_reporter; @@ -33,7 +36,12 @@ static longlong getopt_ll(char *arg, const struct my_option *optp, int *err); static ulonglong getopt_ull(char *arg, const struct my_option *optp, int *err); static double getopt_double(char *arg, const struct my_option *optp, int *err); -static void init_variables(const struct my_option *options); +static void init_variables(const struct my_option *options, + init_func_p init_one_value); +static void init_one_value(const struct my_option *option, uchar* *variable, + longlong value); +static void fini_one_value(const struct my_option *option, uchar* *variable, + longlong value); static int setval(const struct my_option *opts, uchar* *value, char *argument, my_bool set_maximum_value); static char *check_struct_option(char *cur_arg, char *key_name); @@ -117,7 +125,7 @@ int handle_options(int *argc, char ***argv, DBUG_ASSERT(argv && *argv); (*argc)--; /* Skip the program name */ (*argv)++; /* --- || ---- */ - init_variables(longopts); + init_variables(longopts, init_one_value); for (pos= *argv, pos_end=pos+ *argc; pos != pos_end ; pos++) { @@ -906,6 +914,37 @@ static void init_one_value(const struct my_option *option, uchar* *variable, } +/* + Init one value to it's default values + + SYNOPSIS + init_one_value() + option Option to initialize + value Pointer to variable +*/ + +static void fini_one_value(const struct my_option *option, uchar* *variable, + longlong value __attribute__ ((unused))) +{ + DBUG_ENTER("fini_one_value"); + switch ((option->var_type & GET_TYPE_MASK)) { + case GET_STR_ALLOC: + my_free((*(char**) variable), MYF(MY_ALLOW_ZERO_PTR)); + *((char**) variable)= NULL; + break; + default: /* dummy default to avoid compiler warnings */ + break; + } + DBUG_VOID_RETURN; +} + + +void my_cleanup_options(const struct my_option *options) +{ + init_variables(options, fini_one_value); +} + + /* initialize all variables to their default values @@ -919,7 +958,8 @@ static void init_one_value(const struct my_option *option, uchar* *variable, for a value and initialize. */ -static void init_variables(const struct my_option *options) +static void init_variables(const struct my_option *options, + init_func_p init_one_value) { DBUG_ENTER("init_variables"); for (; options->name; options++) diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc index 2d33da29b77..2970ce1d11f 100644 --- a/sql/sql_plugin.cc +++ b/sql/sql_plugin.cc @@ -3092,7 +3092,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, { sql_print_error("Parsing options for plugin '%s' failed.", tmp->name.str); - DBUG_RETURN(error); + goto err; } } @@ -3102,6 +3102,8 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, *enabled= TRUE; } + error= 1; + if (*enabled) { for (opt= tmp->plugin->system_vars; opt && *opt; opt++) @@ -3140,7 +3142,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, { sql_print_error("Plugin '%s' has conflicting system variables", tmp->name.str); - DBUG_RETURN(1); + goto err; } tmp->system_vars= chain.first; } @@ -3150,7 +3152,9 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, if (enabled_saved && global_system_variables.log_warnings) sql_print_information("Plugin '%s' disabled by command line option", tmp->name.str); - DBUG_RETURN(1); +err: + my_cleanup_options(opts); + DBUG_RETURN(error); } -- cgit v1.2.1 From 4686a8c8d169de12c6de472a0fda5cf70319c760 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 4 Oct 2007 17:34:28 -0700 Subject: fix undefined variable which can lead to crash --- sql/sql_plugin.cc | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc index 2970ce1d11f..2af528f6699 100644 --- a/sql/sql_plugin.cc +++ b/sql/sql_plugin.cc @@ -3053,7 +3053,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, MEM_ROOT *mem_root= alloc_root_inited(&tmp->mem_root) ? &tmp->mem_root : &plugin_mem_root; st_mysql_sys_var **opt; - my_option *opts; + my_option *opts= NULL; char *p, *varname; int error; st_mysql_sys_var *o; @@ -3153,7 +3153,8 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, sql_print_information("Plugin '%s' disabled by command line option", tmp->name.str); err: - my_cleanup_options(opts); + if (opts) + my_cleanup_options(opts); DBUG_RETURN(error); } -- cgit v1.2.1 From 0c0042a5de8df6a26fa70d52e10ace7dde3e43b3 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 9 Oct 2007 13:10:51 +0200 Subject: Bug#15522 - create ... select and with merge tables Added test case. The fix for Bug#20662 does also fix this one. mysql-test/r/merge.result: Bug#15522 - create ... select and with merge tables Added test result. mysql-test/t/merge.test: Bug#15522 - create ... select and with merge tables Added test case. --- mysql-test/r/merge.result | 5 +++++ mysql-test/t/merge.test | 14 ++++++++++++++ 2 files changed, 19 insertions(+) diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 42669eeb66f..24a56fbf575 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -876,4 +876,9 @@ CHECK TABLE tm1; Table Op Msg_type Msg_text test.tm1 check status OK DROP TABLE tm1, t1, t2; +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index c3e5cef5e63..b5c1a01fe8e 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -507,4 +507,18 @@ SELECT * FROM tm1; CHECK TABLE tm1; DROP TABLE tm1, t1, t2; +# +# Bug#15522 - create ... select and with merge tables +# +# This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE +# IF NOT EXISTS ... SELECT with locked tables). +# The new behavior for MERGE tables is consistent with the +# CREATE TABLE SELECT behavior for ordinary tables. +# +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +--error ER_UPDATE_TABLE_USED +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +DROP TABLE t1, t2; + --echo End of 5.0 tests -- cgit v1.2.1 From bf82c2f9b43419a243f231c2a30d05ac5483154b Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 18 Oct 2007 16:14:27 +0200 Subject: Bug#31692 - binlog_killed.test crashes sometimes The server crashed when a thread was killed while locking the general_log table at statement begin. The general_log table is handled like a performance schema table. The state of open tables is saved and cleared so that this table seems to be the only open one. Then this table is opened and locked. After writing, the table is closed and the open table state is restored. Before restoring, however, it is asserted that there is no current table open. After locking the table, mysql_lock_tables() checks if the thread was killed in between. If so, it unlocks the table and returns an error. open_ltable() just returns with the error and leaves closing of the table to close_thread_tables(), which is called at statement end. open_performance_schema_table() did not take this into account. It assumed that a failed open_ltable() would not leave an open table behind. Fixed by closing thread tables after open_ltable() and before restore_backup_open_tables_state() if the thread was killed. No test case. It requires correctly timed parallel execution. Since this bug was detected by the test suite, it seems dispensable to add another test. sql/sql_base.cc: Bug#31692 - binlog_killed.test crashes sometimes Closing thread tables after open_ltable() and before restore_backup_open_tables_state() if the thread was killed. --- sql/sql_base.cc | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 337fde53dac..c487c5f034e 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7748,7 +7748,17 @@ open_performance_schema_table(THD *thd, TABLE_LIST *one_table, table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; } else + { + /* + If error in mysql_lock_tables(), open_ltable doesn't close the + table. Thread kill during mysql_lock_tables() is such error. But + open tables cannot be accepted when restoring the open tables + state. + */ + if (thd->killed) + close_thread_tables(thd); thd->restore_backup_open_tables_state(backup); + } thd->utime_after_lock= save_utime_after_lock; DBUG_RETURN(table); -- cgit v1.2.1 From 57600b02d0faaa6d3346a66beb6d3cedb273bae3 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 18 Oct 2007 14:48:04 -0700 Subject: Bug#31473 "CSV does not work with NULL value in datetime fields" Attempting to insert a row with a NULL value for a DATETIME field results in a CSV file which the storage engine cannot read. Don't blindly assume that "0" is acceptable for all field types, Since CSV does not support NULL, we find out from the field the default non-null value. Do not permit the creation of a table with a nullable columns. mysql-test/r/csv.result: test for bug 31473 mysql-test/r/log_tables.result: change in results due to bugfix 31473 mysql-test/r/system_mysql_db.result: change in results due to bugfix 31473 mysql-test/t/csv.test: test for bug 31473 mysql-test/t/log_tables.test: due to bug31473, all columns in CSV tables must be declared as NOT NULL scripts/mysql_system_tables.sql: due to bug31473, all columns in CSV tables must be declared as NOT NULL storage/csv/ha_tina.cc: bug31473 Don't blindly assume that "0" is acceptable for all field types, Since CSV does not support NULL, we find out from the field the default non-null value. Do not permit the creation of a table with a nullable columns; --- mysql-test/r/csv.result | 106 ++++++++++++++++++++++++++++-------- mysql-test/r/log_tables.result | 96 ++++++++++++++++---------------- mysql-test/r/system_mysql_db.result | 18 +++--- mysql-test/t/csv.test | 87 +++++++++++++++++++++-------- mysql-test/t/log_tables.test | 18 +++--- scripts/mysql_system_tables.sql | 4 +- storage/csv/ha_tina.cc | 36 +++++++++--- 7 files changed, 240 insertions(+), 125 deletions(-) diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index 86ba5002af8..69f77dc3cd8 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -4929,7 +4929,7 @@ Note 1051 Unknown table 't2' Note 1051 Unknown table 't3' Note 1051 Unknown table 't4' DROP TABLE IF EXISTS bug13894; -CREATE TABLE bug13894 ( val integer ) ENGINE = CSV; +CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV; INSERT INTO bug13894 VALUES (5); INSERT INTO bug13894 VALUES (10); INSERT INTO bug13894 VALUES (11); @@ -4949,7 +4949,7 @@ val 11 DROP TABLE bug13894; DROP TABLE IF EXISTS bug14672; -CREATE TABLE bug14672 (c1 integer) engine = CSV; +CREATE TABLE bug14672 (c1 integer not null) engine = CSV; INSERT INTO bug14672 VALUES (1), (2), (3); SELECT * FROM bug14672; c1 @@ -4975,7 +4975,7 @@ c1 4 5 DROP TABLE bug14672; -CREATE TABLE test_concurrent_insert ( val integer ) ENGINE = CSV; +CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV; LOCK TABLES test_concurrent_insert READ LOCAL; INSERT INTO test_concurrent_insert VALUES (1); SELECT * FROM test_concurrent_insert; @@ -4992,7 +4992,7 @@ val 2 UNLOCK TABLES; DROP TABLE test_concurrent_insert; -CREATE TABLE test_repair_table ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV; CHECK TABLE test_repair_table; Table Op Msg_type Msg_text test.test_repair_table check status OK @@ -5000,7 +5000,7 @@ REPAIR TABLE test_repair_table; Table Op Msg_type Msg_text test.test_repair_table repair status OK DROP TABLE test_repair_table; -CREATE TABLE test_repair_table2 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV; SELECT * from test_repair_table2; val Warnings: @@ -5011,7 +5011,7 @@ CHECK TABLE test_repair_table2; Table Op Msg_type Msg_text test.test_repair_table2 check status OK DROP TABLE test_repair_table2; -CREATE TABLE test_repair_table3 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV; CHECK TABLE test_repair_table3; Table Op Msg_type Msg_text test.test_repair_table3 check error Corrupt @@ -5114,7 +5114,7 @@ num magic_no company_name founded 1 0102 CORRECT 1876 1 0102 CORRECT2 1876 DROP TABLE test_repair_table5; -create table t1 (a int) engine=csv; +create table t1 (a int not null) engine=csv; insert t1 values (1); delete from t1; affected rows: 1 @@ -5138,7 +5138,7 @@ insert t1 values (1),(2),(3),(4),(5); truncate table t1; affected rows: 0 drop table t1; -create table t1 (v varchar(32)); +create table t1 (v varchar(32) not null); insert into t1 values ('def'),('abc'),('hij'),('3r4f'); select * from t1; v @@ -5193,8 +5193,8 @@ select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); i v 4 3r4f drop table t1; -create table bug15205 (val int(11) default null) engine=csv; -create table bug15205_2 (val int(11) default null) engine=csv; +create table bug15205 (val int(11) not null) engine=csv; +create table bug15205_2 (val int(11) not null) engine=csv; select * from bug15205; ERROR HY000: Can't get stat of './test/bug15205.CSV' (Errcode: 2) select * from bug15205_2; @@ -5205,8 +5205,8 @@ drop table bug15205; drop table bug15205_2; set names latin1; create table t1 ( -c varchar(1), -name varchar(64) +c varchar(1) not null, +name varchar(64) not null ) character set latin1 engine=csv; insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE'); insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE'); @@ -5224,9 +5224,9 @@ FE FF ÿ LATIN SMALL LETTER Y WITH DIAERESIS drop table t1; End of 5.0 tests -create table bug22080_1 (id int,string varchar(64)) Engine=CSV; -create table bug22080_2 (id int,string varchar(64)) Engine=CSV; -create table bug22080_3 (id int,string varchar(64)) Engine=CSV; +create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV; insert into bug22080_1 values(1,'string'); insert into bug22080_1 values(2,'string'); insert into bug22080_1 values(3,'string'); @@ -5237,7 +5237,7 @@ check table bug22080_3; Table Op Msg_type Msg_text test.bug22080_3 check error Corrupt drop tables bug22080_1,bug22080_2,bug22080_3; -create table float_test (id float,string varchar(64)) Engine=CSV; +create table float_test (id float not null,string varchar(64) not null) Engine=CSV; insert into float_test values(1.0,'string'); insert into float_test values(2.23,'serg.g'); insert into float_test values(0.03,'string'); @@ -5254,14 +5254,14 @@ id string 9.67 string drop table float_test; CREATE TABLE `bug21328` ( -`col1` int(11) DEFAULT NULL, -`col2` int(11) DEFAULT NULL, -`col3` int(11) DEFAULT NULL +`col1` int(11) NOT NULL, +`col2` int(11) NOT NULL, +`col3` int(11) NOT NULL ) ENGINE=CSV; -insert into bug21328 values (1,NULL,NULL); +insert into bug21328 values (1,0,0); alter table bug21328 engine=myisam; drop table bug21328; -create table t1(a blob, b int) engine=csv; +create table t1(a blob not null, b int not null) engine=csv; insert into t1 values('a', 1); flush tables; update t1 set b=2; @@ -5269,7 +5269,7 @@ select * from t1; a b a 2 drop table t1; -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values(-1), (-123.34), (2), (-23); select * from t1; a @@ -5281,7 +5281,7 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; -create table t1(a int, b int) engine=csv; +create table t1(a int not null, b int not null) engine=csv; repair table t1; Table Op Msg_type Msg_text test.t1 repair Warning Data truncated for column 'a' at row 5 @@ -5299,7 +5299,7 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values (0), (1), (2); delete from t1 limit 2; check table t1; @@ -5315,4 +5315,62 @@ test.t1 check status OK select * from t1; a drop table t1; +create table t1(a datetime not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a +0000-00-00 00:00:00 +drop table t1; +create table t1(a set('foo','bar') not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a + +drop table t1; +create table t1(a varchar(32) not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a + +drop table t1; +create table t1(a int not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a +0 +drop table t1; +create table t1(a blob not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a + +drop table t1; +create table t1(a bit(1) not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select BIN(a) from t1; +BIN(a) +0 +drop table t1; +create table t1(a enum('foo','bar') default null) engine=csv; +ERROR HY000: Can't create table 'test.t1' (errno: -1) +create table t1(a enum('foo','bar') default 'foo') engine=csv; +ERROR HY000: Can't create table 'test.t1' (errno: -1) +create table t1(a enum('foo','bar') default 'foo' not null) engine=csv; +insert into t1 values(); +select * from t1; +a +foo +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 5e18e1273c0..c8472a128fe 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -42,20 +42,20 @@ show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show fields from mysql.general_log; Field Type Null Key Default Extra event_time timestamp NO CURRENT_TIMESTAMP -user_host mediumtext YES NULL -thread_id int(11) YES NULL -server_id int(11) YES NULL -command_type varchar(64) YES NULL -argument mediumtext YES NULL +user_host mediumtext NO NULL +thread_id int(11) NO NULL +server_id int(11) NO NULL +command_type varchar(64) NO NULL +argument mediumtext NO NULL show create table mysql.slow_log; Table Create Table slow_log CREATE TABLE `slow_log` ( @@ -65,10 +65,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show fields from mysql.slow_log; @@ -79,10 +79,10 @@ query_time time NO NULL lock_time time NO NULL rows_sent int(11) NO NULL rows_examined int(11) NO NULL -db varchar(512) YES NULL -last_insert_id int(11) YES NULL -insert_id int(11) YES NULL -server_id int(11) YES NULL +db varchar(512) NO NULL +last_insert_id int(11) NO NULL +insert_id int(11) NO NULL +server_id int(11) NO NULL sql_text mediumtext NO NULL flush logs; flush tables; @@ -141,11 +141,11 @@ show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show create table mysql.slow_log; Table Create Table @@ -156,10 +156,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' alter table mysql.general_log engine=myisam; @@ -168,11 +168,11 @@ show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' show create table mysql.slow_log; Table Create Table @@ -183,10 +183,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' set global general_log='ON'; @@ -241,11 +241,11 @@ use mysql; CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -`user_host` mediumtext, -`thread_id` int(11) DEFAULT NULL, -`server_id` int(11) DEFAULT NULL, -`command_type` varchar(64) DEFAULT NULL, -`argument` mediumtext +`user_host` mediumtext NOT NULL, +`thread_id` int(11) NOT NULL, +`server_id` int(11) NOT NULL, +`command_type` varchar(64) NOT NULL, +`argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; CREATE TABLE `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP @@ -255,10 +255,10 @@ ON UPDATE CURRENT_TIMESTAMP, `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, -`db` varchar(512) DEFAULT NULL, -`last_insert_id` int(11) DEFAULT NULL, -`insert_id` int(11) DEFAULT NULL, -`server_id` int(11) DEFAULT NULL, +`db` varchar(512) NOT NULL, +`last_insert_id` int(11) NOT NULL, +`insert_id` int(11) NOT NULL, +`server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; set global general_log='ON'; @@ -399,9 +399,9 @@ My own slow query sleep(2) My own slow query 0 SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text seq -START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 2 -START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 3 -START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 4 +START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2 +START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 +START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 SET GLOBAL slow_query_log = 0; SET SESSION long_query_time =@old_long_query_time; FLUSH LOGS; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 7696afdf06d..4d925886db0 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -240,11 +240,11 @@ show create table general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show create table slow_log; Table Create Table @@ -255,10 +255,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show tables; diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 5c877557dfc..6c83fbfdc9c 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1322,7 +1322,7 @@ drop table if exists t1,t2,t3,t4; DROP TABLE IF EXISTS bug13894; --enable_warnings -CREATE TABLE bug13894 ( val integer ) ENGINE = CSV; +CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV; INSERT INTO bug13894 VALUES (5); INSERT INTO bug13894 VALUES (10); INSERT INTO bug13894 VALUES (11); @@ -1340,7 +1340,7 @@ DROP TABLE bug13894; DROP TABLE IF EXISTS bug14672; --enable_warnings -CREATE TABLE bug14672 (c1 integer) engine = CSV; +CREATE TABLE bug14672 (c1 integer not null) engine = CSV; INSERT INTO bug14672 VALUES (1), (2), (3); SELECT * FROM bug14672; DELETE FROM bug14672 WHERE c1 = 2; @@ -1357,7 +1357,7 @@ DROP TABLE bug14672; # Test CONCURRENT INSERT (5.1) # -CREATE TABLE test_concurrent_insert ( val integer ) ENGINE = CSV; +CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV; connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -1393,7 +1393,7 @@ DROP TABLE test_concurrent_insert; # Check that repair on the newly created table works fine -CREATE TABLE test_repair_table ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV; CHECK TABLE test_repair_table; REPAIR TABLE test_repair_table; @@ -1405,7 +1405,7 @@ DROP TABLE test_repair_table; # restore the meta-file # -CREATE TABLE test_repair_table2 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV; --remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table2.CSM # Should give a warning and perform autorepair. We also disable ps-protocol @@ -1423,7 +1423,7 @@ DROP TABLE test_repair_table2; # Corrupt csv file and see if we can repair it -CREATE TABLE test_repair_table3 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV; --remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV --write_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV "1" @@ -1517,7 +1517,7 @@ DROP TABLE test_repair_table5; # BUG#13406 - incorrect amount of "records deleted" # -create table t1 (a int) engine=csv; +create table t1 (a int not null) engine=csv; insert t1 values (1); --enable_info delete from t1; # delete_row @@ -1549,7 +1549,7 @@ drop table t1; # whole alter table code is being tested all around the test suite already. # -create table t1 (v varchar(32)); +create table t1 (v varchar(32) not null); insert into t1 values ('def'),('abc'),('hij'),('3r4f'); select * from t1; # Fast alter, no copy performed @@ -1583,8 +1583,8 @@ drop table t1; # resulted in scanning through deleted memory and we were geting a crash. # that's why we need two tables in the bugtest -create table bug15205 (val int(11) default null) engine=csv; -create table bug15205_2 (val int(11) default null) engine=csv; +create table bug15205 (val int(11) not null) engine=csv; +create table bug15205_2 (val int(11) not null) engine=csv; --remove_file $MYSQLTEST_VARDIR/master-data/test/bug15205.CSV # system error (can't open the datafile) --replace_result $MYSQLTEST_VARDIR . master-data/ '' @@ -1604,8 +1604,8 @@ drop table bug15205_2; # set names latin1; create table t1 ( - c varchar(1), - name varchar(64) + c varchar(1) not null, + name varchar(64) not null ) character set latin1 engine=csv; insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE'); insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE'); @@ -1623,9 +1623,9 @@ drop table t1; # Bug#22080 "CHECK fails to identify some corruption" # -create table bug22080_1 (id int,string varchar(64)) Engine=CSV; -create table bug22080_2 (id int,string varchar(64)) Engine=CSV; -create table bug22080_3 (id int,string varchar(64)) Engine=CSV; +create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV; insert into bug22080_1 values(1,'string'); insert into bug22080_1 values(2,'string'); insert into bug22080_1 values(3,'string'); @@ -1655,7 +1655,7 @@ drop tables bug22080_1,bug22080_2,bug22080_3; # # Testing float type # -create table float_test (id float,string varchar(64)) Engine=CSV; +create table float_test (id float not null,string varchar(64) not null) Engine=CSV; insert into float_test values(1.0,'string'); insert into float_test values(2.23,'serg.g'); insert into float_test values(0.03,'string'); @@ -1670,12 +1670,12 @@ drop table float_test; # CREATE TABLE `bug21328` ( - `col1` int(11) DEFAULT NULL, - `col2` int(11) DEFAULT NULL, - `col3` int(11) DEFAULT NULL + `col1` int(11) NOT NULL, + `col2` int(11) NOT NULL, + `col3` int(11) NOT NULL ) ENGINE=CSV; -insert into bug21328 values (1,NULL,NULL); +insert into bug21328 values (1,0,0); alter table bug21328 engine=myisam; drop table bug21328; @@ -1683,7 +1683,7 @@ drop table bug21328; # BUG#28971 - ALTER TABLE followed by UPDATE for a CSV table make server # crash # -create table t1(a blob, b int) engine=csv; +create table t1(a blob not null, b int not null) engine=csv; insert into t1 values('a', 1); flush tables; update t1 set b=2; @@ -1693,13 +1693,13 @@ drop table t1; # # Bug #29353: negative values # -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values(-1), (-123.34), (2), (-23); select * from t1; check table t1; drop table t1; -create table t1(a int, b int) engine=csv; +create table t1(a int not null, b int not null) engine=csv; --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV --write_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV 1, 1E-2 @@ -1717,7 +1717,7 @@ drop table t1; # # Bug #29411: deleting from a csv table leads to the table corruption # -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values (0), (1), (2); delete from t1 limit 2; check table t1; @@ -1727,4 +1727,43 @@ check table t1; select * from t1; drop table t1; +# +# Bug #31473: does not work with NULL value in datetime field +# +create table t1(a datetime not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a set('foo','bar') not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a varchar(32) not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a int not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a blob not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a bit(1) not null) engine=csv; +insert into t1 values(); +select BIN(a) from t1; +drop table t1; +# We prevent creation of table with nullable ENUM +--error ER_CANT_CREATE_TABLE +create table t1(a enum('foo','bar') default null) engine=csv; +--error ER_CANT_CREATE_TABLE +create table t1(a enum('foo','bar') default 'foo') engine=csv; +# Enum columns must be specified as NOT NULL +create table t1(a enum('foo','bar') default 'foo' not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 89c7c255554..06011b85495 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -253,11 +253,11 @@ use mysql; CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; CREATE TABLE `slow_log` ( @@ -268,10 +268,10 @@ CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index f7b23e4e7ae..4f38ab83f04 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -66,7 +66,7 @@ CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL -- Create general_log if CSV is enabled. -SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT, thread_id INTEGER, server_id INTEGER, command_type VARCHAR(64), argument MEDIUMTEXT) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0'); +SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id INTEGER NOT NULL, server_id INTEGER NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0'); PREPARE stmt FROM @str; EXECUTE stmt; @@ -74,7 +74,7 @@ DROP PREPARE stmt; -- Create slow_log if CSV is enabled. -SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512), last_insert_id INTEGER, insert_id INTEGER, server_id INTEGER, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0'); +SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0'); PREPARE stmt FROM @str; EXECUTE stmt; diff --git a/storage/csv/ha_tina.cc b/storage/csv/ha_tina.cc index 9a7781e017d..6a87b8ecc72 100644 --- a/storage/csv/ha_tina.cc +++ b/storage/csv/ha_tina.cc @@ -471,22 +471,30 @@ int ha_tina::encode_quote(uchar *buf) { const char *ptr; const char *end_ptr; + const bool was_null= (*field)->is_null(); /* - CSV does not support nulls. Write quoted 0 to the buffer. In fact, - (*field)->val_str(&attribute,&attribute) would usually return 0 - in this case but we write it explicitly here. - Basically this is a safety check, as no one ensures that the - field content is cleaned up every time we use Field::set_null() - in the code. + CSV does not support nulls. ::create() prevents creation of a table + with nullable columns so if we encounter them here, there is a bug. + This may only occur if the frm was created by an older version of + mysqld which permitted table creation with nullable columns. */ - if ((*field)->is_null()) + DBUG_ASSERT(!(*field)->maybe_null()); + + /* + assistance for backwards compatibility in production builds. + note: this will not work for ENUM columns. + */ + if (was_null) { - buffer.append(STRING_WITH_LEN("\"0\",")); - continue; + (*field)->set_default(); + (*field)->set_notnull(); } (*field)->val_str(&attribute,&attribute); + + if (was_null) + (*field)->set_null(); if ((*field)->str_needs_quotes()) { @@ -1480,6 +1488,16 @@ int ha_tina::create(const char *name, TABLE *table_arg, File create_file; DBUG_ENTER("ha_tina::create"); + /* + check columns + */ + for (Field **field= table_arg->s->field; *field; field++) + { + if ((*field)->real_maybe_null()) + DBUG_RETURN(-1); + } + + if ((create_file= my_create(fn_format(name_buff, name, "", CSM_EXT, MY_REPLACE_EXT|MY_UNPACK_FILENAME), 0, O_RDWR | O_TRUNC,MYF(MY_WME))) < 0) -- cgit v1.2.1