| Bug #35333 | If a Federated Table can't connect to the remote host, can't retrieve metadata | ||
|---|---|---|---|
| Submitted: | 17 Mar 2008 12:59 | Modified: | 15 Apr 2008 14:02 |
| Reporter: | Santo Leto | ||
| Status: | Verified | ||
| Category: | Server: Federated | Severity: | S1 (Critical) |
| Version: | 5.1.23-beta, 5.0.58, 5.0.60-BK, 5.1.25-BK | OS: | Any |
| Assigned to: | Ramil Kalimullin | Target Version: | |
| Tags: | metadata, federated | ||
| Triage: | Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium) | ||
[7 Apr 2008 4: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 11:40]
Shane Bester
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 11:48]
Shane Bester
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 14: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)
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.