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:
None 
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
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
[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>