- commits - lists.mariadb.org

[Commits] a0164e04f7e: MDEV-19334: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed.
by Varun 25 Apr '19

25 Apr '19

revision-id: a0164e04f7e9c83c9162fdcb76f4f3629ce83f8a (mariadb-10.1.38-134-ga0164e04f7e) parent(s): caa9023c9ed101acbcf6b9bd821a09daeb8271ee author: Varun Gupta committer: Varun Gupta timestamp: 2019-04-25 18:18:26 +0530 message: MDEV-19334: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed. Fixed the assert by making sure that not to use EITS if the column statistics was not allocated --- mysql-test/r/statistics.result | 14 ++++++++++++++ mysql-test/t/statistics.test | 15 +++++++++++++++ sql/sql_statistics.cc | 15 +++++++++++---- 3 files changed, 40 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index 295a9b34e49..135a0806bc3 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1735,4 +1735,18 @@ rename table t1 to t2, t3 to t4; ERROR 42S02: Table 'test.t3' doesn't exist drop table t1, mysql.table_stats; rename table test.table_stats to mysql.table_stats; +# +# MDEV-19334: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed. +# +create temporary table t1(a int); +insert into t1 values (1),(2),(3); +set use_stat_tables=preferably; +set @optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=4; +select * from t1 where a >= 2; +a +2 +3 +drop table t1; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test index e4f9870a622..7d2e7e898d3 100644 --- a/mysql-test/t/statistics.test +++ b/mysql-test/t/statistics.test @@ -809,4 +809,19 @@ rename table t1 to t2, t3 to t4; drop table t1, mysql.table_stats; rename table test.table_stats to mysql.table_stats; +--echo # +--echo # MDEV-19334: bool is_eits_usable(Field*): Assertion `field->table->stats_is_read' failed. +--echo # + +create temporary table t1(a int); +insert into t1 values (1),(2),(3); + +set use_stat_tables=preferably; +set @optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set optimizer_use_condition_selectivity=4; + +select * from t1 where a >= 2; +drop table t1; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; + set use_stat_tables=@save_use_stat_tables; diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index b435971a4d6..061622a2cd6 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -4067,6 +4067,14 @@ bool is_stat_table(const char *db, const char *table) bool is_eits_usable(Field *field) { + Column_statistics* col_stats= field->read_stats; + + // check if column_statistics was allocated for this field + if (!col_stats) + return false; + + DBUG_ASSERT(field->table->stats_is_read); + /* (1): checks if we have EITS statistics for a particular column (2): Don't use EITS for GEOMETRY columns @@ -4074,10 +4082,9 @@ bool is_eits_usable(Field *field) partition list of a table. We assume the selecticivity for such columns would be handled during partition pruning. */ - DBUG_ASSERT(field->table->stats_is_read); - Column_statistics* col_stats= field->read_stats; - return col_stats && !col_stats->no_stat_values_provided() && //(1) - field->type() != MYSQL_TYPE_GEOMETRY && //(2) + + return !col_stats->no_stat_values_provided() && //(1) + field->type() != MYSQL_TYPE_GEOMETRY && //(2) #ifdef WITH_PARTITION_STORAGE_ENGINE (!field->table->part_info || !field->table->part_info->field_in_partition_expr(field)) && //(3)

1 0

[Commits] 0222f7b3988: MDEV-17036: BULK with replace doesn't take the first parameter in account
by Oleksandr Byelkin 25 Apr '19

25 Apr '19

revision-id: 0222f7b3988496c7289321dc606967c888dc658e (mariadb-10.2.23-76-g0222f7b3988) parent(s): bc145193c164b895a52b943e73fff53952d48a60 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-25 13:43:31 +0200 message: MDEV-17036: BULK with replace doesn't take the first parameter in account INSERT and REPLACE served by the same function, so flags (and processing) should be the same. --- sql/sql_parse.cc | 3 ++- tests/mysql_client_test.c | 61 +++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 63 insertions(+), 1 deletion(-) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 146c4d2d02e..68a08dc2b3f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -609,7 +609,8 @@ void init_update_queries(void) CF_CAN_GENERATE_ROW_EVENTS | CF_OPTIMIZER_TRACE | CF_CAN_BE_EXPLAINED | - CF_INSERTS_DATA | CF_SP_BULK_SAFE; + CF_INSERTS_DATA | CF_SP_BULK_SAFE | + CF_SP_BULK_OPTIMIZED; sql_command_flags[SQLCOM_REPLACE_SELECT]= CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE | CF_CAN_GENERATE_ROW_EVENTS | CF_OPTIMIZER_TRACE | diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index be16db0fc9e..365229e126f 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -19743,6 +19743,66 @@ static void test_bulk_delete() rc= mysql_query(mysql, "DROP TABLE t1"); myquery(rc); } + +static void test_bulk_replace() +{ + int rc; + MYSQL_STMT *stmt; + MYSQL_BIND bind[2]; + MYSQL_ROW row; + int i, + id[]= {1, 2, 3, 4}, + val[]= {1, 1, 1, 1}, + count= sizeof(id)/sizeof(id[0]); + MYSQL_RES *result; + + rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1"); + myquery(rc); + rc= mysql_query(mysql, "CREATE TABLE t1 (id int not null primary key, active int)"); + myquery(rc); + rc= mysql_query(mysql, "insert into t1 values (1, 0), (2, 0), (3, 0)"); + myquery(rc); + verify_affected_rows(3); + + stmt= mysql_stmt_init(mysql); + rc= mysql_stmt_prepare(stmt, "replace into t1 (id, active) values (?, ?)", -1); + check_execute(stmt, rc); + + memset(bind, 0, sizeof(bind)); + bind[0].buffer_type = MYSQL_TYPE_LONG; + bind[0].buffer = (void *)id; + bind[0].buffer_length = 0; + bind[1].buffer_type = MYSQL_TYPE_LONG; + bind[1].buffer = (void *)val; + bind[1].buffer_length = 0; + + mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, (void*)&count); + rc= mysql_stmt_bind_param(stmt, bind); + check_execute(stmt, rc); + + rc= mysql_stmt_execute(stmt); + check_execute(stmt, rc); + + mysql_stmt_close(stmt); + + rc= mysql_query(mysql, "SELECT active FROM t1"); + myquery(rc); + + result= mysql_store_result(mysql); + mytest(result); + + i= 0; + while ((row= mysql_fetch_row(result))) + { + i++; + DIE_IF(atoi(row[0]) != 1); + } + DIE_IF(i != 4); + mysql_free_result(result); + + rc= mysql_query(mysql, "DROP TABLE t1"); + myquery(rc); +} #endif static struct my_tests_st my_tests[]= { @@ -20026,6 +20086,7 @@ static struct my_tests_st my_tests[]= { { "test_mdev12579", test_mdev12579 }, #ifndef EMBEDDED_LIBRARY { "test_bulk_delete", test_bulk_delete }, + { "test_bulk_replace", test_bulk_replace }, #endif { 0, 0 } };

