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: | |
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
[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'.