How to understand and avoid MySQL row size too large?

Question

In trying the following statement

CREATE TABLE test_table (
 `acco_shortname` VARCHAR(37) NOT NULL,
 `accotitle_algemeen` VARCHAR(20) NOT NULL,
 `accotitle_dubbel` VARCHAR(28) NOT NULL,
 `accotype` VARCHAR(14) NOT NULL,
 `accourl_dubbel` VARCHAR(173) NOT NULL,
 `acm_costs` VARCHAR(2) NOT NULL,
 `area` VARCHAR(30) NOT NULL,
...
 
 `longitude_57` VARCHAR(17) NOT NULL,
 `longitude_58` VARCHAR(17) NOT NULL,
 `longitude_59` VARCHAR(18) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

It has a lot of columns, but the error I get is 'Row size too large (>8126)'.

I counted all the varchar columns lengths and they come up to 8824. That's larger than 8126 so that would make sense. But why am I able to create a table like this successfully?

CREATE TABLE test_table
(
`a` varchar(1000) NOT NULL,
`b` varchar(1000) NOT NULL,
`c` varchar(1000) NOT NULL,
`d` varchar(1000) NOT NULL,
`e` varchar(1000) NOT NULL,
`f` varchar(1000) NOT NULL,
`g` varchar(1000) NOT NULL,
`h` varchar(1000) NOT NULL,
`i` varchar(1000) NOT NULL
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci

These total columns lengths come up to way more than 8126. So why does this succeed. The apparent limit when doing this is now close to 2 x 8126. What's the difference? One might think that now the columns are stored off-page because they're 1000 characters long, but then why would a table like this succeed as well?

create table test_table (
a varchar(189),
b varchar(189),
c varchar(189),
d varchar(189),
e varchar(189),
f varchar(189),
g varchar(189),
h varchar(189),
i varchar(189),
j varchar(189),
k varchar(189),
l varchar(189),
a2 varchar(189),
b3 varchar(189),
c4 varchar(189),
d5 varchar(189),
e6 varchar(189),
f7 varchar(189),
g8 varchar(189),
h9 varchar(189),
i10 varchar(189),
j11 varchar(189),
k12 varchar(189),
l13 varchar(189),
a14 varchar(189),
b15 varchar(189),
c16 varchar(189),
d17 varchar(189),
e18 varchar(189),
f19 varchar(189),
g20 varchar(189),
h21 varchar(189),
i22 varchar(189),
j23 varchar(189),
k24 varchar(189),
l25 varchar(189),
a26 varchar(189),
b27 varchar(189),
c28 varchar(189),
d29 varchar(189),
e30 varchar(189),
f31 varchar(189),
g32 varchar(189),
h33 varchar(189),
i34 varchar(189),
j35 varchar(189),
k36 varchar(189),
l37 varchar(189)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

All columns fall under the 767 byte limit for off page storage, so all of them should fully count towards the row size limit. The total count here is 9072

And how would I know when a statement like this would fail, assuming I know all the lengths of all columns before creating.

• nbk: split the table in two or more, or use Text and not varchar
• Lehren: Can't split the table. I can use TEXT but that will impact performance querying the table
• nbk: it would need changing the software a bit, but splotting tables s normal if they are too big
• Akina: Limits on Table Column Count and Row Size
• Bill Karwin: This is a duplicate of the question you asked on Stack Overflow. Read dev.mysql.com/doc/refman/8.0/en/… to understand the details of how variable-length columns are stored in DYNAMIC row format.

Answer 1

longitude_57 smacks of having an array of items. Probably those values would be better off in a separate table.

Also, if those are longitudes, I recommend not using VARCHAR but something smaller and computable. See https://mysql.rjweb.org/doc.php/latlng#representation_choices for suggestions. For example, FLOAT would take only 4 bytes and provide a resolution of 1.7m / 5.6 ft. This will significantly shrink the table size.

• Lehren: That's not an option.