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