I recently upgrade mySQL 5.6 to 5.7 to 8
currently they’re working as chain replication but that’s not the case
for some table that I create in mySQL 5.7 im getting row size error in mysql 8, for exmaple:
CREATE TABLE test_max_row_size (
userip varchar(255) DEFAULT NULL,
device varchar(255) DEFAULT NULL,
device_type varchar(255) DEFAULT NULL,
os varchar(255) DEFAULT NULL,
countrycode varchar(255) DEFAULT NULL,
city varchar(255) DEFAULT NULL,
attribution varchar(2048) DEFAULT NULL,
lookback varchar(255) DEFAULT NULL,
date_time varchar(255) DEFAULT NULL,
guid varchar(255) DEFAULT NULL,
package varchar(255) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET=utf8mb3;
the error in mysql 8:
2024-08-11T06:06:32.001169Z 10 [ERROR] [MY-011825] [InnoDB] Cannot add field `package` in table `test`.`test_max_row_size` because after adding it, the row size is 8486 which is greater than maximum allowed size (8126) for a record on index leaf page.
I know that making it as ROW_FORMAT=Dynamic or making innodb_default_row_format = Dynamic in mySQL 8 will fix the issue, but im trying to understand why would it work in 5.7 and raise an error in 8 and if my calculation is even right.
innodb_strict_mode = ON both on 5.7 and 8.
innodb_file_format = Barracuda on 5.7 and on 8 from my understanding its removed variable.
From my understanding there are 2 limits: mySQL’s limit =65,535 bytes and InnoDB’s limit = 8126 bytes.
Since it’s compact my calculation is like this:
255103 + (768 + 2) = 8420
(10 columns of 255 chars and utf8mb3) + (main page contains the first 768 bytes of attribution
and 2 bytes pointer )
8420 > 8126 so it should have failed in mysql 5.7 even
Also, why the ERROR is specifically on package
column?
Hope someone can explain the calculation as well
I want to know how to calculate correctly and why would it work on 5.7 and not on 8