MySQL Bugs: #25672: ALTER/DROP SERVER does not immediately affect open tables

Bug #25672	ALTER/DROP SERVER does not immediately affect open tables
Submitted:	17 Jan 2007 9:33	Modified:	17 Jan 2007 21:54
Reporter:	Giuseppe Maxia
Status:	Verified
Category:	Server: Federated	Severity:	S3 (Non-critical)
Version:	5.1.15	OS:	Linux (linux)
Assigned to:		Target Version:
Tags:	federated, alter server, drop server
Triage:	Triaged: D3 (Medium)

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.

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

test case to document bug 25672

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

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.