Bug #25672 ALTER/DROP SERVER does not immediately affect open tables
Submitted: 17 Jan 2007 8:33 Modified: 17 Jan 2007 20:54
Reporter: Giuseppe Maxia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.1.15 OS:Linux (linux)
Assigned to: CPU Architecture:Any
Tags: alter server, drop server, federated
Triage: Triaged: D3 (Medium)

[17 Jan 2007 8:33] Giuseppe Maxia
Description:
When issuing a DROP/ALTER SERVER, existing federated tables continue to be linked to the old server, until a FLUSH TABLES statement is issued.

For example:

SERVER_A
========
create table test.t1 (id int);
insert into test.t1 values
(1),
(11),
(111);

create schema if not exists test2;
create table test2.t1 (id int);
insert into test.t1 values
(2),
(22),
(222);

SERVER_B
=========

CREATE SERVER 's1' foreign data wrapper 'mysql' options
(HOST 'SERVER_A',
DATABASE 'test',
USER 'datacharmer',
PASSWORD 'datacharmer');

create table test.t1 (id int) engine=federated connection ='s1';

select * from t1;
+-----+
| id  |
+-----+
|   1 |
|  11 |
| 111 |
+-----+

# everything right so far

ALTER SERVER 's1'
(HOST 'SERVER_A',
DATABASE 'test2');

SELECT * from t1;
+-----+
| id  |
+-----+
|   1 |
|  11 |
| 111 |
+-----+

# Odd. The change of server is not detected

FLUSH TABLES;   # this will flush the existing open connection to SERVER_A
SELECT * from t1;
+-----+
| id  |
+-----+
|   2 |
|  22 |
| 222 |
+-----+

# yet another experiment:

DROP SERVER s1;
SELECT * from t1;
+-----+
| id  |
+-----+
|   2 |
|  22 |
| 222 |
+-----+

# An error should be issued here. It only comes after FLUSH TABLES.

FLUSH TABLES;
SELECT * from t1;

# (now the error comes, as expected, even though it mentions the wrong thing)
ERROR 1 (HY000): Can't create/write to file 'server name: 's1' doesn't exist!' (Errcode: 140626822)

How to repeat:
test case to follow

Suggested fix:
none.
[17 Jan 2007 16:26] Patrick Galbraith
Talked to brian about how to solve this. Have access to a handlerton
to mysql.servers table in sql_parse.cc in do_command that flushes/updates
upon modification
[17 Jan 2007 20:46] Giuseppe Maxia
test case to document bug 25672

Attachment: federated_bug_25672.tar.gz (application/gzip, text), 1.30 KiB.

[5 Jan 2008 8:59] Giuseppe Maxia
This was apparently fixed in 5.1.23.

When attempting to use a non-existing server, the federated engine correctly fails, but issuing some sort of random error code (Bug#33702)
To make a test case for this bug, then, Bug#33702 needs to be fixed first.