alter table (mysql) adding column with default value of space (' ')

Question

On my Mac, using 5.5.11 MySQL Community Server (GPL)

alter table foobaz add column (
extendedTitle varchar(3000) DEFAULT NULL,
clasindxIndent int,
spacedash1 enum (' ', '-', '1') default ' ',
plusSubs enum (' ', '+') default ' ');

works perfectly. On my Debian server using 5.1.49-3-log (Debian), it fails with

ERROR 1067 (42000): Invalid default value for 'spacedash1'

I don't understand the error, or why it works on my Mac and not on my server. Any hints or pointers greatly appreciated.

Pat

• ypercubeᵀᴹ: I suggest you change these Enums to CHAR(1).

Answer 1

Either the rules have changed, or somebody missed something in compiler builds.

According to the MySQL Documentation on ENUM

For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown.

Value Index

NULL NULL

'' 0

'one' 1

'two' 2

'three' 3

An enumeration can have a maximum of 65,535 elements.

Trailing spaces are automatically deleted from ENUM member values in the table definition when a table is created.

You are using MySQL 5.1.49. That build probably errors out on seeing a one-character string with a blank.

MySQL 5.5 probably removes trailing spaces leaving an empty string.

• Pat Farrell: Thanks. And I'll look for a 5.5 to upgrade to on the server