Table has no partition for value 217

Question

We have a ugly perl script that is used to parse csv data and populate this into MySQL. The original dev is long gone but now the script is failing and I'm trying to determine why.

MySQL Version 5.7.21

When running it we get this error.

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

I'm trying to determine why exactly this MySQL statement is failing so I can find the underlying cause. All these columns are in the database and are spelled correctly.

Here is the columns we have in that table.

Output of 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) */ |

Answer 1

The root cause of the error seems quite obvious: in the insert statement the value for period_time_pk is 217. If you check out the create table statement for the table, the table is partitioned by period_time_pk field and the last partition expression is:

PARTITION t35 VALUES LESS THAN (217) ENGINE = MyISAM

Therefore there is no partition defined that could hold the value 217. You need to add a new partition to hold this value.

Answer 2

Your table uses RANGE partitioning and is not instructed what to do with values 217 and up.

Read up on what partitioning does and decide if you want to keep it. If so, you will by then also have figured out how you can solve your problem :) (Either by adding partitions or removing partitioning altogether).