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:
None 
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
Description:
For the Moodle project (moodle.org), SHOW CREATE TABLE is used to produce the sql files for the table structures. Many of the tables have text or longtext fields and even though the tables are defined with default '' the default value is not included with SHOW CREATE TABLE. As a result, when the sql file is used and STRICT mode is enabled on a server we experience table creation errors. It seems that this is a bug since it currently generates:

CREATE TABLE `mdl_course_categories` (
  `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' AUTO_INCREMENT=274 ;

How to repeat:
1) Create a table with a non-null text field. 
2) Perform a SHOW CREATE TABLE
3) Use the output to create a table with MySQL in strict mode

Suggested fix:
If a text or longtext field is NOT NULL then by default a default value of '' should be supplied in order to allow for STRICT mode consistency. I think it would be consistent if it output:

CREATE TABLE `mdl_course_categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` text NOT NULL default '',
  `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' AUTO_INCREMENT=274 ;

Hopefully to clarify my logic - 
if (fieldtype=(text or longtext) and NOT NULL and no default defined) then default=''
[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'