Bug #22451 Partitions: duplicate results with engine=federated
Submitted: 18 Sep 2006 20:08 Modified: 16 Jan 2007 1:08
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.12-beta-debug OS:Linux (SUSE 10.0)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: Q1

[18 Sep 2006 20:08] Peter Gulutzan
Description:
I create a partitioned table with engine=federated.
I select from it.
I get 4 rows. But the table has only 2 rows.

How to repeat:
On the "remote server" (i.e. the server where the .MYD file is actually stored):

mysql> use federated
Database changed

mysql> create table t2 (s1 int) partition by list (s1) (partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| s1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

On the "local server" (i.e. the server which only has the stub frm file):

create table t2 (s1 int) engine=federated connection='mysql://root@192.168.1.102/federated/t2' partition by list (s1) (partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.12 sec)

mysql> select * from t2;
+------+
| s1   |
+------+
|    1 |
|    2 |
|    1 |
|    2 |
+------+
4 rows in set (0.00 sec)
[18 Sep 2006 21:16] MySQL Verification Team
Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysqladmin -uroot create federated
miguel@hegel:~/dbs/5.1> bin/mysql -uroot federated
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

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

mysql> grant all on *.* to 'miguel'@'192.168.0.33';
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2 (s1 int) partition by list (s1) (partition p1
    -> values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values (1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| s1   |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.01 sec)

mysql> 

D:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.12-beta-nt

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

mysql> create database federated;
Query OK, 1 row affected (0.03 sec)

mysql> use federated;
Database changed
mysql> create table t2 (s1 int) engine=federated
    -> connection='mysql://miguel@192.168.0.119/federated/t2' partition by list
    -> (s1) (partition p1 values in (1), partition p2 values in (2));
Query OK, 0 rows affected (0.14 sec)

mysql> select * from t2;
+------+
| s1   |
+------+
|    1 |
|    2 |
|    1 |
|    2 |
+------+
4 rows in set (0.23 sec)

mysql>
[30 Oct 2006 8:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14533

ChangeSet@1.2338, 2006-10-30 12:57:22+04:00, holyfoot@mysql.com +3 -0
  bug #22451 (Partitions: duplicate results with FEDERATED)
  
  We should not create PARTITION hton over FEDERATED engine table.
  Here i fix open_binary_frm to not create PARTITION hton if it's over
  FEDERATED.
[19 Dec 2006 12:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17154

ChangeSet@1.2359, 2006-12-19 16:37:50+04:00, holyfoot@mysql.com +3 -0
  bug #22451 (Partitions: duplicating results with ENGINE=FEDERATED)
  
  As presently PARTITIONS + FEDERATED just doesn't work i added error message
  to inform user about that.
  Should be enabled again after releted WL's completion.
[15 Jan 2007 8:21] Sergei Glukhov
Fixed in 5.1.15-beta
[15 Jan 2007 15:11] Jon Stephens
Hi! Please clarify: is partitioning of FEDERATED tables now supported, or is it now disallowed? Thanks!
[15 Jan 2007 15:26] Patrick Galbraith
From the patch, it has been disabled from working with Partition.

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

ChangeSet@1.2359, 2006-12-19 16:37:50+04:00, holyfoot@mysql.com +3 -0
  bug #22451 (Partitions: duplicating results with ENGINE=FEDERATED)
  
  As presently PARTITIONS + FEDERATED just doesn't work i added error message
  to inform user about that.
  Should be enabled again after releted WL's completion.

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

I'm going to add to my list figuring out a way to really "fix" this issue. 
The problem is (without looking at the code) that each partition created
is looking at the same table (since partition is using a handler per partition),
and each partition ends up being federated table pointing 
to the remote table, hence giving you results * number partitions duplicate
results. What is needed is for partitioning to be able to create the separate
partitions remotely. This would require "ha_federated::create" to be able to
create tables on the foreign server end. Also, the federated storage engine
would need to be able to parse the partition name, not the table name, and use that as the name of the remote table.
[16 Jan 2007 1:08] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[16 Jan 2007 1:11] Jon Stephens
Documented feature change in 5.1.15 changelog, Partitioning chapter of 5.1 Manual.