Bug #16876 | SHOW CREATE TABLE does not show default value for NOT NULL text fields | ||
---|---|---|---|
Submitted: | 28 Jan 2006 21:53 | Modified: | 31 May 2006 22:24 |
Reporter: | Anthony Borrow | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | MySQL 5.0.16 | OS: | Windows (WinXP) |
Assigned to: | CPU Architecture: | Any |
[28 Jan 2006 21:53]
Anthony Borrow
[15 Mar 2006 14:41]
Stephen Martindale
This behavior of SHOW CREATE TABLE affects the functionality of the 'mysqldump' command too. If you use mysqldump to backup your databases, default values for TEXT fields are not recorded in the SQL Backup file and will not be applied to the fields when the backup is restored - possibly breaking applications. Suggestion: Fix SHOW CREATE TABLE and test that the fix solves the mysqldump problem. Version 5.0.18
[31 May 2006 22:24]
Jorge del Conde
I was unable to reproduce this bug under 5.0.23bk: mysql> set session sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'sql_mode'; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | sql_mode | STRICT_ALL_TABLES | +---------------+-------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `mdl_course_categories2` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `name` varchar(255) NOT NULL DEFAULT '', -> `description` text NOT NULL, -> `parent` int(10) unsigned NOT NULL DEFAULT '0', -> `sortorder` int(10) unsigned NOT NULL DEFAULT '0', -> `coursecount` int(10) unsigned NOT NULL DEFAULT '0', -> `visible` tinyint(1) NOT NULL DEFAULT '1', -> `timemodified` int(10) unsigned NOT NULL DEFAULT '0', -> PRIMARY KEY (`id`), -> UNIQUE KEY `id` (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Course categories'; Query OK, 0 rows affected (0.01 sec) where the output of 'mdl_course_categories2' came from a SHOW CREATE TABLE statement for table 'mdl_course_categories'