MDEV-21429 TRUNCATE and OPTIMIZE are being refused due to "row size too large"

By default (innodb_strict_mode=ON), InnoDB attempts to guarantee
at DDL time that any INSERT to the table can succeed.
MDEV-19292 recently revised the "row size too large" check in InnoDB.
The check still is somewhat inaccurate;
that should be addressed in MDEV-20194.

Note: If a table contains multiple long string columns so that each column
is part of a column prefix index, then an UPDATE that attempts to modify
all those columns at once may fail, because the undo log record might
not fit in a single undo log page (of innodb_page_size). In the worst case,
the undo log record would grow by about 3KiB of for each updated column.

The DDL-time check (since the InnoDB Plugin for MySQL 5.1) is optional
in the sense that when the maximum B-tree record size or undo log
record size would be exceeded, the DML operation will fail and the
transaction will be properly rolled back.

create_table_info_t::row_size_is_acceptable(): Add the parameter
'bool strict' so that innodb_strict_mode=ON can be overridden during
TRUNCATE, OPTIMIZE and ALTER TABLE...FORCE (when the storage format
is not changing).

create_table_info_t::create_table(): Perform a sloppy check for
TRUNCATE TABLE (create_fk=false).

prepare_inplace_alter_table_dict(): Perform a sloppy check for
simple operations.

trx_is_strict(): Remove. The function became unused in
commit 98694ab0cbaf623c6ad67dd45d6f90c5c6214fd1 (MDEV-20949).

6 files changed, 69 insertions, 47 deletions

diff --git a/mysql-test/suite/innodb/r/row_size_error_log_warnings_3.result b/mysql-test/suite/innodb/r/row_size_error_log_warnings_3.result
index c175f8ee915..fba209dd2b5 100644
--- a/mysql-test/suite/innodb/r/row_size_error_log_warnings_3.result
+++ b/mysql-test/suite/innodb/r/row_size_error_log_warnings_3.result
@@ -16,6 +16,30 @@ col_1 TEXT
 ) ENGINE=INNODB ROW_FORMAT=COMPACT;
 Warnings:
 Warning	139	Row size too large (> 8126). 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.
+TRUNCATE TABLE t1;
+Warnings:
+Warning	139	Row size too large (> 8126). 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.
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+Warnings:
+Warning	139	Row size too large (> 8126). 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.
+ALTER TABLE t1 FORCE;
+Warnings:
+Warning	139	Row size too large (> 8126). 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.
+SET innodb_strict_mode = ON;
+TRUNCATE TABLE t1;
+Warnings:
+Warning	139	Row size too large (> 8126). 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.
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.t1	optimize	status	OK
+Warnings:
+Warning	139	Row size too large (> 8126). 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.
+ALTER TABLE t1 FORCE;
+Warnings:
+Warning	139	Row size too large (> 8126). 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.
 DROP TABLE t1;
 SET @@global.log_warnings = 2;
-SET innodb_strict_mode = 1;
diff --git a/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test b/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test
index 35b86cc4c46..af6c15e0e6b 100644
--- a/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test
+++ b/mysql-test/suite/innodb/t/row_size_error_log_warnings_3.test
@@ -18,7 +18,15 @@ CREATE TABLE t1 (
   ,col_10 TEXT
   ,col_11 TEXT
 ) ENGINE=INNODB ROW_FORMAT=COMPACT;
+--enable_warnings
+TRUNCATE TABLE t1;
+OPTIMIZE TABLE t1;
+ALTER TABLE t1 FORCE;
+SET innodb_strict_mode = ON;
+TRUNCATE TABLE t1;
+OPTIMIZE TABLE t1;
+ALTER TABLE t1 FORCE;
 DROP TABLE t1;
+--disable_warnings
 
 SET @@global.log_warnings = 2;
-SET innodb_strict_mode = 1;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 1e1583804eb..0338e2b682a 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -4,7 +4,7 @@ Copyright (c) 2000, 2019, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2008, 2009 Google Inc.
 Copyright (c) 2009, Percona Inc.
 Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2019, MariaDB Corporation.
