MySQL Bugs: #41565: Deleting from InnoDB table through Federated link doesn't affect the query cache

Bug #41565	Deleting from InnoDB table through Federated link doesn't affect the query cache
Submitted:	17 Dec 2008 19:56	Modified:	16 Feb 2009 12:28
Reporter:	Boris Shargorodskiy	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.30-community-log, 5.1, 6.0 bzr	OS:	Linux (CentOS release 4.5 (Final))
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Federated InnoDB delete query cache, regression

Description:
There are two empty tables: the table created using InnoDB storage engine and the table created using Federated storage engine that is the link to the first one.
If we insert one record into the table, than select it through the federated link, than delete it from the table without specifying "where" condition, and than select it (the deleted record) by the same way as before, then the result will depend on the query cache is enabled or not.

How to repeat:
-- Server 1 (192.168.0.1):

set global query_cache_type = 1;
set global query_cache_size = 104857600;

create database f_test_main;

use f_test_main

create table tbl1 (
   `id` bigint not null auto_increment,
   `cf` varchar(255) not null,
   primary key (`id`),
   index `cf_index` (`cf`)
)
engine = innodb
default charset = latin1;

-- Server 2 (192.168.0.2):

create database f_test_cust;

use f_test_cust

create table tbl1 (
   `id` bigint not null auto_increment,
   `cf` varchar(255) not null,
   primary key (`id`),
   index `cf_index` (`cf`)
)
engine = federated
default charset = latin1
connection = 'mysql://test:test@192.168.0.1:3306/f_test_main/tbl1';

insert into tbl1 (`cf`) values ('cf');
select * from tbl1;
delete from tbl1;

select * from tbl1;

--------------------------

The result:
+----+----+
| id | cf |
+----+----+
|  2 | cf |
+----+----+
1 row in set (0.00 sec)

Expected result:
Empty set (0.00 sec)

Thank you for the report.

Verified as described.

Version 5.0 is not affected. Bug is not repeatable if remote table is MyISAM.

Forgot to add:
MySQL 5.1 Reference Manual (http://dev.mysql.com/doc/refman/5.1/en/federated-usagenotes.html) contains the following item: "FEDERATED tables do not work with the query cache".

Thank you.

This is a bug because we have wrong results if query cache is used for InnoDB table on a remote database:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set global query_cache_type = 1;
Query OK, 0 rows affected (0.05 sec)

mysql> set global query_cache_size = 104857600;
Query OK, 0 rows affected (0.03 sec)

mysql> create table tbl1 (
    ->    `id` bigint not null auto_increment,
    ->    `cf` varchar(255) not null,
    ->    primary key (`id`),
    ->    index `cf_index` (`cf`)
    -> )
    -> engine = innodb
    -> default charset = latin1;
Query OK, 0 rows affected (0.23 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table tbl1;
Query OK, 0 rows affected (0.09 sec)

mysql> create table tbl1 (
    ->    `id` bigint not null auto_increment,
    ->    `cf` varchar(255) not null,
    ->    primary key (`id`),
    ->    index `cf_index` (`cf`)
    -> )
    -> engine = federated
    -> default charset = latin1
    -> connection = 'mysql://root:root@127.0.0.1:3310/test/tbl1';
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tbl1 (`cf`) values ('cf');
Query OK, 1 row affected (0.16 sec)

mysql> select * from tbl1;
+----+----+
| id | cf |
+----+----+
|  1 | cf |
+----+----+
1 row in set (0.24 sec)

mysql> delete from tbl1;
Query OK, 1 row affected (0.27 sec)

mysql> select * from tbl1;
+----+----+
| id | cf |
+----+----+
|  1 | cf |
+----+----+
1 row in set (0.00 sec)

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl1;
+----+----+
| id | cf |
+----+----+
|  1 | cf |
+----+----+
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set global query_cache_size = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl1;
Empty set (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from tbl1;
Empty set (0.01 sec)

This is a duplicate of BUG#40386.

For both DELETE w/o WHERE condition and TRUNCATE, federated is sending TRUNCATE to the remote host. This explains, why direct DELETE from InnoDB table works. This bug is reported as BUG#42878.