0

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:

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';
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.

  • Thx so much, Rick. I am Using Mysql Ver 5.5.42. What you see above is an defined Entity which will be generated by the CMS Drupal and it seem that it clearly is not optimised for any kind of performance. I will take all your great tips into consideration when I am about to optimise the table. – Martin May 5 '17 at 8:50
  • @Martin - Alas, 3rd party packages sometimes cause more grief than benefit. Condolences. – Rick James Feb 27 '18 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.