MySQL Bugs: #18482: mysqldump does not dumps tinytext and mediumtext default

Bug #18482	mysqldump does not dumps tinytext and mediumtext default
Submitted:	24 Mar 2006 13:01	Modified:	27 Mar 2006 20:40
Reporter:	jeka good	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.20-BK, 5.0.18	OS:	Linux (Linux, windows 2003 sp1)
Assigned to:	Chad MILLER	CPU Architecture:	Any

Description:
When doing a dump with mysqldump nothing is printed for
tinytext and mediumtext default if default == ''

How to repeat:
create table 'qqq' (
  `userregexp` tinytext NOT NULL default '',
);

execute:
mysqldump.exe -ubugs -pass bugs > dump.sql

dump.sql - 
create table 'qqq' (
  `userregexp` tinytext NOT NULL,
);

Verified just as described on Linux with 5.0.20-BK (ChangeSet@1.2108, 2006-03-23 22:29:53+01:00):

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database bugs;
Query OK, 1 row affected (0.02 sec)

mysql> use bugs
Database changed
mysql> create table qqq (
    ->   c1 tinytext not null default '',
    ->   c2 varchar(10) not null default '');
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqldump -uroot bugs
-- MySQL dump 10.10
--
-- Host: localhost    Database: bugs
-- ------------------------------------------------------
-- Server version       5.0.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `qqq`
--

DROP TABLE IF EXISTS `qqq`;
CREATE TABLE `qqq` (
  `c1` tinytext NOT NULL,
  `c2` varchar(10) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
...

It is a bug, I think.

According to our manual, "BLOB and TEXT columns cannot have DEFAULT values."

http://dev.mysql.com/doc/refman/5.0/en/blob.html

However, this doesn't explain why the engine allowed you to send (what should be) a bad creation statment and yet not complain about it at table-creation time.  In any case, when the engine tries to apply a default value when one is omitted in an insertion statement, one gets this message:

failed: 1101: BLOB/TEXT column 'c1' can't have a default value

This may be an effort to allow one to ALTER the column to any types without losing DEFAULT information if the column passes through a type that doesn't allow defaults.