Bug #82830 Load data error 1262 behaviour different between 5.6 and 5.7 under strict sqlmod

• Submitted: 1 Sep 2016 2:41
• Modified: 25 Oct 2016 15:20
• Reporter: lou shuai (OCA)
• Status: Closed
• Category: MySQL Server: Errors
• Severity: S2 (Serious)
• Version: 5.7, 5.7.14
• OS: Any
• CPU Architecture: Any
• Tags: 1262, 5.7, error, LOAD DATA, sql mode, sqlmode, strict

[1 Sep 2016 2:41] lou shuai

Description:
Under sql_mode='strict_all_tables', LOAD DATA with more columns in data file will lead to error under 5.6.26, but just warning under 5.7.10.

How to repeat:
use test
drop table if exists t1;
create table t1(c1 varchar(10), c2 varchar(10), c3 varchar(10));
insert into t1 values('aaaaa', 'bbbbb', 'cccccc');
select * from t1 into outfile '/tmp/datafile' FIELDS TERMINATED BY ',';

drop table if exists t2;
create table t2(c1 varchar(10));
set sql_mode='strict_all_tables';
load data infile '/tmp/datafile' into table t2 fields terminated by ',';

Suggested fix:
I see the Strict_error_handler::handle_condition in 5.7:

switch (sql_errno)
  {
  case ER_TRUNCATED_WRONG_VALUE:
  case ER_WRONG_VALUE_FOR_TYPE:
  case ER_WARN_DATA_OUT_OF_RANGE:
  case ER_DIVISION_BY_ZERO:
  case ER_TRUNCATED_WRONG_VALUE_FOR_FIELD:
  case WARN_DATA_TRUNCATED:
  case ER_DATA_TOO_LONG:
  case ER_BAD_NULL_ERROR:
  case ER_NO_DEFAULT_FOR_FIELD:
  case ER_TOO_LONG_KEY:
  case ER_NO_DEFAULT_FOR_VIEW_FIELD:
  case ER_WARN_NULL_TO_NOTNULL:
  case ER_CUT_VALUE_GROUP_CONCAT:
  case ER_DATETIME_FUNCTION_OVERFLOW:
  case ER_WARN_TOO_FEW_RECORDS:
  case ER_INVALID_ARGUMENT_FOR_LOGARITHM:
  case ER_NUMERIC_JSON_VALUE_OUT_OF_RANGE:
  case ER_INVALID_JSON_VALUE_FOR_CAST:
  case ER_WARN_ALLOWED_PACKET_OVERFLOWED:
    if ((*level == Sql_condition::SL_WARNING) &&
        (!thd->get_transaction()->cannot_safely_rollback(Transaction_ctx::STMT)
         || (thd->variables.sql_mode & MODE_STRICT_ALL_TABLES)))
    {
      (*level)= Sql_condition::SL_ERROR;
      thd->killed= THD::KILL_BAD_DATA;
    }
    break;

The code above just can handle ER_WARN_TOO_FEW_RECORDS, which means too few columns in the data file, but not handle ER_WARN_TOO_MANY_RECORDS.

[1 Sep 2016 6:04] MySQL Verification Team

Hello!

Thank you for the report and test case.

Thanks,
Umesh

[25 Oct 2016 15:20] Paul DuBois

Posted by developer:
 
Noted in 5.7.17, 8.0.1 changelogs.

For LOAD DATA statements, input data with too many column values
produced only a warning, rather than an error as in MySQL 5.6. An
error now occurs.