How is the Row size too large message properly resolved when trying to restore db?

When trying to restore a mysql database, I try the following. This db is in a container

cat ~/Downloads/dbname_bkp_20240423-01.sql | docker exec -i mysql-server-main /usr/bin/mysql -u root --password=password dbname

I get this message:

ERROR 1118 (42000) at line 25: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

Line 25

DROP TABLE IF EXISTS `accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `accounts` (
  `id` char(36) NOT NULL,
  `name` varchar(150) DEFAULT NULL,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `description` text,
  `deleted` tinyint(1) DEFAULT '0',
  `assigned_user_id` char(36) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `industry` varchar(50) DEFAULT NULL,
  `annual_revenue` varchar(100) DEFAULT NULL,
  `phone_fax` varchar(100) DEFAULT NULL,
  `billing_address_street` varchar(150) DEFAULT NULL,
  `billing_address_city` varchar(100) DEFAULT NULL,
  `billing_address_state` varchar(100) DEFAULT NULL,
  `billing_address_postalcode` varchar(20) DEFAULT NULL,
  `billing_address_country` varchar(255) DEFAULT NULL,
  `rating` varchar(100) DEFAULT NULL,
  `phone_office` varchar(100) DEFAULT NULL,
  `phone_alternate` varchar(100) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `ownership` varchar(100) DEFAULT NULL,
  `employees` varchar(10) DEFAULT NULL,
  `ticker_symbol` varchar(10) DEFAULT NULL,
  `shipping_address_street` varchar(150) DEFAULT NULL,
  `shipping_address_city` varchar(100) DEFAULT NULL,
  `shipping_address_state` varchar(100) DEFAULT NULL,
  `shipping_address_postalcode` varchar(20) DEFAULT NULL,
  `shipping_address_country` varchar(255) DEFAULT NULL,
  `parent_id` char(36) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_accnt_id_del` (`id`,`deleted`) USING BTREE,
  KEY `idx_accnt_name_del` (`name`,`deleted`) USING BTREE,
  KEY `idx_accnt_assigned_del` (`deleted`,`assigned_user_id`) USING BTREE,
  KEY `idx_accnt_parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
/*!40101 SET character_set_client = @saved_cs_client */;

Any suggestions on how to handle this message?

Greetings and thanks in advance

Solution

In your table I count a total of 2701 characters for the char and varchar columns, which means 8103 bytes because utf8 counts up to 3 bytes per character.

Plus up to 768 bytes for the text column. Any text in excess of 768 bytes will overflow onto additional pages.

That's how the COMPACT row format works. It stores up to 768 bytes on the page per CHAR/VARCHAR/TEXT column, and that must total less than 8126 bytes. But in your case, it exceeds 8126 bytes.

You can fix this by using DYNAMIC row format instead of COMPACT. That stores strings differently; if there are too many to fit on the page, it'll push all of the string onto overflow page(s), and store only a 20-byte reference on the main page.

Or you can shorten some of your string column lengths, so the total length is short enough to fit within the limit.

Or you can use a single-byte character set for some columns (where appropriate), so each string counts only one byte per character.

Or you can eliminate some columns.