MySQL Bugs: #42720: truncate table bugs

Bug #42720	truncate table bugs
Submitted:	10 Feb 2009 6:56	Modified:	17 Feb 2009 6:42
Reporter:	jabincn jabincn	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysql> select count(*) from t_prehandle_qeesoo_visit;  
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> truncate t_prehandle_qeesoo_visit;
Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) from t_prehandle_qeesoo_visit;
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> 

How to repeat:
mysql> select count(*) from t_prehandle_qeesoo_visit;  
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql> truncate t_prehandle_qeesoo_visit;
Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) from t_prehandle_qeesoo_visit;
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

mysql>

Thank you for the problem report. Please, send the results of:

show table status like 't_prehandle_qeesoo_visit'\G

How you had created a table like this, by the way?

mysql> truncate t_prehandle_qeesoo_visit;
Query OK, 0 rows affected (0.13 sec)

mysql> select count(*) from t_prehandle_qeesoo_visit;
+----------+
| count(*) |
+----------+
|      106 | 
+----------+
1 row in set (0.00 sec)

mysql> show table status like 't_prehandle_qeesoo_visit'\G
*************************** 1. row ***************************
           Name: t_prehandle_qeesoo_visit
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 65536
      Data_free: 601882624
 Auto_increment: 1
    Create_time: 2009-01-06 15:37:39
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set, 1 warning (0.00 sec)

CREATE TABLE `t_prehandle_qeesoo_visit` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增长id',
  `guid` varchar(40) NOT NULL DEFAULT '' COMMENT '访问者的guid码，类似用户名，在cookie中记录，用于标识不同的访客。',
  `start_time` datetime NOT NULL COMMENT '访问网站的进入时间',
  `src_ip` varchar(32) NOT NULL DEFAULT '' COMMENT '来源IP',
  `src_href` text COMMENT '来源网址',
  `referrer` text COMMENT '链接网址',
  `type` smallint(6) DEFAULT NULL COMMENT '点击类型:  0: 外部进来,第三方搜索引擎进来  1: 为左侧广告(有收费,有免费)  2: 为右侧广告(免费的)  3: 为普通广告(免费的)',
  `area_id` int(11) DEFAULT NULL COMMENT '地域id',
  `area_name` varchar(100) DEFAULT NULL COMMENT '地域名称',
  `customer_id` int(11) NOT NULL COMMENT '客户id',
  `customer_url` varchar(100) DEFAULT '' COMMENT '客户网址url',
  `domain` varchar(40) DEFAULT '' COMMENT '客户网站域名',
  `media_id` int(11) DEFAULT NULL COMMENT '媒体id',
  `media_name` varchar(100) DEFAULT NULL COMMENT '媒体名称',
  `media_type` smallint(6) DEFAULT NULL COMMENT '媒体类型',
  `media_domain` varchar(40) DEFAULT NULL COMMENT '投放媒体的域名',
  `keyword` varchar(400) DEFAULT NULL COMMENT '关键词',
  `key_name` varchar(255) DEFAULT NULL COMMENT '词根',
  `key_id` int(11) DEFAULT NULL COMMENT '词根id',
  `product_id` int(11) DEFAULT NULL COMMENT '产品id',
  `identify_key` varchar(20) DEFAULT NULL COMMENT '记录识别码',
  `record_status` smallint(6) DEFAULT '0' COMMENT '记录状态',
  `charge_status` smallint(6) DEFAULT '0' COMMENT '计费状态',
  `prehandle_date` datetime DEFAULT NULL COMMENT '记录处理时间',
  `display_valid` smallint(6) DEFAULT NULL,
  `customer_type` smallint(6) DEFAULT NULL,
  `ad_price_id` varchar(40) DEFAULT NULL,
  `adtype` smallint(6) DEFAULT NULL,
  `keyword_id` bigint(20) DEFAULT NULL,
  `channel` smallint(6) DEFAULT NULL,
  `reserve_1` varchar(20) DEFAULT NULL COMMENT '备用字段1',
  `reserve_2` varchar(20) DEFAULT NULL COMMENT '备用字段2',
  `ad_price` float(9,3) DEFAULT NULL,
  `referrer1` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `customer_id` (`customer_id`,`record_status`,`customer_type`,`charge_status`),
  KEY `src_href` (`src_href`(255)),
  KEY `start_time` (`start_time`,`guid`,`src_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thank you for the feedback.

Do you have query cache turned on?

yes.

Thank you for the feedback.

This is duplicate of bug #40386 which is fixed in version 5.1.32. Please wait new release.