Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
10.5, 10.3(EOL), 10.4(EOL)
-
None
Description
INFORMATION_SCHEMA tables add DEFAULT clause for all columns.
SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; |
SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES; |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| TABLES | CREATE TEMPORARY TABLE `TABLES` (
|
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
|
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
|
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
|
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
|
`ENGINE` varchar(64) DEFAULT NULL,
|
`VERSION` bigint(21) unsigned DEFAULT NULL,
|
`ROW_FORMAT` varchar(10) DEFAULT NULL,
|
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
|
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
|
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
|
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
|
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
|
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
|
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
|
`CREATE_TIME` datetime DEFAULT NULL,
|
`UPDATE_TIME` datetime DEFAULT NULL,
|
`CHECK_TIME` datetime DEFAULT NULL,
|
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
|
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
|
`CREATE_OPTIONS` varchar(2048) DEFAULT NULL,
|
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT '',
|
`MAX_INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
|
`TEMPORARY` varchar(1) DEFAULT NULL
|
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
|
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
This does not work well in combination with EMPTY_STRING_IS_NULL.
If I copy a table definition and try to create it in another database, I get an error:
SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; |
CREATE OR REPLACE TABLE t1 (TABLE_CATALOG varchar(512) NOT NULL DEFAULT ''); |
ERROR 1067 (42000): Invalid default value for 'TABLE_CATALOG'
|
I_S tables should probably not add DEFAULT clauses into columns.
Attachments
Issue Links
- duplicates
-
MDEV-18918 SQL mode EMPTY_STRING_IS_NULL breaks RBR upon CREATE TABLE .. SELECT
- Closed
- relates to
-
MDEV-21537 InnoDB INFORMATION_SCHEMA tables fail to define DEFAULT for ENUM NOT NULL
- Closed
-
MDEV-18918 SQL mode EMPTY_STRING_IS_NULL breaks RBR upon CREATE TABLE .. SELECT
- Closed
-
MDEV-23285 mysql.transaction_registry contains default values for the timestamp columns that conflict with NO_ZERO_DATE sql mode
- Confirmed