title: Table has no partition for value 217
body: <p>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.</p>
  
  <p>MySQL Version 5.7.21</p>
  
  <p>When running it we get this error.</p>
  
  <p><code>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</code></p>
  
  <p>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. </p>
  
  <p>Here is the columns we have in that table.</p>
  
  <p><a href="https://i.stack.imgur.com/VdKd2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VdKd2.png" alt="table columns"></a></p>
  
  <p>Output of <code>show create table transaction</code>:</p>
  
  <pre><code>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) */ |
  </code></pre>