BIT datatype and trying to use it: data too long error. Could this be a bug?

[Martijn Tonies]

Hi there,

With the help of the people at CoreLab, we found out this problem:

> After long testing we detected source of the problem. It's
STRICT_TRANS_TABLES
> flag in sql-mode my.ini variable.
> This variable affects only CREATE TABLE and CREATE PROCEDURE statements.
>
> Even if you simplify script to create server objects to
>
> DROP TABLE IF EXISTS newtab;
>
> CREATE TABLE newtab (
>    b BIT
>  )
>  ENGINE=MYISAM
>  ROW_FORMAT=FIXED
>  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
>
>  DROP PROCEDURE IF EXISTS newtab_I;
>  CREATE PROCEDURE newtab_I(IN b BIT)
>  BEGIN
>    INSERT INTO newtab (b) VALUES (b);
>  END;
>
> you still get the error. We think, this is MySQL server problem. To check
this
> hypothesis you can call 'CALL newtab_I(1)' statement from mysql.exe
command line
> utility.

Trying to call newtab_I with (1) for the BIT parameter will result into:
Data too long for column 'b' at row 1


If this is "as designed", how should one use this particular datatype?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com