Results 1 to 6 of 6

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

  1. #1
    Join Date
    Mar 2012
    Posts
    167

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

    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!

  2. #2
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,018
    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...unt-limit.html

  3. #3
    Join Date
    Mar 2012
    Posts
    167
    Thanks. But in my localhost, I can create additinal fields without any problem. I don't know which is difference.

  4. #4
    Join Date
    May 2008
    Posts
    652
    You cannot create table with total size of all columns exceeding 65535 bytes.

    https://dev.mysql.com/doc/refman/5.7...unt-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

  5. #5
    Join Date
    Mar 2012
    Posts
    167
    OK, thank you very much!

  6. #6
    Join Date
    Apr 2005
    Location
    GMT +7.00
    Posts
    12,018
    As one possible fix we used is to change varchar(255) to tinytext.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •