MySQL Bugs: #13118: INSERT VALUES(...) statement doesn't work properly on federated tables

Bug #13118	INSERT VALUES(...) statement doesn't work properly on federated tables
Submitted:	12 Sep 2005 7:00	Modified:	18 Jul 2006 18:21
Reporter:	Hiroshi Takenaka	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.0.12	OS:	FreeBSD (FreeBSD 4.11)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
INSERT VALUES(...) statement without column list doesn't work properly on
federated tables. For example:

------
mysql> create table federated_table (foo integer, bar integer) engine="federated" comment="mysql://myhost/mydb/mytabe";

mysql> select foo from federated_table;
Empty set (0.01 sec)

mysql> insert into  federated_table values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from federated_table;
+------+------+
| foo  | bar  |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.01 sec)

mysql> insert into  federated_table values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from federated_table;
+------+------+
| foo  | bar  |
+------+------+
| NULL | NULL |
|    1 |    1 |
+------+------+
2 rows in set (0.01 sec)
------

In above example, the query "select foo from federated_table"
changes query_id of the Field foo, but not query_id of bar.
So, right  after such query, all_fields_have_same_query_id trick
in ha_federated::write_row doesn't seem to work properly.

How to repeat:
follow steps shown above.

Verified with 5.0 from bk tree:
ChangeSet@1.1961.1.1, 2005-09-11 01:23:08+02:00, lars@mysql.com

I'm looking into this. The first think I note is that there is no primary key. I've talked with Pat Galbraith about whether or not a primary key should be or can be a requirement of a federated table. Pehaps people will want to be able to federate across tables without primary keys but we won't know until more people are using it. There have been a few tough issues around tables without primary keys, so I'll dig in and see what I find.

test script for BUG#13118

Attachment: federated_bug_13118.test (application/octet-stream, text), 816 bytes.

test results from script for BUG#13118

Attachment: federated_bug_13118.result (application/octet-stream, text), 999 bytes.

I've created a test script (see files) to attempt to demonstrate this bug. Interestingly, I thought I saw the test fail the first time I ran it but it passes very consistently now that I'm hoping to debug it.  Still working on it.

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/internals/30000

In the test script, either of two SELECT statements before INSERT
should be 'SELECT foo(or bar) from federated.t1',
because this bug will only happen when INSERT is issued just after
'SELECT not_all_columns from table'.

INSERTing after 'SELECT * from federated.t1' works fine,
because 'SELECT *' will set query_ids of all fields to a same value.

from IRC:
<patg>	it has to do with query id.
[21:00] 	<patg>	eric: dbug print the query id. It may not be incrementing in the first insert, and so the insert statement isn't created correctly.
<patg>	you'll also have to ensure that however you fix it doesn't break "INSERT INTO tablename VALUES ()"
if the query id doesn't increment and the field values aren't set, then it is a 'NULL' insert to all fields, otherwise, it simply means that there are values but not fields specified.

Great tip, Hiroshi. I've updated the tests and can reliably reproduce the error.

Works in 5.0.20

mysql> create table t1 (foo integer, bar integer) ENGINE=FEDERATED CONNECTION="mysql://root@localhost/test/t1";
Query OK, 0 rows affected (0.11 sec)

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

mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> insert into  t1 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
|    1 |    1 |
+------+------+
2 rows in set (0.00 sec)

mysql>

Hi Patrick,
it doesn't work even in 5.0.21.

As I mentioned earlier, to reproduce this bug,
you should perform 'select foo from t2' before you insert.

mysql> create table t2 (foo integer, bar integer) engine=federated connection="m
ysql://root@localhost/test/t1";
Query OK, 0 rows affected (0.07 sec)

mysql> select * from t2;
Empty set (0.09 sec)

mysql> insert into t2 values (1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.07 sec)

mysql> insert into t2 values (1, 1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
|    1 |    1 |
+------+------+
2 rows in set (0.07 sec)

mysql> select foo from t2;
+------+
| foo  |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.06 sec)

mysql> insert into t2 values (1, 1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 |
|    1 |    1 |
| NULL | NULL |
+------+------+
3 rows in set (0.07 sec)

See?

Still repeatable with latest BK server:

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

mysql> insert into t2 values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+------+
| foo  | bar  |
+------+------+
|    1 |    1 | 
|    1 |    1 | 
| NULL | NULL | 
+------+------+
3 rows in set (0.01 sec)

mysql>

Bug 15133 was the same issue. So 15133 was a duplicate of this bug, but now 15133 is fixed and 'need doc info'. 

http://bugs.mysql.com/bug.php?id=15133

 5.0.24-debug-log |