On Sun, Mar 8, 2009 at 3:31 PM, Steve Edberg <sbedb...@ucdavis.edu> wrote:
> If you want to keep the enum column, you can set the strict SQL mode:
>
> mysql> show variables like 'SQL_MODE';
> +---------------+-------+
> | Variable_name | Value |
> +---------------+-------+
> | sql_mode | |
> +---------------+-------+
> 1 row in set (0.00 sec)
>
> mysql> create table test2 (test enum('foo','bar'));
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> insert into test2 values('baz');
> Query OK, 1 row affected, 1 warning (0.00 sec)
>
> mysql> show warnings;
> +---------+------+-------------------------------------------+
> | Level | Code | Message |
> +---------+------+-------------------------------------------+
> | Warning | 1265 | Data truncated for column 'test' at row 1 |
> +---------+------+-------------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> set session sql_mode='traditional';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into test2 values('bloop');
> ERROR 1265 (01000): Data truncated for column 'test' at row 1
> mysql> select * from test2;
> +------+
> | test |
> +------+
> | |
> +------+
> 1 row in set (0.00 sec)
>
> I'm running v5.0.51a; as you can see, the first invalid value was truncated
> to '' with a warning, the second caused an error and did not insert. I don't
> know what version you're running, perhaps this does not apply to you. For
> more info:
>
>
I think you missed the point of my question. I'm currently in strict mode.
I think that's a good idea, in general. The error (not warning) I get when I
submit an invalid enum value is essentially "data truncated for column X".
My point is that that error message doesn't give any clue (except for the
column name) what the actual problem is. Another poster pointed out that
there's no resolution to this, outside of not using enum columns, or simply
accepting their limitations.
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
> http://dev.mysql.com/doc/refman/5.0/en/enum.html
>
> - steve edberg
>
>
>
> At 2:00 PM -0800 3/6/09, David Karr wrote:
>
>> Thanks. I thought that was the case, but I wanted to be sure.
>>
>> On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins <per...@elem.com> wrote:
>>
>> I'm afraid enums are useless for anything except compressing your
>>> data, since they don't reject bad values. If you want to limit a
>>> field to a set of values, you need to use a lookup table and a foreign
>>> key constraint.
>>>
>>> - Perrin
>>>
>>> On Fri, Mar 6, 2009 at 1:35 PM, David Karr <davidmichaelk...@gmail.com>
>>> wrote:
>>> > If I define an enum parameter for a stored program, and the calling
>>> code
>>> > sends an invalid value, they get the less than useful "data truncated"
>>> > error. Is it possible to define the stored program to produce better
>>> error
>>> > handling for that kind of error?
>>> >
>>> > This is probably a FAQ, but in general, it appears that error
>>> diagnostics
>>> in
>>> > stored programs are very primitive. Are there any plans in a roadmap
>>> to
>>> > improve this?
>>> >
>>>
>>>
>
> --
> +--------------- my people are the people of the dessert, ---------------+
> | Steve Edberg http://pgfsun.ucdavis.edu/ |
> | UC Davis Genome Center sbedb...@ucdavis.edu |
> | Bioinformatics programming/database/sysadmin (530)754-9127 |
> +---------------- said t e lawrence, picking up his fork ----------------+
>
