MySQL Bugs: #33766: Updating the "PARTITION BY" field don't work from first time

Bug #33766	Updating the "PARTITION BY" field don't work from first time
Submitted:	9 Jan 2008 13:23	Modified:	14 Mar 2008 5:45
Reporter:	Nicolae Namolovan	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.22	OS:	FreeBSD (6.1)
Assigned to:		CPU Architecture:	Any

Description:
Look here

mysql> update posts set forumid=25 where topicid=63517;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=63517;
Query OK, 29 rows affected (0.02 sec)
Rows matched: 29  Changed: 29  Warnings: 0

No data were added nor removed between these two queries.

Here are the table

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `topicid` int(10) unsigned NOT NULL DEFAULT '0',
  `page` mediumint(10) unsigned NOT NULL DEFAULT '1',
  `forumid` tinyint(3) unsigned NOT NULL,
  KEY `topicid` (`topicid`),
  KEY `userid` (`userid`),
  KEY `page` (`page`),
  KEY `forum_topic_page` (`forumid`,`topicid`,`page`)
) ENGINE=MyISAM AUTO_INCREMENT=1765623 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITION BY RANGE (forumid) (PARTITION p0 VALUES LESS THAN (2) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (4) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (8) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (12) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (14) ENGINE = MyISAM, PARTITION p7 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (18) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (21) ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

How to repeat:
^

Mm.. Not everytime from second time..

mysql> update posts set forumid=25 where topicid=63444;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=63444;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=63444;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Thank you for the bug report. The create table statement you've provided
has the below issue:

ERROR 1072 (42000): Key column 'userid' doesn't exist in table

could you please provide a complete test case, create table statement,
insert data enough to test the reported behavior?. Thanks in advance.

Miguel, Unfortunately I can't.. It's on production.
Remove userid key from create..
Just INSERT data to every partition, and then try to update "PARTITION BY" column..

Hah, see that..

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update posts set forumid=25 where topicid=59903;
Query OK, 69 rows affected (0.16 sec)
Rows matched: 69  Changed: 69  Warnings: 0

Maybe for reproduction you need some load to the database..
As I said, this is on protion.

I found a workaround.

If you specify in the WHERE current partition location (like for pruning), everything works like normal.

Example a post forumid is 20 I want to update it to 25

update posts set forumid=25 where topicid=59903 AND forumid=20;

And it works fine..

I might be that it just reports 0 updates, if possible could you please check if the query did the update or not when it reports 0 updates?

Nope, when it writes 0 updates, nothing is updated.. I found this bug when one a forum feature stops to working.

If I write partition location(in where), it works every time fine..

Please, try to repeat with a newer version, 5.1.23-rc, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Nicolae,

we still need to know if you have this issue by using newest MySQL version (5.1.23).