Bug #34469 FEDERATED engine fails to update table containing BIT(1) column
Submitted: 11 Feb 2008 19:31 Modified: 27 Jun 2012 6:21
Reporter: Cyrille Béraud Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S1 (Critical)
Version:5.0.54 OS:Windows (Windows 2003 x86)
Assigned to: CPU Architecture:Any
Tags: 2003, bit, federated, UPDATE, windows

[11 Feb 2008 19: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 20: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)
[26 Jun 2012 9:04] Valeriy Kravchuk
This is what we have on 5.6.5:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.5-m8-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> select * from tbf;
+----+------+-------+
| id | bf   | other |
+----+------+-------+
|  1 |     |     1 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> update tbf set other=3 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbf;
+----+------+-------+
| id | bf   | other |
+----+------+-------+
|  1 |     |     3 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> show create table tb\G
*************************** 1. row ***************************
       Table: tb
Create Table: CREATE TABLE `tb` (
  `id` int(11) NOT NULL,
  `bf` bit(1) DEFAULT NULL,
  `other` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table tbf\G
*************************** 1. row ***************************
       Table: tbf
Create Table: CREATE TABLE `tbf` (
  `id` int(11) NOT NULL,
  `bf` bit(1) DEFAULT NULL,
  `other` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@localhost:3306/test/tb'
1 row in set (0.00 sec)

So, looks like original problem is somehow fixed there.
[27 Jun 2012 6:16] Valeriy Kravchuk
Actually not repeatable with 5.5.25 on 32-bit Windows XP also:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine             | Support | Comment
                | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine
                | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables
                | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine
                | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to
 it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine
                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for tempor
ary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine
                | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and f
oreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema
                | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9 rows in set (0.02 sec)

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

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

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

mysql> select * from tbf;
+----+------+-------+
| id | bf   | other |
+----+------+-------+
|  1 | ☺    |     1 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> update tbf set other = 333 where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbf;
+----+------+-------+
| id | bf   | other |
+----+------+-------+
|  1 | ☺    |   333 |
+----+------+-------+
1 row in set (0.00 sec)
[27 Jun 2012 6:21] Valeriy Kravchuk
So, this is not repeatable on current versions.
[31 Jan 2013 19:04] Matheus Matruskan
I still have a problem while updating and querying a federated table containing BIT column.

The wrong updates and queries come from a Windows 32bits machine:

[code]mysql  Ver 14.14 Distrib 5.5.25a, for Win32 (x86)[/code]

The original InnoDB table, which the federated table points to, is on a Ubuntu 64bits:

[code]mysql  Ver 14.14 Distrib 5.5.24, for debian-linux-gnu (x86_64) using readline 6.2[/code]

The only update command that seems to work as expected is setting the bit column to NULL. If the update command sets it to true, false, b'1', b'0', (1), (0), the column is always set to false.

Querying the federated table, the windows MySQL sees every row as b'1', while the linux MySQL indicates that they are b'0'.