Bug #35333 If a Federated Table can't connect to the remote host, can't retrieve metadata
Submitted: 17 Mar 2008 11:59 Modified: 11 Jan 2011 16:36
Reporter: Santo Leto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S1 (Critical)
Version:5.1.23-beta, 5.0.58, 5.0.60-BK, 5.1.25-BK OS:Any
Assigned to: Christopher Powers CPU Architecture:Any
Tags: federated, metadata

[17 Mar 2008 11:59] Santo Leto
Description:
If a Federated Table can't connect to the remote host, can't select over the local table information_schema.tables.

How to repeat:
We will use 2 servers: a 'remote' server and a local server.
First we create a table on the remote server and then we create a federated table on the local server and we make sure all is working fine.

Then we'll shutdown the remote server and we'll use the Test Script to simulate the bug.

*** PRELIMINARY SCRIPT ***

Remote Server:

SELECT CURRENT_USER(), VERSION();

DROP DATABASE IF EXISTS `federated`;
CREATE DATABASE `federated`;

DROP TABLE IF EXISTS `federated`.`test_table`;

CREATE TABLE `federated`.`test_table` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `other` int(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `other_key` (`other`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `federated`.`test_table` (`name`,`other`) VALUES ('remote_value1', '1');

SELECT * FROM `federated`.`test_table`;

Local Server:

SELECT CURRENT_USER(), VERSION();

DROP TABLE IF EXISTS `test`.`federated_table`;

CREATE TABLE `test`.`federated_table` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `other` int(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `other_key` (`other`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://sleto@localhost:6004/federated/test_table';

INSERT INTO `test`.`federated_table` (`name`,`other`) VALUES ('local_value1', '1');

SELECT * FROM `test`.`federated_table`;

*** END PRELIMINARY SCRIPT ***

*** TEST SCRIPT ***

Local Server

SELECT * FROM `information_schema`.`tables` LIMIT 1\G
## please, shutdown the remote server, then execute once again
SELECT * FROM `information_schema`.`tables` LIMIT 1\G

*** END TEST SCRIPT ***

*** PRELIMINARY SCRIPT OUTPUT ***

Remote Server:

mysql> SELECT CURRENT_USER(), VERSION();
+----------------+---------------------------+
| CURRENT_USER() | VERSION()                 |
+----------------+---------------------------+
| root@localhost | 6.0.4-alpha-community-log |
+----------------+---------------------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP DATABASE IF EXISTS `federated`;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE `federated`;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS `federated`.`test_table`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TABLE `federated`.`test_table` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(32) NOT NULL DEFAULT '',
    ->   `other` int(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
    ->   KEY `name` (`name`),
    ->   KEY `other_key` (`other`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO `federated`.`test_table` (`name`,`other`) VALUES ('remote_val
ue1', '1');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM `federated`.`test_table`;
+----+---------------+-------+
| id | name          | other |
+----+---------------+-------+
|  1 | remote_value1 |     1 |
+----+---------------+-------+
1 row in set (0.00 sec)

mysql>

Local Server:

mysql> SELECT CURRENT_USER(), VERSION();
+----------------+-------------------------+
| CURRENT_USER() | VERSION()               |
+----------------+-------------------------+
| root@localhost | 5.1.23-rc-community-log |
+----------------+-------------------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS `test`.`federated_table`;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE `test`.`federated_table` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(32) NOT NULL DEFAULT '',
    ->   `other` int(20) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
    ->   KEY `name` (`name`),
    ->   KEY `other_key` (`other`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://sleto@local
host:6004/federated/test_table';
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO `test`.`federated_table` (`name`,`other`) VALUES ('local_valu
e1', '1');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM `test`.`federated_table`;
+----+---------------+-------+
| id | name          | other |
+----+---------------+-------+
|  1 | remote_value1 |     1 |
|  2 | local_value1  |     1 |
+----+---------------+-------+
2 rows in set (0.01 sec)

*** END PRELIMINARY SCRIPT OUTPUT ***

*** TEST SCRIPT OUTPUT ***

Local Server

mysql> SELECT * FROM `information_schema`.`tables` LIMIT 1\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: information_schema
     TABLE_NAME: CHARACTER_SETS
     TABLE_TYPE: SYSTEM VIEW
         ENGINE: MEMORY
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: 576
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 16661376
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: max_rows=29127
  TABLE_COMMENT:
1 row in set (0.19 sec)

# the remote server is now down

mysql> SELECT * FROM `information_schema`.`tables` LIMIT 1\G
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to M
ySQL server on 'localhost' (10061)

*** END TEST SCRIPT OUTPUT ***

Suggested fix:
If the remote server is down, simply discard it while retrieving metadata.
[7 Apr 2008 2:11] Peter Brawley
I've also tripped over this bug. If you need to declare Federated tables and you need metadata, then the remote server going down is a showstopper. I agree the bug is critical.
[15 Apr 2008 9:40] MySQL Verification Team
this demonstrates the problem also:

create database if not exists `realdb`;
create database if not exists `test`;
drop table if exists `test`.`t0`;
drop table if exists `realdb`.`t0`;
create table `realdb`.`t0`(`a` text,`b` text)engine=myisam;
create table `test`.`t0`(`a` text,`b` text)engine=federated connection='mysql://root@127.0.0.1:3333/realdb/t0';
show table status;
select * from information_schema.tables;

port 3333 is not open even and there's no mysqld listening on it.
[15 Apr 2008 9:48] MySQL Verification Team
In reality the bug is a small Denial of Service!  A low-privileged user can 
prevent the root user from running SHOW TABLE STATUS or selecting from INFORMATION_SCHEMA by creating a bunch of tables with nonsense URLS.
[15 Apr 2008 12:02] Valeriy Kravchuk
Verified just as described using Shane's test case:

openxs@suse:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.60 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database if not exists `realdb`;
create database if not exists `test`;
Query OK, 1 row affected (0.00 sec)

mysql> create database if not exists `test`;
Query OK, 1 row affected (0.00 sec)

mysql> drop table if exists `test`.`t0`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists `realdb`.`t0`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `realdb`.`t0`(`a` text,`b` text)engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table `test`.`t0`(`a` text,`b` text)engine=federated
    -> connection='mysql://root@127.0.0.1:3333/realdb/t0';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.tables;
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on '127.0.0.1' (111)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show table status;
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on '127.0.0.1' (111)
[27 Jan 2010 14:39] Kriss Parker
Hi,

Have you tried adding the remote host IP address to your Hosts file
C:\Windows\System32\Drivers\etc

Add the remote host IP address and hostname (for example 'localhost') to your hosts file on the machine you wish to access.

Now as long as the user is added into your 'user table' in your database, and the remote IP address is added in your hosts file it should work.

For example remote host 10.0.10.2 wants to access the database on the localhost 10.0.10.1 so add the ip address 10.0.10.2 to your hosts file.

You may just want to try MySQL Query Broswer after doing this and connect.
[16 Nov 2010 2:10] Bugs System
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/commits/123969

3115 Christopher Powers	2010-11-15
      Bug#35333, "If a Federated Table can't connect to the remote host, can't retrieve metadata"
      
      Improved error handling such that queries against Information_Schema.Tables won't fail if
      a Federated table is unable to connect to remote host.
     @ sql/sql_show.cc
        If Handler::Info() fails, save error text in TABLE COMMENTS column,
        clear error.
[18 Nov 2010 0:23] Bugs System
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/commits/124213

3128 Christopher Powers	2010-11-17
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve metadata"
            
      Improved error handling such that queries against Information_Schema.Tables won't
      fail if a federated table can't make a remote connection.
     @ sql/sql_show.cc
        If get_schema_tables_record() encounters an error, push a warning,
        set the TABLE COMMENT column with the error text, and clear the
        error so that the operation can continue.
[18 Nov 2010 19:57] Bugs System
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/commits/124301

3129 Christopher Powers	2010-11-18
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve metadata"
                  
      Improved error handling such that queries against Information_Schema.Tables won't
      fail if a federated table can't make a remote connection.
     @ mysql-test/r/lock_multi.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/mdl_sync.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/merge.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/show_check.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/view.result
        Updated with warnings that were previously masked.
     @ mysql-test/suite/federated/federated_bug_35333.result
        Changes per code review.
     @ mysql-test/suite/federated/federated_bug_35333.test
        Changes per code review.
     @ sql/sql_show.cc
        If get_schema_tables_record() encounters an error, push a warning,
        set the TABLE COMMENT column with the error text, and clear the
        error so that the operation can continue.
[19 Nov 2010 15:03] Bugs System
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/commits/124479

3130 Christopher Powers	2010-11-19
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve metadata"
                        
      Removed debug statement from test files.
[22 Nov 2010 22:13] Bugs System
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/commits/124691

3131 Christopher Powers	2010-11-22
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve metadata"
      
      Removed O/S-specific socket errors
     @ mysql-test/suite/federated/federated_bug_35333.result
        Strip out O/S-specific socket errors
     @ mysql-test/suite/federated/federated_bug_35333.test
        Strip out O/S-specific socket errors
[30 Nov 2010 0:52] Bugs System
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/commits/125429

2890 Christopher Powers	2010-11-29
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve metadata"
      
      Improved error handling such that queries against Information_Schema.Tables won't
      fail if a Federated table is unable to connect to remote host.
     @ sql/sql_show.cc
        If Handler::Info() fails, save error text in TABLE COMMENTS column, clear error.
[30 Nov 2010 1:12] Bugs System
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/commits/125430

3516 Christopher Powers	2010-11-29
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve
      metadata"
      
      Improved error handling such that queries against Information_Schema.Tables won't
      fail if a Federated table is unable to connect to remote host.
     @ sql/sql_show.cc
        If Handler::Info() fails, save error text in TABLE COMMENTS column, clear error.
[30 Nov 2010 4:47] Bugs System
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/commits/125440
[30 Nov 2010 16:59] Bugs System
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/commits/125530

3395 Christopher Powers	2010-11-30
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve
      metadata"
      
      Improved error handling such that queries against Information_Schema.Tables won't
      fail if a federated table can't make a remote connection.
     @ mysql-test/r/lock_multi.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/mdl_sync.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/merge.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/show_check.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/view.result
        Updated with warnings that were previously masked.
     @ mysql-test/suite/federated/federated_bug_35333.result
        New test results for bug#35333
     @ mysql-test/suite/federated/federated_bug_35333.test
        New test for bug#35333
[30 Nov 2010 18:22] Bugs System
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/commits/125542

3151 Christopher Powers	2010-11-30
      Bug#35333, "If Federated table can't connect to remote host, can't retrieve
      metadata"
      
      Improved error handling such that queries against Information_Schema.Tables won't
      fail if a federated table can't make a remote connection.
     @ mysql-test/r/lock_multi.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/mdl_sync.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/merge.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/show_check.result
        Updated with warnings that were previously masked.
     @ mysql-test/r/view.result
        Updated with warnings that were previously masked.
     @ mysql-test/suite/federated/federated_bug_35333.result
        New test results for bug#35333
     @ mysql-test/suite/federated/federated_bug_35333.test
        New test or bug#35333
     @ sql/sql_show.cc
        If get_schema_tables_record() encounters an error, push a warning,
        set the TABLE COMMENT column with the error text, and clear the
        error so that the operation can continue.
[1 Dec 2010 17:34] Christopher Powers
Pushed into mysql-5.0-bugteam, mysql-5.1-bugteam, mysql-5.5-bugteam, and mysql-trunk-bugfixing
[5 Dec 2010 12:38] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[15 Dec 2010 0:50] Paul DuBois
Bug does not appear in any released 5.6.x version.

Setting report to Need Merge pending push to other trees.
[17 Dec 2010 12:44] Bugs System
Pushed into mysql-5.0 5.0.92 (revid:georgi.kodinov@oracle.com-20101217124230-1o4se00exicjd3uo) (version source revid:chris.powers@oracle.com-20101130005146-47t2izsgx5xbgy23) (merge vers: 5.0.92) (pib:24)
[17 Dec 2010 12:49] Bugs System
Pushed into mysql-5.1 5.1.55 (revid:georgi.kodinov@oracle.com-20101217124435-9imm43geck5u55qw) (version source revid:mats.kindahl@oracle.com-20101201193331-1c07sjno2g7m46ix) (merge vers: 5.1.55) (pib:24)
[17 Dec 2010 12:53] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:chris.powers@oracle.com-20101130175311-fh7a4zdtf90nm32d) (merge vers: 5.5.8) (pib:24)
[11 Jan 2011 16:36] Paul DuBois
Noted in 5.0.92, 5.1.55, 5.5.9 changelogs.

If the remote server for a FEDERATED table could not be accessed,
queries for the INFORMATION_SCHEMA.TABLES table failed.