MySQL Bugs: #28551: The warning 'No database selected' is reported when calling stored procedures

Bug #28551	The warning 'No database selected' is reported when calling stored procedures
Submitted:	21 May 2007 7:09	Modified:	22 Jul 2007 18:23
Reporter:	Asuka Kenji Siu Ching Pong (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.18-beta, 5.0 BK	OS:	Any
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	backport_050044SP1, No database selected, regression, stored procedure

Description:
This issue did not happen in 5.1.17.

In the 'How to repeat' session below, it shows that if the stored procedure is created and invoked ***WITHOUT FIRST EXECUTING A 'USE' STATEMENT***, the 'No database selected' warning will be reported. If a 'USE' statement is executed, even if the target database is different from the stored procedure's database, the warnings disappear.

This happens in both the Windows and the Linux binaries downloaded from MySQL. It seems that the 'sql_mode' system variable does not affect the result.

This behavior may be caused by a much more serious bug inside a core module. Besides, programmers (like me) may think that there are some bugs in their code (rather than in MySQL), and spend hours and hours debugging a non-existing bug in their code. So, I set the 'Severity' to 'S2'.

How to repeat:
mysql> CREATE PROCEDURE test.temp() begin end;
Query OK, 0 rows affected (0.02 sec)

mysql> CALL test.temp;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------+
| Level | Code | Message              |
+-------+------+----------------------+
| Note  | 1046 | No database selected |
| Note  | 1046 | No database selected |
+-------+------+----------------------+
2 rows in set (0.00 sec)

mysql> USE mysql;
Database changed

mysql> CALL test.temp;
Query OK, 0 rows affected (0.00 sec)

Thank you for the report.

Verified as described.

May be duplicate of bug #13616

Bug doesn't exists in versions 5.0.41 an 5.1.17

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/30340

ChangeSet@1.2514, 2007-07-05 02:20:32+04:00, kostja@bodhi.(none) +4 -0
  A fix and a teset case for Bug#28551 The warning 
  'No database selected' is reported when calling stored procedures
  
  Remove the offending warning introduced by the fix for Bug
  25082
  This minimal patch relies on the intrinsic knowledge of the fact that
  mysql_change_db is never called with 'force_switch' set to TRUE
  when such a warning may be needed:
   * every stored routine belongs to a database (unlike, e.g., a 
  user defined function, which does not), so if we're activating the
  database of a stored routine, it can never be NULL.
  Therefore, this branch is never called for activation.
   * if we're restoring the 'old' current database after routine
  execution is complete, we should not issue a warning, since it's OK to 
  call a routine without having previously selected the current database.
  
  TODO: 'force_switch' is an ambiguous flag, since we do not actually
  have to 'force' the switch in case of stored routines at all.
  When we activate the routine's database, we should perform
  all the checks as in case of 'use db', and so we already do (in this
  case 'force_switch' is unused).
  When we load a routine into cache, we should not use mysql_change_db
  at all, since there it's enough to call thd->reset_db(). We
  do it this way for triggers, but code for routines is different (wrongly). 
  
  TODO: bugs are lurking in replication, since it bypasses mysql_change_db
  and calls thd->[re_]set_db to set the current database.
  The latter does not change thd->db_charset, thd->sctx->db_access
  and thd->variables.collation_database (and this may have nasty side
  effects).
  
  These todo items are to be addressed in a separate patch, if at all.

Actually queued it into 5.0-runtime (a small patch).

Pushed into 5.0.48

Pushed into 5.1.21-beta

Noted in 5.0.48, 5.1.21 changelogs.

If a stored procedure was created and invoked prior to selecting a
default database with USE, a No database selected error occurred.

This issue might be more an issue with the UPDATE statement, than the stored procedure. I attempted to execute an UPDATE statement involving 3 tables without first executing a USE database, and I got the same error warning. However, after I executed "use mysql" then reran the statement it worked.