Bug #120953 Cannot create table - row size too large
Submitted: 18 Jul 16:47 Modified: 20 Jul 7:08
Reporter: Anthony Marston Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:9.7 OS:Windows (Windows 11)
Assigned to: CPU Architecture:Any

[18 Jul 16:47] Anthony Marston 
Description:
I am trying to upgrade from mysql 8.4 to 9.7. I have dumped all the 8.4 databases, but when I try to reload them into the 9.7 server one of the tables fails with "row size too large (> 8126)". The table script is as follows:

use `demo_finance_gl`;

DROP TABLE IF EXISTS `functional_unit`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `functional_unit` (
  `party_id_functional` decimal(38,0) NOT NULL,
  `currency_code_reporting` varchar(16) DEFAULT NULL,
  `chart_id` decimal(38,0) NOT NULL,
  `fiscal_calendar_id` decimal(38,0) NOT NULL,
  `last_closed_fiscal_period_year` smallint unsigned DEFAULT NULL,
  `last_closed_fiscal_period` smallint unsigned DEFAULT NULL,
  `is_audit_period_closed` char(1) NOT NULL DEFAULT 'N',
  `account_id_cash_in_bank` varchar(40) NOT NULL,
  `account_id_trade_receivables` varchar(40) NOT NULL,
  `account_id_vendor_deposit` varchar(40) DEFAULT NULL,
  `account_id_ffe` varchar(40) DEFAULT NULL,
  `account_id_accum_depreciation` varchar(40) DEFAULT NULL,
  `account_id_inventory` varchar(40) DEFAULT NULL,
  `account_id_wip` varchar(40) NOT NULL,
  `account_id_trade_payables` varchar(40) NOT NULL,
  `account_id_sales_tax_payable` varchar(40) DEFAULT NULL,
  `account_id_tax_wh_payable` varchar(40) DEFAULT NULL,
  `account_id_customer_deposit` varchar(40) DEFAULT NULL,
  `account_id_commission_payable` varchar(40) DEFAULT NULL,
  `account_id_pension_payable` varchar(40) DEFAULT NULL,
  `account_id_commission_liability` varchar(40) DEFAULT NULL,
  `account_id_sales_tax_liability` varchar(40) DEFAULT NULL,
  `account_id_accrued_expense` varchar(40) NOT NULL,
  `account_id_retained_earnings` varchar(40) NOT NULL,
  `account_id_sales_supply` varchar(40) DEFAULT NULL,
  `account_id_sales_use` varchar(40) DEFAULT NULL,
  `account_id_sales_service` varchar(40) DEFAULT NULL,
  `account_id_exp_recovery` varchar(40) DEFAULT NULL,
  `account_id_tax_recovery` varchar(40) DEFAULT NULL,
  `account_id_cogs` varchar(40) DEFAULT NULL,
  `account_id_ppv` varchar(40) DEFAULT NULL,
  `party_id_commission` decimal(38,0) DEFAULT NULL,
  `commission_percent` decimal(7,4) DEFAULT NULL,
  `account_id_commission_expense` varchar(40) DEFAULT NULL,
  `account_id_direct_labour` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_reimb` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_mileage` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_air` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_hotel` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_meal_own` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_entertain` varchar(40) DEFAULT NULL,
  `account_id_staff_direct_other` varchar(40) DEFAULT NULL,
  `pension_percent` decimal(7,4) DEFAULT NULL,
  `account_id_pension_expense` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_reimb` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_mileage` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_air` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_hotel` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_meal_own` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_entertain` varchar(40) DEFAULT NULL,
  `account_id_staff_indirect_other` varchar(40) DEFAULT NULL,
  `account_id_depreciation` varchar(40) DEFAULT NULL,
  `account_id_debtor_fin_dsc_offered` varchar(40) NOT NULL,
  `account_id_creditor_fin_dsc_taken` varchar(40) NOT NULL,
  `account_id_book_gain_loss` varchar(40) DEFAULT NULL,
  `account_id_crystallised_gain_loss` varchar(40) DEFAULT NULL,
  `account_id_tax_reimb` varchar(40) DEFAULT NULL,
  `account_id_tax_wh_prepaid` varchar(40) DEFAULT NULL,
  `account_id_tax_wh_final` varchar(40) DEFAULT NULL,
  `account_id_commissions_fees` varchar(40) DEFAULT NULL,
  `account_id_auc` varchar(40) DEFAULT NULL,
  `account_id_expense` varchar(40) DEFAULT NULL,
  `account_id_impairment` varchar(40) DEFAULT NULL,
  `account_id_disposal` varchar(40) DEFAULT NULL,
  `account_id_reserve` varchar(40) DEFAULT NULL,
  `account_id_ylv` varchar(40) DEFAULT NULL,
  `account_id_variance` varchar(40) DEFAULT NULL,
  `created_date` datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
  `created_user` varchar(16) NOT NULL DEFAULT 'UNKNOWN',
  `revised_date` datetime DEFAULT NULL,
  `revised_user` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`party_id_functional`),
  CONSTRAINT `functional_unit_chk_1` CHECK ((`party_id_functional` >= 0)),
  CONSTRAINT `functional_unit_chk_2` CHECK ((`chart_id` >= 0)),
  CONSTRAINT `functional_unit_chk_3` CHECK ((`fiscal_calendar_id` >= 0)),
  CONSTRAINT `functional_unit_chk_4` CHECK ((`party_id_commission` >= 0)),
  CONSTRAINT `functional_unit_chk_5` CHECK ((`commission_percent` >= 0)),
  CONSTRAINT `functional_unit_chk_6` CHECK ((`pension_percent` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;

As you can see there are no columns greater than varchar(40), and I cannot see how the total column size can be anywhere near 8000 characters. This did not fail on version 8.4, so why is it failing in 9.7?

How to repeat:
Run the CREATE TABLE script that I supplied.

Suggested fix:
Find out why it is calculating the wrong row size.
[20 Jul 5:41] Daniël van Eeden 
Full error:
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Besides the solutions that the error suggest this also works:
--innodb-page-size=32768

Normalizing could also help here.

Why trying to create this table on MySQL 8.4.9, I get the same error.

Could you share the `SHOW CREATE TABLE functional_unit\G` output for the existing table?
[20 Jul 5:49] Daniël van Eeden 
Might be related to:
- Bug #120323
- Bug #120504
[20 Jul 7:04] MySQL Verification Team 
actually,  on current trunk we still need innodb_strict_mode=OFF...

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
mysql> select @@innodb_strict_mode,version();
+----------------------+--------------+
| @@innodb_strict_mode | version()    |
+----------------------+--------------+
|                    1 | 26.10.0-asan |
+----------------------+--------------+
1 row in set (0.001 sec)

Setting OFF works:

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected, 1 warning (0.112 sec)

mysql> select @@innodb_strict_mode,version();
+----------------------+--------------+
| @@innodb_strict_mode | version()    |
+----------------------+--------------+
|                    0 | 26.10.0-asan |
+----------------------+--------------+
1 row in set (0.001 sec)
[20 Jul 7:08] MySQL Verification Team 
Verifying this so dev could take a look why it fails in trunk which contains the fix for https://bugs.mysql.com/bug.php?id=120323