1 0

[Commits] 2736a0f1763: MDEV-17260: Memory leaks in mysqlbinlog
by sujatha 25 Apr '19

25 Apr '19

revision-id: 2736a0f1763d2554f346e873b16524e76a2a0692 (mariadb-10.2.23-76-g2736a0f1763) parent(s): bc145193c164b895a52b943e73fff53952d48a60 author: Sujatha Sivakumar committer: Sujatha Sivakumar timestamp: 2019-04-25 12:18:22 +0530 message: MDEV-17260: Memory leaks in mysqlbinlog Problem: ======== The mysqlbinlog tool is leaking memory, causing failures in various tests when compiling and testing with AddressSanitizer or LeakSanitizer like this: cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN:BOOL=ON /path/to/source make -j$(nproc) cd mysql-test ASAN_OPTIONS=abort_on_error=1 ./mtr --parallel=auto Analysis: ========= Two types of leaks were observed during above execution. 1) Leak in Log_event::read_log_event(char const*, unsigned int, char const**, Format_description_log_event const*, char) File: sql/log_event.cc:2150 For all row based replication events the memory which is allocated during read_log_event is not freed after the event is processed. The event specific memory has to be retained only when flashback option is enabled with mysqlbinlog tool. In this case all the events are retained till the end statement is received and they are processed in reverse order and they are destroyed. But in the existing code all events are retained irrespective of flashback mode. Hence the memory leaks are observed. 2) read_remote_annotate_event(unsigned char*, unsigned long, char const**) File: client/mysqlbinlog.cc:194 In general the Annotate event is not printed immediately because all subsequent rbr-events can be filtered away. Instead it will be printed together with the first not filtered away Table map or the last rbr will be processed. While reading remote annotate events memory is allocated for event buffer and event's temp_buf is made to point to the allocated buffer as shown below. The TRUE flag is used for doing proper cleanup using free_temp_buf(). i.e at the time of deletion of annotate event its destructor takes care of clearing the temp_buf. /* Ensure the event->temp_buf is pointing to the allocated buffer. (TRUE = free temp_buf on the event deletion) */ event->register_temp_buf((char*)event_buf, TRUE); But existing code does the following when it receives a remote annotate_event. if (remote_opt) ev->temp_buf= 0; That is code immediately sets temp_buf=0, because of which free_temp_buf() call will return empty handed as it has lost the reference to the allocated temporary buffer. This results in memory leak Fix: ==== 1) If not in flashback mode, destroy the memory for events once they are processed. 2) Remove the ev->temp_buf=0 code for remote option. Let the proper cleanup to be done as part of free_temp_buf(). --- client/mysqlbinlog.cc | 9 +++------ mysql-test/suite/binlog/r/flashback.result | 18 ++++++++++++++++++ mysql-test/suite/binlog/t/flashback.test | 30 +++++++++++++++++++++++++++++- 3 files changed, 50 insertions(+), 7 deletions(-) diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index cfc05cbf794..3dad4fef67b 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -227,8 +227,7 @@ void print_annotate_event(PRINT_EVENT_INFO *print_event_info) if (annotate_event) { annotate_event->print(result_file, print_event_info); - delete annotate_event; // the event should not be printed more than once - annotate_event= 0; + free_annotate_event(); } } @@ -1465,7 +1464,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1478,7 +1477,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Old_rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1539,8 +1538,6 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, } } - if (remote_opt) - ev->temp_buf= 0; if (destroy_evt) /* destroy it later if not set (ignored table map) */ delete ev; } diff --git a/mysql-test/suite/binlog/r/flashback.result b/mysql-test/suite/binlog/r/flashback.result index c96eaebe838..0d189b735a3 100644 --- a/mysql-test/suite/binlog/r/flashback.result +++ b/mysql-test/suite/binlog/r/flashback.result @@ -674,6 +674,24 @@ world.city 563256876 DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; +# < CASE 7 > +# Test Case for MDEV-17260 +# +RESET MASTER; +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +# 6- Rows must be present +SELECT COUNT(*) FROM t1; +COUNT(*) +6 +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +# 0- Rows must be present +include/assert.inc [Table t1 should have 0 rows.] +# 6- Rows must be present upon restoring from flashback +include/assert.inc [Table t1 should have six rows.] +DROP TABLE t1; SET binlog_format=statement; Warnings: Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test index 3fc8c44c60c..9782fa4ec83 100644 --- a/mysql-test/suite/binlog/t/flashback.test +++ b/mysql-test/suite/binlog/t/flashback.test @@ -335,8 +335,36 @@ DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; -## Clear +--echo # < CASE 7 > +--echo # Test Case for MDEV-17260 +--echo # + +RESET MASTER; + +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +--echo # 6- Rows must be present +SELECT COUNT(*) FROM t1; +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +--echo # 0- Rows must be present +--let $assert_cond= COUNT(*) = 0 FROM t1 +--let $assert_text= Table t1 should have 0 rows. +--source include/assert.inc + +--exec $MYSQL_BINLOG -vv -B --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql +--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;" + +--echo # 6- Rows must be present upon restoring from flashback +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Table t1 should have six rows. +--source include/assert.inc + +DROP TABLE t1; + +## Clear SET binlog_format=statement; --error ER_FLASHBACK_NOT_SUPPORTED SET GLOBAL binlog_format=statement;

