| Bug #35175 | SHOW FUNCTION STATUS returns an error if no database is selected | ||
|---|---|---|---|
| Submitted: | 10 Mar 2008 0:21 | Modified: | 1 Feb 2009 23:09 |
| Reporter: | Santo Leto | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 6.0.3-alpha, 6.0.4-alpha, 6.0.10-alpha | OS: | Any |
| Assigned to: | Alexey Kopytov | CPU Architecture: | Any |
| Tags: | 1046, ERROR 1046, function status, No database selected, unknown database name | ||
[10 Mar 2008 22:10]
Santo Leto
It seems that the issue is reproducible in version 6.0.4-alpha, too.
mysql> select version(), current_user(), database();
+---------------------------+----------------+------------+
| version() | current_user() | database() |
+---------------------------+----------------+------------+
| 6.0.4-alpha-community-log | root@localhost | NULL |
+---------------------------+----------------+------------+
1 row in set (0.00 sec)
mysql> show function status\G
ERROR 1046 (3D000): No database selected
mysql> show procedure status\G
*************************** 1. row ***************************
Db: database1
Name: test001
Type: PROCEDURE
Definer: root@localhost
Modified: 2008-03-10 22:56:05
Created: 2008-03-10 22:56:05
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> show function status\G
*************************** 1. row ***************************
Db: database1
Name: test001
Type: FUNCTION
Definer: root@localhost
Modified: 2008-03-10 22:56:38
Created: 2008-03-10 22:56:38
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.02 sec)
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.05 sec)
Please, before using the Test Script, ensure you have at least one function in your server, otherwise the bug is not reproducible (see below).
Let's establish a new connection:
mysql> drop function database1.test001;
Query OK, 0 rows affected (0.13 sec)
mysql> select version(), current_user(), database();
+---------------------------+----------------+------------+
| version() | current_user() | database() |
+---------------------------+----------------+------------+
| 6.0.4-alpha-community-log | root@localhost | NULL |
+---------------------------+----------------+------------+
1 row in set (0.05 sec)
mysql> show function status\G
Empty set (0.01 sec)
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 9 |
+-----------------+
1 row in set (0.00 sec)
[19 Mar 2008 12:30]
Susanne Ebrecht
Verified with MySQL 6.0.5-alpha-debug (newest bk tree).
To make sure this is a bug I also tested 5.1.24-rc-debug (bk tree)
and 5.0.60-debug
mysql> \u testdb
mysql> delimiter //
mysql> create function f() returns integer begin return 23; end;
-> //
mysql> delimiter ;
mysql> \q
Start mysql 6.0 CLI again:
$ mysql-6.0-bk/bin/mysql
mysql> show function status\G
ERROR 1046 (3D000): No database selected
Now test the same with 5.1:
$ mysql-5.1-bk/bin/mysql
mysql> show function status\G
*************************** 1. row ***************************
Db: testdb
Name: f
Type: FUNCTION
Definer: myuser@localhost
Modified: 2008-03-19 13:16:20
Created: 2008-03-19 13:16:20
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
5.1 and 5.0 have the same behaviour. Only 6.0 has the opinion that functions belongs to databases and won't give a result here.
Also documentation didn't say that this only should work by selecting a database. It wouldn't make sense to get the database name back when "show function status" only would work for one single database.
[5 Jan 2009 17:51]
Santo Leto
Hi, This bug is quite important when developing applications over different server's versions. Verified as described also in version 6.0.10-alpha. Furthermore, I get the same error even if I select over information schema: Script: SELECT VERSION(); SELECT DATABASE(); SELECT `SPECIFIC_NAME` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'your-schema-name-here' AND `ROUTINE_TYPE` = 'function' AND `SPECIFIC_NAME`='your-function-name-here'; Script Output: mysql> SELECT VERSION(); +--------------+ | VERSION() | +--------------+ | 6.0.10-alpha | +--------------+ 1 row in set (0.08 sec) mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | NULL | +------------+ 1 row in set (0.08 sec) mysql> SELECT `SPECIFIC_NAME` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'your-schema-name-here' AND `ROUTINE_TYPE` = 'function' AND `SPECIFIC_NAME`='your-function-name-here'; ERROR 1046 (3D000): No database selected mysql> Thanks, Santo Leto.
[5 Jan 2009 19:54]
Sergei Glukhov
duplicated with bug#38916?
[6 Jan 2009 3:04]
Santo Leto
Hi Sergey, Thanks for your help. It seems that Bug #38916 is a duplicate of this. I was not aware that #38916 was in status "Patch queued". That's good for me. I'll test the new version after pach is pushed. Regards, Santo Leto.
[1 Feb 2009 23:09]
Santo Leto
Fixed in the new version 6.0.9-alpha.
Description: SHOW FUNCTION STATUS returns a "no database selected" error in no database is selected. How to repeat: select version(), current_user(), database(); show function status\G Output 1 mysql> select version(), current_user(), database(); +---------------------------+----------------+------------+ | version() | current_user() | database() | +---------------------------+----------------+------------+ | 6.0.3-alpha-community-log | root@localhost | NULL | +---------------------------+----------------+------------+ 1 row in set (0.00 sec) mysql> show function status\G ERROR 1046 (3D000): No database selected mysql> Output 2 mysql> select version(), current_user(), database(); +-------------------------+----------------+------------+ | version() | current_user() | database() | +-------------------------+----------------+------------+ | 5.1.23-rc-community-log | root@localhost | NULL | +-------------------------+----------------+------------+ 1 row in set (0.00 sec) mysql> show function status\G *************************** 1. row *************************** Db: database1 Name: test01 Type: FUNCTION Definer: root@localhost Modified: 2008-02-16 11:51:03 Created: 2008-02-16 11:51:03 Security_type: DEFINER Comment: comment to func test01 character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.01 sec) Suggested fix: The workaround is to USE a database before executing the SHOW statement: select version(), current_user(), database(); use test; show function status\G mysql> select version(), current_user(), database(); +---------------------------+----------------+------------+ | version() | current_user() | database() | +---------------------------+----------------+------------+ | 6.0.3-alpha-community-log | root@localhost | NULL | +---------------------------+----------------+------------+ 1 row in set (0.00 sec) mysql> use test; Database changed mysql> show function status\G *************************** 1. row *************************** Db: database1 Name: func1 Type: FUNCTION Definer: root@localhost Modified: 2007-12-09 18:58:14 Created: 2007-12-09 18:58:14 Security_type: DEFINER Comment: comment to func1 character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci *************************** 2. row *************************** ... ...