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

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

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15872 Crash in online ALTER TABLE…ADD PRIMARY KEY after instant ADD COLUMN…NULL

          • Major - Major loss of function.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14168 Unconditionally allow ALGORITHM=INPLACE for setting a column NOT NULL

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18732 InnoDB: Wrong results in ALTER IGNORE TABLE with ALGORITHM=INPLACE

          • Major - Major loss of function.
          • Closed

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.