Bug #48148 Can select but 1032 error when update and delete

• Submitted: 19 Oct 2009 10:31
• Modified: 21 Dec 2009 10:49
• Reporter: Jason Yang
• Status: No Feedback
• Category: MySQL Server: Partitions
• Severity: S2 (Serious)
• Version: 5.1.34-log
• OS: Linux
• CPU Architecture: Any
• Tags: 1032, partition

[19 Oct 2009 10:31] Jason Yang

Description:
We've encountered a weird problem:
After partitioning a table, it works perfectly for a while then it will fail when update or delete a record giving 1032 error. myisamchk does not find any problem. mysqldump the data out and put them in a non-partitioned table the problem goes away. We tried 5.1.37 the issue remains.

Here is the select and update operation:
mysql> SELECT * FROM requestMessage_bug where id=1566286;
+---------+-------------+---------------------+---------------------+-----+-------+-------+-----------+------------+---------+-------------+-------------+----------+---------+----------+----------------+-----------------+--------------+----------+---------+------+--------+-------------------+
| id      | sendNumber  | recieveDate         | times               | mnc | lac   | ci    | seq       | resultCode | feetype | serviceSPId | serviceCPId | platInfo | batchId | vendorId | intrucChannlId | protocolVersion | spUsageCount | regionId | carrier | risk | profit | responseMessageId |
+---------+-------------+---------------------+---------------------+-----+-------+-------+-----------+------------+---------+-------------+-------------+----------+---------+----------+----------------+-----------------+--------------+----------+---------+------+--------+-------------------+
| 1566286 | 15836796058 | 2009-10-14 15:55:41 | 2009-08-01 15:23:29 |   0 | 35018 | 14234 | 222333444 |          5 |       0 |          14 |          68 | NULL     |      29 |        1 |             17 |               9 |            0 | 41170000 |       1 |    0 |      0 | NULL              |
+---------+-------------+---------------------+---------------------+-----+-------+-------+-----------+------------+---------+-------------+-------------+----------+---------+----------+----------------+-----------------+--------------+----------+---------+------+--------+-------------------+
1 row in set

mysql> update requestMessage_bug set profit=2 where id=1566286;
ERROR 1032 : Can't find record in 'requestMessage_bug'

Our database has high volume concurrent hits. And we constantly repartioning the table. 

Here is the table definition:

| requestMessage_bug | CREATE TABLE `requestMessage_bug` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sendNumber` bigint(20) NOT NULL,
  `recieveDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `times` timestamp NULL DEFAULT NULL,
  `mnc` tinyint(4) DEFAULT NULL,
  `lac` int(11) DEFAULT NULL,
  `ci` int(11) DEFAULT NULL,
  `seq` varchar(50) DEFAULT NULL,
  `resultCode` tinyint(4) NOT NULL DEFAULT '2' COMMENT 'Internal Status including error code',
  `feetype` smallint(6) NOT NULL DEFAULT '0' COMMENT '0代表短信,1代表gprs,2代表wap,4代表GAME',
  `serviceSPId` int(11) NOT NULL,
  `serviceCPId` int(11) NOT NULL DEFAULT '76',
  `platInfo` varchar(5) DEFAULT NULL,
  `batchId` int(11) NOT NULL DEFAULT '2',
  `vendorId` int(11) NOT NULL DEFAULT '1',
  `intrucChannlId` int(10) NOT NULL,
  `protocolVersion` smallint(6) NOT NULL DEFAULT '10',
  `spUsageCount` smallint(6) NOT NULL DEFAULT '0',
  `regionId` int(10) DEFAULT NULL,
  `carrier` tinyint(4) NOT NULL DEFAULT '0',
  `risk` smallint(4) NOT NULL DEFAULT '0',
  `profit` tinyint(4) NOT NULL DEFAULT '0',
  `responseMessageId` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`recieveDate`),
  KEY `idx_req_receiveDate` (`recieveDate`),
  KEY `idx_req_seq` (`seq`)
) ENGINE=MyISAM AUTO_INCREMENT=1616102 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(recieveDate))
(PARTITION p0906 VALUES LESS THAN (733954) ENGINE = MyISAM,
 PARTITION p0907 VALUES LESS THAN (733985) ENGINE = MyISAM,
 PARTITION p0908 VALUES LESS THAN (734016) ENGINE = MyISAM,
 PARTITION p0909 VALUES LESS THAN (734046) ENGINE = MyISAM,
 PARTITION p091001 VALUES LESS THAN (734047) ENGINE = MyISAM,
 PARTITION p091002 VALUES LESS THAN (734048) ENGINE = MyISAM,
 PARTITION p091003 VALUES LESS THAN (734049) ENGINE = MyISAM,
 PARTITION p091004 VALUES LESS THAN (734050) ENGINE = MyISAM,
 PARTITION p091005 VALUES LESS THAN (734051) ENGINE = MyISAM,
 PARTITION p091006 VALUES LESS THAN (734052) ENGINE = MyISAM,
 PARTITION p091007 VALUES LESS THAN (734053) ENGINE = MyISAM,
 PARTITION p091008 VALUES LESS THAN (734054) ENGINE = MyISAM,
 PARTITION p091009 VALUES LESS THAN (734055) ENGINE = MyISAM,
 PARTITION p091010 VALUES LESS THAN (734056) ENGINE = MyISAM,
 PARTITION p091011 VALUES LESS THAN (734057) ENGINE = MyISAM,
 PARTITION p091012 VALUES LESS THAN (734058) ENGINE = MyISAM,
 PARTITION p091013 VALUES LESS THAN (734059) ENGINE = MyISAM,
 PARTITION p091014 VALUES LESS THAN (734060) ENGINE = MyISAM,
 PARTITION p091015 VALUES LESS THAN (734061) ENGINE = MyISAM,
 PARTITION p091016 VALUES LESS THAN (734062) ENGINE = MyISAM,
 PARTITION p091017 VALUES LESS THAN (734063) ENGINE = MyISAM,
 PARTITION p091018 VALUES LESS THAN (734064) ENGINE = MyISAM,
 PARTITION p091019 VALUES LESS THAN (734065) ENGINE = MyISAM,
 PARTITION p091020 VALUES LESS THAN (734066) ENGINE = MyISAM,
 PARTITION p091021 VALUES LESS THAN (734067) ENGINE = MyISAM,
 PARTITION p091022 VALUES LESS THAN (734068) ENGINE = MyISAM,
 PARTITION p091023 VALUES LESS THAN (734069) ENGINE = MyISAM,
 PARTITION p091024 VALUES LESS THAN (734070) ENGINE = MyISAM,
 PARTITION p091025 VALUES LESS THAN (734071) ENGINE = MyISAM,
 PARTITION p091026 VALUES LESS THAN (734072) ENGINE = MyISAM,
 PARTITION p091027 VALUES LESS THAN (734073) ENGINE = MyISAM,
 PARTITION p091028 VALUES LESS THAN (734074) ENGINE = MyISAM,
 PARTITION p091029 VALUES LESS THAN (734075) ENGINE = MyISAM,
 PARTITION p091030 VALUES LESS THAN (734076) ENGINE = MyISAM,
 PARTITION p091031 VALUES LESS THAN (734077) ENGINE = MyISAM,
 PARTITION p091101 VALUES LESS THAN (734078) ENGINE = MyISAM,
 PARTITION p091102 VALUES LESS THAN (734079) ENGINE = MyISAM,
 PARTITION p091103 VALUES LESS THAN (734080) ENGINE = MyISAM,
 PARTITION p091104 VALUES LESS THAN (734081) ENGINE = MyISAM,
 PARTITION p091105 VALUES LESS THAN (734082) ENGINE = MyISAM,
 PARTITION p091106 VALUES LESS THAN (734083) ENGINE = MyISAM,
 PARTITION p091107 VALUES LESS THAN (734084) ENGINE = MyISAM,
 PARTITION p091108 VALUES LESS THAN (734085) ENGINE = MyISAM,
 PARTITION p091109 VALUES LESS THAN (734086) ENGINE = MyISAM,
 PARTITION p091110 VALUES LESS THAN (734087) ENGINE = MyISAM,
 PARTITION p091111 VALUES LESS THAN (734088) ENGINE = MyISAM,
 PARTITION p091112 VALUES LESS THAN (734089) ENGINE = MyISAM,
 PARTITION p091113 VALUES LESS THAN (734090) ENGINE = MyISAM,
 PARTITION p091114 VALUES LESS THAN (734091) ENGINE = MyISAM,
 PARTITION p091115 VALUES LESS THAN (734092) ENGINE = MyISAM,
 PARTITION p091116 VALUES LESS THAN (734093) ENGINE = MyISAM,
 PARTITION p091117 VALUES LESS THAN (734094) ENGINE = MyISAM,
 PARTITION p091118 VALUES LESS THAN (734095) ENGINE = MyISAM,
 PARTITION p091119 VALUES LESS THAN (734096) ENGINE = MyISAM,
 PARTITION p091120 VALUES LESS THAN (734097) ENGINE = MyISAM,
 PARTITION p091121 VALUES LESS THAN (734098) ENGINE = MyISAM,
 PARTITION p091122 VALUES LESS THAN (734099) ENGINE = MyISAM,
 PARTITION p091123 VALUES LESS THAN (734100) ENGINE = MyISAM,
 PARTITION p091124 VALUES LESS THAN (734101) ENGINE = MyISAM,
 PARTITION p091125 VALUES LESS THAN (734102) ENGINE = MyISAM,
 PARTITION p091126 VALUES LESS THAN (734103) ENGINE = MyISAM,
 PARTITION p091127 VALUES LESS THAN (734104) ENGINE = MyISAM,
 PARTITION p091128 VALUES LESS THAN (734105) ENGINE = MyISAM,
 PARTITION p091129 VALUES LESS THAN (734106) ENGINE = MyISAM,
 PARTITION p091130 VALUES LESS THAN (734107) ENGINE = MyISAM) */ |