1 0

[Commits] cb8d888c42a: MDEV-17260: Memory leaks in mysqlbinlog
by sujatha 24 Apr '19

24 Apr '19

revision-id: cb8d888c42aa9504db2df686f0e963b99079e287 (mariadb-5.5.63-20-gcb8d888c42a) parent(s): e5aa8ea52552759453fdd86eeab0007df1781b62 author: Sujatha Sivakumar committer: Sujatha Sivakumar timestamp: 2019-04-24 11:40:52 +0530 message: MDEV-17260: Memory leaks in mysqlbinlog Problem: ======== The mysqlbinlog tool is leaking memory, causing failures in various tests when compiling and testing with AddressSanitizer or LeakSanitizer like this: cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN:BOOL=ON /path/to/source make -j$(nproc) cd mysql-test ASAN_OPTIONS=abort_on_error=1 ./mtr --parallel=auto rpl.rpl_row_mysqlbinlog CURRENT_TEST: rpl.rpl_row_mysqlbinlog Direct leak of 112 byte(s) in 1 object(s) allocated from: #0 0x4eff87 in __interceptor_malloc (/dev/shm/5.5/client/mysqlbinlog+0x4eff87) #1 0x60eaab in my_malloc /mariadb/5.5/mysys/my_malloc.c:41:10 #2 0x5300dd in Log_event::read_log_event(char const*, unsigned int, char const**, Format_description_log_event const*, char) /mariadb/5.5/sql/log_event.cc:1568: #3 0x564a9c in dump_remote_log_entries(st_print_event_info*, char const*) /mariadb/5.5/client/mysqlbinlog.cc:1978:17 Analysis: ======== 'mysqlbinlog' tool is being used to read binary log events from a remote server. While reading binary log, if a fake rotate event is found following actions are taken. If 'to-last-log' option is specified, then fake rotate event is processed. In the absence of 'to-last-log' skip the fake rotate event. In this skipped case the fake rotate event object is not getting cleaned up resulting in memory leak. Fix: === Cleanup the fake rotate event. This issues is already fixed in MariaDB 10.0.23 and higher versions as part of commit c3018b0ff4fb02c029787d03867adf0530607bab --- client/mysqlbinlog.cc | 2 ++ 1 file changed, 2 insertions(+) diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index 250dc609891..1a11e3e697b 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -2020,6 +2020,7 @@ static Exit_status dump_remote_log_entries(PRINT_EVENT_INFO *print_event_info, if ((rev->ident_len != logname_len) || memcmp(rev->new_log_ident, logname, logname_len)) { + delete ev; DBUG_RETURN(OK_CONTINUE); } /* @@ -2028,6 +2029,7 @@ static Exit_status dump_remote_log_entries(PRINT_EVENT_INFO *print_event_info, log. If we are running with to_last_remote_log, we print it, because it serves as a useful marker between binlogs then. */ + delete ev; continue; } len= 1; // fake Rotate, so don't increment old_off

2 1

[Commits] 3615a604f60: MDEV-16240: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase
by Oleksandr Byelkin 24 Apr '19

24 Apr '19

