诡异的delete失败事件，求助

Question

今天在做一个试验的时候发现一个很怪异的情况，仔细看我的操作步骤，我先select出where限制的记录，发现有771条记录，然后我delete了符合where的记录，只有213条记录受影响，最奇怪的是然后我再select同样where条件的记录，发现还是有771条记录，怎么个回事啊

mysql> select * from NETWORK_CPULOAD WHERE TO_DAYS(NOW())-TO_DAYS(collectdate)>=750;|      788 |    6.17034632034632 | 2008-06-06 02:04:18 |      2 | 
|      789 |     5.1530303030303 | 2008-06-06 02:11:26 |      2 | 
|      790 |    2.45606060606061 | 2008-06-06 02:01:09 |      2 | 
|      791 |    9.86818181818182 | 2008-06-06 02:04:18 |      2 | 
|      792 |    3.79545454545455 | 2008-06-06 02:02:52 |      2 | 
|      793 |    7.43333333333333 | 2008-06-06 02:19:19 |      2 | 
|      794 |    4.67121212121212 | 2008-06-06 02:01:22 |      2 | 
|      795 |    4.64069264069264 | 2008-06-06 02:02:52 |      2 | 
|      796 |    5.11969696969697 | 2008-06-06 02:02:51 |      2 | 
|      797 |    31.5909090909091 | 2008-06-06 02:11:06 |      2 | 
|      798 |    13.2471861471861 | 2008-06-06 02:01:17 |      2 | 
|      799 |    10.6876623376623 | 2008-06-06 02:01:51 |      2 | 
|      802 |    6.13333333333333 | 2008-06-06 02:09:50 |      2 | 
+----------+---------------------+---------------------+--------+
711 rows in set (2 min 7.00 sec)mysql> delete from NETWORK_CPULOAD where TO_DAYS(NOW())-TO_DAYS(collectdate)>=750;
Query OK, 213 rows affected (59.16 sec)mysql> select * from NETWORK_CPULOAD WHERE TO_DAYS(NOW())-TO_DAYS(collectdate)>=750;|      710 |    17.0606060606061 | 2008-06-06 02:01:03 |      2 | 
|      711 |                   0 | 2008-06-06 02:26:24 |      2 | 
|      712 |    9.57337662337662 | 2008-06-06 02:02:53 |      2 | 
|      713 |                   5 | 2008-06-06 02:01:53 |      2 | 
|      714 |                   1 | 2008-06-06 02:00:53 |      2 | 
|      715 |    12.9878787878788 | 2008-06-06 02:03:56 |      2 | 
|      716 |    1.00757575757576 | 2008-06-06 02:02:53 |      2 | 
|      717 |    4.00757575757576 | 2008-06-06 02:01:55 |      2 | 
|      719 |    4.95454545454545 | 2008-06-06 02:04:06 |      2 | 
|      721 |                  49 | 2008-06-06 02:01:37 |      2 | 
|      722 |    6.51969696969697 | 2008-06-06 02:10:47 |      2 | 
|      723 |    10.5350649350649 | 2008-06-06 02:03:49 |      2 | 
|      724 |    4.12272727272727 | 2008-06-06 02:02:36 |      2 | 
|      725 |  0.0263157894736842 | 2008-06-06 02:24:33 |      2 | 
|      727 |                   1 | 2008-06-06 02:03:42 |      2 | 
|      731 |                   1 | 2008-06-06 02:03:03 |      2 | 
|      732 |                   1 | 2008-06-06 02:02:07 |      2 | 
|      733 |    19.0253246753247 | 2008-06-06 02:00:41 |      2 | 
|      734 |    27.5179653679654 | 2008-06-06 02:04:09 |      2 | 
|      735 |     25.925974025974 | 2008-06-06 02:02:43 |      2 | 
|      736 |    29.8305194805195 | 2008-06-06 02:01:42 |      2 | 
|      737 |    36.6820346320346 | 2008-06-06 02:03:15 |      2 | 
|      738 |    19.4445887445887 | 2008-06-06 02:02:11 |      2 | 
|      739 |    18.4675324675325 | 2008-06-06 02:01:11 |      2 | 
|      740 |    20.5530303030303 | 2008-06-06 02:10:21 |      2 | 
|      741 |     22.374025974026 | 2008-06-06 02:03:36 |      2 | 
|      742 |    8.42316017316017 | 2008-06-06 02:02:32 |      2 | 
|      743 |    11.0969696969697 | 2008-06-06 02:01:23 |      2 | 
|      744 |    4.18333333333333 | 2008-06-06 02:10:17 |      2 | 
|      745 |    19.0757575757576 | 2008-06-06 02:10:08 |      2 | 
|      746 |    7.77077922077922 | 2008-06-06 02:02:21 |      2 | 
|      747 |    31.3341991341991 | 2008-06-06 02:01:10 |      2 | 
|      748 |   0.601515151515152 | 2008-06-06 02:27:11 |      2 | 
|      749 |     1.8969696969697 | 2008-06-06 02:03:27 |      2 | 
|      750 |    25.4640692640693 | 2008-06-06 02:02:26 |      2 | 
|      751 |    19.8536796536797 | 2008-06-06 02:01:21 |      2 | 
|      752 |    5.58030303030303 | 2008-06-06 02:10:11 |      2 | 
|      753 |    8.93593073593074 | 2008-06-06 02:02:01 |      2 | 
|      754 |    27.4277056277056 | 2008-06-06 02:00:55 |      2 | 
|      755 |  0.0119047619047619 | 2008-06-06 02:26:17 |      2 | 
|      756 |    21.4530303030303 | 2008-06-06 02:10:45 |      2 | 
|      757 |    8.97186147186147 | 2008-06-06 02:01:17 |      2 | 
|      758 |    7.40151515151515 | 2008-06-06 02:02:16 |      2 | 
|      759 |    10.2818181818182 | 2008-06-06 02:03:23 |      2 | 
|      760 |     6.5030303030303 | 2008-06-06 02:10:53 |      2 | 
|      761 |    4.91125541125541 | 2008-06-06 02:02:14 |      2 | 
|      762 |    19.6590909090909 | 2008-06-06 02:03:00 |      2 | 
|      763 |    23.9984848484848 | 2008-06-06 02:04:10 |      2 | 
|      764 |    8.55606060606061 | 2008-06-06 02:19:07 |      2 | 
|      765 |    29.7484848484849 | 2008-06-06 02:02:36 |      2 | 
|      766 |    39.6261904761905 | 2008-06-06 02:04:09 |      2 | 
|      767 |    21.8181818181818 | 2008-06-06 02:10:24 |      2 | 
|      768 |    18.2272727272727 | 2008-06-06 02:10:29 |      2 | 
|      770 |               55.25 | 2008-06-06 02:11:15 |      2 | 
|      771 |                   3 | 2008-06-06 02:00:43 |      2 | 
|      772 |    5.01515151515152 | 2008-06-06 02:01:48 |      2 | 
|      773 |                   6 | 2008-06-06 02:03:01 |      2 | 
|      774 |    18.4982683982684 | 2008-06-06 02:03:55 |      2 | 
|      775 |    15.2324675324675 | 2008-06-06 02:01:26 |      2 | 
|      776 |    8.59285714285714 | 2008-06-06 02:02:37 |      2 | 
|      777 |    2.58333333333333 | 2008-06-06 02:03:31 |      2 | 
|      778 |    3.83787878787879 | 2008-06-06 02:04:21 |      2 | 
|      779 |                   1 | 2008-06-06 02:01:22 |      2 | 
|      780 |                   1 | 2008-06-06 02:02:00 |      2 | 
|      781 |    7.04134199134199 | 2008-06-06 02:03:03 |      2 | 
|      782 |                   0 | 2008-06-06 02:26:58 |      2 | 
|      783 |                   0 | 2008-06-06 02:23:46 |      2 | 
|      786 |                   1 | 2008-06-06 02:06:38 |      2 | 
|      787 |    3.01515151515152 | 2008-06-06 02:02:00 |      2 | 
|      788 |    6.17034632034632 | 2008-06-06 02:04:18 |      2 | 
|      789 |     5.1530303030303 | 2008-06-06 02:11:26 |      2 | 
|      790 |    2.45606060606061 | 2008-06-06 02:01:09 |      2 | 
|      791 |    9.86818181818182 | 2008-06-06 02:04:18 |      2 | 
|      792 |    3.79545454545455 | 2008-06-06 02:02:52 |      2 | 
|      793 |    7.43333333333333 | 2008-06-06 02:19:19 |      2 | 
|      794 |    4.67121212121212 | 2008-06-06 02:01:22 |      2 | 
|      795 |    4.64069264069264 | 2008-06-06 02:02:52 |      2 | 
|      796 |    5.11969696969697 | 2008-06-06 02:02:51 |      2 | 
|      797 |    31.5909090909091 | 2008-06-06 02:11:06 |      2 | 
|      798 |    13.2471861471861 | 2008-06-06 02:01:17 |      2 | 
|      799 |    10.6876623376623 | 2008-06-06 02:01:51 |      2 | 
|      802 |    6.13333333333333 | 2008-06-06 02:09:50 |      2 | 
+----------+---------------------+---------------------+--------+
711 rows in set (2 min 27.27 sec)

