revision-id: d1ccc60360c79cd456abbebafc1c80c8043b7dce (mariadb-10.3.6-106-gd1ccc60) parent(s): ead9a34a3e934f607c2ea7a6c68f7f6d9d29b5bd committer: Alexey Botchkov timestamp: 2018-08-21 14:47:45 +0400 message: MDEV-14005 Remove need for Partition Key to be part of Primary Key. The limitation was removed, so now we check all the partition for unique key duplicates in these cases. --- mysql-test/main/partition_unique.result | 51 ++++++++ mysql-test/main/partition_unique.test | 58 ++++++++ sql/ha_partition.cc | 225 ++++++++++++++++++++++++++++++-- sql/ha_partition.h | 9 +- sql/partition_info.h | 10 +- sql/sql_partition.cc | 122 +++++++++-------- sql/sql_partition.h | 1 + sql/sql_update.cc | 3 +- 8 files changed, 407 insertions(+), 72 deletions(-) diff --git a/mysql-test/main/partition_unique.result b/mysql-test/main/partition_unique.result new file mode 100644 index 0000000..17a9550 --- /dev/null +++ b/mysql-test/main/partition_unique.result @@ -0,0 +1,51 @@ +CREATE TABLE t1 ( +id int(10) NOT NULL, +status varchar(1) DEFAULT NULL, +PRIMARY KEY (`id`) +) +PARTITION BY LIST COLUMNS(`status`) +( +PARTITION `a` VALUES IN ('A'), +PARTITION `b` VALUES IN ('B'), +PARTITION `c` VALUES IN ('C'), +PARTITION `d` DEFAULT); +INSERT INTO t1 VALUES (4, 'A'); +INSERT INTO t1 VALUES (6, 'A'); +INSERT INTO t1 VALUES (4, 'C'); +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +INSERT INTO t1 VALUES (5, 'C'); +UPDATE t1 SET id=4 WHERE id=5; +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +UPDATE t1 SET id=4 WHERE id=5 AND status='C'; +ERROR 23000: Duplicate entry '4' for key 'PRIMARY' +UPDATE t1 SET id=6 WHERE id=4 AND status='A'; +ERROR 23000: Duplicate entry '6' for key 'PRIMARY' +select * from t1; +id status +4 A +6 A +5 C +connect con1,localhost,root,,test; +connect con2,localhost,root,,test; +connection con1; +SET DEBUG_SYNC= 'berfore_part_unique_check SIGNAL bpu_hit WAIT_FOR bpu_flushed'; +INSERT INTO t1 VALUES(7, 'A'); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR bpu_hit'; +INSERT INTO t1 VALUES(7, 'C'); +connection default; +SET DEBUG_SYNC= 'now SIGNAL bpu_flushed'; +connection con1; +connection con2; +ERROR 23000: Duplicate entry '7' for key 'PRIMARY' +disconnect con1; +disconnect con2; +connection default; +select * from t1; +id status +4 A +6 A +7 A +5 C +DROP TABLE t1; +set debug_sync= "RESET"; diff --git a/mysql-test/main/partition_unique.test b/mysql-test/main/partition_unique.test new file mode 100644 index 0000000..f292359 --- /dev/null +++ b/mysql-test/main/partition_unique.test @@ -0,0 +1,58 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE t1 ( + id int(10) NOT NULL, + status varchar(1) DEFAULT NULL, + PRIMARY KEY (`id`) + ) + PARTITION BY LIST COLUMNS(`status`) + ( + PARTITION `a` VALUES IN ('A'), + PARTITION `b` VALUES IN ('B'), + PARTITION `c` VALUES IN ('C'), + PARTITION `d` DEFAULT); + +INSERT INTO t1 VALUES (4, 'A'); +INSERT INTO t1 VALUES (6, 'A'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES (4, 'C'); +INSERT INTO t1 VALUES (5, 'C'); +--error ER_DUP_ENTRY +UPDATE t1 SET id=4 WHERE id=5; +--error ER_DUP_ENTRY +UPDATE t1 SET id=4 WHERE id=5 AND status='C'; +--error ER_DUP_ENTRY +UPDATE t1 SET id=6 WHERE id=4 AND status='A'; +select * from t1; + +connect (con1,localhost,root,,test); +connect (con2,localhost,root,,test); +connection con1; +SET DEBUG_SYNC= 'berfore_part_unique_check SIGNAL bpu_hit WAIT_FOR bpu_flushed'; +send INSERT INTO t1 VALUES(7, 'A'); + +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR bpu_hit'; +send INSERT INTO t1 VALUES(7, 'C'); + +connection default; +SET DEBUG_SYNC= 'now SIGNAL bpu_flushed'; + +connection con1; +--reap +connection con2; +--error ER_DUP_ENTRY +--reap + +disconnect con1; +disconnect con2; + +connection default; + +select * from t1; + +DROP TABLE t1; + +set debug_sync= "RESET"; + diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 262e791..da07075 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -368,6 +368,7 @@ void ha_partition::init_handler_variables() part_share= NULL; m_new_partitions_share_refs.empty(); m_part_ids_sorted_by_num_of_records= NULL; + m_cu_errkey= (uint) -1; m_partitions_to_open= NULL; m_range_info= NULL; @@ -4198,6 +4199,95 @@ void ha_partition::try_semi_consistent_read(bool yes) } +int ha_partition::check_files_for_key(uchar *key, int n_key, + int part_begin, int part_end, + int part_to_skip, + int *res) +{ + DBUG_ASSERT(inited == NONE || + (inited == RND && !m_scan_value)); + + for (int n=part_begin; n < part_end; n++) + { + handler *f= m_file[n]; + init_stat sav_inited; + + if ((int) n == part_to_skip) + continue; + + if ((sav_inited= f->inited) == RND) + f->ha_rnd_end(); + + *res= f->index_read_idx_map(m_part_info->table->record[0], + n_key, key, HA_WHOLE_KEY, HA_READ_KEY_EXACT); + + f->ha_end_keyread(); + + if (sav_inited == RND) + { + int ires= f->ha_rnd_init(FALSE); + if (ires) + *res= ires; + } + + if (*res == HA_ERR_KEY_NOT_FOUND) + continue; + + if (*res == 0) + *res= HA_ERR_FOUND_DUPP_KEY; + + m_last_part= n; + m_cu_errkey= n_key; + return 1; + } + + *res= 0; + return 0; +} + + +int ha_partition::check_uniques_insert(uchar *buf, + int part_begin, int part_end, + KEY **dup_key, + int *res) +{ + uint n_key; + + for (n_key=0; n_key < m_part_info->n_uniques_to_check; n_key++) + { + uchar *cbuf= m_part_info->unique_key_buf[0]; + KEY *ckey= m_part_info->uniques_to_check[n_key]; + uint n; + + for (n=0; n < ckey->user_defined_key_parts; n++) + { + const KEY_PART_INFO *cpart= ckey->key_part + n; + uint maybe_null= MY_TEST(cpart->null_bit); + Field *f= cpart->field; + my_ptrdiff_t ofs= buf-table->record[0]; + + f->move_field_offset(ofs); + if (maybe_null) + cbuf[0]= f->is_null(); + f->get_key_image(cbuf+maybe_null, cpart->length, Field::itRAW); + cbuf+= cpart->store_length; + f->move_field_offset(-ofs); + } + + if (check_files_for_key(m_part_info->unique_key_buf[0], + table->key_info - ckey, + part_begin, part_end, -1, res)) + { + *dup_key= ckey; + return 1; + } + } + + *res= 0; + return 0; +} + + /**************************************************************************** MODULE change record ****************************************************************************/ @@ -4249,6 +4339,7 @@ int ha_partition::write_row(uchar * buf) THD *thd= ha_thd(); sql_mode_t saved_sql_mode= thd->variables.sql_mode; bool saved_auto_inc_field_not_null= table->auto_increment_field_not_null; + KEY *dup_key; DBUG_ENTER("ha_partition::write_row"); DBUG_PRINT("enter", ("partition this: %p", this)); @@ -4307,7 +4398,41 @@ int ha_partition::write_row(uchar * buf) start_part_bulk_insert(thd, part_id); tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */ + + /* + Check unique keys (if there is any) for duplications in + partitions 0 .. inserted partition, then + do the write_row then check the unique in + partitions inserted partition +1 .. m_tot_parts. + We do so to keep the order of locking always same to + avoid deadlocks. + */ + if (table->part_info->n_uniques_to_check && + check_uniques_insert(buf, 0, part_id, &dup_key, &error)) + { + goto exit; + } + error= m_file[part_id]->ha_write_row(buf); + + DEBUG_SYNC(thd, "berfore_part_unique_check"); + + if (!error && table->part_info->n_uniques_to_check && + check_uniques_insert(buf, part_id+1, m_tot_parts, &dup_key, &error)) + { + /* + Errors here are ignored, as the error already found. + and i don't have a good idea what to do if things go + wrong here. + */ + if (!(m_file[part_id]->ha_index_read_idx_map(table->record[1], + dup_key - table->key_info, table->part_info->unique_key_buf[0], + HA_WHOLE_KEY, HA_READ_KEY_EXACT))) + { + (void) m_file[part_id]->ha_delete_row(buf); + } + } + if (have_auto_increment && !table->s->next_number_keypart) set_auto_increment_if_higher(table->next_number_field); reenable_binlog(thd); @@ -4317,6 +4442,59 @@ int ha_partition::write_row(uchar * buf) table->auto_increment_field_not_null= saved_auto_inc_field_not_null; DBUG_RETURN(error); } + + +int ha_partition::check_uniques_update(const uchar *old_data, + const uchar *new_data, + int new_part_id, int *res) +{ + uint n_key; + + for (n_key=0; n_key < m_part_info->n_uniques_to_check; n_key++) + { + uchar *buf0= m_part_info->unique_key_buf[0]; + uchar *buf1= m_part_info->unique_key_buf[1]; + KEY *ckey= m_part_info->uniques_to_check[n_key]; + uint n; + + for (n=0; n < ckey->user_defined_key_parts; n++) + { + const KEY_PART_INFO *cpart= ckey->key_part + n; + uint maybe_null= MY_TEST(cpart->null_bit); + Field *f= cpart->field; + my_ptrdiff_t dif; + + dif= old_data - table->record[0]; + f->move_field_offset(dif); + if (maybe_null) + buf0[0]= f->is_null(); + f->get_key_image(buf0+maybe_null, cpart->length, Field::itRAW); + buf0+= cpart->store_length; + f->move_field_offset(-dif); + + dif= new_data - table->record[0]; + f->move_field_offset(dif); + if (maybe_null) + buf1[0]= f->is_null(); + f->get_key_image(buf1+maybe_null, cpart->length, Field::itRAW); + buf1+= cpart->store_length; + f->move_field_offset(-dif); + } + + if (memcmp(m_part_info->unique_key_buf[0], m_part_info->unique_key_buf[1], + buf0 - m_part_info->unique_key_buf[0]) == 0) + { + /* Key did not change. */ + continue; + } + + if (check_files_for_key(m_part_info->unique_key_buf[1], + table->key_info - ckey, 0, m_tot_parts, new_part_id, res)) + return 1; + } + + return 0; +} /* @@ -4387,6 +4565,9 @@ int ha_partition::update_row(const uchar *old_data, const uchar *new_data) goto exit; } + if (table->part_info->n_uniques_to_check && + check_uniques_update(old_data, new_data, new_part_id, &error)) + goto exit; m_last_part= new_part_id; start_part_bulk_insert(thd, new_part_id); @@ -5754,11 +5935,14 @@ int ha_partition::index_read_idx_map(uchar *buf, uint index, get_partition_set(table, buf, index, &m_start_key, &m_part_spec); /* - We have either found exactly 1 partition + If there is no 'unbound' unique keys where not all keyparts + are partition definition fields, + we have either found exactly 1 partition (in which case start_part == end_part) - or no matching partitions (start_part > end_part) + or no matching partitions (start_part > end_part), */ - DBUG_ASSERT(m_part_spec.start_part >= m_part_spec.end_part); + DBUG_ASSERT(m_part_spec.start_part >= m_part_spec.end_part || + m_part_info->n_uniques_to_check); /* The start part is must be marked as used. */ DBUG_ASSERT(m_part_spec.start_part > m_part_spec.end_part || bitmap_is_set(&(m_part_info->read_partitions), @@ -8315,15 +8499,20 @@ int ha_partition::info(uint flag) { handler *file= m_file[m_last_part]; DBUG_PRINT("info", ("info: HA_STATUS_ERRKEY")); - /* - This flag is used to get index number of the unique index that - reported duplicate key - We will report the errkey on the last handler used and ignore the rest - Note: all engines does not support HA_STATUS_ERRKEY, so set errkey. - */ - file->errkey= errkey; - file->info(HA_STATUS_ERRKEY | no_lock_flag); - errkey= file->errkey; + if ((int) m_cu_errkey >= 0) + errkey= m_cu_errkey; + else + { + /* + This flag is used to get index number of the unique index that + reported duplicate key + We will report the errkey on the last handler used and ignore the rest + Note: all engines does not support HA_STATUS_ERRKEY, so set errkey. + */ + file->errkey= errkey; + file->info(HA_STATUS_ERRKEY | no_lock_flag); + errkey= file->errkey; + } } if (flag & HA_STATUS_TIME) { @@ -9711,6 +9900,18 @@ void ha_partition::print_error(int error, myf errflag) m_part_info->print_no_partition_found(table, errflag); DBUG_VOID_RETURN; } + else if (error == HA_ERR_FOUND_DUPP_KEY) + { + if ((int) m_cu_errkey >=0) + { + print_keydup_error(table, + m_cu_errkey == MAX_KEY ? NULL : + &table->key_info[m_cu_errkey], errflag); + m_cu_errkey= -1; + DBUG_VOID_RETURN; + } + /* fall through */ + } else if (error == HA_ERR_ROW_IN_WRONG_PARTITION) { /* Should only happen on DELETE or UPDATE! */ diff --git a/sql/ha_partition.h b/sql/ha_partition.h index 8a25101..54267e3 100644 --- a/sql/ha_partition.h +++ b/sql/ha_partition.h @@ -385,6 +385,7 @@ class ha_partition :public handler /** partitions that returned HA_ERR_KEY_NOT_FOUND. */ MY_BITMAP m_key_not_found_partitions; bool m_key_not_found; + uint m_cu_errkey; /* Last dup key */ List<String> *m_partitions_to_open; MY_BITMAP m_opened_partitions; /** This is one of the m_file-s that it guaranteed to be opened. */ @@ -523,7 +524,13 @@ class ha_partition :public handler void fix_data_dir(char* path); bool init_partition_bitmaps(); void free_partition_bitmaps(); - + int check_files_for_key(uchar *key, int n_key, + int part_begin, int part_end, + int partition_to_skip, int *res); + int check_uniques_insert(uchar *buf, int part_begin, int part_end, + KEY** dup_key, int *res); + int check_uniques_update(const uchar *old_data, const uchar *new_data, + int new_part_id, int *res); public: /* diff --git a/sql/partition_info.h b/sql/partition_info.h index e00a2c4..eeac0d9 100644 --- a/sql/partition_info.h +++ b/sql/partition_info.h @@ -283,6 +283,14 @@ class partition_info : public Sql_alloc bool is_auto_partitioned; bool has_null_value; bool column_list; // COLUMNS PARTITIONING, 5.5+ + /* + Unique keys that don't have all the partitioning fields in them + need to be checked when INSERT/UPDATE. + So they are collected here. + */ + KEY **uniques_to_check; + uint n_uniques_to_check; + uchar *unique_key_buf[2]; partition_info() : get_partition_id(NULL), get_part_partition_id(NULL), @@ -314,7 +322,7 @@ class partition_info : public Sql_alloc list_of_part_fields(FALSE), list_of_subpart_fields(FALSE), linear_hash_ind(FALSE), fixed(FALSE), is_auto_partitioned(FALSE), - has_null_value(FALSE), column_list(FALSE) + has_null_value(FALSE), column_list(FALSE), n_uniques_to_check(0) { all_fields_in_PF.clear_all(); all_fields_in_PPF.clear_all(); diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index 3133b94..6cd46dd 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -910,53 +910,10 @@ static bool fix_fields_part_func(THD *thd, Item* func_expr, TABLE *table, /* - Check that the primary key contains all partition fields if defined - - SYNOPSIS - check_primary_key() - table TABLE object for which partition fields are set-up - - RETURN VALUES - TRUE Not all fields in partitioning function was part - of primary key - FALSE Ok, all fields of partitioning function were part - of primary key - - DESCRIPTION - This function verifies that if there is a primary key that it contains - all the fields of the partition function. - This is a temporary limitation that will hopefully be removed after a - while. -*/ - -static bool check_primary_key(TABLE *table) -{ - uint primary_key= table->s->primary_key; - bool all_fields, some_fields; - bool result= FALSE; - DBUG_ENTER("check_primary_key"); - - if (primary_key < MAX_KEY) - { - set_indicator_in_key_fields(table->key_info+primary_key); - check_fields_in_PF(table->part_info->full_part_field_array, - &all_fields, &some_fields); - clear_indicator_in_key_fields(table->key_info+primary_key); - if (unlikely(!all_fields)) - { - my_error(ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF,MYF(0),"PRIMARY KEY"); - result= TRUE; - } - } - DBUG_RETURN(result); -} - - -/* Check that unique keys contains all partition fields SYNOPSIS - check_unique_keys() + find_uniques_to_check() table TABLE object for which partition fields are set-up RETURN VALUES @@ -972,12 +929,12 @@ static bool check_primary_key(TABLE *table) while. */ -static bool check_unique_keys(TABLE *table) +static uint find_uniques_to_check(TABLE *table) { bool all_fields, some_fields; - bool result= FALSE; uint keys= table->s->keys; uint i; + uint keys_found= 0; DBUG_ENTER("check_unique_keys"); for (i= 0; i < keys; i++) @@ -988,15 +945,15 @@ static bool check_unique_keys(TABLE *table) check_fields_in_PF(table->part_info->full_part_field_array, &all_fields, &some_fields); clear_indicator_in_key_fields(table->key_info+i); - if (unlikely(!all_fields)) + if (!all_fields) { - my_error(ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF,MYF(0),"UNIQUE INDEX"); - result= TRUE; - break; + ++keys_found; + if (table->part_info->n_uniques_to_check >= keys_found) + table->part_info->uniques_to_check[keys_found-1]= table->key_info+i; } } } - DBUG_RETURN(result); + DBUG_RETURN(keys_found); } @@ -2043,12 +2000,22 @@ bool fix_partition_func(THD *thd, TABLE *table, bool is_create_table_ind) } if (unlikely(create_full_part_field_array(thd, table, part_info))) goto end; - if (unlikely(check_primary_key(table))) - goto end; - if (unlikely((!(table->s->db_type()->partition_flags && - (table->s->db_type()->partition_flags() & HA_CAN_PARTITION_UNIQUE))) && - check_unique_keys(table))) - goto end; + if ((table->part_info->n_uniques_to_check= find_uniques_to_check(table))) + { + table->part_info->uniques_to_check= + (KEY **) alloc_root(&table->mem_root, + sizeof(KEY *) * table->part_info->n_uniques_to_check); + table->part_info->unique_key_buf[0]= + (uchar *) alloc_root(&table->mem_root, MAX_KEY_LENGTH); + table->part_info->unique_key_buf[1]= + (uchar *) alloc_root(&table->mem_root, MAX_KEY_LENGTH); + if (unlikely(!table->part_info->uniques_to_check) || + !table->part_info->unique_key_buf[0] || + !table->part_info->unique_key_buf[1]) + goto end; + + (void) find_uniques_to_check(table); + } if (unlikely(set_up_partition_bitmaps(thd, part_info))) goto end; if (unlikely(part_info->set_up_charset_field_preps(thd))) @@ -2798,6 +2765,47 @@ bool partition_key_modified(TABLE *table, const MY_BITMAP *fields) } + /* + Check if there are unique keys that not entirely 'inside' the partition + key and if any fields of such keys are modified. + If it's so, it usually means we have to use tamporary storage for records + handling the UPDATE command. + + SYNOPSIS + partition_unique_modified + table TABLE object for which partition fields are set-up + fields Bitmap representing fields to be modified + + RETURN VALUES + TRUE Need special handling of UPDATE + FALSE Normal UPDATE handling is ok +*/ + +bool partition_unique_modified(TABLE *table, const MY_BITMAP *fields) +{ + partition_info *part_info= table->part_info; + DBUG_ENTER("partition_unique_modified"); + + if (!part_info) + DBUG_RETURN(FALSE); + + if (part_info->uniques_to_check == 0) + DBUG_RETURN(FALSE); + + for (uint n_key=0; n_key < part_info->n_uniques_to_check; n_key++) + { + KEY *ckey= part_info->uniques_to_check[n_key]; + for (uint n_part=0; n_part < ckey->user_defined_key_parts; n_part++) + { + if (bitmap_is_set(fields, ckey->key_part[n_part].field->field_index)) + DBUG_RETURN(TRUE); + } + } + + DBUG_RETURN(FALSE); +} + + /* A function to handle correct handling of NULL values in partition functions. diff --git a/sql/sql_partition.h b/sql/sql_partition.h index 170ae8c..0df0269 100644 --- a/sql/sql_partition.h +++ b/sql/sql_partition.h @@ -281,6 +281,7 @@ bool verify_data_with_partition(TABLE *table, TABLE *part_table, bool compare_partition_options(HA_CREATE_INFO *table_create_info, partition_element *part_elem); bool partition_key_modified(TABLE *table, const MY_BITMAP *fields); +bool partition_unique_modified(TABLE *table, const MY_BITMAP *fields); #else #define partition_key_modified(X,Y) 0 #endif diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 6994ffa..b75cc31 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -562,7 +562,8 @@ int mysql_update(THD *thd, query_plan.possible_keys= select? select->possible_keys: key_map(0); if (used_key_is_modified || order || - partition_key_modified(table, table->write_set)) + partition_key_modified(table, table->write_set) || + partition_unique_modified(table, table->write_set)) { if (order && need_sort) query_plan.using_filesort= true;