SQL Statement with barely 100 columns causes ERROR 1118 (42000)?

Question

During the development of a big project I have run into some troubles with a table being way too big due to over 90+ columns

I have hit the problem of a syntax error:

ERROR 1118 (42000) at line 9: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

I am a little rusty on the inner workings of MySQL, so I have read into the documentation and some other Stackexchange posts with different solutions and talked to one of the other programmers.

The following sql snippet (column names altered due to hide any real column names) shows the structure. As soon as I use import the sql with the w1 column, I get the problem from above.

My question now is:

With the sql statements below with barely 100 columns, have I really hit the row limit of 65535 bytes already?

I thought I am still way below the 65 kb of row size.

Comparing my statement to this one, I thought I should be fine with mine.

CREATE TABLE t (
    a VARCHAR(8000),
    b VARCHAR(10000),
    c VARCHAR(10000),
    d VARCHAR(10000),
    e VARCHAR(10000),
    f VARCHAR(10000),
    g VARCHAR(10000))
ENGINE=InnoDB;

I am a bit overwhelmed by the error, so any respond is greatly appreciated.

Questions read:

• https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large
• InnoDB create table error: "Row size too large"
• https://craftcms.stackexchange.com/questions/11480/internal-server-error-sqlstate42000-1118-row-size-too-large
• https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

