Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
I suggest you change these Enums to CHAR(1). –  ypercube Mar 5 '12 at 21:09

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks. And I'll look for a 5.5 to upgrade to on the server –  Pat Farrell Mar 5 '12 at 21:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.