Skip to content

Commit a474ff8

Browse files
Alexey KopytovAlexey Kopytov
Alexey Kopytov
authored and
Alexey Kopytov
committed
Bug #42849: innodb crash with varying time_zone on partitioned
 timestamp primary key Since TIMESTAMP values are adjusted by the current time zone settings in both numeric and string contexts, using any expressions involving TIMESTAMP values as a (sub)partitioning function leads to undeterministic behavior of partitioned tables. The effect may vary depending on a storage engine, it can be either incorrect data being retrieved or stored, or an assertion failure. The root cause of this is the fact that the calculated partition ID may differ from a previously calculated ID for the same data due to timezone adjustments of the partitioning expression value. Fixed by disabling any expressions involving TIMESTAMP values to be used in partitioning functions with the follwing two exceptions: 1. Creating or altering into a partitioned table that violates the above rule is not allowed, but opening existing such tables results in a warning rather than an error so that such tables could be fixed. 2. UNIX_TIMESTAMP() is the only way to get a timezone-independent value from a TIMESTAMP column, because it returns the internal representation (a time_t value) of a TIMESTAMP argument verbatim. So UNIX_TIMESTAMP(timestamp_column) is allowed and should be used to fix existing tables if one wants to use TIMESTAMP columns with partitioning. --BZR-- revision-id: [email protected] property-branch-nick: my51-bug42849 property-file-info: ld7:file_id73:sp1f-partition_bug18198.r-20070412183249-2zywzajt4whjqp5wz4c7argi77yqhbwb7:message20:Corrected the error.4:path38:mysql-test/r/partition_bug18198.resulted7:file_id73:sp1f-partition_error.resu-20050720124221-iacosg62tkflqhizuvhtiem5oeicwjit7:message55:Corrected error texts. property-file-info: Added test cases for bug #42849.4:path35:mysql-test/r/partition_error.resulted7:file_id73:sp1f-partition_bug18198.t-20070412183249-7r7ram5fqiyssvy4t5irs2a7sdd4oimt7:message21:Corrected error code.4:path36:mysql-test/t/partition_bug18198.tested7:file_id73:sp1f-partition_error.test-20050720124214-od2aou4vzloggrqktgmbjzvuhajiukpm7:message55:Corrected error codes. property-file-info: Added test cases for bug #42849.4:path33:mysql-test/t/partition_error.tested7:file_id59:sp1f-item.h-19700101030959-rrkb43htudd62batmoteashkebcwykpa7:message48:Added is_timezone_dependent_processor() to Item.4:path10:sql/item.hed7:file_id64:sp1f-item_func.h-19700101030959-fbjcbwkg66qubbzptqwh5w5evhnpukze7:message101:Added has_timestamp_args() and the implementation of property-file-info: is_timezone_dependent_processor() for Item_func.4:path15:sql/item_func.hed7:file_id68:sp1f-item_timefunc.h-19700101030959-o34ypz6ggolzqmhgsjnqh6inkvgugi467:message69:Added is_timezone_dependent_processor() to property-file-info: Item_func_unix_timestamp.4:path19:sql/item_timefunc.hed7:file_id63:sp1f-errmsg.txt-20041213212820-do5w642w224ja7ctyqhyl6iihdmpkzv57:message145:Renamed ER_CONST_EXPR_IN_PARTITION_FUNC_ERROR to property-file-info: ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR to better reflect the property-file-info: meaning. Adjusted the error message.4:path20:sql/share/errmsg.txted7:file_id69:sp1f-sql_partition.cc-20050718113038-57h5bzswps6cel2y7k7qideue3ghbg3u7:message288:Modified fix_fields_part_func() to walk through partitioning property-file-info: expression tree with is_timezone_dependent_processor() and issue property-file-info: a warning/error if it depends on the timezone settings. property-file-info: property-file-info: Changed fix_fields_part_func() to a static function since it is property-file-info: not used anywhere except sql_partition.cc4:path20:sql/sql_partition.cced7:file_id68:sp1f-sql_partition.h-20060216163825-un6ect3xl76xucer2pubythjlegvmy437:message93:Removed the unneeded declaration of fix_fields_part_func() property-file-info: since it is now a static function.4:path19:sql/sql_partition.hed7:file_id64:sp1f-sql_yacc.yy-19700101030959-wvn4qyy2drpmge7kaq3dysprbhlrv27j7:message80:ER_CONST_EXPR_IN_PARTITION_FUNC_ERROR -> property-file-info: ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR. 4:path15:sql/sql_yacc.yyee testament3-sha1: 3cb287baa4b10aa15a7a35c6d0f57b21ec07d890
1 parent a7780b3 commit a474ff8

11 files changed

Lines changed: 743 additions & 25 deletions

‎mysql-test/r/partition_bug18198.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -126,7 +126,7 @@ ERROR HY000: This partition function is not allowed
126126
create table t1 (col1 date)
127127
partition by range(unix_timestamp(col1))
128128
(partition p0 values less than (10), partition p1 values less than (30));
129-
ERROR HY000: This partition function is not allowed
129+
ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
130130
create table t1 (col1 datetime)
131131
partition by range(week(col1))
132132
(partition p0 values less than (10), partition p1 values less than (30));

‎mysql-test/r/partition_error.result‎

Lines changed: 301 additions & 9 deletions
Large diffs are not rendered by default.

‎mysql-test/t/partition_bug18198.test‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -158,7 +158,7 @@ create table t1 (col1 datetime)
158158
partition by range(timestampdiff(day,5,col1))
159159
(partition p0 values less than (10), partition p1 values less than (30));
160160

161-
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
161+
-- error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
162162
create table t1 (col1 date)
163163
partition by range(unix_timestamp(col1))
164164
(partition p0 values less than (10), partition p1 values less than (30));

0 commit comments

Comments
 (0)