+Copyright (c) 2013, 2020, MariaDB Corporation.
 
 Portions of this file contain modifications contributed and copyrighted by
 Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -3504,17 +3504,6 @@ trx_is_interrupted(
 	return(trx && trx->mysql_thd && thd_kill_level(trx->mysql_thd));
 }
 
-/**********************************************************************//**
-Determines if the currently running transaction is in strict mode.
-@return TRUE if strict */
-ibool
-trx_is_strict(
-/*==========*/
-	trx_t*	trx)	/*!< in: transaction */
-{
-	return(trx && trx->mysql_thd && THDVAR(trx->mysql_thd, strict_mode));
-}
-
 /**************************************************************//**
 Resets some fields of a m_prebuilt struct. The template is used in fast
 retrieval of just those column values MySQL needs in its processing. */
@@ -12742,7 +12731,10 @@ int create_table_info_t::create_table(bool create_fk)
 						 DICT_ERR_IGNORE_NONE);
 	ut_ad(innobase_table);
 
-	const bool is_acceptable = row_size_is_acceptable(*innobase_table);
+	/* In TRUNCATE TABLE, we will merely warn about the maximum
+	row size being too large. */
+	const bool is_acceptable = row_size_is_acceptable(*innobase_table,
+							  create_fk);
 
 	dict_table_close(innobase_table, true, false);
 
@@ -12755,18 +12747,12 @@ int create_table_info_t::create_table(bool create_fk)
 }
 
 bool create_table_info_t::row_size_is_acceptable(
-    const dict_table_t &table) const
+  const dict_table_t &table, bool strict) const
 {
   for (dict_index_t *index= dict_table_get_first_index(&table); index;
        index= dict_table_get_next_index(index))
-  {
-
-    if (!row_size_is_acceptable(*index))
-    {
+    if (!row_size_is_acceptable(*index, strict))
       return false;
-    }
-  }
-
   return true;
 }
 