How to repeat:
Right now we don't know the exact way to reproduce the issue. However, it will happen in our environment for sure after period of time. The characteristics of our environment: High volume concurrent select and update on this table. 

We can attach the data files if you want. (mysqldump is not the right way since it will hide the problem)

[19 Oct 2009 12:15] MySQL Verification Team

Are you tested with the lasted release?. Thanks in advance.

[19 Oct 2009 13:47] Jason Yang

Miguel,
Thanks for replying.
We haven't tried w/ the latest release.
However, is there any known issues before? I searched the bug database and didn't see any bugs similar to this. I doubt the latest release will fix it. Of course I can try it later.

Is there anyway to explain the behavior that you can select and found it in the database but report 1032 when update and delete? These operations using different approach to nail the record? Kind of strange. Since I have no idea of the internal structure of the data file, I think the better way is that someone familiar w/ the development hook up w/ the debugger to see what's going on when navigating the data file. Is it?

Thanks again.

BTW, partioning is kind of critical for our applicatons. If mysql partitioning is not stable, I think we have to use application logic to deal w/ it (putting same data into different tables). So, please help, anyone. ...:-(

[19 Oct 2009 13:53] Jason Yang

Sorry, The characteristics of our environment should be :
High volume concurrent insert, select and update on this table.

[3 Nov 2009 8:34] Mattias Jonsson

Is it possible that you have a different timezone, in the session where you update/delete, from where you did the insert?

This seems related to bug#42849.

What one could do is to add support for UNIX_TIMESTAMP(timestamp_column) as partitioning function (and remove the support for all other timestamp use in the partitioning function, since they have this problem).

[22 Dec 2009 0:00] Bugs System

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".