From: Mattias Jonsson
Date: April 22 2008 9:30pm
Subject: Re: bk commit into 6.0 tree (svoj:1.2629) BUG#33404
List-Archive: http://lists.mysql.com/commits/45851
Message-Id: <480E5907.6020102@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

Should the TABLESPACE be removed from the table level, when you remove 
the partitioning? (It might be consistent with other table options, but 
please check this and add a comment in the test case if this is correct).

It does not use the table level TABLESPACE when adding a new partition, 
instead it uses the database defaults.

The comments are also below, starting with 'MJ:'.

Otherwise the code change is good.


Regards
Mattias


Sergey Vojtovich wrote:
> Below is the list of changes that have just been committed into a local
> 6.0 repository of svoj.  When svoj does a push these changes
> will be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
> 
> ChangeSet@stripped, 2008-04-22 18:05:23+05:00, svoj@stripped +4 -0
>   BUG#33404 - Falcon ignores partition-level TABLESPACE option
>   
>   Creating partitioned table that supports tablespaces, ignores
>   [sub]partition level tablespace option. It was only possible
>   to specify table level tablespace option.
>   
>   With this fix tablespace option is handled as following:
>   - If tablespace option was specified for subpartition, use it;
>     otherwise inherit partition level tablespace option.
>   - If tablespace option was specified for partition, use it;
>     otherwise inherit table level tablespace option.
>   - If tablespace option was specified for table, use it;
>     otherwise use default tablespace.
> 
>   mysql-test/suite/falcon/r/falcon_bug_33404.result@stripped, 2008-04-22 18:05:21+05:00, svoj@stripped +96 -0
>     A test case for BUG#33404.
> 
>   mysql-test/suite/falcon/r/falcon_bug_33404.result@stripped, 2008-04-22 18:05:21+05:00, svoj@stripped +0 -0
> 
>   mysql-test/suite/falcon/t/falcon_bug_33404.test@stripped, 2008-04-22 18:05:21+05:00, svoj@stripped +66 -0
>     A test case for BUG#33404.
> 
>   mysql-test/suite/falcon/t/falcon_bug_33404.test@stripped, 2008-04-22 18:05:21+05:00, svoj@stripped +0 -0
> 
>   sql/ha_partition.cc@stripped, 2008-04-22 18:05:21+05:00, svoj@stripped +1 -0
>     When creating partitions, use partition level tablespace option
>     instead of table level tablespace option.
> 
>   sql/partition_info.cc@stripped, 2008-04-22 18:05:21+05:00, svoj@stripped +4 -0
>     If tablespace option was not specified for partition/subpartition,
>     inherit it from table (for partition) or from partition
>     (for subpartition).
> 
> diff -Nrup a/mysql-test/suite/falcon/r/falcon_bug_33404.result b/mysql-test/suite/falcon/r/falcon_bug_33404.result
> --- /dev/null	Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/falcon/r/falcon_bug_33404.result	2008-04-22 18:05:21 +05:00
> @@ -0,0 +1,96 @@
> +CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.fts' ENGINE=Falcon;
> +CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.fts' ENGINE=Falcon;
> +CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.fts' ENGINE=Falcon;
> +CREATE TABLE t1 (a INT) ENGINE=Falcon TABLESPACE ts1
> +PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (
> +PARTITION p1 VALUES LESS THAN (100) TABLESPACE ts2 (
> +SUBPARTITION p1s1 TABLESPACE ts3,
> +SUBPARTITION p1s2
> +),
> +PARTITION p2 VALUES LESS THAN MAXVALUE (
> +SUBPARTITION p2s1,
> +SUBPARTITION p2s2
> +)
> +);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1	P1S1	ts3	T1#P#P1#SP#P1S1
> +TEST	T1	P1S2	ts2	T1#P#P1#SP#P1S2
> +TEST	T1	P2S1	ts1	T1#P#P2#SP#P2S1
> +TEST	T1	P2S2	ts1	T1#P#P2#SP#P2S2
> +SHOW CREATE TABLE t1;
> +Table	Create Table
> +t1	CREATE TABLE `t1` (
> +  `a` int(11) DEFAULT NULL
> +) /*!50100 TABLESPACE `ts1` */ ENGINE=Falcon DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) (PARTITION p1 VALUES LESS THAN (100) (SUBPARTITION p1s1 TABLESPACE = ts3 ENGINE = Falcon, SUBPARTITION p1s2 TABLESPACE = ts2 ENGINE = Falcon), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION p2s1 TABLESPACE = ts1 ENGINE = Falcon, SUBPARTITION p2s2 TABLESPACE = ts1 ENGINE = Falcon)) */
> +ALTER TABLE t1 REMOVE PARTITIONING;
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1		FALCON_USER	T1