@@ -12944,7 +12930,7 @@ static void ib_warn_row_too_big(THD *thd, const dict_table_t *table)
 }
 
 bool create_table_info_t::row_size_is_acceptable(
-    const dict_index_t &index) const
+    const dict_index_t &index, bool strict) const
 {
   if ((index.type & DICT_FTS) || index.table->is_system_db)
   {
@@ -12953,7 +12939,7 @@ bool create_table_info_t::row_size_is_acceptable(
     return true;
   }
 
-  const bool strict= THDVAR(m_thd, strict_mode);
+  const bool innodb_strict_mode= THDVAR(m_thd, strict_mode);
   dict_index_t::record_size_info_t info= index.record_size_info();
 
   if (info.row_is_too_big())
@@ -12964,9 +12950,9 @@ bool create_table_info_t::row_size_is_acceptable(
     const size_t idx= info.get_first_overrun_field_index();
     const dict_field_t *field= dict_index_get_nth_field(&index, idx);
 
-    if (strict || global_system_variables.log_warnings > 2)
+    if (innodb_strict_mode || global_system_variables.log_warnings > 2)
     {
-      ib::error_or_warn(strict)
+      ib::error_or_warn(strict && innodb_strict_mode)
           << "Cannot add field " << field->name << " in table "
           << index.table->name << " because after adding it, the row size is "
           << info.get_overrun_size()
@@ -12974,10 +12960,8 @@ bool create_table_info_t::row_size_is_acceptable(
           << info.max_leaf_size << " bytes) for a record on index leaf page.";
     }
 
-    if (strict)
-    {
+    if (strict && innodb_strict_mode)
       return false;
-    }
 
     ib_warn_row_too_big(m_thd, index.table);
   }
diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h
index 312da6451f0..1de26e03607 100644
--- a/storage/innobase/handler/ha_innodb.h
+++ b/storage/innobase/handler/ha_innodb.h
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2013, 2019, MariaDB Corporation.
+Copyright (c) 2013, 2020, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it under
 the terms of the GNU General Public License as published by the Free Software
@@ -680,9 +680,11 @@ public:
 	void allocate_trx();
 
 	/** Checks that every index have sane size. Depends on strict mode */
-	bool row_size_is_acceptable(const dict_table_t& table) const;
+	bool row_size_is_acceptable(const dict_table_t& table,
+				    bool strict) const;
 	/** Checks that given index have sane size. Depends on strict mode */
-	bool row_size_is_acceptable(const dict_index_t& index) const;
+	bool row_size_is_acceptable(const dict_index_t& index,
+				    bool strict) const;
 
 	/** Determines InnoDB table flags.
 	If strict_mode=OFF, this will adjust the flags to what should be assumed.
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 4cc1250b719..64b1a806e44 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 2005, 2019, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2013, 2019, MariaDB Corporation.
+Copyright (c) 2013, 2020, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it under
 the terms of the GNU General Public License as published by the Free Software
@@ -101,19 +101,23 @@ static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_FOREIGN_OPERATIONS
 	= Alter_inplace_info::DROP_FOREIGN_KEY
 	| Alter_inplace_info::ADD_FOREIGN_KEY;
 
-/** Operations that InnoDB cares about and can perform without rebuild */
-static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_NOREBUILD
+/** Operations that InnoDB cares about and can perform without validation */
+static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_NOVALIDATE
 	= INNOBASE_ONLINE_CREATE
 	| INNOBASE_FOREIGN_OPERATIONS
 	| Alter_inplace_info::DROP_INDEX
 	| Alter_inplace_info::DROP_UNIQUE_INDEX
 	| Alter_inplace_info::ALTER_COLUMN_NAME
-	| Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH
 	//| Alter_inplace_info::ALTER_INDEX_COMMENT
-	| Alter_inplace_info::ADD_VIRTUAL_COLUMN
 	| Alter_inplace_info::DROP_VIRTUAL_COLUMN
 	| Alter_inplace_info::ALTER_VIRTUAL_COLUMN_ORDER;
 
+/** Operations that InnoDB cares about and can perform without rebuild */
+static const Alter_inplace_info::HA_ALTER_FLAGS INNOBASE_ALTER_NOREBUILD
+	= INNOBASE_ALTER_NOVALIDATE
+	| Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH
+	| Alter_inplace_info::ADD_VIRTUAL_COLUMN;
+
 struct ha_innobase_inplace_ctx : public inplace_alter_handler_ctx
 {
 	/** Dummy query graph */
@@ -4844,7 +4848,14 @@ index_created:
 			goto error_handling;
 		}
 
-		if (!info.row_size_is_acceptable(*ctx->add_index[a])) {
+		/* For ALTER TABLE...FORCE or OPTIMIZE TABLE, we may
+		only issue warnings, because there will be no schema change. */
+		if (!info.row_size_is_acceptable(
+			    *ctx->add_index[a],
+			    !!(ha_alter_info->handler_flags
+			       & ~(INNOBASE_INPLACE_IGNORE
+				   | INNOBASE_ALTER_NOVALIDATE
+				   | Alter_inplace_info::RECREATE_TABLE)))) {
 			error = DB_TOO_BIG_RECORD;
 			goto error_handling;
 		}
diff --git a/storage/innobase/include/trx0trx.h b/storage/innobase/include/trx0trx.h
index 1d71920d6c5..7e46304b6ee 100644
--- a/storage/innobase/include/trx0trx.h
+++ b/storage/innobase/include/trx0trx.h
@@ -1,7 +1,7 @@
 /*****************************************************************************
 
 Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2015, 2019, MariaDB Corporation.
+Copyright (c) 2015, 2020, MariaDB Corporation.
 
 This program is free software; you can redistribute it and/or modify it under
 the terms of the GNU General Public License as published by the Free Software
@@ -432,13 +432,6 @@ ibool
 trx_is_interrupted(
 /*===============*/
 	const trx_t*	trx);	/*!< in: transaction */
-/**********************************************************************//**
-Determines if the currently running transaction is in strict mode.
-@return TRUE if strict */
-ibool
-trx_is_strict(
-/*==========*/
-	trx_t*	trx);	/*!< in: transaction */
 
 /*******************************************************************//**
 Calculates the "weight" of a transaction. The weight of one transaction