我们有一个丑陋的perl脚本，用于解析csv数据并将其填充到MySQL中。原始开发人员早已消失，但现在脚本失败了，我正在尝试确定原因。
MySQL版本5.7.21
运行它时，我们会收到此错误。DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Table has no partition for value 217 [for Statement "INSERT INTO transaction ( case_qty, customer_pk, da_allocation, dist_customer_pk, dist_invoice_number, dist_item_pk, dist_manufacturer_pk, dist_quantity, dist_txn_date, distributor_pk, fb_item_pk, fb_manufacturer_pk, historical_hierarchy_pk, mfr_operator_va, mfr_rebateable_volume, min, original_rebate, period_time_pk, reporting_qty, reporting_uom, total_qty_purchased, total_vol, transaction_time_pk) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues: 0=4, 1='19457', 2=0, 3='373809', 4='17755751', 5='106004', 6='75959', 7=4, 8='2018-01-19', 9='63', 10='227174', 11='130', 12='29546', 13=0, 14=0, 15='7888-928', 16=0, 17=217, 18=4, 19='CA', 20=41.3, 21=165.2, 22='217'] at load_usage.pl line 152
我试图确定为什么这个MySQL语句确实失败了，所以我可以找到根本原因。所有这些列都在数据库中，并且拼写正确。
这是我们在该表中的列。show create table transaction的输出：
CREATE TABLE `transaction` (
`pk` int(10) unsigned NOT NULL AUTO_INCREMENT,
`historical_hierarchy_pk` smallint(5) unsigned NOT NULL,
`dist_item_pk` int(10) unsigned NOT NULL,
`dist_manufacturer_pk` mediumint(8) unsigned NOT NULL,
`distributor_pk` smallint(5) unsigned NOT NULL,
`fb_item_pk` int(10) unsigned NOT NULL,
`fb_manufacturer_pk` mediumint(8) unsigned NOT NULL,
`customer_pk` mediumint(8) unsigned NOT NULL,
`dist_customer_pk` mediumint(8) unsigned NOT NULL,
`period_time_pk` smallint(5) unsigned NOT NULL,
`transaction_time_pk` smallint(5) unsigned NOT NULL,
`dist_txn_date` date NOT NULL,
`dist_quantity` decimal(7,2) NOT NULL DEFAULT '0.00',
`total_vol` decimal(9,2) NOT NULL DEFAULT '0.00',
`total_qty_purchased` decimal(10,4) NOT NULL DEFAULT '0.0000',
`reporting_qty` decimal(10,4) NOT NULL DEFAULT '0.0000',
`reporting_uom` char(2) NOT NULL DEFAULT '',
`case_qty` decimal(10,4) NOT NULL DEFAULT '0.0000',
`mfr_rebateable_volume` decimal(9,2) NOT NULL DEFAULT '0.00',
`mfr_operator_va` decimal(9,2) NOT NULL DEFAULT '0.00',
`dist_invoice_number` varchar(30) NOT NULL DEFAULT '',
`min` varchar(30) NOT NULL DEFAULT '',
`original_rebate` decimal(9,2) NOT NULL DEFAULT '0.00',
`da_allocation` decimal(9,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`pk`,`period_time_pk`),
KEY `historical_hierarchy_pk_key` (`historical_hierarchy_pk`),
KEY `dist_item_pk_key` (`dist_item_pk`),
KEY `dist_manufacturer_pk_key` (`dist_manufacturer_pk`),
KEY `distributor_pk_key` (`distributor_pk`),
KEY `fb_item_pk_key` (`fb_item_pk`),
KEY `fb_manufacturer_pk_key` (`fb_manufacturer_pk`),
KEY `customer_pk_key` (`customer_pk`),
KEY `period_time_pk_key` (`period_time_pk`),
KEY `transaction_time_pk_key` (`transaction_time_pk`),
KEY `dist_customer_key` (`dist_customer_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=351758733 DEFAULT CHARSET=latin1 COMMENT='$Revision$'
/*!50100 PARTITION BY RANGE (period_time_pk)
(PARTITION t0 VALUES LESS THAN (112) ENGINE = MyISAM,
PARTITION t1 VALUES LESS THAN (116) ENGINE = MyISAM,
PARTITION t2 VALUES LESS THAN (119) ENGINE = MyISAM,
PARTITION t3 VALUES LESS THAN (121) ENGINE = MyISAM,
PARTITION t4 VALUES LESS THAN (124) ENGINE = MyISAM,
PARTITION t5 VALUES LESS THAN (127) ENGINE = MyISAM,
PARTITION t6 VALUES LESS THAN (130) ENGINE = MyISAM,
PARTITION t7 VALUES LESS THAN (133) ENGINE = MyISAM,
PARTITION t8 VALUES LESS THAN (136) ENGINE = MyISAM,
PARTITION t9 VALUES LESS THAN (139) ENGINE = MyISAM,
PARTITION t10 VALUES LESS THAN (142) ENGINE = MyISAM,
PARTITION t11 VALUES LESS THAN (145) ENGINE = MyISAM,
PARTITION t12 VALUES LESS THAN (148) ENGINE = MyISAM,
PARTITION t13 VALUES LESS THAN (151) ENGINE = MyISAM,
PARTITION t14 VALUES LESS THAN (154) ENGINE = MyISAM,
PARTITION t15 VALUES LESS THAN (157) ENGINE = MyISAM,
PARTITION t16 VALUES LESS THAN (160) ENGINE = MyISAM,
PARTITION t17 VALUES LESS THAN (163) ENGINE = MyISAM,
PARTITION t18 VALUES LESS THAN (166) ENGINE = MyISAM,
PARTITION t19 VALUES LESS THAN (169) ENGINE = MyISAM,
PARTITION t20 VALUES LESS THAN (172) ENGINE = MyISAM,
PARTITION t21 VALUES LESS THAN (175) ENGINE = MyISAM,
PARTITION t22 VALUES LESS THAN (178) ENGINE = MyISAM,
PARTITION t23 VALUES LESS THAN (181) ENGINE = MyISAM,
PARTITION t24 VALUES LESS THAN (184) ENGINE = MyISAM,
PARTITION t25 VALUES LESS THAN (187) ENGINE = MyISAM,
PARTITION t26 VALUES LESS THAN (190) ENGINE = MyISAM,
PARTITION t27 VALUES LESS THAN (193) ENGINE = MyISAM,
PARTITION t28 VALUES LESS THAN (196) ENGINE = MyISAM,
PARTITION t29 VALUES LESS THAN (199) ENGINE = MyISAM,
PARTITION t30 VALUES LESS THAN (202) ENGINE = MyISAM,
PARTITION t31 VALUES LESS THAN (205) ENGINE = MyISAM,
PARTITION t32 VALUES LESS THAN (208) ENGINE = MyISAM,
PARTITION t33 VALUES LESS THAN (211) ENGINE = MyISAM,
PARTITION t34 VALUES LESS THAN (214) ENGINE = MyISAM,
PARTITION t35 VALUES LESS THAN (217) ENGINE = MyISAM) */ |
最佳答案
您的表使用RANGE partitioning，并且没有指示如何处理值217及更高值。
阅读有关分区功能的信息，并决定是否要保留它。如果是这样，那么到那时您还将弄清楚如何解决您的问题:)（通过添加分区或完全删除分区）。
关于mysql - 表格没有值217的分区，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48915439/