| 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 | ||
[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>
Description: With ALTER TABLE..EXCHANGE PARTITION.. this error is shown when the values for the partition don't match: ERROR 1526 (HY000): Table has no partition for value 0 Here value is not 0, it's UNIX_TIMESTAMP('2015-02-10 00:00:00'), which is 1423522800. How to repeat: mysql> select * from foo1; +----+---------------------+--------+-------------+-----------+---------+ | id | sent_at | status | received_at | expire_at | payload | +----+---------------------+--------+-------------+-----------+---------+ | 2 | 2015-02-10 00:00:00 | 1 | NULL | NULL | NULL | +----+---------------------+--------+-------------+-----------+---------+ 1 row in set (0.00 sec) mysql> show create table FooArch\G *************************** 1. row *************************** Table: FooArch Create Table: 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) */ 1 row in set (0.00 sec) mysql> ALTER TABLE FooArch EXCHANGE PARTITION p201501 WITH TABLE foo1; ERROR 1526 (HY000): Table has no partition for value 0 mysql> UPDATE foo1 SET sent_at='2015-01-10 00:00:00' where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE FooArch EXCHANGE PARTITION p201501 WITH TABLE foo1; Query OK, 0 rows affected (0.02 sec) Suggested fix: So it should have said: ERROR 1526 (HY000): Table has no partition for value 1423522800