Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2
-
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.