Bug #38916 Select from I_S.ROUTINES results in "No database selected" error

• Submitted: 20 Aug 2008 13:43
• Modified: 29 Jan 2009 21:07
• Reporter: Alexander Nozdrin
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 6.0-TRUNK
• OS: Any
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[20 Aug 2008 13:43] Alexander Nozdrin

Description:
If there is no current database, 'SELECT * FROM INFORMATION_SCHEMA.ROUTINES;' fails if there is a database with some specific structure.

How to repeat:
Prepare the databases:
-----------------------------------------------
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;

use db1;

CREATE TABLE t1(c1 INT, c2 INT);

CREATE TABLE t2(s varchar(255));

CREATE FUNCTION f1() RETURNS INT
  RETURN 1;

delimiter |
CREATE PROCEDURE `p1`()
begin
  select 1, 2, 3;
  select 11, 12, 13;
  select 21, 22, 23;
end|
delimiter ;

CREATE PROCEDURE `p2`() select 1;

CREATE VIEW v1 AS SELECT 'hello, world';

CREATE VIEW v2 AS SELECT * FROM t1;

CREATE VIEW v3 AS SELECT c1 FROM t1 UNION SELECT * FROM t2;

DROP DATABASE IF EXISTS db2;
CREATE DATABASE db2;
-----------------------------------------------

Then run the following SELECT with no current database
(start a new session, for example):

[(none)]> select routine_name, routine_type from information_schema.routines where routine_schema = 'db1';
ERROR 1046 (3D000): No database selected

[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
+--------------------+
4 rows in set (0.00 sec)

[(none)]> select version();
+-------------------+
| version()         |
+-------------------+
| 6.0.7-alpha-debug |
+-------------------+
1 row in set (0.00 sec)

The test case is not minimalistic.
The bug does not appear in 5.1.

[20 Aug 2008 14:22] MySQL Verification Team

Thank you for the bug report. Verified with mysql-6.0-backup:

Server version: 6.0.7-alpha-debug Source distribution

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

mysql> select routine_name, routine_type from information_schema.routines where
    -> routine_schema = 'db1';
ERROR 1046 (3D000): No database selected
mysql>

[16 Dec 2008 13:08] Jørgen Løland

This bug causes BACKUP to fail. See bug#38294

[19 Dec 2008 12:18] 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/62084

2814 Sergey Glukhov	2008-12-19
      Bug#38916 Select from I_S.ROUTINES results in "No database selected" error
      The problem is that db name name is not added when
      we create compilable string for the routine.
      The fix is to add db name to this string.

[24 Dec 2008 12:48] Alexander Nozdrin

The patch is approved with minor comments discussed on IRC.

[24 Dec 2008 15:37] 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/62305

2827 Sergey Glukhov	2008-12-24
      Bug#38916 Select from I_S.ROUTINES results in "No database selected" error
      The problem is that db name name is not added when
      we create compilable string for the routine.
      The fix is to add db name to this string.

[20 Jan 2009 18:58] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090119171328-2hemf2ndc1dxl0et) (version source revid:[e-mail]-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

[29 Jan 2009 21:07] Paul DuBois

Noted in 6.0.10 changelog.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES could fail if there was no
default database.