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 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

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.