Answer 1

是在同一个会话中吗？
如果不是，delete之后，commit了吗？

Answer 2

show create table NETWORK_CPULOAD 看一下，估计是你的日期为字符型数据。

Answer 3

mysql> show create table NETWORK_CPULOAD ;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                         |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NETWORK_CPULOAD | CREATE TABLE `NETWORK_CPULOAD` (
  `EQUIP_ID` bigint(20) NOT NULL,
  `VALUE` double DEFAULT NULL,
  `COLLECTDATE` datetime DEFAULT NULL,
  `PERIOD` tinyint(4) DEFAULT '0',
  KEY `IDX1` (`EQUIP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (EQUIP_ID) PARTITIONS 1000  */ | 

Answer 4

理论上没有问题的啊，我曾经用上述语句delete了一个类似表结构的表的数据

Answer 5

那你 select  TO_DAYS(NOW())-TO_DAYS(collectdate) from tb 看看

Answer 6

强烈关注，什么原因，难道是mysql的bug,

Answer 7

初步断定，跟partition有关。或许你的表分区已经有损坏。你这分区表也挺奇怪的，居然要分区数为1000？可以建一新表，不分区，将原数据导入新表中，再做上述操作。
另一方法：
修复分区。
先使用show partitions, show partitions status看看分区状态，可能需要alter table repair partitions来修复。

Answer 8

真诡异。。
你用
create table A select * from NETWORK_CPULOAD WHERE TO_DAYS(NOW())-TO_DAYS(collectdate)>=750;
把查出来的插入到A表，然后做DELETE
然后再两表查询，看看是哪些数据没有被删除掉、