Bug #77282 | Wrong value in error when using exchange partition | ||
---|---|---|---|
Submitted: | 9 Jun 2015 14:41 | Modified: | 10 Jun 2015 7:18 |
Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Errors | Severity: | S3 (Non-critical) |
Version: | 5.6.24, 5.6.25, 5.6.26 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | partitioning |
[9 Jun 2015 14:41]
Daniël van Eeden
[10 Jun 2015 7:18]
MySQL Verification Team
Hello Daniël, Thank you for the report. Thanks, Umesh
[10 Jun 2015 7:19]
MySQL Verification Team
// 5.6.25 root@myhostname [test]> drop table if exists FooArch; Query OK, 0 rows affected (0.01 sec) root@myhostname [test]> drop table if exists foo; Query OK, 0 rows affected (0.01 sec) root@myhostname [test]> root@myhostname [test]> CREATE TABLE `FooArch` ( -> `id` int(10) unsigned NOT NULL, -> `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `status` tinyint(3) unsigned NOT NULL, -> PRIMARY KEY (`id`,`sent_at`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(sent_at)) -> (PARTITION p201501 VALUES LESS THAN (1422745200) ENGINE = InnoDB) */; Query OK, 0 rows affected (0.01 sec) root@myhostname [test]> root@myhostname [test]> CREATE TABLE `foo` ( -> `id` int(10) unsigned NOT NULL, -> `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `status` tinyint(3) unsigned NOT NULL, -> PRIMARY KEY (`id`,`sent_at`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) root@myhostname [test]> root@myhostname [test]> insert into foo values(1,'2015-02-10 00:00:00',1); Query OK, 1 row affected (0.00 sec) root@myhostname [test]> ALTER TABLE FooArch EXCHANGE PARTITION p201501 WITH TABLE foo; ERROR 1526 (HY000): Table has no partition for value 0
[10 Jun 2015 7:20]
MySQL Verification Team
// 5.7.8 - seems to report correctly [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8: bin/mysql -u root -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.8-rc-enterprise-commercial-advanced-debug-log MySQL Enterprise Server - Advanced Edition Debug (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table if exists FooArch; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists foo; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE `FooArch` ( -> `id` int(10) unsigned NOT NULL, -> `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `status` tinyint(3) unsigned NOT NULL, -> PRIMARY KEY (`id`,`sent_at`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(sent_at)) -> (PARTITION p201501 VALUES LESS THAN (1422745200) ENGINE = InnoDB) */; Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE `foo` ( -> `id` int(10) unsigned NOT NULL, -> `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `status` tinyint(3) unsigned NOT NULL, -> PRIMARY KEY (`id`,`sent_at`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into foo values(1,'2015-02-10 00:00:00',1); Query OK, 1 row affected (0.00 sec) mysql> ALTER TABLE FooArch EXCHANGE PARTITION p201501 WITH TABLE foo; ERROR 1526 (HY000): Table has no partition for value 1423522800 mysql>
[10 Jun 2015 7:23]
MySQL Verification Team
// 5.6.26 also affected [umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.26: bin/mysql -u root -S/tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.26-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> use test Database changed mysql> drop table if exists FooArch; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists foo; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE `FooArch` ( -> `id` int(10) unsigned NOT NULL, -> `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `status` tinyint(3) unsigned NOT NULL, -> PRIMARY KEY (`id`,`sent_at`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(sent_at)) -> (PARTITION p201501 VALUES LESS THAN (1422745200) ENGINE = InnoDB) */; Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE TABLE `foo` ( -> `id` int(10) unsigned NOT NULL, -> `sent_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `status` tinyint(3) unsigned NOT NULL, -> PRIMARY KEY (`id`,`sent_at`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into foo values(1,'2015-02-10 00:00:00',1); Query OK, 1 row affected (0.01 sec) mysql> ALTER TABLE FooArch EXCHANGE PARTITION p201501 WITH TABLE foo; ERROR 1526 (HY000): Table has no partition for value 0 mysql>