[MDEV-20194] Warnings inconsistently issued upon CHECK on table from older versions Created: 2019-07-27  Updated: 2023-08-22  Resolved: 2022-04-21

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.3.35, 10.4.25, 10.5.16, 10.6.8

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 2
Labels: None

Issue Links:
Blocks
blocks MDEV-20400 Implement a way to query an InnoDB ta... Confirmed
Duplicate
PartOf
includes MDEV-20949 Stop performing unnecessary maximum r... Closed
Relates
relates to MDEV-19292 "Row size too large" error when creat... Closed
relates to MDEV-20000 Consider adopting innodb.innodb_pages... Open
relates to MDEV-20400 Implement a way to query an InnoDB ta... Confirmed
relates to MDEV-20832 Don't print "row size too large" warn... Closed
relates to MDEV-21261 When performing instant add column, I... Confirmed
relates to MDEV-21624 Unique index length is able to exceed... Closed
relates to MDEV-21748 ASAN use-after-poison in PageBulk::in... Closed
relates to MDEV-23521 Implement an InnoDB row format that c... Open
relates to MDEV-16969 Fix error message as promised in upst... Open
relates to MDEV-20400 Implement a way to query an InnoDB ta... Confirmed
relates to MDEV-20786 MariaDB server writes warnings into e... Closed
relates to MDEV-21429 TRUNCATE and OPTIMIZE are being refus... Closed
relates to MDEV-22585 Incorrect note being written to the e... Open

 Description   

Note: I'm fine with closing it as not a bug as long as all aspects of the new behavior are considered and confirmed to be expected. It was discussed a little on slack as a probably innocent consequence of MDEV-19292 fix, but I am not sure there was a final conclusion.

Here is an example of the table:

