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

Redundant truncation errors/warnings with optimizer_trace enabled

Details

    Description

      The problem is repeatable with at least MyISAM and InnoDB.

      SET @@optimizer_trace='enabled=on';
      		 
       
      		 
      CREATE OR REPLACE TABLE t1 (
      		 
        a CHAR(2) NOT NULL PRIMARY KEY,
      		 
        b VARCHAR(20) NOT NULL,
      		 
        KEY (b)
      		 
      ) ENGINE=InnoDB CHARSET=utf8mb4;
      		 
       
      		 
      CREATE  OR REPLACE TABLE t2 (
      		 
        a CHAR(2) NOT NULL PRIMARY KEY,
      		 
        b VARCHAR(20) NOT NULL,
      		 
        KEY (b)
      		 
      ) ENGINE=InnoDB CHARSET=utf8mb4;
      		 
       
      		 
      INSERT INTO t1 VALUES
      		 
      ('AB','MySQLAB'),
      		 
      ('JA','Sun Microsystems'),
      		 
      ('MS','Microsoft'),
      		 
      ('IB','IBM- Inc.'),
      		 
      ('GO','Google Inc.');
      		 
       
      		 
      INSERT IGNORE INTO t2 VALUES
      		 
      ('AB','Sweden'),
      		 
      ('JA','USA'),
      		 
      ('MS','United States of America'),
      		 
      ('IB','North America'),
      		 
      ('GO','South America');
      		 
       
      		 
      UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';


      ERROR 1406 (22001): Data too long for column 'b' at row 1

      The above error is not expected.

      Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script:

      SET @@optimizer_trace='enabled=on';
      		 
       
      		 
      CREATE OR REPLACE TABLE t1 (
      		 
        a CHAR(2) NOT NULL PRIMARY KEY,
      		 
        b VARCHAR(20) NOT NULL,
      		 
        KEY (b)
      		 
      ) ENGINE=InnoDB CHARSET=utf8mb4;
      		 
       
      		 
      INSERT INTO t1 VALUES
      		 
      ('AB','MySQLAB'),
      		 
      ('JA','Sun Microsystems'),
      		 
      ('MS','Microsoft'),
      		 
      ('IB','IBM- Inc.'),
      		 
      ('GO','Google Inc.');
      		 
       
      		 
      UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';


      +---------+------+----------------------------------------+
      		 
      | Level   | Code | Message                                |
      		 
      +---------+------+----------------------------------------+
      		 
      | Warning | 1265 | Data truncated for column 'b' at row 1 |
      		 
      | Warning | 1265 | Data truncated for column 'b' at row 1 |
      		 
      | Warning | 1265 | Data truncated for column 'b' at row 1 |
      		 
      | Warning | 1265 | Data truncated for column 'b' at row 1 |
      		 
      +---------+------+----------------------------------------+

      These warnings are not expected.

      The problem seems to be in the fact that the call for like_range() in Item_func_like::get_mm_leaf() produces more characters than the size of the index.

      The patch below fixes the problem, but the real patch should probably fix like_range() not to return more characters than the size of the index.

      index 5793b19f695..108b4ac78ae 100644
      		 
      --- a/sql/opt_range.cc
      		 
      +++ b/sql/opt_range.cc
      		 
      @@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
      		 
                                          &min_length, &max_length))
      		 
           DBUG_RETURN(0);              // Can't optimize with LIKE
      		 
       
      +  min_length= Well_formed_prefix(field->charset(),
      		 
      +                                 (const char *) min_str + offset, min_length,
      		 
      +                                 field->char_length()).length();
      		 
      +  max_length= Well_formed_prefix(field->charset(),
      		 
      +                                 (const char *) max_str + offset, max_length,
      		 
      +                                 field->char_length()).length();
      		 
         if (offset != maybe_null)                    // BLOB or VARCHAR
      		 
         {
      		 
           int2store(min_str + maybe_null, min_length);

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19123 Change default charset from latin1 to utf8mb4

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            bar Alexander Barkov added a comment - - edited

            Also repeatable with the TINYTEXT data type:

            SET @@optimizer_trace='enabled=on';
            		 
             
            		 
            CREATE OR REPLACE TABLE t1 (
            		 
              a CHAR(2) NOT NULL PRIMARY KEY,
            		 
              b TINYTEXT NOT NULL,
            		 
              KEY (b(255))
            		 
            ) ENGINE=InnoDB CHARSET=utf8mb4;
            		 
             
            		 
            CREATE  OR REPLACE TABLE t2 (
            		 
              a CHAR(2) NOT NULL PRIMARY KEY,
            		 
              b TINYTEXT NOT NULL,
            		 
              KEY (b(255))
            		 
            ) ENGINE=InnoDB CHARSET=utf8mb4;
            		 
             
            		 
            INSERT INTO t1 VALUES
            		 
            ('AB','MySQLAB'),
            		 
            ('JA','Sun Microsystems'),
            		 
            ('MS','Microsoft'),
            		 
            ('IB','IBM- Inc.'),
            		 
            ('GO','Google Inc.');
            		 
             
            		 
            INSERT IGNORE INTO t2 VALUES
            		 
            ('AB','Sweden'),
            		 
            ('JA','USA'),
            		 
            ('MS','United States of America'),
            		 
            ('IB','North America'),
            		 
            ('GO','South America');
            		 
             
            		 
            UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';


            ERROR 1406 (22001): Data too long for column 'b' at row 1

            bar Alexander Barkov added a comment - - edited Also repeatable with the TINYTEXT data type: SET @@optimizer_trace='enabled=on';   CREATE OR REPLACE TABLE t1 ( a CHAR(2) NOT NULL PRIMARY KEY, b TINYTEXT NOT NULL, KEY (b(255)) ) ENGINE=InnoDB CHARSET=utf8mb4;   CREATE OR REPLACE TABLE t2 ( a CHAR(2) NOT NULL PRIMARY KEY, b TINYTEXT NOT NULL, KEY (b(255)) ) ENGINE=InnoDB CHARSET=utf8mb4;   INSERT INTO t1 VALUES ('AB','MySQLAB'), ('JA','Sun Microsystems'), ('MS','Microsoft'), ('IB','IBM- Inc.'), ('GO','Google Inc.');   INSERT IGNORE INTO t2 VALUES ('AB','Sweden'), ('JA','USA'), ('MS','United States of America'), ('IB','North America'), ('GO','South America');   UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; ERROR 1406 (22001): Data too long for column 'b' at row 1

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.