[Commits] Rev 4485: MDEV-6794 XtraDB no longer using UNIQUE as clustered index when PK missing in lp:~maria-captains/maria/10.0

Sergei Golubchik serg at mariadb.org
Mon Nov 17 01:31:35 EET 2014


At lp:~maria-captains/maria/10.0

------------------------------------------------------------
revno: 4485
revision-id: sergii at pisem.net-20141116233134-3gy1lojlwestgzgc
parent: sergii at pisem.net-20141115120902-6la9b5wpk1n0b2sk
fixes bug: https://mariadb.atlassian.net/browse/MDEV-6794
committer: Sergei Golubchik <sergii at pisem.net>
branch nick: 10.0
timestamp: Mon 2014-11-17 00:31:34 +0100
message:
  MDEV-6794 XtraDB no longer using UNIQUE as clustered index when PK missing
  
  try the first unique key as a surrogate PK *before* disabling extended
  keys because of missing PK
=== added file 'mysql-test/r/ext_key_noPK_6794.result'
--- a/mysql-test/r/ext_key_noPK_6794.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/ext_key_noPK_6794.result	2014-11-16 23:31:34 +0000
@@ -0,0 +1,20 @@
+create table t1 (c1 int not null, c2 int, unique index(c1), index (c2)) engine=innodb;
+insert into t1 (c1, c2) select 1, round(rand()*100);
+insert into t1 (c1, c2) select (select max(c1) from t1) + c1, c1*93563%100 from t1;
+insert into t1 (c1, c2) select (select max(c1) from t1) + c1, c1*93563%100 from t1;
+insert into t1 (c1, c2) select (select max(c1) from t1) + c1, c1*93563%100 from t1;
+select count(*) from t1;
+count(*)
+8
+explain select * from t1 where c2 = 1 order by c1;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	c2
+key	c2
+key_len	5
+ref	const
+rows	1
+Extra	Using where; Using index
+drop table t1;

=== added file 'mysql-test/t/ext_key_noPK_6794.test'
--- a/mysql-test/t/ext_key_noPK_6794.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/ext_key_noPK_6794.test	2014-11-16 23:31:34 +0000
@@ -0,0 +1,15 @@
+#
+# MDEV-6794 XtraDB no longer using UNIQUE as clustered index when PK missing
+# 
+
+--source include/have_innodb.inc
+
+create table t1 (c1 int not null, c2 int, unique index(c1), index (c2)) engine=innodb;
+insert into t1 (c1, c2) select 1, round(rand()*100);
+insert into t1 (c1, c2) select (select max(c1) from t1) + c1, c1*93563%100 from t1;
+insert into t1 (c1, c2) select (select max(c1) from t1) + c1, c1*93563%100 from t1;
+insert into t1 (c1, c2) select (select max(c1) from t1) + c1, c1*93563%100 from t1;
+select count(*) from t1;
+--query_vertical explain select * from t1 where c2 = 1 order by c1
+drop table t1;
+

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2014-11-13 12:40:19 +0000
+++ b/sql/table.cc	2014-11-16 23:31:34 +0000
@@ -1666,10 +1666,44 @@ int TABLE_SHARE::init_from_binary_frm_im
   if (key_parts)
   {
     uint add_first_key_parts= 0;
-    uint primary_key=(uint) (find_type(primary_key_name, &share->keynames,
-                                       FIND_TYPE_NO_PREFIX) - 1);
     longlong ha_option= handler_file->ha_table_flags();
     keyinfo= share->key_info;
+    uint primary_key= my_strcasecmp(system_charset_info, share->keynames.type_names[0],
+                                    primary_key_name) ? MAX_KEY : 0;
+
+    if (primary_key >= MAX_KEY && keyinfo->flags & HA_NOSAME)
+    {
+      /*
+        If the UNIQUE key doesn't have NULL columns and is not a part key
+        declare this as a primary key.
+      */
+      primary_key= 0;
+      key_part= keyinfo->key_part;
+      for (i=0 ; i < keyinfo->user_defined_key_parts ;i++)
+      {
+        DBUG_ASSERT(key_part[i].fieldnr > 0);
+        // Table field corresponding to the i'th key part.
+        Field *table_field= share->field[key_part[i].fieldnr - 1];
+
+        /*
+          If the key column is of NOT NULL BLOB type, then it
+          will definitly have key prefix. And if key part prefix size
+          is equal to the BLOB column max size, then we can promote
+          it to primary key.
+        */
+        if (!table_field->real_maybe_null() &&
+            table_field->type() == MYSQL_TYPE_BLOB &&
+            table_field->field_length == key_part[i].length)
+          continue;
+
+        if (table_field->real_maybe_null() ||
+            table_field->key_length() != key_part[i].length)
+        {
+          primary_key= MAX_KEY;		// Can't be used
+          break;
+        }
+      }
+    }
 
     if (share->use_ext_keys)
     { 
@@ -1764,40 +1798,6 @@ int TABLE_SHARE::init_from_binary_frm_im
       if (share->key_info[key].flags & HA_FULLTEXT)
 	share->key_info[key].algorithm= HA_KEY_ALG_FULLTEXT;
 
-      if (primary_key >= MAX_KEY && (keyinfo->flags & HA_NOSAME))
-      {
-	/*
-	  If the UNIQUE key doesn't have NULL columns and is not a part key
-	  declare this as a primary key.
-	*/
-	primary_key=key;
-        key_part= keyinfo->key_part;
-	for (i=0 ; i < keyinfo->user_defined_key_parts ;i++)
-	{
-          DBUG_ASSERT(key_part[i].fieldnr > 0);
-          // Table field corresponding to the i'th key part.
-          Field *table_field= share->field[key_part[i].fieldnr - 1];
-
-          /*
-            If the key column is of NOT NULL BLOB type, then it
-            will definitly have key prefix. And if key part prefix size
-            is equal to the BLOB column max size, then we can promote
-            it to primary key.
-          */
-          if (!table_field->real_maybe_null() &&
-              table_field->type() == MYSQL_TYPE_BLOB &&
-              table_field->field_length == key_part[i].length)
-            continue;
-
-	  if (table_field->real_maybe_null() ||
-	      table_field->key_length() != key_part[i].length)
-	  {
-	    primary_key= MAX_KEY;		// Can't be used
-	    break;
-	  }
-	}
-      }
-
       key_part= keyinfo->key_part;
       uint key_parts= share->use_ext_keys ? keyinfo->ext_key_parts :
 	                                    keyinfo->user_defined_key_parts;



More information about the commits mailing list