Bug #34469 FEDERATED engine fails to update table containing BIT(1) column

• Submitted: 11 Feb 2008 20:31
• Modified: 11 Feb 2008 21:03
• Reporter: Cyrille Béraud
• Status: Verified
• Category: Server: Federated
• Severity: S1 (Critical)
• Version: 5.0.54
• OS: Microsoft Windows (Windows 2003 x86)
• Tags: federated, windows, 2003, UPDATE, bit
• Triage: Triaged: D2 (Serious)

[11 Feb 2008 20:31] Cyrille Béraud

Description:
We have the following bug : Doing an UPDATE on a FEDERATED table, pointing the the exact
same InnoDB table on another mysql server running on localhost produces the following
output :

mysql> UPDATE table set version=96  where
ticketid='222P4CWMIHV2VMUERT' ;
-
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1292:
Truncated incorrect DOUBLE value: '☺'' from FEDERATED

------------------------------------------------
The table looks like that :
CREATE TABLE `table` (
  `ticketId` varchar(20) NOT NULL,
  `discriminatorType` varchar(15) NOT NULL,
  `version` int(11) NOT NULL,
  `siteId` smallint(6) default NULL,
  `posId` int(11) default NULL,
  `creationDate` datetime default NULL,
  `dateFirstUsed` datetime default NULL,
  `ticketSequence` int(11) unsigned default NULL,
  `online` bit(1) default '\0',
  `ticketStatus` varchar(10) NOT NULL,
  `transactionSequence` int(11) default '0',
  `lastModifiedDate` date default NULL,
  `seller` bigint(20) NOT NULL,
  `verifiedByPos` bigint(20) default NULL,
  `verifiedBy` bigint(20) default NULL,
  `ticketType` bigint(20) NOT NULL,
  `voidUser` bigint(20) default NULL,
  `workstationid` bigint(20) NOT NULL,
  PRIMARY KEY  (`ticketId`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1
  CONNECTION='mysql://user:password@localhost:3306/db/table';

The destination table is exactly the same, using the InnoDB storage engine.

Concerning "Truncated incorrect DOUBLE value: '☺'' from FEDERATED": 

The '☺' character is the ascii representation of the b'1' value in a windows DOS
shell.

Changing the 'online' column from BIT(1) to TINYINT fixes the problem.
Changing back to BIT(1) makes it come back.

However the UPDATE we post is unrelated to the online column. This is strange.

We were unable to reproduce this behavior on linux with the same dataset.

How to repeat:
-Using windows 2003
-Setup main mysql server
-Setup federated mysql server

On main server :
CREATE TABLE `table` (
  `ticketId` varchar(20) NOT NULL,
  `discriminatorType` varchar(15) NOT NULL,
  `version` int(11) NOT NULL,
  `siteId` smallint(6) default NULL,
  `posId` int(11) default NULL,
  `creationDate` datetime default NULL,
  `dateFirstUsed` datetime default NULL,
  `ticketSequence` int(11) unsigned default NULL,
  `online` bit(1) default '\0',
  `ticketStatus` varchar(10) NOT NULL,
  `transactionSequence` int(11) default '0',
  `lastModifiedDate` date default NULL,
  `seller` bigint(20) NOT NULL,
  `verifiedByPos` bigint(20) default NULL,
  `verifiedBy` bigint(20) default NULL,
  `ticketType` bigint(20) NOT NULL,
  `voidUser` bigint(20) default NULL,
  `workstationid` bigint(20) NOT NULL,
  PRIMARY KEY  (`ticketId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Feed main table with some data :
INSERT INTO table VALUES ('222P4CWMIHV2VMUERT','sometext',97,15,3,'2008-01-29
17:46:22',NULL,2451,b'1','ACTIVE',33,'2008-01-29',30,NULL,NULL,3,NULL,131);

Then Setup 2nd mysql server and create federated table :
CREATE TABLE `table` (
  `ticketId` varchar(20) NOT NULL,
  `discriminatorType` varchar(15) NOT NULL,
  `version` int(11) NOT NULL,
  `siteId` smallint(6) default NULL,
  `posId` int(11) default NULL,
  `creationDate` datetime default NULL,
  `dateFirstUsed` datetime default NULL,
  `ticketSequence` int(11) unsigned default NULL,
  `online` bit(1) default '\0',
  `ticketStatus` varchar(10) NOT NULL,
  `transactionSequence` int(11) default '0',
  `lastModifiedDate` date default NULL,
  `seller` bigint(20) NOT NULL,
  `verifiedByPos` bigint(20) default NULL,
  `verifiedBy` bigint(20) default NULL,
  `ticketType` bigint(20) NOT NULL,
  `voidUser` bigint(20) default NULL,
  `workstationid` bigint(20) NOT NULL,
  PRIMARY KEY  (`ticketId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
  CONNECTION='mysql://user:password@localhost:3306/db/table';

Now use the federated server to push an UPDATE.
mysql> UPDATE table set version=96  WHERE
ticketid='222P4CWMIHV2VMUERT' ;
-
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1292:
Truncated incorrect DOUBLE value: '☺'' from FEDERATED

[11 Feb 2008 21:03] Valeriy Kravchuk

I can repeat the behaviour described with 5.0.54 and simpler test case:

mysql> create table tb(id int primary key, bf bit(1), other int) engine=InnoDB;
Query OK, 0 rows affected (0.61 sec)

mysql> insert into tb values(1, 1, 1);
Query OK, 1 row affected (0.03 sec)

mysql> create table tbf(id int primary key, bf bit(1), other int) engine=federat
ed connection='mysql://root:root@localhost:3308/test/tb';
Query OK, 0 rows affected (0.06 sec)

mysql> update tbf set other=3 where id=1;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1292: Truncated inc
orrect DOUBLE value: '☺'' from FEDERATED

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.54-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)