revision-id: 3615a604f60d19390c5dd22712c77386bc7743dc (mariadb-10.2.23-75-g3615a604f60) parent(s): 1f1a61cfc41a01ffa65d568eebdc037a54b5c463 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-24 16:17:00 +0200 message: MDEV-16240: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase Set table in row ID position mode before using this function. --- mysql-test/r/multi_update_innodb.result | 40 ++++++++++++++++++++++ mysql-test/t/multi_update_innodb.test | 49 +++++++++++++++++++++++++++ sql/handler.cc | 11 ++++-- sql/item_subselect.cc | 9 +++-- sql/item_subselect.h | 2 +- sql/log_event.cc | 9 +++++ sql/sql_insert.cc | 60 +++++++++++++++++++++++++++------ sql/sql_load.cc | 9 +++++ sql/sql_select.cc | 16 +++++++++ sql/sql_update.cc | 8 +++++ 10 files changed, 197 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result index 5890fd24f5f..294ebfcebdf 100644 --- a/mysql-test/r/multi_update_innodb.result +++ b/mysql-test/r/multi_update_innodb.result @@ -151,3 +151,43 @@ create table t2 like t1; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; +# +# MDEV-16240: Assertion `0' failed in +# row_sel_convert_mysql_key_to_innobase +# +SET @save_sql_mode=@@sql_mode; +set sql_mode=''; +CREATE TABLE `t3` ( +`f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), +`f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), +`f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', +`pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +`f4` datetime DEFAULT current_timestamp(), +PRIMARY KEY (`pk`), +UNIQUE KEY `f2k` (`f2`), +KEY `f4k` (`f4`) +) ENGINE=InnoDB; +INSERT INTO `t3` VALUES ('2018-05-18 15:08:07','2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00'),('0000-00-00 00:00:00','0000-00-00 00:00:00','1999-12-31 23:00:00','2002-07-03 23:04:40','0000-00-00 00:00:00'); +CREATE VIEW `v1` AS +SELECT `t3`.`pk` AS `pk`, +`t3`.`f3` AS `f3`, +`t3`.`f4` AS `f4`, +`t3`.`f2` AS `f2`, +`t3`.`f1` AS `f1` +FROM `t3`; +CREATE TABLE `t4` ( +`f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), +`f3` timestamp NULL DEFAULT NULL, +`f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), +`pk` int(11) NOT NULL, +`f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), +PRIMARY KEY (`pk`) +) ENGINE=InnoDB; +INSERT INTO `t4` VALUES ('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,1,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,3,'2018-05-18 15:08:06'),('0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,1976,'0000-00-00 00:00:00'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2000,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2001,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2002,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2003,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2004,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2005,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2018,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2019,'2018-05-1 8 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2024,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','1999-12-31 23:00:00',2025,'2018-05-18 15:08:06'),('0000-00-00 00:00:00',NULL,'2018-05-18 15:08:06',2026,'2018-05-18 15:08:06'),('2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00',2027,'0000-00-00 00:00:00'); +UPDATE `v1` t1, `t4` t2 +SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; +ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'f2k' +DROP VIEW v1; +DROP TABLE t3,t4; +SET @@sql_mode=@save_sql_mode; +# End of 10.2 tests diff --git a/mysql-test/t/multi_update_innodb.test b/mysql-test/t/multi_update_innodb.test index f5f8f91edb8..2e46ee06d4d 100644 --- a/mysql-test/t/multi_update_innodb.test +++ b/mysql-test/t/multi_update_innodb.test @@ -173,3 +173,52 @@ delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; +--echo # +--echo # MDEV-16240: Assertion `0' failed in +--echo # row_sel_convert_mysql_key_to_innobase +--echo # + +SET @save_sql_mode=@@sql_mode; +set sql_mode=''; + +CREATE TABLE `t3` ( + `f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), + `f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), + `f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', + `pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `f4` datetime DEFAULT current_timestamp(), + PRIMARY KEY (`pk`), + UNIQUE KEY `f2k` (`f2`), + KEY `f4k` (`f4`) + ) ENGINE=InnoDB; + +INSERT INTO `t3` VALUES ('2018-05-18 15:08:07','2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00'),('0000-00-00 00:00:00','0000-00-00 00:00:00','1999-12-31 23:00:00','2002-07-03 23:04:40','0000-00-00 00:00:00'); + +CREATE VIEW `v1` AS +SELECT `t3`.`pk` AS `pk`, + `t3`.`f3` AS `f3`, + `t3`.`f4` AS `f4`, + `t3`.`f2` AS `f2`, + `t3`.`f1` AS `f1` +FROM `t3`; + +CREATE TABLE `t4` ( + `f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `f3` timestamp NULL DEFAULT NULL, + `f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), + `pk` int(11) NOT NULL, + `f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY (`pk`) +) ENGINE=InnoDB; + +INSERT INTO `t4` VALUES ('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,1,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,3,'2018-05-18 15:08:06'),('0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,1976,'0000-00-00 00:00:00'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2000,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2001,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2002,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2003,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2004,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2005,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2018,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2019,'2018-05-1 8 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2024,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','1999-12-31 23:00:00',2025,'2018-05-18 15:08:06'),('0000-00-00 00:00:00',NULL,'2018-05-18 15:08:06',2026,'2018-05-18 15:08:06'),('2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00',2027,'0000-00-00 00:00:00'); + +--error ER_DUP_ENTRY +UPDATE `v1` t1, `t4` t2 +SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; + +DROP VIEW v1; +DROP TABLE t3,t4; +SET @@sql_mode=@save_sql_mode; + +--echo # End of 10.2 tests diff --git a/sql/handler.cc b/sql/handler.cc index ae63640ae5c..ec966389d5d 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -2648,8 +2648,7 @@ int handler::ha_rnd_pos(uchar *buf, uchar *pos) DBUG_ENTER("handler::ha_rnd_pos"); DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK); - /* TODO: Find out how to solve ha_rnd_pos when finding duplicate update. */ - /* DBUG_ASSERT(inited == RND); */ + DBUG_ASSERT(inited == RND); TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_FETCH_ROW, MAX_KEY, 0, { result= rnd_pos(buf, pos); }) @@ -3307,6 +3306,10 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, ulonglong nr; int error; MY_BITMAP *old_read_set; + bool rnd_inited= (inited == RND); + + if (rnd_inited && ha_rnd_end()) + return; old_read_set= table->prepare_for_keyread(table->s->next_number_index); @@ -3316,6 +3319,8 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, DBUG_ASSERT(0); (void) extra(HA_EXTRA_NO_KEYREAD); *first_value= ULONGLONG_MAX; + if (rnd_inited && ha_rnd_init_with_error(0)) + DBUG_ASSERT(0); return; } @@ -3362,6 +3367,8 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, ha_index_end(); table->restore_column_maps_after_keyread(old_read_set); *first_value= nr; + if (rnd_inited && ha_rnd_init_with_error(0)) + DBUG_ASSERT(0); return; } diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 8a9dd083911..8cff8f3a5c4 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -5855,12 +5855,16 @@ Ordered_key::cmp_keys_by_row_data_and_rownum(Ordered_key *key, } -void Ordered_key::sort_keys() +bool Ordered_key::sort_keys() { + if (tbl->file->ha_rnd_init_with_error(0)) + return TRUE; my_qsort2(key_buff, (size_t) key_buff_elements, sizeof(rownum_t), (qsort2_cmp) &cmp_keys_by_row_data_and_rownum, (void*) this); /* Invalidate the current row position. */ cur_key_idx= HA_POS_ERROR; + tbl->file->ha_rnd_end(); + return FALSE; } @@ -6313,7 +6317,8 @@ subselect_rowid_merge_engine::init(MY_BITMAP *non_null_key_parts, /* Sort the keys in each of the indexes. */ for (uint i= 0; i < merge_keys_count; i++) - merge_keys[i]->sort_keys(); + if (merge_keys[i]->sort_keys()) + return TRUE; if (init_queue(&pq, merge_keys_count, 0, FALSE, subselect_rowid_merge_engine::cmp_keys_by_cur_rownum, NULL, diff --git a/sql/item_subselect.h b/sql/item_subselect.h index bd6a1bdc498..5366c759795 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -1287,7 +1287,7 @@ class Ordered_key : public Sql_alloc ++cur_key_idx; } - void sort_keys(); + bool sort_keys(); double null_selectivity(); /* diff --git a/sql/log_event.cc b/sql/log_event.cc index 8990e1953b6..c17c7ef6e06 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -12845,6 +12845,14 @@ Rows_log_event::write_row(rpl_group_info *rgi, if (table->file->ha_table_flags() & HA_DUPLICATE_POS) { DBUG_PRINT("info",("Locating offending record using rnd_pos()")); + error= table->file->ha_rnd_init(0); + if (error) + { + DBUG_PRINT("info",("rnd_init() returns error %d",error)); + table->file->print_error(error, MYF(0)); + DBUG_RETURN(error); + } + error= table->file->ha_rnd_pos(table->record[1], table->file->dup_ref); if (error) { @@ -12854,6 +12862,7 @@ Rows_log_event::write_row(rpl_group_info *rgi, table->file->print_error(error, MYF(0)); DBUG_RETURN(error); } + table->file->ha_rnd_end(); } else { diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index c623336fdba..4005153cb64 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -881,7 +881,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, #endif /* EMBEDDED_LIBRARY */ { if (duplic != DUP_ERROR || ignore) + { table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && + table->file->ha_rnd_init_with_error(0)) + goto abort; + } /** This is a simple check for the case when the table has a trigger that reads from it, or when the statement invokes a stored function @@ -942,7 +947,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, { DBUG_PRINT("info", ("iteration %llu", iteration)); if (iteration && bulk_parameters_set(thd)) - goto abort; + { + error= 1; + goto values_loop_end; + } while ((values= its++)) { @@ -1094,7 +1102,11 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, error=1; } if (duplic != DUP_ERROR || ignore) + { table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS) + table->file->ha_rnd_end(); + } transactional_table= table->file->has_transactions(); @@ -1705,6 +1717,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) goto err; if (table->file->ha_table_flags() & HA_DUPLICATE_POS) { + DBUG_ASSERT(table->file->inited == handler::RND); if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref)) goto err; } @@ -3177,6 +3190,9 @@ bool Delayed_insert::handle_inserts(void) max_rows= ULONG_MAX; // Do as much as possible } + if (table->file->ha_rnd_init_with_error(0)) + goto err; + /* We can't use row caching when using the binary log because if we get a crash, then binary log will contain rows that are not yet @@ -3352,6 +3368,8 @@ bool Delayed_insert::handle_inserts(void) } } + table->file->ha_rnd_end(); + if (WSREP((&thd))) thd_proc_info(&thd, "insert done"); else @@ -3649,7 +3667,12 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) thd->cuted_fields=0; if (info.ignore || info.handle_duplicates != DUP_ERROR) + { table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && + table->file->ha_rnd_init_with_error(0)) + DBUG_RETURN(1); + } if (info.handle_duplicates == DUP_REPLACE && (!table->triggers || !table->triggers->has_delete_triggers())) table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE); @@ -3818,6 +3841,9 @@ bool select_insert::prepare_eof() if (!error && thd->is_error()) error= thd->get_stmt_da()->sql_errno(); + if (info.ignore || info.handle_duplicates != DUP_ERROR) + if (table->file->ha_table_flags() & HA_DUPLICATE_POS) + table->file->ha_rnd_end(); table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); @@ -3929,6 +3955,11 @@ void select_insert::abort_result_set() { if (thd->locked_tables_mode <= LTM_LOCK_TABLES) table->file->ha_end_bulk_insert(); + if (table->file->inited) + table->file->ha_rnd_end(); + table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); + table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); + /* If at least one row has been inserted/modified and will stay in the table (the table doesn't have transactions) we must write to @@ -4341,7 +4372,12 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) restore_record(table,s->default_values); // Get empty record thd->cuted_fields=0; if (info.ignore || info.handle_duplicates != DUP_ERROR) + { table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && + table->file->ha_rnd_init_with_error(0)) + DBUG_RETURN(1); + } if (info.handle_duplicates == DUP_REPLACE && (!table->triggers || !table->triggers->has_delete_triggers())) table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE); @@ -4522,9 +4558,6 @@ bool select_create::send_eof() */ exit_done= 1; // Avoid double calls - table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); - table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); - send_ok_packet(); if (m_plock) @@ -4600,13 +4633,6 @@ void select_create::abort_result_set() thd->locked_tables_list.unlock_locked_table(thd, create_info->mdl_ticket); } - if (m_plock) - { - mysql_unlock_tables(thd, *m_plock); - *m_plock= NULL; - m_plock= NULL; - } - if (table) { bool tmp_table= table->s->tmp_table; @@ -4617,9 +4643,21 @@ void select_create::abort_result_set() thd->restore_tmp_table_share(saved_tmp_table_share); } + if (table->file->inited && + (info.ignore || info.handle_duplicates != DUP_ERROR) && + (table->file->ha_table_flags() & HA_DUPLICATE_POS)) + table->file->ha_rnd_end(); table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); table->auto_increment_field_not_null= FALSE; + + if (m_plock) + { + mysql_unlock_tables(thd, *m_plock); + *m_plock= NULL; + m_plock= NULL; + } + drop_open_table(thd, table, create_table->db, create_table->table_name); table=0; // Safety if (thd->log_current_statement && mysql_bin_log.is_open()) diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 49558f8b694..8994b436cab 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -660,6 +660,12 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, thd->abort_on_warning= !ignore && thd->is_strict_mode(); + if ((table_list->table->file->ha_table_flags() & HA_DUPLICATE_POS) && + (error= table_list->table->file->ha_rnd_init(0))) + { + table->file->print_error(error, MYF(0)); + goto err; + } thd_progress_init(thd, 2); if (table_list->table->validate_default_values_of_unset_fields(thd)) { @@ -679,6 +685,9 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, set_fields, set_values, read_info, *ex->enclosed, skip_lines, ignore); + if (table_list->table->file->ha_table_flags() & HA_DUPLICATE_POS) + table_list->table->file->ha_rnd_end(); + thd_proc_info(thd, "End bulk insert"); if (!error) thd_progress_next_stage(thd); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3c1cea6be51..291560dc098 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20318,6 +20318,15 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), table->file->print_error(error,MYF(0)); /* purecov: inspected */ DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } + /* Prepare table for random positioning */ + bool rnd_inited= (table->file->inited == handler::RND); + if (!rnd_inited && + ((error= table->file->ha_index_end()) || + (error= table->file->ha_rnd_init(0)))) + { + table->file->print_error(error, MYF(0)); + DBUG_RETURN(NESTED_LOOP_ERROR); + } if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref)) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ @@ -20331,6 +20340,13 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), table->file->print_error(error,MYF(0)); /* purecov: inspected */ DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } + if (!rnd_inited && + ((error= table->file->ha_rnd_end()) || + (error= table->file->ha_index_init(0, 0)))) + { + table->file->print_error(error, MYF(0)); + DBUG_RETURN(NESTED_LOOP_ERROR); + } } if (join->thd->check_killed()) { diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 11ffa684216..f5284350094 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -211,6 +211,14 @@ static void prepare_record_for_error_message(int error, TABLE *table) bitmap_union(table->read_set, &unique_map); /* Tell the engine about the new set. */ table->file->column_bitmaps_signal(); + + if ((error= table->file->ha_index_or_rnd_end()) || + (error= table->file->ha_rnd_init(0))) + { + table->file->print_error(error, MYF(0)); + DBUG_VOID_RETURN; + } + /* Read record that is identified by table->file->ref. */ (void) table->file->ha_rnd_pos(table->record[1], table->file->ref); /* Copy the newly read columns into the new record. */