CREATE TABLE t1 (
 
  `c01` int(10) unsigned zerofill NOT NULL DEFAULT 0000000000,
 
  `c03` varchar(1024) NOT NULL DEFAULT '',
 
  `c04` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
 
  `c06` double(16,8) DEFAULT NULL COMMENT 'Percentage',
 
  `c07` date DEFAULT NULL,
 
  `c12` varchar(64) GENERATED ALWAYS AS (concat(`c09`,' ',`c10`)) VIRTUAL,
 
  `c05` longblob NOT NULL DEFAULT '',
 
  `c11` varchar(64) GENERATED ALWAYS AS (concat(`c09`,' ',`c10`)) PERSISTENT,
 
  `c08` text DEFAULT '',
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `c02` char(3) NOT NULL DEFAULT '',
 
  `c10` bigint(20) DEFAULT NULL COMMENT 'Y',
 
  `c09` bigint(20) DEFAULT NULL COMMENT 'X',
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `idx1` (`c01`,`c02`),
 
  KEY `idx2` (`c05`(128))
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

Server options: --innodb-compression-algorithm=none --innodb-page-size=4K, otherwise defaults.

The table gets created on a pre-MDEV-19292 server with without any warnings (on a post-MDEV-19292 server it gets rejected).
When a post-MDEV-19292 server starts on the old datadir, CHECK TABLE t1 produces the warning:

10.2 fc77a66c

 
+---------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table   | Op    | Msg_type | Msg_text                                                                                                                                                                                            |
 
+---------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| test.t1 | check | Warning  | Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. |
 
| test.t1 | check | status   | OK                                                                                                                                                                                                  |
 
+---------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

First, it's unclear whether MDEV-19292 patch was supposed to affect REDUNDANT / COMPACT tables at all.

Secondly, the warning, if it's true, could have been helpful if it was reliable; but it is only issued on the first opening of the table:

MariaDB [test]> check table t1;
 
+---------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table   | Op    | Msg_type | Msg_text                                                                                                                                                                                            |
 
+---------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| test.t1 | check | Warning  | Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. |
 
| test.t1 | check | status   | OK                                                                                                                                                                                                  |
 
+---------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
2 rows in set (0.005 sec)
 
 
 
MariaDB [test]> check table t1;
 
+---------+-------+----------+----------+
 
| Table   | Op    | Msg_type | Msg_text |
 
+---------+-------+----------+----------+
 
| test.t1 | check | status   | OK       |
 
+---------+-------+----------+----------+
 
1 row in set (0.005 sec)

Which makes it fairly useless.

And finally, the error log gets a different strange-looking warning instead:

2019-07-28  0:57:51 140189814937344 [Warning] InnoDB: Cannot add field `c08` in table `test`.`t1` because after adding it, the row size is 2484 which is greater than maximum allowed size (1982) for a record on index leaf page.



 Comments   
Comment by Elena Stepanova [ 2019-07-27 ]

Also, innodb.innodb-32k-crash test non-deterministically produces the same warning:
http://buildbot.askmonty.org/buildbot/builders/kvm-rpm-centos73-amd64/builds/11353/steps/mtr/logs/stdio

10.3 5e112a26201d4b343df3e401f8695974a8fa852c

 
innodb.innodb-32k-crash 'innodb'         w1 [ fail ]
 
        Test ended at 2019-07-27 13:16:40
 
 
 
CURRENT_TEST: innodb.innodb-32k-crash
 
--- /usr/share/mysql-test/suite/innodb/r/innodb-32k-crash.result	2019-07-27 11:56:37.000000000 +0000
 
+++ /dev/shm/var/1/log/innodb-32k-crash.reject	2019-07-27 13:16:40.754215752 +0000
 
@@ -136,6 +136,8 @@
 
 aa=@h,ba=@h,ca=@h,da=@h,ea=@h,fa=@h,ga=@h,ha=@h,ia=@h,ja=@h,
 
 ka=@h,la=@h,ma=@h,na=@h,oa=@h,pa=@h,qa=@h,ra=@h,sa=@h,ta=@b,ua=@h,
 
 va=@h,wa=@h,xa=@h,ya=@h,za=@h;
 
+Warnings:
 
+Warning	139	Row size too large (> 16318). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
 
 BEGIN;
 
 UPDATE t1 SET a=@g,b=@g,c=@g,d=@g,e=@g;
 
 UPDATE t1 SET f=@g,g=@g,h=@g,i=@g,j=@g;
 
 
 
mysqltest: Result length mismatch

Comment by Geoff Montee (Inactive) [ 2019-08-20 ] 

2019-07-28  0:57:51 140189814937344 [Warning] InnoDB: Cannot add field `c08` in table `test`.`t1` because after adding it, the row size is 2484 which is greater than maximum allowed size (1982) for a record on index leaf page.

This warning would seem to imply that the c08 column is being added by some DDL statement, so it doesn't really make sense in the context of CHECK TABLE. I think this warning should be improved. See also MDEV-16969.

Comment by Geoff Montee (Inactive) [ 2019-08-20 ]

This bug also affects mysql_upgrade, since that tool executes "CHECK TABLE ... FOR UPGRADE".

To reproduce with mysql_upgrade, do the following:

Create a table with a row size that is too large:

SET SESSION innodb_strict_mode=OFF;
 
CREATE TABLE test.tab (
 
   col1 varchar(40) NOT NULL,
 
   col2 varchar(40) NOT NULL,
 
   col3 varchar(40) NOT NULL,
 
   col4 varchar(40) NOT NULL,
 
   col5 varchar(40) NOT NULL,
 
   col6 varchar(40) NOT NULL,
 
   col7 varchar(40) NOT NULL,
 
   col8 varchar(40) NOT NULL,
 
   col9 varchar(40) NOT NULL,
 
   col10 varchar(40) NOT NULL,
 
   col11 varchar(40) NOT NULL,
 
   col12 varchar(40) NOT NULL,
 
   col13 varchar(40) NOT NULL,
 
   col14 varchar(40) NOT NULL,
 
   col15 varchar(40) NOT NULL,
 
   col16 varchar(40) NOT NULL,
 
   col17 varchar(40) NOT NULL,
 
   col18 varchar(40) NOT NULL,
 
   col19 varchar(40) NOT NULL,
 
   col20 varchar(40) NOT NULL,
 
   col21 varchar(40) NOT NULL,
 
   col22 varchar(40) NOT NULL,
 
   col23 varchar(40) NOT NULL,
 
   col24 varchar(40) NOT NULL,
 
   col25 varchar(40) NOT NULL,
 
   col26 varchar(40) NOT NULL,
 
   col27 varchar(40) NOT NULL,
 
   col28 varchar(40) NOT NULL,
 
   col29 varchar(40) NOT NULL,
 
   col30 varchar(40) NOT NULL,
 
   col31 varchar(40) NOT NULL,
 
   col32 varchar(40) NOT NULL,
 
   col33 varchar(40) NOT NULL,
 
   col34 varchar(40) NOT NULL,
 
   col35 varchar(40) NOT NULL,
 
   col36 varchar(40) NOT NULL,
 
   col37 varchar(40) NOT NULL,
 
   col38 varchar(40) NOT NULL,
 
   col39 varchar(40) NOT NULL,
 
   col40 varchar(40) NOT NULL,
 
   col41 varchar(40) NOT NULL,
 
   col42 varchar(40) NOT NULL,
 
   col43 varchar(40) NOT NULL,
 
   col44 varchar(40) NOT NULL,
 
   col45 varchar(40) NOT NULL,
 
   col46 varchar(40) NOT NULL,
 
   col47 varchar(40) NOT NULL,
 
   col48 varchar(40) NOT NULL,
 
   col49 varchar(40) NOT NULL,
 
   col50 varchar(40) NOT NULL,
 
   col51 varchar(40) NOT NULL,
 
   col52 varchar(40) NOT NULL,
 
   col53 varchar(40) NOT NULL,
 
   col54 varchar(40) NOT NULL,
 
   col55 varchar(40) NOT NULL,
 
   col56 varchar(40) NOT NULL,
 
   col57 varchar(40) NOT NULL,
 
   col58 varchar(40) NOT NULL,
 
   col59 varchar(40) NOT NULL,
 
   col60 varchar(40) NOT NULL,
 
   col61 varchar(40) NOT NULL,
 
   col62 varchar(40) NOT NULL,
 
   col63 varchar(40) NOT NULL,
 
   col64 varchar(40) NOT NULL,
 
   col65 varchar(40) NOT NULL,
 
   col66 varchar(40) NOT NULL,
 
   col67 varchar(40) NOT NULL,
 
   col68 varchar(40) NOT NULL,
 
   col69 varchar(40) NOT NULL,
 
   col70 varchar(40) NOT NULL,
 
   col71 varchar(40) NOT NULL,
 
   col72 varchar(40) NOT NULL,
 
   col73 varchar(40) NOT NULL,
 
   col74 varchar(40) NOT NULL,
 
   col75 varchar(40) NOT NULL,
 
   col76 varchar(40) NOT NULL,
 
   col77 varchar(40) NOT NULL,
 
   col78 varchar(40) NOT NULL,
 
   col79 varchar(40) NOT NULL,
 
   col80 varchar(40) NOT NULL,
 
   col81 varchar(40) NOT NULL,
 
   col82 varchar(40) NOT NULL,
 
   col83 varchar(40) NOT NULL,
 
   col84 varchar(40) NOT NULL,
 
   col85 varchar(40) NOT NULL,
 
   col86 varchar(40) NOT NULL,
 
   col87 varchar(40) NOT NULL,
 
   col88 varchar(40) NOT NULL,
 
   col89 varchar(40) NOT NULL,
 
   col90 varchar(40) NOT NULL,
 
   col91 varchar(40) NOT NULL,
 
   col92 varchar(40) NOT NULL,
 
   col93 varchar(40) NOT NULL,
 
   col94 varchar(40) NOT NULL,
 
   col95 varchar(40) NOT NULL,
 
   col96 varchar(40) NOT NULL,
 
   col97 varchar(40) NOT NULL,
 
   col98 varchar(40) NOT NULL,
 
   col99 varchar(40) NOT NULL,
 
   col100 varchar(40) NOT NULL,
 
   col101 varchar(40) NOT NULL,
 
   col102 varchar(40) NOT NULL,
 
   col103 varchar(40) NOT NULL,
 
   col104 varchar(40) NOT NULL,
 
   col105 varchar(40) NOT NULL,
 
   col106 varchar(40) NOT NULL,
 
   col107 varchar(40) NOT NULL,
 
   col108 varchar(40) NOT NULL,
 
   col109 varchar(40) NOT NULL,
 
   col110 varchar(40) NOT NULL,
 
   col111 varchar(40) NOT NULL,
 
   col112 varchar(40) NOT NULL,
 
   col113 varchar(40) NOT NULL,
 
   col114 varchar(40) NOT NULL,
 
   col115 varchar(40) NOT NULL,
 
   col116 varchar(40) NOT NULL,
 
   col117 varchar(40) NOT NULL,
 
   col118 varchar(40) NOT NULL,
 
   col119 varchar(40) NOT NULL,
 
   col120 varchar(40) NOT NULL,
 
   col121 varchar(40) NOT NULL,
 
   col122 varchar(40) NOT NULL,
 
   col123 varchar(40) NOT NULL,
 
   col124 varchar(40) NOT NULL,
 
   col125 varchar(40) NOT NULL,
 
   col126 varchar(40) NOT NULL,
 
   col127 varchar(40) NOT NULL,
 
   col128 varchar(40) NOT NULL,
 
   col129 varchar(40) NOT NULL,
 
   col130 varchar(40) NOT NULL,
 
   col131 varchar(40) NOT NULL,
 
   col132 varchar(40) NOT NULL,
 
   col133 varchar(40) NOT NULL,
 
   col134 varchar(40) NOT NULL,
 
   col135 varchar(40) NOT NULL,
 
   col136 varchar(40) NOT NULL,
 
   col137 varchar(40) NOT NULL,
 
   col138 varchar(40) NOT NULL,
 
   col139 varchar(40) NOT NULL,
 
   col140 varchar(40) NOT NULL,
 
   col141 varchar(40) NOT NULL,
 
   col142 varchar(40) NOT NULL,
 
   col143 varchar(40) NOT NULL,
 
   col144 varchar(40) NOT NULL,
 
   col145 varchar(40) NOT NULL,
 
   col146 varchar(40) NOT NULL,
 
   col147 varchar(40) NOT NULL,
 
   col148 varchar(40) NOT NULL,
 
   col149 varchar(40) NOT NULL,
 
   col150 varchar(40) NOT NULL,
 
   col151 varchar(40) NOT NULL,
 
   col152 varchar(40) NOT NULL,
 
   col153 varchar(40) NOT NULL,
 
   col154 varchar(40) NOT NULL,
 
   col155 varchar(40) NOT NULL,
 
   col156 varchar(40) NOT NULL,
 
   col157 varchar(40) NOT NULL,
 
   col158 varchar(40) NOT NULL,
 
   col159 varchar(40) NOT NULL,
 
   col160 varchar(40) NOT NULL,
 
   col161 varchar(40) NOT NULL,
 
   col162 varchar(40) NOT NULL,
 
   col163 varchar(40) NOT NULL,
 
   col164 varchar(40) NOT NULL,
 
   col165 varchar(40) NOT NULL,
 
   col166 varchar(40) NOT NULL,
 
   col167 varchar(40) NOT NULL,
 
   col168 varchar(40) NOT NULL,
 
   col169 varchar(40) NOT NULL,
 
   col170 varchar(40) NOT NULL,
 
   col171 varchar(40) NOT NULL,
 
   col172 varchar(40) NOT NULL,
 
   col173 varchar(40) NOT NULL,
 
   col174 varchar(40) NOT NULL,
 
   col175 varchar(40) NOT NULL,
 
   col176 varchar(40) NOT NULL,
 
   col177 varchar(40) NOT NULL,
 
   col178 varchar(40) NOT NULL,
 
   col179 varchar(40) NOT NULL,
 
   col180 varchar(40) NOT NULL,
 
   col181 varchar(40) NOT NULL,
 
   col182 varchar(40) NOT NULL,
 
   col183 varchar(40) NOT NULL,
 
   col184 varchar(40) NOT NULL,
 
   col185 varchar(40) NOT NULL,
 
   col186 varchar(40) NOT NULL,
 
   col187 varchar(40) NOT NULL,
 
   col188 varchar(40) NOT NULL,
 
   col189 varchar(40) NOT NULL,
 
   col190 varchar(40) NOT NULL,
 
   col191 varchar(40) NOT NULL,
 
   col192 varchar(40) NOT NULL,
 
   col193 varchar(40) NOT NULL,
 
   col194 varchar(40) NOT NULL,
 
   col195 varchar(40) NOT NULL,
 
   col196 varchar(40) NOT NULL,
 
   col197 varchar(40) NOT NULL,
 
   col198 varchar(40) NOT NULL,
 
   PRIMARY KEY (col1)
 
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8;

Then, restart the server:

sudo systemctl restart mariadb

Then, before you do anything else with the database, run mysql_upgrade:

sudo mysql_upgrade

mysql_upgrade will finish successfully, and it won't report any errors. However, it will cause the misleading warning to be written to the MariaDB error log.

2019-08-20 19:11:45 22 [Warning] InnoDB: Cannot add field `col68` in table `test`.`tab` because after adding it, the row size is 8246 which is greater than maximum allowed size (8126) for a record on index leaf page.

Comment by Marko Mäkelä [ 2019-08-21 ]

kevg, I think that such messages should only be issued to the client connection when executing DDL statements. If we are loading an ‘unsafe’ table definition to the data dictionary cache, we should be silent. If the row size is too large, that should then result in an error on an INSERT or UPDATE operation.

Side note: The FOR UPGRADE clause of CHECK TABLE is being ignored by InnoDB.

Comment by Marko Mäkelä [ 2019-09-20 ]

The big picture looks good.

I think that I found some inaccuracy in the calculation of the maximum node pointer record size. You do not seem to be accounting for the 4-byte child page number field.

For the clustered index node pointer records, the node pointer size should never cause problems. This is because the node pointer records will be (primary_key_columns,child_page_number), while the leaf page records are (primary_key_columns,DB_TRX_ID,DB_ROLL_PTR,any_non_pk_columns). The system columns DB_TRX_ID,DB_ROLL_PTR are 13 bytes, while the child_page_number is only 4 bytes.

So, we should enforce the maximum node pointer record size only for secondary indexes. Note that for SPATIAL INDEX, the maximum node pointer record size can never become a problem. I think that the node pointer is DATA_MBR_LEN (4*sizeof(double)) followed by the child page number (no PRIMARY KEY columns, but you’d better check that). The leaf page records are DATA_MBR_LEN followed by the PRIMARY KEY columns. That might theoretically become a bottleneck with a 16-column PRIMARY KEY consisting of very long maximum values, if all columns of the table belong to the PRIMARY KEY.

Comment by Hartmut Holzgraefe [ 2019-10-14 ]

"If we are loading an ‘unsafe’ table definition to the data dictionary cache, we should be silent."

This is also an issue as the error log can be flooded with such messages, especially after startup when the table_definition_cache is still "cold". This can become especially "interesting" when having heavily partition tables, where the warning is logged for each individual partition.

Should we keep this aspect as part of this ticked, or should we actually split this aspect out into a new MDEV of its own?

Comment by Hartmut Holzgraefe [ 2019-10-22 ]

Also if we decide to continue having these warnings in the error log, could we at least only have them logged if innodb_strict_mode=ON?

Comment by Geoff Montee (Inactive) [ 2019-10-22 ]

Also if we decide to continue having these warnings in the error log, could we at least only have them logged if innodb_strict_mode=ON?

Another option is that if innodb_strict_mode=OFF is set, then the logging behavior can be based on log_warnings, as requested in MDEV-20832.

Comment by Marko Mäkelä [ 2019-11-14 ]

Some of my comments related to the MDEV-20949 fix should be helpful for fixing this.

Comment by Marko Mäkelä [ 2020-03-10 ]

As demonstrated in MDEV-21748, in MariaDB 10.3 the function instant_alter_column_possible() is computing max_size in the wrong way, not taking blob_prefix correctly into account. This will unnecessarily prevent instant ADD COLUMN operations.

Comment by Thirunarayanan Balathandayuthapani [ 2022-04-13 ]

Patch is in bb-10.3-MDEV-20194

Comment by Marko Mäkelä [ 2022-04-20 ]

Thank you. I spotted a possible off-by-one error for ROW_FORMAT=REDUNDANT node pointer records. Otherwise, it looks OK to me.

Generated at Thu Feb 08 08:57:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.