Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16365

Setting a column NOT NULL fails to return error for NULL values when there is no DEFAULT

    XMLWordPrintable

    Details

      Description

      MDEV-14168 changed behaviour in an undesired way. If the test of the feature is executed on MariaDB Server 10.2 or earlier, it will fail:

      CURRENT_TEST: innodb.alter_not_null
      mysqltest: At line 8: query 'ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL' failed: 1138: Invalid use of NULL value
      

      If I change the statement to use ALGORITHM=COPY, it will fail with a different message (in both 10.2 and 10.3):

      CURRENT_TEST: innodb.alter_not_null
      mysqltest: At line 8: query 'ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL, ALGORITHM=COPY' failed: 1265: Data truncated for column 'f1' at row 1
      

      The invariant should be that ALGORITHM=INPLACE and ALGORITHM=COPY produce the same result. After MDEV-14168, ALGORITHM=INPLACE would incorrectly convert the NULL value to the implicit default value 0 of the INT data type.

      With SQL_MODE='' (not STRICT_TRANS_TABLES), warnings would be reported for each row in ALGORITHM=COPY:

      +ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL, ALGORITHM=COPY;
      +affected rows: 1
      +info: Records: 1  Duplicates: 0  Warnings: 1
      +Warnings:
      +Warning	1265	Data truncated for column 'f1' at row 1
      

      Similarly, warnings will be reported by ALTER IGNORE TABLE, also for SQL_MODE='STRICT_TRANS_TABLES' values.

      I think that ALGORITHM=INPLACE should be fixed so that it will report errors or warnings for the affected rows.

      As part of the fix, the following code (which I expected MDEV-14168 to remove) should be removed:

      diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
      index 769eced242c..94545321052 100644
      --- a/storage/innobase/handler/handler0alter.cc
      +++ b/storage/innobase/handler/handler0alter.cc
      @@ -913,18 +913,6 @@ ha_innobase::check_if_supported_inplace_alter(
       		DBUG_RETURN(HA_ALTER_INPLACE_INSTANT);
       	}
       
      -	/* Only support NULL -> NOT NULL change if strict table sql_mode
      -	is set. Fall back to COPY for conversion if not strict tables.
      -	In-Place will fail with an error when trying to convert
      -	NULL to a NOT NULL value. */
      -	if ((ha_alter_info->handler_flags
      -	     & ALTER_COLUMN_NOT_NULLABLE)
      -	    && !thd_is_strict_mode(m_user_thd)) {
      -		ha_alter_info->unsupported_reason = my_get_err_msg(
      -			ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL);
      -		DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
      -	}
      -
       	/* DROP PRIMARY KEY is only allowed in combination with ADD
       	PRIMARY KEY. */
       	if ((ha_alter_info->handler_flags
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                thiru Thirunarayanan Balathandayuthapani
                Reporter:
                marko Marko Mäkelä
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: