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

Inconsistent behavior of DEFAULT clause for TEXT column upon upgrade from previous versions

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.2
    • 10.2
    • Server
    • None

    Description

      Before 10.2, TEXT columns can't have a default value, in 10.2 they can.
      When a 10.0 or MySQL 5.6 table with such column is migrated to 10.2, it gets a default value:

      Before upgrade - 5.6

      MySQL [test]> CREATE TABLE t1 ( 
          -> t1 text NOT NULL,
          -> t2 text NOT NULL DEFAULT '',
          -> b1 bool NOT NULL,
          -> b2 bool NOT NULL DEFAULT 0
          -> ) ENGINE=InnoDB;
      Query OK, 0 rows affected, 1 warning (0.29 sec)
       
      MySQL [test]> SHOW WARNINGS;
      +---------+------+--------------------------------------------------+
      | Level   | Code | Message                                          |
      +---------+------+--------------------------------------------------+
      | Warning | 1101 | BLOB/TEXT column 't2' can't have a default value |
      +---------+------+--------------------------------------------------+
      1 row in set (0.00 sec)
       
      MySQL [test]> SHOW CREATE TABLE t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `t1` text NOT NULL,
        `t2` text NOT NULL,
        `b1` tinyint(1) NOT NULL,
        `b2` tinyint(1) NOT NULL DEFAULT '0'
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.01 sec)
       
      MySQL [test]> SELECT @@version;
      +--------------+
      | @@version    |
      +--------------+
      | 5.6.33-debug |
      +--------------+
      1 row in set (0.00 sec)
      

      After upgrade - 10.2

      MariaDB [test]> SHOW CREATE TABLE t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `t1` text NOT NULL,
        `t2` text NOT NULL DEFAULT '',
        `b1` tinyint(1) NOT NULL,
        `b2` tinyint(1) NOT NULL DEFAULT 0
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.01 sec)
       
      MariaDB [test]> SELECT @@version;
      +----------------------+
      | @@version            |
      +----------------------+
      | 10.2.4-MariaDB-debug |
      +----------------------+
      1 row in set (0.00 sec)
      

      But when a similar table is migrated from 10.1 to 10.2, it does not get a default value:

      Before upgrade - 10.1

      MariaDB [test]> CREATE TABLE t1 ( 
          -> t1 text NOT NULL,
          -> t2 text NOT NULL DEFAULT '',
          -> b1 bool NOT NULL,
          -> b2 bool NOT NULL DEFAULT 0
          -> ) ENGINE=InnoDB;
      Query OK, 0 rows affected, 1 warning (0.31 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+--------------------------------------------------+
      | Level   | Code | Message                                          |
      +---------+------+--------------------------------------------------+
      | Warning | 1101 | BLOB/TEXT column 't2' can't have a default value |
      +---------+------+--------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> SHOW CREATE TABLE t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `t1` text NOT NULL,
        `t2` text NOT NULL,
        `b1` tinyint(1) NOT NULL,
        `b2` tinyint(1) NOT NULL DEFAULT '0'
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      MariaDB [test]> SELECT @@version;
      +-----------------------+
      | @@version             |
      +-----------------------+
      | 10.1.22-MariaDB-debug |
      +-----------------------+
      1 row in set (0.00 sec)
      

      After upgrade - 10.2

      MariaDB [test]> SHOW CREATE TABLE t1 \G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `t1` text NOT NULL,
        `t2` text NOT NULL,
        `b1` tinyint(1) NOT NULL,
        `b2` tinyint(1) NOT NULL DEFAULT 0
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      MariaDB [test]> SELECT @@version;
      +----------------------+
      | @@version            |
      +----------------------+
      | 10.2.4-MariaDB-debug |
      +----------------------+
      1 row in set (0.00 sec)
      

      This is suspicious.

      Attachments

        Activity

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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