SQL Statement

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(128) CHARACTER SET ascii NOT NULL,
  `c` int(10) UNSIGNED NOT NULL COMMENT 'The ID of the target entity.',
  `d` int(10) UNSIGNED NOT NULL COMMENT 'The ID of the target entity.',
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `g` varchar(255) DEFAULT NULL,
  `h` varchar(255) DEFAULT NULL,
  `i` varchar(255) DEFAULT NULL,
  `j` varchar(255) DEFAULT NULL,
  `k` varchar(255) DEFAULT NULL,
  `l` varchar(255) DEFAULT NULL,
  `m` varchar(255) DEFAULT NULL,
  `n` varchar(255) DEFAULT NULL,
  `o` varchar(255) DEFAULT NULL,
  `p` varchar(255) DEFAULT NULL,
  `q` varchar(255) DEFAULT NULL,
  `r` varchar(255) DEFAULT NULL,
  `s` varchar(255) DEFAULT NULL,
  `t` varchar(255) DEFAULT NULL,
  `u` varchar(255) DEFAULT NULL,
  `v` varchar(255) DEFAULT NULL,
  `w` varchar(255) DEFAULT NULL,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  `z` varchar(255) DEFAULT NULL,
  `aa` int(11) DEFAULT NULL,
  `bb` varchar(255) DEFAULT NULL,
  `cc` varchar(255) DEFAULT NULL,
  `dd` varchar(255) DEFAULT NULL,
  `ee` varchar(255) DEFAULT NULL,
  `ff` varchar(255) DEFAULT NULL,
  `gg` varchar(255) DEFAULT NULL,
  `hh` varchar(255) DEFAULT NULL,
  `jj` varchar(255) DEFAULT NULL,
  `ii` varchar(255) DEFAULT NULL,
  `kk` varchar(255) DEFAULT NULL,
  `ll` varchar(20) DEFAULT NULL COMMENT 'The date value.',
  `mm` varchar(255) DEFAULT NULL,
  `nn` varchar(255) DEFAULT NULL,
  `oo` varchar(20) DEFAULT NULL COMMENT 'The date value.',
  `pp` varchar(255) DEFAULT NULL,
  `qq` varchar(255) DEFAULT NULL,
  `rr` varchar(255) DEFAULT NULL,
  `ss` varchar(255) DEFAULT NULL,
  `tt` varchar(255) DEFAULT NULL,
  `uu` tinyint(4) DEFAULT NULL,
  `vv` varchar(20) DEFAULT NULL COMMENT 'The date value.',
  `ww` varchar(255) DEFAULT NULL,
  `xx` varchar(255) DEFAULT NULL,
  `yy` varchar(20) DEFAULT NULL COMMENT 'The date value.',
  `zz` tinyint(4) DEFAULT NULL,
  `aaa` varchar(255) DEFAULT NULL,
  `bbb` varchar(255) DEFAULT NULL,
  `ccc` varchar(255) DEFAULT NULL,
  `ddd` varchar(255) DEFAULT NULL,
  `eee` tinyint(4) DEFAULT NULL,
  `fff` varchar(4) DEFAULT NULL,
  `ggg` varchar(4) DEFAULT NULL,
  `hhh` varchar(255) DEFAULT NULL,
  `iii` varchar(255) DEFAULT NULL,
  `jjj` varchar(4) DEFAULT NULL,
  `kkk` varchar(4) DEFAULT NULL,
  `lll` varchar(255) DEFAULT NULL,
  `mmm` varchar(255) DEFAULT NULL,
  `nnn` varchar(4) DEFAULT NULL,
  `ooo` varchar(4) DEFAULT NULL,
  `ppp` varchar(255) DEFAULT NULL,
  `qqq` varchar(255) DEFAULT NULL,
  `rrr` varchar(4) DEFAULT NULL,
  `sss` varchar(4) DEFAULT NULL,
  `ttt` varchar(255) DEFAULT NULL,
  `www` varchar(255) DEFAULT NULL,
  `uuu` varchar(4) DEFAULT NULL,
  `vvv` varchar(4) DEFAULT NULL,
  `www2` varchar(255) DEFAULT NULL,
  `xxx` varchar(255) DEFAULT NULL,
  `yyyy` varchar(4) DEFAULT NULL,
  `zzzz` varchar(4) DEFAULT NULL,
  `a1` varchar(255) DEFAULT NULL,
  `b1` varchar(255) DEFAULT NULL,
  `c1` varchar(4) DEFAULT NULL,
  `d1` varchar(4) DEFAULT NULL,
  `e1` varchar(255) DEFAULT NULL,
  `f1` varchar(255) DEFAULT NULL,
  `g1` varchar(4) DEFAULT NULL,
  `h1` varchar(4) DEFAULT NULL,
  `j1` varchar(255) DEFAULT NULL,
  `l1` varchar(255) DEFAULT NULL,
  `m1` varchar(4) DEFAULT NULL,
  `n1` varchar(4) DEFAULT NULL,
  `o1` varchar(255) DEFAULT NULL,
  `p1` varchar(255) DEFAULT NULL,

  `q1` varchar(4) DEFAULT NULL,
  `r1` varchar(4) DEFAULT NULL,
  `s1` varchar(255) DEFAULT NULL,
  `t1` varchar(255) DEFAULT NULL,

  `u1` varchar(4) DEFAULT NULL,
  `v1` varchar(4) DEFAULT NULL,
  `w1` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`),
UNIQUE KEY `field__uuid__value` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='asfd';

Answer 1

What version are you running? The ROW_FORMAT may have some relief.

Don't blindly use (255); use realistic limits. This number does matter in leading to the error.

Don't splay an "array" of values across columns; put the array into rows in another table. (Without seeing the column names, I can't tell whether this is an issue.)

Use 3-byte DATE datatype for dates, not VARCHAR(20).

Explicitly override utf8mb4 for any columns that are truly limited to ascii characters (hex, country_code, postal_code, etc).

Use CHAR instead of VARCHAR for truly fixed length columns. (It is rare for a 4-character column to be really variable length.)

Note that the overhead for string that is stored elsewhere is 20+ bytes. 90*20 = 1800 that counts toward the limit. (This may partially explain why you are thinking you should not have hit the limit yet.)

Normalize clumps of things -- such as address1+address2+city+country+postal_code.

Typical UUIDs are 36 characters (ascii) and can be compressed into BINARY(16) (16 bytes).

Consider using shorter integers. INT is 4 bytes; MEDIUMINT is 3, etc.

Note that your obfuscation of the column names led me to making some guesses. With the real column names, some more suggestions may come to mind.