MJ: Hmm, here the table level tablespace is changed to FALCON_USER 
instead of ts1!

> +ALTER TABLE t1 TABLESPACE ts1
> +PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (
> +PARTITION p1 VALUES LESS THAN (100) TABLESPACE ts2 (
> +SUBPARTITION p1s1 TABLESPACE ts3,
> +SUBPARTITION p1s2
> +),
> +PARTITION p2 VALUES LESS THAN (200) (
> +SUBPARTITION p2s1,
> +SUBPARTITION p2s2
> +)
> +);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1	P1S1	ts3	T1#P#P1#SP#P1S1
> +TEST	T1	P1S2	ts2	T1#P#P1#SP#P1S2
> +TEST	T1	P2S1	ts1	T1#P#P2#SP#P2S1
> +TEST	T1	P2S2	ts1	T1#P#P2#SP#P2S2
> +ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1	P1S1	ts3	T1#P#P1#SP#P1S1
> +TEST	T1	P1S2	ts2	T1#P#P1#SP#P1S2
> +TEST	T1	P2S1	ts1	T1#P#P2#SP#P2S1
> +TEST	T1	P2S2	ts1	T1#P#P2#SP#P2S2
> +TEST	T1	P3SP0	FALCON_USER	T1#P#P3#SP#P3SP0
> +TEST	T1	P3SP1	FALCON_USER	T1#P#P3#SP#P3SP1

MJ: Should not the p3 subpartitions now use ts1 since that is the table 
level tablespace?

