Bug #29050 Creation of a legal stored procedure fails if a database is not selected prior
Submitted: 12 Jun 2007 13:18 Modified: 18 Jul 2007 19:08
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.42 OS:Any
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: backport_050044SP1, bfsm_2007_06_21, bfsm_2007_06_28, No database selected, regression

[12 Jun 2007 13:18] Harrison Fisk
Description:
When you create a stored procedure using an unqualified table name inside of the body without first selecting any database, then you get an error, No database selected.  If you then 'use' any database, you will no longer get that error.

This might be related to bug #28551, but it has different symptoms and possibly causes.

This behavior was introduced in 5.0.23.  Prior versions allow it and it functions correctly.

How to repeat:
-- do not select a database, just login to mysql

CREATE TABLE test.tbl (id int);

delimiter ||

-- fails with error

CREATE PROCEDURE test.p1 () BEGIN SELECT * FROM tbl; END||

-- works if you use *any* database
-- 'tbl' is still referred to the 'test' database

use mysql

CREATE PROCEDURE test.p1() BEGIN SELECT * FROM tbl; END||

delimiter ;

Suggested fix:
Allow the statement to be executed without having to choose a database first.
[29 Jun 2007 15:20] Marc Alff
See related Bug#28318
[4 Jul 2007 23: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/30341

ChangeSet@1.2515, 2007-07-05 03:18:23+04:00, kostja@bodhi.(none) +6 -0
  A fix and a test case for Bug#29050 Creation of a legal stored procedure
  fails if a database is not selected prior.
  
  The problem manifested itself when a user tried to
  create a routine that had non-fully-qualified identifiers in its bodies
  and there was no current database selected.
  
  This is a regression introduced by the fix for Bug 19022:
  
  The patch for Bug 19022 changes the code to always produce a warning
  if we can't resolve the current database in the parser. 
  In this case this was not necessary, since even though the produced
  parsed tree was incorrect, we never re-use sphead
  that was obtained at first parsing of CREATE PROCEDURE.
  The sphead that is anyhow used is always obtained through db_load_routine,
  and there we change the current database to sphead->m_db before
  calling yyparse.
  
  The idea of the fix is to resolve the current database directly using 
  lex->sphead->m_db member when parsing a stored routine body, when
  such is present.
  
  This patch removes the need to reset the current database
  when loading a trigger or routine definition into SP cache.
  The redundant code will be removed in 5.1.
[5 Jul 2007 7:34] 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/30349

ChangeSet@1.2515, 2007-07-05 11:34:04+04:00, kostja@bodhi.(none) +8 -0
  A fix and a test case for Bug#29050 Creation of a legal stored procedure
  fails if a database is not selected prior.
  
  The problem manifested itself when a user tried to
  create a routine that had non-fully-qualified identifiers in its bodies
  and there was no current database selected.
  
  This is a regression introduced by the fix for Bug 19022:
  
  The patch for Bug 19022 changes the code to always produce a warning
  if we can't resolve the current database in the parser. 
  In this case this was not necessary, since even though the produced
  parsed tree was incorrect, we never re-use sphead
  that was obtained at first parsing of CREATE PROCEDURE.
  The sphead that is anyhow used is always obtained through db_load_routine,
  and there we change the current database to sphead->m_db before
  calling yyparse.
  
  The idea of the fix is to resolve the current database directly using 
  lex->sphead->m_db member when parsing a stored routine body, when
  such is present.
  
  This patch removes the need to reset the current database
  when loading a trigger or routine definition into SP cache.
  The redundant code will be removed in 5.1.
[12 Jul 2007 18:29] Konstantin Osipov
Actually queued it into 5.0 (a small patch).
[16 Jul 2007 19: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/30985

ChangeSet@1.2522, 2007-07-16 23:37:02+04:00, kostja@bodhi.(none) +1 -0
  Bug#29050 Creation of a legal stored procedure fails if a database is not 
  selected prior: ensure the fix also works for information_schema
  tables.
[17 Jul 2007 15:29] Bugs System
Pushed into 5.0.48
[17 Jul 2007 15:30] Bugs System
Pushed into 5.1.21-beta
[18 Jul 2007 19:08] Paul Dubois
Noted in 5.0.48, 5.1.21 changelog.

Creation of a legal stored procedure could fail if no default database 
had been selected.
[7 Aug 2007 6:56] 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/32178

ChangeSet@1.2490, 2007-08-07 00:54:47-06:00, tsmith@ramayana.hindu.god +8 -0
  Backport fix for bug #29050, Creation of a legal stored procedure fails if a database is not selected prior