1 0

[Commits] 05b1082aba0: added check
by Oleksandr Byelkin 24 Apr '19

24 Apr '19

revision-id: 05b1082aba0593e308c2feb5a81c874027d59b26 (mariadb-10.2.23-76-g05b1082aba0) parent(s): 386882b70328d12cc165fa8051066b93e315e15c author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-24 16:16:48 +0200 message: added check --- sql/handler.cc | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/sql/handler.cc b/sql/handler.cc index 67971cad872..ec966389d5d 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3319,8 +3319,8 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, DBUG_ASSERT(0); (void) extra(HA_EXTRA_NO_KEYREAD); *first_value= ULONGLONG_MAX; - if (rnd_inited) - ha_rnd_init_with_error(0); + if (rnd_inited && ha_rnd_init_with_error(0)) + DBUG_ASSERT(0); return; } @@ -3367,8 +3367,8 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, ha_index_end(); table->restore_column_maps_after_keyread(old_read_set); *first_value= nr; - if (rnd_inited) - ha_rnd_init_with_error(0); + if (rnd_inited && ha_rnd_init_with_error(0)) + DBUG_ASSERT(0); return; }

1 0

[Commits] a21374c8453: MDEV-19139: pushdown condition with Item_func_set_user_var
by Galina 24 Apr '19

