Row size too large. The maximum row size for the used table type is 65535

thunn · Nov 28, 2017

Hi,
I have a table with 75 fields currently.
When I add a new field, then it raise error:

Code:

ALTER TABLE `mytable`
ADD COLUMN `myfieldname`  varchar(255) NULL AFTER `xxxxxxx`
MySQL said: Documentation
#1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

I have tried to set the following in /etc/my.cnf but no result, error still there:

Code:

[mysqld]
innodb_file_per_table=1
innodb_file_format=barracuda
#innodb_strict_mode=1
innodb_strict_mode=ON
innodb_page_size=32768

This is my table structure:

Code:

CREATE TABLE `mytable` (
  `id` bigint(20) NOT NULL,
  `myfieldname` bigint(20) DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` tinyint(1) DEFAULT '0' COMMENT '0: Ca nhan, 1: Cong ty',
  `myfieldname` bigint(20) DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `myfieldname` text COLLATE utf8mb4_unicode_ci,
  `myfieldname` datetime DEFAULT NULL,
  `myfieldname` bigint(20) DEFAULT NULL,
  `myfieldname` datetime DEFAULT NULL,
  `myfieldname` bigint(20) DEFAULT NULL,
  `myfieldname` tinyint(1) DEFAULT '0' COMMENT '0: Pending - 1: Approve - 2: Cancel'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `mytable`
--
ALTER TABLE `mytable`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `mytable`
--
ALTER TABLE `mytable`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;COMMIT;

Can you help me on this?
Thank you very much!

zEitEr · Nov 29, 2017

Hello,

Official docs say: Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

thunn · Nov 29, 2017

Thanks. But in my localhost, I can create additinal fields without any problem. I don't know which is difference.

wattie · Nov 29, 2017

You cannot create table with total size of all columns exceeding 65535 bytes.

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows

thunn · Dec 5, 2017

OK, thank you very much!

zEitEr · Dec 30, 2017

As one possible fix we used is to change varchar(255) to tinytext.

Row size too large. The maximum row size for the used table type is 65535

thunn

Verified User

zEitEr

Super Moderator

thunn

Verified User

wattie

Verified User

thunn

Verified User

zEitEr

Super Moderator