From 069f3bc2b60d01d0d7ac1d28b8180347b46db1c8 Mon Sep 17 00:00:00 2001 From: Meng-Hsiu Chiang Date: Tue, 5 May 2026 20:46:04 +0000 Subject: [PATCH] Fix "Row size too large" regression (Bug#120323) MySQL 8.0.46 / 8.4.9 / 9.x introduced a regression where CREATE TABLE fails with "Row size too large (> 8126)" for tables with many varchar(41-255) columns using ROW_FORMAT=DYNAMIC. Root cause ---------- Commits a87a5dbb2a4 (Bug#25579578) and f9c88132a87 (Bug#39129182) refactored dict_index_node_ptr_max_size() to use get_field_max_size() and added a DATA_BIG_COL(col) guard to the external-storage eligibility check. This prevents columns with col->len <= 255 from being counted as external-storage eligible, inflating the row-size estimate beyond the page limit. Old code (before the regression): Any variable-length column with get_max_size() > 40 in the clustered index was eligible for external storage, capping its row-size contribution at BTR_EXTERN_LOCAL_STORED_MAX_SIZE (40 bytes). New code (after the regression): The DATA_BIG_COL(col) condition (col->len > 255) was added, excluding columns like varchar(200) latin1 (col->len=200) from the cap. Fix --- Remove the DATA_BIG_COL(col) condition from the external-storage eligibility check in get_field_max_size(), restoring the original behavior where any variable-length column with get_max_size() > BTR_EXTERN_LOCAL_STORED_MAX_SIZE is eligible for the external storage cap in the row-size estimate. Why this does not affect the deadlock fix (Bug#39129182) -------------------------------------------------------- The deadlock fix introduced the `always_inlined` check, which ensures that the first dict_index_get_n_unique_in_tree(index) fields (navigation columns used for B-tree binary search) are never capped at 40 bytes. This guarantees btr_cur_will_modify_tree() gets an accurate node pointer size estimate and takes the correct latch (X vs SX). Our change is in the `else if (!always_inlined && ...)` branch, which by definition only applies to non-navigation columns. These columns do not appear in node pointer records and are irrelevant to the latch prediction. The deadlock fix remains fully intact. Trade-off: DDL gatekeeping vs backward compatibility ---------------------------------------------------- The DATA_BIG_COL check made the DDL estimation consistent with the runtime check in dtuple_convert_big_rec(), which also refuses to store columns with col->len <= 255 externally (due to the 1-byte length encoding having no room for the external storage flag). This means removing DATA_BIG_COL allows CREATE TABLE to succeed for schemas where, if every column were filled to its maximum declared length simultaneously, the INSERT would fail with DB_TOO_BIG_RECORD at runtime. However: - This is the same behavior as all MySQL versions prior to 8.0.46. - In practice, users rarely fill every varchar column to its declared maximum simultaneously. - These schemas have worked in production for years without issues. - The runtime DB_TOO_BIG_RECORD error remains as a safety net for the rare case where a row actually overflows. - The MySQL documentation states that columns <= 40 bytes are stored inline, with no mention of a col->len > 255 requirement for external storage eligibility. The regression breaks existing real-world schemas on upgrade, which is a more severe impact than the theoretical worst-case INSERT failure that the gatekeeping prevents. Also updates innodb_zip/bug52745.result: the "Row size too large" warning for that test was introduced by the same commit (f9c88132a87) and did not exist prior to the regression. The warning was a side effect of the same inflated estimation for columns like varbinary(221) (col->len=221 <= 255). Removing it is safe because innodb_strict_mode=OFF means it is a warning (not an error), and the table is created either way. Upstream bug: https://bugs.mysql.com/bug.php?id=120323 This contribution is under the OCA signed by Amazon and covering submissions to the MySQL project. --- .../bug120323_row_size_insert_boundary.result | 22 +++ ...20323_row_size_too_large_regression.result | 71 +++++++++ .../t/bug120323_row_size_insert_boundary.test | 66 +++++++++ ...g120323_row_size_too_large_regression.test | 136 ++++++++++++++++++ mysql-test/suite/innodb_zip/r/bug52745.result | 1 - storage/innobase/dict/dict0dict.cc | 2 +- 6 files changed, 296 insertions(+), 2 deletions(-) create mode 100644 mysql-test/suite/innodb/r/bug120323_row_size_insert_boundary.result create mode 100644 mysql-test/suite/innodb/r/bug120323_row_size_too_large_regression.result create mode 100644 mysql-test/suite/innodb/t/bug120323_row_size_insert_boundary.test create mode 100644 mysql-test/suite/innodb/t/bug120323_row_size_too_large_regression.test diff --git a/mysql-test/suite/innodb/r/bug120323_row_size_insert_boundary.result b/mysql-test/suite/innodb/r/bug120323_row_size_insert_boundary.result new file mode 100644 index 000000000000..7fc0a4f2ddea --- /dev/null +++ b/mysql-test/suite/innodb/r/bug120323_row_size_insert_boundary.result @@ -0,0 +1,22 @@ +DROP DATABASE IF EXISTS bug120323; +CREATE DATABASE bug120323; +USE bug120323; +# +# Create a table with 100 x varchar(200) latin1 DYNAMIC. +# With the fix, CREATE TABLE succeeds. +# +CREATE TABLE t1 ( c1 varchar(200), c2 varchar(200), c3 varchar(200), c4 varchar(200), c5 varchar(200), c6 varchar(200), c7 varchar(200), c8 varchar(200), c9 varchar(200), c10 varchar(200), c11 varchar(200), c12 varchar(200), c13 varchar(200), c14 varchar(200), c15 varchar(200), c16 varchar(200), c17 varchar(200), c18 varchar(200), c19 varchar(200), c20 varchar(200), c21 varchar(200), c22 varchar(200), c23 varchar(200), c24 varchar(200), c25 varchar(200), c26 varchar(200), c27 varchar(200), c28 varchar(200), c29 varchar(200), c30 varchar(200), c31 varchar(200), c32 varchar(200), c33 varchar(200), c34 varchar(200), c35 varchar(200), c36 varchar(200), c37 varchar(200), c38 varchar(200), c39 varchar(200), c40 varchar(200), c41 varchar(200), c42 varchar(200), c43 varchar(200), c44 varchar(200), c45 varchar(200), c46 varchar(200), c47 varchar(200), c48 varchar(200), c49 varchar(200), c50 varchar(200), c51 varchar(200), c52 varchar(200), c53 varchar(200), c54 varchar(200), c55 varchar(200), c56 varchar(200), c57 varchar(200), c58 varchar(200), c59 varchar(200), c60 varchar(200), c61 varchar(200), c62 varchar(200), c63 varchar(200), c64 varchar(200), c65 varchar(200), c66 varchar(200), c67 varchar(200), c68 varchar(200), c69 varchar(200), c70 varchar(200), c71 varchar(200), c72 varchar(200), c73 varchar(200), c74 varchar(200), c75 varchar(200), c76 varchar(200), c77 varchar(200), c78 varchar(200), c79 varchar(200), c80 varchar(200), c81 varchar(200), c82 varchar(200), c83 varchar(200), c84 varchar(200), c85 varchar(200), c86 varchar(200), c87 varchar(200), c88 varchar(200), c89 varchar(200), c90 varchar(200), c91 varchar(200), c92 varchar(200), c93 varchar(200), c94 varchar(200), c95 varchar(200), c96 varchar(200), c97 varchar(200), c98 varchar(200), c99 varchar(200), c100 varchar(200) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; +# +# INSERT with short data succeeds - row fits on page. +# +INSERT INTO t1 (c1, c2, c3) VALUES ('a', 'b', 'c'); +# +# INSERT with all 100 columns at max length (200 bytes each) fails. +# Total payload = 100 * 200 = 20000 bytes, exceeds page limit. +# InnoDB cannot store these columns externally because col->len <= 255 +# (1-byte length encoding has no external storage flag). +# This is expected and matches pre-8.0.46 behavior. +# +INSERT INTO t1 VALUES ( REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200), REPEAT('x', 200)); +ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. +DROP DATABASE bug120323; diff --git a/mysql-test/suite/innodb/r/bug120323_row_size_too_large_regression.result b/mysql-test/suite/innodb/r/bug120323_row_size_too_large_regression.result new file mode 100644 index 000000000000..ba6cf466a314 --- /dev/null +++ b/mysql-test/suite/innodb/r/bug120323_row_size_too_large_regression.result @@ -0,0 +1,71 @@ +DROP DATABASE IF EXISTS bug120323; +CREATE DATABASE bug120323; +USE bug120323; +# +# Test 1: 100 x varchar(200) latin1 columns with ROW_FORMAT=DYNAMIC +# This is the minimal repro - fails without the fix. +# +CREATE TABLE t_varchar200 ( c1 varchar(200), c2 varchar(200), c3 varchar(200), c4 varchar(200), c5 varchar(200), c6 varchar(200), c7 varchar(200), c8 varchar(200), c9 varchar(200), c10 varchar(200), c11 varchar(200), c12 varchar(200), c13 varchar(200), c14 varchar(200), c15 varchar(200), c16 varchar(200), c17 varchar(200), c18 varchar(200), c19 varchar(200), c20 varchar(200), c21 varchar(200), c22 varchar(200), c23 varchar(200), c24 varchar(200), c25 varchar(200), c26 varchar(200), c27 varchar(200), c28 varchar(200), c29 varchar(200), c30 varchar(200), c31 varchar(200), c32 varchar(200), c33 varchar(200), c34 varchar(200), c35 varchar(200), c36 varchar(200), c37 varchar(200), c38 varchar(200), c39 varchar(200), c40 varchar(200), c41 varchar(200), c42 varchar(200), c43 varchar(200), c44 varchar(200), c45 varchar(200), c46 varchar(200), c47 varchar(200), c48 varchar(200), c49 varchar(200), c50 varchar(200), c51 varchar(200), c52 varchar(200), c53 varchar(200), c54 varchar(200), c55 varchar(200), c56 varchar(200), c57 varchar(200), c58 varchar(200), c59 varchar(200), c60 varchar(200), c61 varchar(200), c62 varchar(200), c63 varchar(200), c64 varchar(200), c65 varchar(200), c66 varchar(200), c67 varchar(200), c68 varchar(200), c69 varchar(200), c70 varchar(200), c71 varchar(200), c72 varchar(200), c73 varchar(200), c74 varchar(200), c75 varchar(200), c76 varchar(200), c77 varchar(200), c78 varchar(200), c79 varchar(200), c80 varchar(200), c81 varchar(200), c82 varchar(200), c83 varchar(200), c84 varchar(200), c85 varchar(200), c86 varchar(200), c87 varchar(200), c88 varchar(200), c89 varchar(200), c90 varchar(200), c91 varchar(200), c92 varchar(200), c93 varchar(200), c94 varchar(200), c95 varchar(200), c96 varchar(200), c97 varchar(200), c98 varchar(200), c99 varchar(200), c100 varchar(200) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; +SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='bug120323' AND table_name='t_varchar200'; +COUNT(*) +100 +DROP TABLE t_varchar200; +# +# Test 2: 100 x varchar(255) latin1 - boundary at DATA_BIG_COL threshold +# +CREATE TABLE t_varchar255 ( c1 varchar(255), c2 varchar(255), c3 varchar(255), c4 varchar(255), c5 varchar(255), c6 varchar(255), c7 varchar(255), c8 varchar(255), c9 varchar(255), c10 varchar(255), c11 varchar(255), c12 varchar(255), c13 varchar(255), c14 varchar(255), c15 varchar(255), c16 varchar(255), c17 varchar(255), c18 varchar(255), c19 varchar(255), c20 varchar(255), c21 varchar(255), c22 varchar(255), c23 varchar(255), c24 varchar(255), c25 varchar(255), c26 varchar(255), c27 varchar(255), c28 varchar(255), c29 varchar(255), c30 varchar(255), c31 varchar(255), c32 varchar(255), c33 varchar(255), c34 varchar(255), c35 varchar(255), c36 varchar(255), c37 varchar(255), c38 varchar(255), c39 varchar(255), c40 varchar(255), c41 varchar(255), c42 varchar(255), c43 varchar(255), c44 varchar(255), c45 varchar(255), c46 varchar(255), c47 varchar(255), c48 varchar(255), c49 varchar(255), c50 varchar(255), c51 varchar(255), c52 varchar(255), c53 varchar(255), c54 varchar(255), c55 varchar(255), c56 varchar(255), c57 varchar(255), c58 varchar(255), c59 varchar(255), c60 varchar(255), c61 varchar(255), c62 varchar(255), c63 varchar(255), c64 varchar(255), c65 varchar(255), c66 varchar(255), c67 varchar(255), c68 varchar(255), c69 varchar(255), c70 varchar(255), c71 varchar(255), c72 varchar(255), c73 varchar(255), c74 varchar(255), c75 varchar(255), c76 varchar(255), c77 varchar(255), c78 varchar(255), c79 varchar(255), c80 varchar(255), c81 varchar(255), c82 varchar(255), c83 varchar(255), c84 varchar(255), c85 varchar(255), c86 varchar(255), c87 varchar(255), c88 varchar(255), c89 varchar(255), c90 varchar(255), c91 varchar(255), c92 varchar(255), c93 varchar(255), c94 varchar(255), c95 varchar(255), c96 varchar(255), c97 varchar(255), c98 varchar(255), c99 varchar(255), c100 varchar(255) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; +SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='bug120323' AND table_name='t_varchar255'; +COUNT(*) +100 +DROP TABLE t_varchar255; +# +# Test 3: varchar(256) columns - these always worked because +# col->len > 255 satisfies DATA_BIG_COL. Verify no regression. +# +CREATE TABLE t_varchar256 ( c1 varchar(256), c2 varchar(256), c3 varchar(256), c4 varchar(256), c5 varchar(256), c6 varchar(256), c7 varchar(256), c8 varchar(256), c9 varchar(256), c10 varchar(256), c11 varchar(256), c12 varchar(256), c13 varchar(256), c14 varchar(256), c15 varchar(256), c16 varchar(256), c17 varchar(256), c18 varchar(256), c19 varchar(256), c20 varchar(256), c21 varchar(256), c22 varchar(256), c23 varchar(256), c24 varchar(256), c25 varchar(256), c26 varchar(256), c27 varchar(256), c28 varchar(256), c29 varchar(256), c30 varchar(256), c31 varchar(256), c32 varchar(256), c33 varchar(256), c34 varchar(256), c35 varchar(256), c36 varchar(256), c37 varchar(256), c38 varchar(256), c39 varchar(256), c40 varchar(256), c41 varchar(256), c42 varchar(256), c43 varchar(256), c44 varchar(256), c45 varchar(256), c46 varchar(256), c47 varchar(256), c48 varchar(256), c49 varchar(256), c50 varchar(256), c51 varchar(256), c52 varchar(256), c53 varchar(256), c54 varchar(256), c55 varchar(256), c56 varchar(256), c57 varchar(256), c58 varchar(256), c59 varchar(256), c60 varchar(256), c61 varchar(256), c62 varchar(256), c63 varchar(256), c64 varchar(256), c65 varchar(256), c66 varchar(256), c67 varchar(256), c68 varchar(256), c69 varchar(256), c70 varchar(256), c71 varchar(256), c72 varchar(256), c73 varchar(256), c74 varchar(256), c75 varchar(256), c76 varchar(256), c77 varchar(256), c78 varchar(256), c79 varchar(256), c80 varchar(256), c81 varchar(256), c82 varchar(256), c83 varchar(256), c84 varchar(256), c85 varchar(256), c86 varchar(256), c87 varchar(256), c88 varchar(256), c89 varchar(256), c90 varchar(256), c91 varchar(256), c92 varchar(256), c93 varchar(256), c94 varchar(256), c95 varchar(256), c96 varchar(256), c97 varchar(256), c98 varchar(256), c99 varchar(256), c100 varchar(256) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; +SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='bug120323' AND table_name='t_varchar256'; +COUNT(*) +100 +DROP TABLE t_varchar256; +# +# Test 4: Mixed varchar lengths (41-255) simulating real-world schema +# +CREATE TABLE t_mixed ( +c1 varchar(41), c2 varchar(50), c3 varchar(80), c4 varchar(100), +c5 varchar(120), c6 varchar(150), c7 varchar(180), c8 varchar(200), +c9 varchar(220), c10 varchar(255), c11 varchar(41), c12 varchar(50), +c13 varchar(80), c14 varchar(100), c15 varchar(120), c16 varchar(150), +c17 varchar(180), c18 varchar(200), c19 varchar(220), c20 varchar(255), +c21 varchar(41), c22 varchar(50), c23 varchar(80), c24 varchar(100), +c25 varchar(120), c26 varchar(150), c27 varchar(180), c28 varchar(200), +c29 varchar(220), c30 varchar(255), c31 varchar(41), c32 varchar(50), +c33 varchar(80), c34 varchar(100), c35 varchar(120), c36 varchar(150), +c37 varchar(180), c38 varchar(200), c39 varchar(220), c40 varchar(255), +c41 varchar(41), c42 varchar(50), c43 varchar(80), c44 varchar(100), +c45 varchar(120), c46 varchar(150), c47 varchar(180), c48 varchar(200), +c49 varchar(220), c50 varchar(255), c51 varchar(41), c52 varchar(50), +c53 varchar(80), c54 varchar(100), c55 varchar(120), c56 varchar(150), +c57 varchar(180), c58 varchar(200), c59 varchar(220), c60 varchar(255), +c61 varchar(41), c62 varchar(50), c63 varchar(80), c64 varchar(100), +c65 varchar(120), c66 varchar(150), c67 varchar(180), c68 varchar(200), +c69 varchar(220), c70 varchar(255), c71 varchar(41), c72 varchar(50), +c73 varchar(80), c74 varchar(100), c75 varchar(120), c76 varchar(150), +c77 varchar(180), c78 varchar(200), c79 varchar(220), c80 varchar(255) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; +# Verify INSERT and SELECT work +INSERT INTO t_mixed (c1, c2, c3) VALUES ('a', 'b', 'c'); +SELECT c1, c2, c3 FROM t_mixed; +c1 c2 c3 +a b c +# Verify CREATE TABLE LIKE works (upgrade path scenario) +CREATE TABLE t_mixed_copy LIKE t_mixed; +DROP TABLE t_mixed_copy; +DROP TABLE t_mixed; +# +# Test 5: 100 x varchar(40) latin1 - at the external storage threshold +# (get_max_size() <= BTR_EXTERN_LOCAL_STORED_MAX_SIZE, never stored externally). +# Should work regardless of fix. Sanity check. +# +CREATE TABLE t_varchar40 ( c1 varchar(40), c2 varchar(40), c3 varchar(40), c4 varchar(40), c5 varchar(40), c6 varchar(40), c7 varchar(40), c8 varchar(40), c9 varchar(40), c10 varchar(40), c11 varchar(40), c12 varchar(40), c13 varchar(40), c14 varchar(40), c15 varchar(40), c16 varchar(40), c17 varchar(40), c18 varchar(40), c19 varchar(40), c20 varchar(40), c21 varchar(40), c22 varchar(40), c23 varchar(40), c24 varchar(40), c25 varchar(40), c26 varchar(40), c27 varchar(40), c28 varchar(40), c29 varchar(40), c30 varchar(40), c31 varchar(40), c32 varchar(40), c33 varchar(40), c34 varchar(40), c35 varchar(40), c36 varchar(40), c37 varchar(40), c38 varchar(40), c39 varchar(40), c40 varchar(40), c41 varchar(40), c42 varchar(40), c43 varchar(40), c44 varchar(40), c45 varchar(40), c46 varchar(40), c47 varchar(40), c48 varchar(40), c49 varchar(40), c50 varchar(40), c51 varchar(40), c52 varchar(40), c53 varchar(40), c54 varchar(40), c55 varchar(40), c56 varchar(40), c57 varchar(40), c58 varchar(40), c59 varchar(40), c60 varchar(40), c61 varchar(40), c62 varchar(40), c63 varchar(40), c64 varchar(40), c65 varchar(40), c66 varchar(40), c67 varchar(40), c68 varchar(40), c69 varchar(40), c70 varchar(40), c71 varchar(40), c72 varchar(40), c73 varchar(40), c74 varchar(40), c75 varchar(40), c76 varchar(40), c77 varchar(40), c78 varchar(40), c79 varchar(40), c80 varchar(40), c81 varchar(40), c82 varchar(40), c83 varchar(40), c84 varchar(40), c85 varchar(40), c86 varchar(40), c87 varchar(40), c88 varchar(40), c89 varchar(40), c90 varchar(40), c91 varchar(40), c92 varchar(40), c93 varchar(40), c94 varchar(40), c95 varchar(40), c96 varchar(40), c97 varchar(40), c98 varchar(40), c99 varchar(40), c100 varchar(40) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; +DROP TABLE t_varchar40; +DROP DATABASE bug120323; diff --git a/mysql-test/suite/innodb/t/bug120323_row_size_insert_boundary.test b/mysql-test/suite/innodb/t/bug120323_row_size_insert_boundary.test new file mode 100644 index 000000000000..50f0f4db6077 --- /dev/null +++ b/mysql-test/suite/innodb/t/bug120323_row_size_insert_boundary.test @@ -0,0 +1,66 @@ +# Bug#120323: Row size too large regression - INSERT boundary test +# +# This test documents the known trade-off of the fix: CREATE TABLE succeeds +# for schemas with many varchar(41-255) columns, but INSERT will fail with +# DB_TOO_BIG_RECORD if every column is filled to its maximum declared length. +# +# This is the same behavior as all MySQL versions prior to 8.0.46 and is +# expected because the record format cannot flag columns with col->len <= 255 +# for external storage (1-byte length encoding has no external storage bit). + +--disable_warnings +DROP DATABASE IF EXISTS bug120323; +--enable_warnings +CREATE DATABASE bug120323; +USE bug120323; + +--echo # +--echo # Create a table with 100 x varchar(200) latin1 DYNAMIC. +--echo # With the fix, CREATE TABLE succeeds. +--echo # + +--let $create_stmt= CREATE TABLE t1 ( +--let $i= 1 +while ($i <= 100) +{ + if ($i > 1) + { + --let $create_stmt= $create_stmt, + } + --let $create_stmt= $create_stmt c$i varchar(200) + --inc $i +} +--let $create_stmt= $create_stmt ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1 +--eval $create_stmt + +--echo # +--echo # INSERT with short data succeeds - row fits on page. +--echo # + +INSERT INTO t1 (c1, c2, c3) VALUES ('a', 'b', 'c'); + +--echo # +--echo # INSERT with all 100 columns at max length (200 bytes each) fails. +--echo # Total payload = 100 * 200 = 20000 bytes, exceeds page limit. +--echo # InnoDB cannot store these columns externally because col->len <= 255 +--echo # (1-byte length encoding has no external storage flag). +--echo # This is expected and matches pre-8.0.46 behavior. +--echo # + +--let $insert_vals= +--let $i= 1 +while ($i <= 100) +{ + if ($i > 1) + { + --let $insert_vals= $insert_vals, + } + --let $insert_vals= $insert_vals REPEAT('x', 200) + --inc $i +} + +--error ER_TOO_BIG_ROWSIZE +--eval INSERT INTO t1 VALUES ($insert_vals) + +# cleanup +DROP DATABASE bug120323; diff --git a/mysql-test/suite/innodb/t/bug120323_row_size_too_large_regression.test b/mysql-test/suite/innodb/t/bug120323_row_size_too_large_regression.test new file mode 100644 index 000000000000..bd3321d46e0b --- /dev/null +++ b/mysql-test/suite/innodb/t/bug120323_row_size_too_large_regression.test @@ -0,0 +1,136 @@ +# Test for Bug#120323: "Row size too large (> 8126)" regression +# https://bugs.mysql.com/bug.php?id=120323 +# +# MySQL 8.0.46 / 8.4.9 introduced a regression where CREATE TABLE fails +# for tables with many varchar(41-255) columns using ROW_FORMAT=DYNAMIC. +# The root cause is a DATA_BIG_COL(col) guard in get_field_max_size() +# that prevents columns with col->len <= 255 from being counted as +# external-storage eligible, inflating the row-size estimate. + +--disable_warnings +DROP DATABASE IF EXISTS bug120323; +--enable_warnings +CREATE DATABASE bug120323; +USE bug120323; + +--echo # +--echo # Test 1: 100 x varchar(200) latin1 columns with ROW_FORMAT=DYNAMIC +--echo # This is the minimal repro - fails without the fix. +--echo # + +--let $create_stmt= CREATE TABLE t_varchar200 ( +--let $i= 1 +while ($i <= 100) +{ + if ($i > 1) + { + --let $create_stmt= $create_stmt, + } + --let $create_stmt= $create_stmt c$i varchar(200) + --inc $i +} +--let $create_stmt= $create_stmt ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1 +--eval $create_stmt +SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='bug120323' AND table_name='t_varchar200'; +DROP TABLE t_varchar200; + +--echo # +--echo # Test 2: 100 x varchar(255) latin1 - boundary at DATA_BIG_COL threshold +--echo # + +--let $create_stmt= CREATE TABLE t_varchar255 ( +--let $i= 1 +while ($i <= 100) +{ + if ($i > 1) + { + --let $create_stmt= $create_stmt, + } + --let $create_stmt= $create_stmt c$i varchar(255) + --inc $i +} +--let $create_stmt= $create_stmt ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1 +--eval $create_stmt +SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='bug120323' AND table_name='t_varchar255'; +DROP TABLE t_varchar255; + +--echo # +--echo # Test 3: varchar(256) columns - these always worked because +--echo # col->len > 255 satisfies DATA_BIG_COL. Verify no regression. +--echo # + +--let $create_stmt= CREATE TABLE t_varchar256 ( +--let $i= 1 +while ($i <= 100) +{ + if ($i > 1) + { + --let $create_stmt= $create_stmt, + } + --let $create_stmt= $create_stmt c$i varchar(256) + --inc $i +} +--let $create_stmt= $create_stmt ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1 +--eval $create_stmt +SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='bug120323' AND table_name='t_varchar256'; +DROP TABLE t_varchar256; + +--echo # +--echo # Test 4: Mixed varchar lengths (41-255) simulating real-world schema +--echo # + +CREATE TABLE t_mixed ( + c1 varchar(41), c2 varchar(50), c3 varchar(80), c4 varchar(100), + c5 varchar(120), c6 varchar(150), c7 varchar(180), c8 varchar(200), + c9 varchar(220), c10 varchar(255), c11 varchar(41), c12 varchar(50), + c13 varchar(80), c14 varchar(100), c15 varchar(120), c16 varchar(150), + c17 varchar(180), c18 varchar(200), c19 varchar(220), c20 varchar(255), + c21 varchar(41), c22 varchar(50), c23 varchar(80), c24 varchar(100), + c25 varchar(120), c26 varchar(150), c27 varchar(180), c28 varchar(200), + c29 varchar(220), c30 varchar(255), c31 varchar(41), c32 varchar(50), + c33 varchar(80), c34 varchar(100), c35 varchar(120), c36 varchar(150), + c37 varchar(180), c38 varchar(200), c39 varchar(220), c40 varchar(255), + c41 varchar(41), c42 varchar(50), c43 varchar(80), c44 varchar(100), + c45 varchar(120), c46 varchar(150), c47 varchar(180), c48 varchar(200), + c49 varchar(220), c50 varchar(255), c51 varchar(41), c52 varchar(50), + c53 varchar(80), c54 varchar(100), c55 varchar(120), c56 varchar(150), + c57 varchar(180), c58 varchar(200), c59 varchar(220), c60 varchar(255), + c61 varchar(41), c62 varchar(50), c63 varchar(80), c64 varchar(100), + c65 varchar(120), c66 varchar(150), c67 varchar(180), c68 varchar(200), + c69 varchar(220), c70 varchar(255), c71 varchar(41), c72 varchar(50), + c73 varchar(80), c74 varchar(100), c75 varchar(120), c76 varchar(150), + c77 varchar(180), c78 varchar(200), c79 varchar(220), c80 varchar(255) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1; + +--echo # Verify INSERT and SELECT work +INSERT INTO t_mixed (c1, c2, c3) VALUES ('a', 'b', 'c'); +SELECT c1, c2, c3 FROM t_mixed; + +--echo # Verify CREATE TABLE LIKE works (upgrade path scenario) +CREATE TABLE t_mixed_copy LIKE t_mixed; +DROP TABLE t_mixed_copy; +DROP TABLE t_mixed; + +--echo # +--echo # Test 5: 100 x varchar(40) latin1 - at the external storage threshold +--echo # (get_max_size() <= BTR_EXTERN_LOCAL_STORED_MAX_SIZE, never stored externally). +--echo # Should work regardless of fix. Sanity check. +--echo # + +--let $create_stmt= CREATE TABLE t_varchar40 ( +--let $i= 1 +while ($i <= 100) +{ + if ($i > 1) + { + --let $create_stmt= $create_stmt, + } + --let $create_stmt= $create_stmt c$i varchar(40) + --inc $i +} +--let $create_stmt= $create_stmt ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=latin1 +--eval $create_stmt +DROP TABLE t_varchar40; + +# cleanup +DROP DATABASE bug120323; diff --git a/mysql-test/suite/innodb_zip/r/bug52745.result b/mysql-test/suite/innodb_zip/r/bug52745.result index fcab025f9711..5acdee1e53c9 100644 --- a/mysql-test/suite/innodb_zip/r/bug52745.result +++ b/mysql-test/suite/innodb_zip/r/bug52745.result @@ -98,7 +98,6 @@ Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a futur Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release. Note 1291 Column 'col82' has duplicated value '' in ENUM Note 1291 Column 'col82' has duplicated value '' in ENUM -Warning 139 Row size too large (> NNNN). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode; SET sql_mode = (SELECT replace(@@sql_mode,'NO_ZERO_DATE','')); INSERT IGNORE INTO bug52745 SET diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc index 90a962229fe8..8142c8c3f2e3 100644 --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -2167,7 +2167,7 @@ void get_field_max_size(const dict_table_t *table, const dict_index_t *index, } } else if (!always_inlined && field_max_size > BTR_EXTERN_LOCAL_STORED_MAX_SIZE && - DATA_BIG_COL(col) && index->is_clustered()) { + index->is_clustered()) { /* In dtuple_convert_big_rec(), a "big" variable-length column that is longer than BTR_EXTERN_LOCAL_STORED_MAX_SIZE and is not used for navigation may be chosen for external storage. */