Root Cause Analysis of MySQL Index and Implicit Conversion Issue Leading to Production Failure

Introduction: The article describes a sudden production outage of a 5‑year‑old service caused by an unexpected MySQL query returning no data.

Investigation: The problematic SQL is select * from w_location where location_type = 'SORTING_TEMPORARY' AND status = '1' . The status column is defined as bit(1) , but the query uses a string literal.

Root cause analysis: Because an index (idx_location_type_status) exists on location_type and status , MySQL performs an implicit type conversion. When the condition is status='1' , the bit value is converted to a string, which does not match the stored binary value, causing the index to be missed. Without the index, MySQL would scan the whole table and perform a uniform conversion.

Table definition of w_location (relevant excerpt): CREATE TABLE `w_location` ( `id` bigint(19) NOT NULL COMMENT '主键id', `warehouse_id` bigint(19) DEFAULT NULL COMMENT '仓库id', `warehouse_code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '仓库code', `location_code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '库位编码', `location_type` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '库位类型', `status` bit(1) DEFAULT NULL COMMENT '状态（1：生效，0：失效）', ... PRIMARY KEY (`id`) USING BTREE, KEY `idx_wh_location_type` (`warehouse_code`,`location_code`,`location_type`), KEY `index_location_type_status` (`location_type`,`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='库位';

Solution: The urgent fix was to ask the DBA to drop the newly added index, allowing the query to use a full‑table scan and return correct results. The article also shows screenshots illustrating the issue.

Conclusion: Indexes combined with implicit conversion of BIT fields can lead to subtle bugs; developers should avoid comparing BIT columns with string literals and keep implicit conversions to a minimum.