> +ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN
> +MAXVALUE TABLESPACE ts1);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1	P1S1	ts3	T1#P#P1#SP#P1S1
> +TEST	T1	P1S2	ts2	T1#P#P1#SP#P1S2
> +TEST	T1	P2S1	ts1	T1#P#P2#SP#P2S1
> +TEST	T1	P2S2	ts1	T1#P#P2#SP#P2S2
> +TEST	T1	P3SP0	ts1	T1#P#P3#SP#P3SP0
> +TEST	T1	P3SP1	ts1	T1#P#P3#SP#P3SP1
> +ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN
> +MAXVALUE TABLESPACE ts2);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1	P1S1	ts3	T1#P#P1#SP#P1S1
> +TEST	T1	P1S2	ts2	T1#P#P1#SP#P1S2
> +TEST	T1	P2S1	ts1	T1#P#P2#SP#P2S1
> +TEST	T1	P2S2	ts1	T1#P#P2#SP#P2S2
> +TEST	T1	P3SP0	ts2	T1#P#P3#SP#P3SP0
> +TEST	T1	P3SP1	ts2	T1#P#P3#SP#P3SP1
> +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES LESS THAN
> +(100) TABLESPACE ts2);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SCHEMA_NAME	TABLE_NAME	PARTITION	TABLESPACE	INTERNAL_NAME
> +TEST	T1	P2S1	ts1	T1#P#P2#SP#P2S1
> +TEST	T1	P2S2	ts1	T1#P#P2#SP#P2S2
> +TEST	T1	P3SP0	ts2	T1#P#P3#SP#P3SP0
> +TEST	T1	P3SP1	ts2	T1#P#P3#SP#P3SP1
> +TEST	T1	P1SP0	ts2	T1#P#P1#SP#P1SP0
> +TEST	T1	P1SP1	ts2	T1#P#P1#SP#P1SP1
> +DROP TABLE t1;
> +DROP TABLESPACE ts1 ENGINE=Falcon;
> +DROP TABLESPACE ts2 ENGINE=Falcon;
> +DROP TABLESPACE ts3 ENGINE=Falcon;
> diff -Nrup a/mysql-test/suite/falcon/t/falcon_bug_33404.test b/mysql-test/suite/falcon/t/falcon_bug_33404.test
> --- /dev/null	Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/falcon/t/falcon_bug_33404.test	2008-04-22 18:05:21 +05:00
> @@ -0,0 +1,66 @@
> +--source include/have_falcon.inc
> +--source include/have_partition.inc
> +
> +#
> +# BUG#33404 - Falcon ignores partition-level TABLESPACE option
> +#
> +CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.fts' ENGINE=Falcon;
> +CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.fts' ENGINE=Falcon;
> +CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.fts' ENGINE=Falcon;
> +
> +CREATE TABLE t1 (a INT) ENGINE=Falcon TABLESPACE ts1
> +PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (
> +  PARTITION p1 VALUES LESS THAN (100) TABLESPACE ts2 (
> +    SUBPARTITION p1s1 TABLESPACE ts3,
> +    SUBPARTITION p1s2
> +  ),
> +  PARTITION p2 VALUES LESS THAN MAXVALUE (
> +    SUBPARTITION p2s1,
> +    SUBPARTITION p2s2
> +  )
> +);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +SHOW CREATE TABLE t1;
> +
> +ALTER TABLE t1 REMOVE PARTITIONING;
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +
> +ALTER TABLE t1 TABLESPACE ts1
> +PARTITION BY RANGE(a) SUBPARTITION BY HASH(a) (
> +  PARTITION p1 VALUES LESS THAN (100) TABLESPACE ts2 (
> +    SUBPARTITION p1s1 TABLESPACE ts3,
> +    SUBPARTITION p1s2
> +  ),
> +  PARTITION p2 VALUES LESS THAN (200) (
> +    SUBPARTITION p2s1,
> +    SUBPARTITION p2s2
> +  )
> +);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +
> +ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN MAXVALUE);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +
> +ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN
> +MAXVALUE TABLESPACE ts1);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +
> +ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p3 VALUES LESS THAN
> +MAXVALUE TABLESPACE ts2);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +
> +ALTER TABLE t1 REORGANIZE PARTITION p1 INTO (PARTITION p1 VALUES LESS THAN
> +(100) TABLESPACE ts2);
> +SELECT * FROM INFORMATION_SCHEMA.FALCON_TABLES WHERE
> +  TABLE_NAME = 't1' AND SCHEMA_NAME='test';
> +
> +DROP TABLE t1;
> +DROP TABLESPACE ts1 ENGINE=Falcon;
> +DROP TABLESPACE ts2 ENGINE=Falcon;
> +DROP TABLESPACE ts3 ENGINE=Falcon;
> diff -Nrup a/sql/ha_partition.cc b/sql/ha_partition.cc
> --- a/sql/ha_partition.cc	2008-04-14 15:09:57 +05:00
> +++ b/sql/ha_partition.cc	2008-04-22 18:05:21 +05:00
> @@ -1866,6 +1866,7 @@ int ha_partition::set_up_table_before_cr
>    }
>    info->index_file_name= part_elem->index_file_name;
>    info->data_file_name= part_elem->data_file_name;
> +  info->tablespace= part_elem->tablespace_name;
>    DBUG_RETURN(0);
>  }
>  
> diff -Nrup a/sql/partition_info.cc b/sql/partition_info.cc
> --- a/sql/partition_info.cc	2008-04-14 15:09:59 +05:00
> +++ b/sql/partition_info.cc	2008-04-22 18:05:21 +05:00
> @@ -963,6 +963,8 @@ bool partition_info::check_partition_inf
>                         "INDEX DIRECTORY option ignored");
>          part_elem->data_file_name= part_elem->index_file_name= NULL;
>        }
> +      if (info && !part_elem->tablespace_name)
> +        part_elem->tablespace_name= (char *)info->tablespace;

MJ: Is this correct also in an ALTER? It might be an ADD PARTITION, and 
then if not explicitly set, it should use the tables default.

>        if (!is_sub_partitioned())
>        {
>          if (part_elem->engine_type == NULL)
> @@ -1004,6 +1006,8 @@ bool partition_info::check_partition_inf
>                no_subparts_not_set++;
>              }
>            }
> +          if (!sub_elem->tablespace_name)
> +            sub_elem->tablespace_name= part_elem->tablespace_name;
>            DBUG_PRINT("info", ("part = %d sub = %d engine = %s", i, j,
>                       ha_resolve_storage_engine_name(sub_elem->engine_type)));
>          } while (++j < no_subparts);
> 

-- 
Mattias Jonsson, Software Engineer
MySQL AB, www.mysql.com

Are you MySQL certified?  www.mysql.com/certification