MySQL Bugs: #22451: Partitions: duplicate results with engine=federated

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

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)

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>

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.

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.

Fixed in 5.1.15-beta

Hi! Please clarify: is partitioning of FEDERATED tables now supported, or is it now disallowed? Thanks!

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.

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.

Documented feature change in 5.1.15 changelog, Partitioning chapter of 5.1 Manual.