24 Apr '19

revision-id: a21374c84531ded0e947d7c8f1df7473a8bd7d86 (mariadb-10.2.23-75-ga21374c8453) parent(s): 1f1a61cfc41a01ffa65d568eebdc037a54b5c463 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-04-24 12:47:40 +0300 message: MDEV-19139: pushdown condition with Item_func_set_user_var The bug occurs because Item_func_set_user var is allowed to be pushed into materialized derived table/view. To fix it excl_dep_on_table() as added to Item_func_set_user_var class to prevent pushdown. --- mysql-test/r/derived_cond_pushdown.result | 49 +++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 14 +++++++++ sql/item_func.h | 1 + 3 files changed, 64 insertions(+) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 00acdf241a6..d040aaf609d 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10501,4 +10501,53 @@ a b max_d c 1 2 3 1 5 6 1 5 DROP TABLE t1,t2; +# +# MDEV-19139: pushdown condition with Item_func_set_user_var +# +CREATE TABLE t1 (a INT, b INT); +CREATE VIEW v1 AS SELECT a, MAX(b) FROM t1 GROUP BY a; +SELECT * FROM (SELECT 1 FROM v1 UNION (SELECT 1 FROM v1 WHERE @a := uuid())) dt; +1 +EXPLAIN FORMAT=JSON +SELECT * FROM (SELECT 1 FROM v1 UNION (SELECT 1 FROM v1 WHERE @a := uuid())) dt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "message": "no matching row in const table" + } + } + }, + { + "query_block": { + "select_id": 3, + "table": { + "message": "no matching row in const table" + } + } + } + ] + } + } + } + } + } +} +DROP TABLE t1; +DROP VIEW v1; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 313d77d2332..8592b887966 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2124,4 +2124,18 @@ WHERE t1.a=tab.c AND DROP TABLE t1,t2; +--echo # +--echo # MDEV-19139: pushdown condition with Item_func_set_user_var +--echo # + +CREATE TABLE t1 (a INT, b INT); +CREATE VIEW v1 AS SELECT a, MAX(b) FROM t1 GROUP BY a; + +SELECT * FROM (SELECT 1 FROM v1 UNION (SELECT 1 FROM v1 WHERE @a := uuid())) dt; +EXPLAIN FORMAT=JSON +SELECT * FROM (SELECT 1 FROM v1 UNION (SELECT 1 FROM v1 WHERE @a := uuid())) dt; + +DROP TABLE t1; +DROP VIEW v1; + --echo # End of 10.2 tests diff --git a/sql/item_func.h b/sql/item_func.h index 41eb019bd6b..1664f585d81 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1989,6 +1989,7 @@ class Item_func_set_user_var :public Item_func_user_var void cleanup(); Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return get_item_copy<Item_func_set_user_var>(thd, mem_root, this); } + bool excl_dep_on_table(table_map tab_map) { return false; } };

