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

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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.