I have encountered a strange problem with the enum type in version
3.23.49. It may be a bug, but I'd be grateful to hear comments.
Here's a short example which illustrates the problem. First, I create
a table with an enum column whose allowed elements are single letters,
including a single space:
CREATE TABLE cats (
name VARCHAR(12) NOT NULL,
gender ENUM(' ', 'F', 'M') NOT NULL,
PRIMARY KEY(name)
);
Then I populate the table with a couple of records, specifying only the
name fields:
INSERT INTO cats(name) VALUES('Dinah'),('Molly');
When I check the contents of the table, everything looks fine:
mysql> select * from cats;
+-------+--------+
| name | gender |
+-------+--------+
| Dinah | |
| Molly | |
+-------+--------+
2 rows in set (0.00 sec)
The "gender" column has been set to the default value, a single space,
but that's impossible to see in the query above. This query shows
that the "gender" column is *not* the empty string:
mysql> select name,length(gender) from cats;
+-------+----------------+
| name | length(gender) |
+-------+----------------+
| Dinah | 1 |
| Molly | 1 |
+-------+----------------+
2 rows in set (0.00 sec)
And for completeness, this query shows the index value of the "gender"
column, which is the default (1) in both cases:
mysql> select name,gender+0 from cats;
+-------+----------+
| name | gender+0 |
+-------+----------+
| Dinah | 1 |
| Molly | 1 |
+-------+----------+
2 rows in set (0.00 sec)
Now I run mysqldump to save that table to a file, using the --opt
option:
-- MySQL dump 8.21
--
-- Host: pcs3 Database: test
---------------------------------------------------------
-- Server version 3.23.49-log
--
-- Table structure for table 'cats'
--
DROP TABLE IF EXISTS cats;
CREATE TABLE cats (
name varchar(12) NOT NULL default '',
gender enum(' ','F','M') NOT NULL default ' ',
PRIMARY KEY (name)
) TYPE=MyISAM;
/*!40000 ALTER TABLE cats DISABLE KEYS */;
--
-- Dumping data for table 'cats'
--
LOCK TABLES cats WRITE;
INSERT INTO cats VALUES ('Dinah',' '),('Molly',' ');
/*!40000 ALTER TABLE cats ENABLE KEYS */;
UNLOCK TABLES;
Notice that the table definition statement now explicitly states that
the default value for the "gender" column is ' '. I didn't specify
this explicitly when I defined the table originally.
Now, when I try to read the backup file back into MySQL, I get this
error:
ERROR 1067 at line 12: Invalid default value for 'gender'
But *why* should this happen? MySQL seems to be happy enough to
let me include a single space as an element of the enumeration,
but it won't let me specify it explicitly as the default value.
This behaviour can be replicated at will in versions 3.23.38 and
3.23.49 of MySQL, running on both Compaq Alpha OSF5.1 and Red
Hat Linux 7.2 for i386.
Thanks in advance
David Harper
Wellcome Trust Sanger Institute, Cambridge, England
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