1 0

[Commits] e5aa8ea5255: MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error
by sujatha 24 Apr '19

24 Apr '19

revision-id: e5aa8ea52552759453fdd86eeab0007df1781b62 (mariadb-5.5.63-19-ge5aa8ea5255) parent(s): 38b6dc5a3d11d9917c02ad10293807b5a0a4f5a0 author: Marko Mäkelä committer: Marko Mäkelä timestamp: 2019-04-23 17:56:43 +0300 message: MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error dict_create_foreign_constraints_low(): Tolerate the keywords IGNORE and ONLINE between the keywords ALTER and TABLE. We should really remove the hacky FOREIGN KEY constraint parser from InnoDB. --- mysql-test/suite/innodb/r/foreign_key.result | 20 ++++++++++++++++++++ mysql-test/suite/innodb/t/foreign_key.test | 22 ++++++++++++++++++++++ storage/innobase/dict/dict0dict.c | 23 ++++++++++++++++++----- storage/xtradb/dict/dict0dict.c | 23 ++++++++++++++++++----- 4 files changed, 78 insertions(+), 10 deletions(-) diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result new file mode 100644 index 00000000000..39f99513ee0 --- /dev/null +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -0,0 +1,20 @@ +# +# MDEV-18630 Conditional jump or move depends on uninitialised value +# in ib_push_warning / dict_create_foreign_constraints_low +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); +ERROR HY000: Can't create table 'test.#sql-temporary' (errno: 150) +SHOW WARNINGS; +Level Code Message +Warning 150 Alter table `test`.`t1` with foreign key constraint failed. Referenced table `test`.`t2` not found in the data dictionary near 'FOREIGN KEY (a) REFERENCES t2 (b)'. +Error 1005 Can't create table 'test.#sql-temporary' (errno: 150) +DROP TABLE t1; +# +# MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error +# +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB; +CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f); +ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1); +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test new file mode 100644 index 00000000000..281193d60ec --- /dev/null +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -0,0 +1,22 @@ +--source include/have_innodb.inc + +--echo # +--echo # MDEV-18630 Conditional jump or move depends on uninitialised value +--echo # in ib_push_warning / dict_create_foreign_constraints_low +--echo # +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +--replace_regex /#sql-[0-9_a-f-]*/#sql-temporary/ +--error ER_CANT_CREATE_TABLE +ALTER IGNORE TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (b); +--replace_regex /#sql-[0-9_a-f-]*/#sql-temporary/ +SHOW WARNINGS; +DROP TABLE t1; + +--echo # +--echo # MDEV-18139 ALTER IGNORE ... ADD FOREIGN KEY causes bogus error +--echo # +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f1)) ENGINE=InnoDB; +CREATE TABLE t2 (f INT, KEY(f)) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t2 (f); +ALTER IGNORE TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f1); +DROP TABLE t1, t2; diff --git a/storage/innobase/dict/dict0dict.c b/storage/innobase/dict/dict0dict.c index 588c2968b6a..fcb9a48e4c0 100644 --- a/storage/innobase/dict/dict0dict.c +++ b/storage/innobase/dict/dict0dict.c @@ -1,6 +1,7 @@ /***************************************************************************** Copyright (c) 1996, 2015, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -3787,6 +3788,9 @@ dict_create_foreign_constraints_low( } goto loop; + } else { + strncpy(create_name, name, sizeof create_name); + create_name[(sizeof create_name) - 1] = '\0'; } if (table == NULL) { @@ -3811,11 +3815,20 @@ dict_create_foreign_constraints_low( goto loop; } - ptr = dict_accept(cs, ptr, "TABLE", &success); - - if (!success) { - - goto loop; + orig = ptr; + for (;;) { + ptr = dict_accept(cs, ptr, "TABLE", &success); + if (success) { + break; + } + ptr = dict_accept(cs, ptr, "ONLINE", &success); + if (success) { + continue; + } + ptr = dict_accept(cs, ptr, "IGNORE", &success); + if (!success) { + goto loop; + } } /* We are doing an ALTER TABLE: scan the table name we are altering */ diff --git a/storage/xtradb/dict/dict0dict.c b/storage/xtradb/dict/dict0dict.c index 5da4509599e..12d3a600e54 100644 --- a/storage/xtradb/dict/dict0dict.c +++ b/storage/xtradb/dict/dict0dict.c @@ -1,6 +1,7 @@ /***************************************************************************** Copyright (c) 1996, 2015, Oracle and/or its affiliates. All Rights Reserved. +Copyright (c) 2019, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -3921,6 +3922,9 @@ dict_create_foreign_constraints_low( } goto loop; + } else { + strncpy(create_name, name, sizeof create_name); + create_name[(sizeof create_name) - 1] = '\0'; } if (table == NULL) { @@ -3945,11 +3949,20 @@ dict_create_foreign_constraints_low( goto loop; } - ptr = dict_accept(cs, ptr, "TABLE", &success); - - if (!success) { - - goto loop; + orig = ptr; + for (;;) { + ptr = dict_accept(cs, ptr, "TABLE", &success); + if (success) { + break; + } + ptr = dict_accept(cs, ptr, "ONLINE", &success); + if (success) { + continue; + } + ptr = dict_accept(cs, ptr, "IGNORE", &success); + if (!success) { + goto loop; + } } /* We are doing an ALTER TABLE: scan the table name we are altering */

1 0

[Commits] 5fc8dd8: MDEV-17796 WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y)
by IgorBabaev 24 Apr '19

24 Apr '19

