CODEWITHSUNDEEP
mysql
noback
noback

Reputation: 53

ERROR 1526 (HY000): Table has no partition for value 1426566990

my mysql server can not partition:

mysql server version is : 5.1.71-log

OS : CentOS 6.5 x64

mysql>show create table 
| history | CREATE TABLE `history` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |


mysql>select * from history limit 11;
+--------+------------+--------------+-----------+
| itemid | clock      | value        | ns        |
+--------+------------+--------------+-----------+
|  35210 | 1426566411 |  189626.1400 | 856563617 |
|  35211 | 1426566414 |  328805.1900 |   3954734 |
|  35231 | 1426566432 |  497665.5600 | 124983500 |
|  35232 | 1426566433 |  839002.1200 |  66033134 |
|  35252 | 1426566453 |  175085.9200 |  58097601 |
|  35253 | 1426566454 |  113664.0000 | 104347387 |
|  35273 | 1426566474 |   11188.8300 |  95493093 |
|  35274 | 1426566475 |   12394.8100 | 109145645 |
|  35168 | 1426566969 | 2793042.2500 | 919270427 |
|  35169 | 1426566970 | 1148138.7500 | 649565410 |
|  35189 | 1426566990 |   65273.8800 | 718286083 |
+--------+------------+--------------+-----------+
11 rows in set (0.00 sec)

mysql> alter table history partition by range (clock)(partition p1 values less than(1426566990));
ERROR 1526 (HY000): Table has no partition for value 1426566990

But, the value already in the table, why the error occurs?

Who can help me?

Upvotes: 4

Views: 31261

Answers (2)

noback
noback

Reputation: 53

@Xokker

:)

how to extend the partition later on.

I try to use follow code:

alter table history add partition(partition p3 values less than (1426567221));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

have other method to extentd the partition,have not effect the DB performance?

Upvotes: 1

Ernest Sadykov
Ernest Sadykov

Reputation: 831

The problem is that server does not know where to place a row with value 1426566990. The less than condition is not inclusive. That means you should use less than(1426566991) in order to make this working. But if you decide to add a new row with value more than or equals to 1426566991, you'll get the same error. Therefore, I recommend the following approach:

alter table history partition by range (clock)(partition p1 values less than MAXVALUE);

More about range partitioning: RANGE Partitioning. There is an example for your case:

<...> Under this scheme, there is no rule that covers a row whose store_id is greater than 20, so an error results because the server does not know where to place it.

Upvotes: 5

Related Questions