Description:
When I truncate a table I get a success message. But issuing another truncate right after show a row in the table. The following were run on my prod server Exactly in the below sequence ...
mysql> truncate raw_career_salary_non_metro_area;
Query OK, 1 row affected (0.03 sec)
mysql> truncate raw_career_salary_non_metro_area;
Query OK, 1 row affected (0.03 sec)
mysql> delete from raw_career_salary_non_metro_area;
Query OK, 0 rows affected (0.02 sec)
mysql> truncate raw_career_salary_non_metro_area;
Query OK, 1 row affected (0.05 sec)
mysql> optimize table raw_career_salary_non_metro_area;
+--------------------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------------------+----------+----------+----------+
| exp.raw_career_salary_non_metro_area | optimize | status | OK |
+--------------------------------------+----------+----------+----------+
1 row in set (0.05 sec)
mysql> truncate raw_career_salary_non_metro_area;
Query OK, 1 row affected (0.03 sec)
mysql> show errors
-> ;
Empty set (0.04 sec)
mysql> show table status like 'raw_career_salary_national';
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+
| raw_career_salary_national | InnoDB | 10 | Compact | 5709 | 278 | 1589248 | 0 | 1622016 | 0 | 14677 | 2009-01-30 12:47:23 | NULL | NULL | utf8_unicode_ci | NULL | | BLS data on salary by SOC id; InnoDB free: 2163712 kB; (`career_id`) REFER `exp/ |
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+
1 row in set (0.13 sec)
How to repeat:
Here is a general script to repeat the error:
create database bug
;
use bug
create table foo(
foo_id int unsigned not null auto_increment primary key,
sometext varchar(50),
afloat float,
anint int,
anotherint int,
unique key (anint,anotherint)
) engine = innodb
comment = ''
;
insert into `foo`values
(1,'xxx',0,2006,4)
, (2,'xxx',106750,2006,5)
, (3,'xxx',0,2006,6)
, (4,'xxx',75890,2006,9)
, (5,'xxx',81850,2006,11)
, (6,'xxx',102520,2006,12)
, (7,'xxx',112190,2006,13)
, (8,'xxx',88520,2006,22)
, (9,'xxx',124340,2006,30)
, (10,'xxx',92990,2006,32)
, (11,'xxx',56290,2006,36)
, (12,'xxx',56720,2006,39)
, (13,'xxx',98690,2006,40)
, (14,'xxx',117240,2006,41)
, (15,'xxx',91350,2006,43)
, (16,'xxx',77520,2006,44)
, (17,'xxx',102550,2006,45)
, (18,'xxx',86430,2006,49)
, (19,'xxx',69470,2006,54)
, (20,'xxx',63870,2006,65)
, (21,'xxx',80030,2006,67)
, (22,'xxx',80790,2006,69)
, (23,'xxx',91810,2006,71)
, (24,'xxx',76030,2006,72)
, (25,'xxx',88410,2006,78)
, (26,'xxx',81550,2006,85)
, (27,'xxx',81830,2006,88)
, (28,'xxx',79490,2006,90)
, (29,'xxx',62320,2006,93)
, (30,'xxx',100400,2006,94)
, (31,'xxx',82360,2006,96)
, (32,'xxx',77180,2006,98)
, (33,'xxx',98430,2006,104)
, (34,'xxx',102000,2006,111)
, (35,'xxx',77080,2006,114)
, (36,'xxx',98440,2006,119)
, (37,'xxx',76040,2006,147)
, (38,'xxx',75620,2006,155)
, (39,'xxx',79620,2006,165)
, (40,'xxx',84840,2006,166)
, (41,'xxx',94900,2006,167)
, (42,'xxx',78470,2006,171)
, (43,'xxx',80440,2006,180)
, (44,'xxx',85660,2006,194)
, (45,'xxx',46220,2006,205)
, (46,'xxx',59040,2006,217)
, (47,'xxx',60060,2006,218)
, (48,'xxx',60010,2006,219)
, (49,'xxx',65690,2006,220)
, (50,'xxx',57120,2006,222)
, (51,'xxx',45750,2006,223)
, (52,'xxx',58610,2006,225)
, (53,'xxx',63630,2006,226)
, (54,'xxx',42560,2006,227)
, (55,'xxx',60440,2006,229)
, (56,'xxx',37970,2006,231)
, (57,'xxx',58560,2006,232)
, (58,'xxx',120160,2006,236)
, (59,'xxx',57890,2006,240)
, (60,'xxx',34030,2006,283)
, (61,'xxx',70490,2006,285)
, (62,'xxx',67710,2006,286)
, (63,'xxx',64970,2006,288)
, (64,'xxx',66470,2006,289)
, (65,'xxx',56240,2006,290)
, (66,'xxx',59160,2006,292)
, (67,'xxx',72890,2006,295)
, (68,'xxx',64140,2006,299)
, (69,'xxx',75580,2006,303)
, (70,'xxx',34180,2006,304)
, (71,'xxx',48780,2006,310)
, (72,'xxx',88120,2006,342)
, (73,'xxx',131070,2006,366)
, (74,'xxx',0,2006,368)
, (75,'xxx',76330,2006,377)
, (76,'xxx',89520,2006,380)
, (77,'xxx',109580,2006,390)
, (78,'xxx',76380,2006,394)
, (79,'xxx',36860,2006,399)
, (80,'xxx',51410,2006,404)
, (81,'xxx',48050,2006,405)
, (82,'xxx',37610,2006,414)
, (83,'xxx',0,2006,420)
, (84,'xxx',67240,2006,421)
, (85,'xxx',42610,2006,422)
, (86,'xxx',40550,2006,424)
, (87,'xxx',36650,2006,427)
, (88,'xxx',94460,2006,429)
, (89,'xxx',47180,2006,434)
, (90,'xxx',71940,2006,450)
, (91,'xxx',35240,2006,457)
, (92,'xxx',31450,2006,459)
, (93,'xxx',57960,2006,461)
, (94,'xxx',49680,2006,462)
, (95,'xxx',51050,2006,463)
, (96,'xxx',46680,2006,465)
, (97,'xxx',34090,2006,467)
, (98,'xxx',30510,2006,470)
, (99,'xxx',31360,2006,471)
, (100,'xxx',23410,2006,472)
, (101,'xxx',31830,2006,473)
, (102,'xxx',26580,2006,474)
, (103,'xxx',25910,2006,476)
, (104,'xxx',21070,2006,477)
, (105,'xxx',23500,2006,478)
, (106,'xxx',66250,2006,480)
, (107,'xxx',35190,2006,482)
, (108,'xxx',31710,2006,483)
, (109,'xxx',39540,2006,486)
, (110,'xxx',43330,2006,489)
, (111,'xxx',57240,2006,492)
, (112,'xxx',28210,2006,496)
, (113,'xxx',39140,2006,507)
, (114,'xxx',38030,2006,514)
, (115,'xxx',28010,2006,518)
, (116,'xxx',34940,2006,520)
, (117,'xxx',58940,2006,521)
, (118,'xxx',40860,2006,522)
, (119,'xxx',31900,2006,524)
, (120,'xxx',61830,2006,525)
, (121,'xxx',30550,2006,527)
, (122,'xxx',32840,2006,529)
, (123,'xxx',43370,2006,530)
, (124,'xxx',29500,2006,531)
, (125,'xxx',48810,2006,532)
, (126,'xxx',84530,2006,533)
, (127,'xxx',35400,2006,537)
, (128,'xxx',51620,2006,538)
, (129,'xxx',79430,2006,540)
, (130,'xxx',73910,2006,544)
, (131,'xxx',75170,2006,549)
, (132,'xxx',40580,2006,553)
, (133,'xxx',46460,2006,554)
, (134,'xxx',48310,2006,558)
, (135,'xxx',51480,2006,560)
, (136,'xxx',63630,2006,561)
, (137,'xxx',31450,2006,562)
, (138,'xxx',58880,2006,565)
, (139,'xxx',43870,2006,572)
, (140,'xxx',50600,2006,573)
, (141,'xxx',28600,2006,575)
, (142,'xxx',40220,2006,576)
, (143,'xxx',39880,2006,577)
, (144,'xxx',47900,2006,578)
, (145,'xxx',33150,2006,579)
, (146,'xxx',47280,2006,581)
, (147,'xxx',46000,2006,582)
, (148,'xxx',52930,2006,583)
, (149,'xxx',59560,2006,585)
, (150,'xxx',50290,2006,587)
, (151,'xxx',53720,2006,588)
, (152,'xxx',49740,2006,590)
, (153,'xxx',51230,2006,591)
, (154,'xxx',49690,2006,592)
, (155,'xxx',46240,2006,594)
, (156,'xxx',42320,2006,595)
, (157,'xxx',56520,2006,601)
, (158,'xxx',44700,2006,602)
, (159,'xxx',45710,2006,604)
, (160,'xxx',39750,2006,613)
, (161,'xxx',55420,2006,617)
, (162,'xxx',103310,2006,636)
, (163,'xxx',49680,2006,637)
, (164,'xxx',38950,2006,639)
, (165,'xxx',87100,2006,640)
, (166,'xxx',62900,2006,644)
, (167,'xxx',82140,2006,653)
, (168,'xxx',68860,2006,656)
, (169,'xxx',73200,2006,659)
, (170,'xxx',75350,2006,666)
, (171,'xxx',81110,2006,703)
, (172,'xxx',74840,2006,706)
, (173,'xxx',72320,2006,715)
, (174,'xxx',61520,2006,718)
, (175,'xxx',64520,2006,721)
, (176,'xxx',67370,2006,723)
, (177,'xxx',46700,2006,725)
, (178,'xxx',60710,2006,737)
, (179,'xxx',57910,2006,738)
, (180,'xxx',78970,2006,742)
, (181,'xxx',42970,2006,756)
, (182,'xxx',66340,2006,757)
, (183,'xxx',74690,2006,758)
, (184,'xxx',41510,2006,769)
, (185,'xxx',46440,2006,770)
, (186,'xxx',27840,2006,771)
, (187,'xxx',58990,2006,795)
, (188,'xxx',68080,2006,833)
, (189,'xxx',63830,2006,835)
, (190,'xxx',38630,2006,866)
, (191,'xxx',41890,2006,875)
, (192,'xxx',69470,2006,879)
, (193,'xxx',0,2006,880)
, (194,'xxx',88850,2006,881)
, (195,'xxx',80080,2006,882)
, (196,'xxx',49660,2006,885)
, (197,'xxx',43860,2006,886)
, (198,'xxx',52390,2006,887)
, (199,'xxx',56910,2006,888)
, (200,'xxx',39780,2006,889);
select count(*) from foo
;
truncate foo
;
select count(*) from foo
;
show table status like 'foo'
;
truncate foo
;
Description: When I truncate a table I get a success message. But issuing another truncate right after show a row in the table. The following were run on my prod server Exactly in the below sequence ... mysql> truncate raw_career_salary_non_metro_area; Query OK, 1 row affected (0.03 sec) mysql> truncate raw_career_salary_non_metro_area; Query OK, 1 row affected (0.03 sec) mysql> delete from raw_career_salary_non_metro_area; Query OK, 0 rows affected (0.02 sec) mysql> truncate raw_career_salary_non_metro_area; Query OK, 1 row affected (0.05 sec) mysql> optimize table raw_career_salary_non_metro_area; +--------------------------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------------------+----------+----------+----------+ | exp.raw_career_salary_non_metro_area | optimize | status | OK | +--------------------------------------+----------+----------+----------+ 1 row in set (0.05 sec) mysql> truncate raw_career_salary_non_metro_area; Query OK, 1 row affected (0.03 sec) mysql> show errors -> ; Empty set (0.04 sec) mysql> show table status like 'raw_career_salary_national'; +----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+ | raw_career_salary_national | InnoDB | 10 | Compact | 5709 | 278 | 1589248 | 0 | 1622016 | 0 | 14677 | 2009-01-30 12:47:23 | NULL | NULL | utf8_unicode_ci | NULL | | BLS data on salary by SOC id; InnoDB free: 2163712 kB; (`career_id`) REFER `exp/ | +----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+ 1 row in set (0.13 sec) How to repeat: Here is a general script to repeat the error: create database bug ; use bug create table foo( foo_id int unsigned not null auto_increment primary key, sometext varchar(50), afloat float, anint int, anotherint int, unique key (anint,anotherint) ) engine = innodb comment = '' ; insert into `foo`values (1,'xxx',0,2006,4) , (2,'xxx',106750,2006,5) , (3,'xxx',0,2006,6) , (4,'xxx',75890,2006,9) , (5,'xxx',81850,2006,11) , (6,'xxx',102520,2006,12) , (7,'xxx',112190,2006,13) , (8,'xxx',88520,2006,22) , (9,'xxx',124340,2006,30) , (10,'xxx',92990,2006,32) , (11,'xxx',56290,2006,36) , (12,'xxx',56720,2006,39) , (13,'xxx',98690,2006,40) , (14,'xxx',117240,2006,41) , (15,'xxx',91350,2006,43) , (16,'xxx',77520,2006,44) , (17,'xxx',102550,2006,45) , (18,'xxx',86430,2006,49) , (19,'xxx',69470,2006,54) , (20,'xxx',63870,2006,65) , (21,'xxx',80030,2006,67) , (22,'xxx',80790,2006,69) , (23,'xxx',91810,2006,71) , (24,'xxx',76030,2006,72) , (25,'xxx',88410,2006,78) , (26,'xxx',81550,2006,85) , (27,'xxx',81830,2006,88) , (28,'xxx',79490,2006,90) , (29,'xxx',62320,2006,93) , (30,'xxx',100400,2006,94) , (31,'xxx',82360,2006,96) , (32,'xxx',77180,2006,98) , (33,'xxx',98430,2006,104) , (34,'xxx',102000,2006,111) , (35,'xxx',77080,2006,114) , (36,'xxx',98440,2006,119) , (37,'xxx',76040,2006,147) , (38,'xxx',75620,2006,155) , (39,'xxx',79620,2006,165) , (40,'xxx',84840,2006,166) , (41,'xxx',94900,2006,167) , (42,'xxx',78470,2006,171) , (43,'xxx',80440,2006,180) , (44,'xxx',85660,2006,194) , (45,'xxx',46220,2006,205) , (46,'xxx',59040,2006,217) , (47,'xxx',60060,2006,218) , (48,'xxx',60010,2006,219) , (49,'xxx',65690,2006,220) , (50,'xxx',57120,2006,222) , (51,'xxx',45750,2006,223) , (52,'xxx',58610,2006,225) , (53,'xxx',63630,2006,226) , (54,'xxx',42560,2006,227) , (55,'xxx',60440,2006,229) , (56,'xxx',37970,2006,231) , (57,'xxx',58560,2006,232) , (58,'xxx',120160,2006,236) , (59,'xxx',57890,2006,240) , (60,'xxx',34030,2006,283) , (61,'xxx',70490,2006,285) , (62,'xxx',67710,2006,286) , (63,'xxx',64970,2006,288) , (64,'xxx',66470,2006,289) , (65,'xxx',56240,2006,290) , (66,'xxx',59160,2006,292) , (67,'xxx',72890,2006,295) , (68,'xxx',64140,2006,299) , (69,'xxx',75580,2006,303) , (70,'xxx',34180,2006,304) , (71,'xxx',48780,2006,310) , (72,'xxx',88120,2006,342) , (73,'xxx',131070,2006,366) , (74,'xxx',0,2006,368) , (75,'xxx',76330,2006,377) , (76,'xxx',89520,2006,380) , (77,'xxx',109580,2006,390) , (78,'xxx',76380,2006,394) , (79,'xxx',36860,2006,399) , (80,'xxx',51410,2006,404) , (81,'xxx',48050,2006,405) , (82,'xxx',37610,2006,414) , (83,'xxx',0,2006,420) , (84,'xxx',67240,2006,421) , (85,'xxx',42610,2006,422) , (86,'xxx',40550,2006,424) , (87,'xxx',36650,2006,427) , (88,'xxx',94460,2006,429) , (89,'xxx',47180,2006,434) , (90,'xxx',71940,2006,450) , (91,'xxx',35240,2006,457) , (92,'xxx',31450,2006,459) , (93,'xxx',57960,2006,461) , (94,'xxx',49680,2006,462) , (95,'xxx',51050,2006,463) , (96,'xxx',46680,2006,465) , (97,'xxx',34090,2006,467) , (98,'xxx',30510,2006,470) , (99,'xxx',31360,2006,471) , (100,'xxx',23410,2006,472) , (101,'xxx',31830,2006,473) , (102,'xxx',26580,2006,474) , (103,'xxx',25910,2006,476) , (104,'xxx',21070,2006,477) , (105,'xxx',23500,2006,478) , (106,'xxx',66250,2006,480) , (107,'xxx',35190,2006,482) , (108,'xxx',31710,2006,483) , (109,'xxx',39540,2006,486) , (110,'xxx',43330,2006,489) , (111,'xxx',57240,2006,492) , (112,'xxx',28210,2006,496) , (113,'xxx',39140,2006,507) , (114,'xxx',38030,2006,514) , (115,'xxx',28010,2006,518) , (116,'xxx',34940,2006,520) , (117,'xxx',58940,2006,521) , (118,'xxx',40860,2006,522) , (119,'xxx',31900,2006,524) , (120,'xxx',61830,2006,525) , (121,'xxx',30550,2006,527) , (122,'xxx',32840,2006,529) , (123,'xxx',43370,2006,530) , (124,'xxx',29500,2006,531) , (125,'xxx',48810,2006,532) , (126,'xxx',84530,2006,533) , (127,'xxx',35400,2006,537) , (128,'xxx',51620,2006,538) , (129,'xxx',79430,2006,540) , (130,'xxx',73910,2006,544) , (131,'xxx',75170,2006,549) , (132,'xxx',40580,2006,553) , (133,'xxx',46460,2006,554) , (134,'xxx',48310,2006,558) , (135,'xxx',51480,2006,560) , (136,'xxx',63630,2006,561) , (137,'xxx',31450,2006,562) , (138,'xxx',58880,2006,565) , (139,'xxx',43870,2006,572) , (140,'xxx',50600,2006,573) , (141,'xxx',28600,2006,575) , (142,'xxx',40220,2006,576) , (143,'xxx',39880,2006,577) , (144,'xxx',47900,2006,578) , (145,'xxx',33150,2006,579) , (146,'xxx',47280,2006,581) , (147,'xxx',46000,2006,582) , (148,'xxx',52930,2006,583) , (149,'xxx',59560,2006,585) , (150,'xxx',50290,2006,587) , (151,'xxx',53720,2006,588) , (152,'xxx',49740,2006,590) , (153,'xxx',51230,2006,591) , (154,'xxx',49690,2006,592) , (155,'xxx',46240,2006,594) , (156,'xxx',42320,2006,595) , (157,'xxx',56520,2006,601) , (158,'xxx',44700,2006,602) , (159,'xxx',45710,2006,604) , (160,'xxx',39750,2006,613) , (161,'xxx',55420,2006,617) , (162,'xxx',103310,2006,636) , (163,'xxx',49680,2006,637) , (164,'xxx',38950,2006,639) , (165,'xxx',87100,2006,640) , (166,'xxx',62900,2006,644) , (167,'xxx',82140,2006,653) , (168,'xxx',68860,2006,656) , (169,'xxx',73200,2006,659) , (170,'xxx',75350,2006,666) , (171,'xxx',81110,2006,703) , (172,'xxx',74840,2006,706) , (173,'xxx',72320,2006,715) , (174,'xxx',61520,2006,718) , (175,'xxx',64520,2006,721) , (176,'xxx',67370,2006,723) , (177,'xxx',46700,2006,725) , (178,'xxx',60710,2006,737) , (179,'xxx',57910,2006,738) , (180,'xxx',78970,2006,742) , (181,'xxx',42970,2006,756) , (182,'xxx',66340,2006,757) , (183,'xxx',74690,2006,758) , (184,'xxx',41510,2006,769) , (185,'xxx',46440,2006,770) , (186,'xxx',27840,2006,771) , (187,'xxx',58990,2006,795) , (188,'xxx',68080,2006,833) , (189,'xxx',63830,2006,835) , (190,'xxx',38630,2006,866) , (191,'xxx',41890,2006,875) , (192,'xxx',69470,2006,879) , (193,'xxx',0,2006,880) , (194,'xxx',88850,2006,881) , (195,'xxx',80080,2006,882) , (196,'xxx',49660,2006,885) , (197,'xxx',43860,2006,886) , (198,'xxx',52390,2006,887) , (199,'xxx',56910,2006,888) , (200,'xxx',39780,2006,889); select count(*) from foo ; truncate foo ; select count(*) from foo ; show table status like 'foo' ; truncate foo ;