revision-id: 5fc8dd8b82e995b3c685f4e71700201097431358 (mariadb-10.2.23-73-g5fc8dd8) parent(s): 6b5d3c51b3d4260ba692bb84c64eb2705635e051 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-23 23:10:46 -0700 message: MDEV-17796 WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y) with GROUP BY + ORDER BY The method JOIN::create_postjoin_aggr_table() should not call call JOIN::add_sorting_to_table() unless the first non-constant join table is passed as the first parameter to the method. --- mysql-test/r/order_by.result | 13 +++++++++++++ mysql-test/t/order_by.test | 14 ++++++++++++++ sql/sql_select.cc | 3 ++- 3 files changed, 29 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 1ca3034..28b63da 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3253,3 +3253,16 @@ Warnings: Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` set optimizer_switch= @save_optimizer_switch; DROP TABLE books, wings; +# +# MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY +# +CREATE TABLE t1 (id int, gr int, v1 varchar(10)); +INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C'); +SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1 +WHERE gr in (4,2) +GROUP BY id +ORDER BY id+1 DESC; +NULLIF(GROUP_CONCAT(v1), null) +C +B +DROP TABLE t1; diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index b047a31..d67c67d 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -2187,3 +2187,17 @@ eval explain extended $q; set optimizer_switch= @save_optimizer_switch; DROP TABLE books, wings; + +--echo # +--echo # MDEV-17796: query with DISTINCT, GROUP BY and ORDER BY +--echo # + +CREATE TABLE t1 (id int, gr int, v1 varchar(10)); +INSERT INTO t1 VALUES (1,1,'A'), (2,2,'B'), (3,3,NULL), (4,4,'C'); + +SELECT DISTINCT NULLIF(GROUP_CONCAT(v1), null) FROM t1 + WHERE gr in (4,2) +GROUP BY id +ORDER BY id+1 DESC; + +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c3acdf7..3c1cea6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3007,7 +3007,8 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields, if (setup_sum_funcs(thd, sum_funcs)) goto err; - if (!group_list && !table->distinct && order && simple_order) + if (!group_list && !table->distinct && order && simple_order && + tab == join_tab + const_tables) { DBUG_PRINT("info",("Sorting for order")); THD_STAGE_INFO(thd, stage_sorting_for_order);

1 0

[Commits] 4352397eb83: MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key
by sachin.setiya＠mariadb.com 23 Apr '19

23 Apr '19

revision-id: 4352397eb8383ecc23e15dd4f92d248e0d36cc85 (mariadb-10.4.3-105-g4352397eb83) parent(s): 5469d88e7b4e723e545cd7d511801a16a3642dc4 author: Sachin committer: Sachin timestamp: 2019-03-28 14:16:13 +0530 message: MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key Don't Ignore Any error during index lookup, And throw duplicate key error only if error is HA_ERR_FOUND_DUPP_KEY --- mysql-test/main/long_unique_bugs.result | 26 ++++++++++++++++++++ mysql-test/main/long_unique_bugs.test | 43 +++++++++++++++++++++++++++++++++ sql/handler.cc | 6 ++--- 3 files changed, 72 insertions(+), 3 deletions(-) diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 48e74bdd564..9b0df9e32fe 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -285,3 +285,29 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ALTER TABLE t1 FORCE; DROP TABLE t1; +set innodb_lock_wait_timeout= 10; +CREATE TABLE t1 ( +id int primary key, +f INT unique +) ENGINE=InnoDB; +CREATE TABLE t2 ( +id int primary key, +a blob unique +) ENGINE=InnoDB; +START TRANSACTION; +connect con1,localhost,root,,test; +connection con1; +set innodb_lock_wait_timeout= 10; +START TRANSACTION; +INSERT INTO t1 VALUES (1,1)/*1*/; +connection default; +INSERT INTO t2 VALUES (2, 1)/*2*/ ; +connection con1; +INSERT INTO t2 VALUES (3, 1)/*3*/; +connection default; +INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/; +connection con1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +disconnect con1; +connection default; +DROP TABLE t1, t2; diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index 11b1c4f09b6..8e4b950b250 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -294,3 +294,46 @@ ALTER TABLE t1 ADD UNIQUE (b); show create table t1; ALTER TABLE t1 FORCE; DROP TABLE t1; + +# +# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' +# + +--source include/have_innodb.inc +set innodb_lock_wait_timeout= 10; + +CREATE TABLE t1 ( + id int primary key, + f INT unique +) ENGINE=InnoDB; + +CREATE TABLE t2 ( + id int primary key, + a blob unique +) ENGINE=InnoDB; + +START TRANSACTION; + +--connect (con1,localhost,root,,test) + +--connection con1 +set innodb_lock_wait_timeout= 10; +START TRANSACTION; +INSERT INTO t1 VALUES (1,1)/*1*/; + +--connection default +INSERT INTO t2 VALUES (2, 1)/*2*/ ; + +--connection con1 +--send + INSERT INTO t2 VALUES (3, 1)/*3*/; + +--connection default +INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/; + +--connection con1 +--error ER_LOCK_DEADLOCK +--reap +--disconnect con1 +--connection default +DROP TABLE t1, t2; diff --git a/sql/handler.cc b/sql/handler.cc index da7850c5c4e..22af3f8bc81 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -6590,10 +6590,10 @@ static int check_duplicate_long_entry_key(TABLE *table, handler *h, error= HA_ERR_FOUND_DUPP_KEY; goto exit; } - if (result == HA_ERR_LOCK_WAIT_TIMEOUT) - error= HA_ERR_LOCK_WAIT_TIMEOUT; + if (result != HA_ERR_KEY_NOT_FOUND) + error= result; exit: - if (error) + if (error == HA_ERR_FOUND_DUPP_KEY) { table->file->errkey= key_no; if (h->ha_table_flags() & HA_DUPLICATE_POS)

1 0