ALTER TABLE with not null ENUM causes invalid table data - Kevin Miller - com.mysql.lists.bugs

2 messages in com.mysql.lists.bugsALTER TABLE with not null ENUM causes...

From	Sent On	Attachments
Kevin Miller	01 Jul 2001 20:13
Michael Widenius	03 Jul 2001 13:10

Subject:	ALTER TABLE with not null ENUM causes invalid table data
From:	Kevin Miller (kev...@andrew.cmu.edu)
Date:	07/01/2001 08:13:58 PM
List:	com.mysql.lists.bugs

Description:

When executing an ALTER TABLE against a column which is defined as ENUM, if the new type is ENUM and defined NOT NULL with a default value specified, the data in the table is incorrectly converted to having a value of ''. According to the documentation, it should be updated to the default value.

How-To-Repeat:

CREATE TABLE bar (id int not null auto_increment primary key, test enum('Y', 'N') not null default 'N');

INSERT INTO bar (test) VALUES ('Y'); INSERT INTO bar (test) VALUES ('N');

SELECT * FROM bar; DESCRIBE bar; ALTER TABLE bar CHANGE COLUMN test test enum ('Yes', 'No') default 'No' not null; SELECT * FROM bar;

Fix:

After issuing the ALTER TABLE, issue an UPDATE TABLE to set the correct default value.

Submitter-Id: kev...@andrew.cmu.edu Originator: Kevin Miller

Organization: Carnegie Mellon University; Network Development MySQL support: none Synopsis: ALTER TABLE with ENUM/not null results in wrong table data Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.39 (Official MySQL binary)

Environment:

Initially noted the problem on mysql-3.23.22-beta compiled locally

System: Linux localhost 2.2.16-22 #1 Tue Aug 22 16:49:06 EDT 2000 i686 unknown Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx 1 root root 14 Feb 15 12:16 /lib/libc.so.6 -> libc-2.1.92.so -rwxr-xr-x 1 root root 4776568 Aug 30 2000 /lib/libc-2.1.92.so -rw-r--r-- 1 root root 22607104 Aug 30 2000 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Aug 30 2000 /usr/lib/