List:General Discussion« Previous MessageNext Message »
From:David Karr Date:March 8 2009 10:55pm
Subject:Re: Possible to get better error handling for invalid enum parameter
to stored program?
View as plain text  
On Sun, Mar 8, 2009 at 3:31 PM, Steve Edberg <sbedberg@stripped> 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 <perrin@stripped> 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
> <davidmichaelkarr@stripped>
>>>  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                            sbedberg@stripped |
> | Bioinformatics programming/database/sysadmin             (530)754-9127 |
> +---------------- said t e lawrence, picking up his fork ----------------+
>

Thread
Possible to get better error handling for invalid enum parameter to stored program?David Karr6 Mar
  • Re: Possible to get better error handling for invalid enum parameter to stored program?Perrin Harkins6 Mar
    • Re: Possible to get better error handling for invalid enum parameter to stored program?David Karr6 Mar
      • Re: Possible to get better error handling for invalid enumparameter to stored program?Steve Edberg8 Mar
Re: Possible to get better error handling for invalid enum parameter to stored program?David Karr8 Mar