Bug #2301 use [database] doesn't seem to work all the time
Submitted: 7 Jan 2004 1:18 Modified: 7 Jan 2004 1:26
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:4.1.1-alpha OS:Windows (Windows 2000 pro)
Assigned to: CPU Architecture:Any

[7 Jan 2004 1:18] [ name withheld ]
Description:
I have the following batch file to create my database.  It's for a simple e-commerce application.  The basic idea is to drop the previous database, create a new database, then create the tables and grant the appropriate permissions to the appropriate user.  

The problem is that if I remove the [dbname] from the CREATE TABLE [dbname].[table name] statements, then source this file after logging into the server from the command line (everything on windows), mysql outputs a bunch of messages about no database selected.  This also happens if I just type use [dbname] on the mysql command prompt.  This used to work in 4.0.15.

DROP DATABASE IF EXISTS [dbname];
CREATE DATABASE [dbname];

use [dbname];

# stores user data
CREATE TABLE [dbname].Users (
userId    INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
firstName VARCHAR(30) NOT NULL,
lastName  VARCHAR(30) NOT NULL,
email     VARCHAR(30) NOT NULL UNIQUE,
passwd    VARCHAR(12) NOT NULL,
joinDate  DATE NOT NULL
);

[grant statement]

CREATE TABLE [dbname].UserAddresses (
userAddrId   INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
userId       INT NOT NULL REFERENCES Users,
addrId       INT NOT NULL REFERENCES Addresses,
);

[grant statement]

# stores categories.  This uses the nested set method of storing trees as described by Joe Celko.
#  See http://www.dbmsmag.com/9603d06.html, http://www.dbmsmag.com/9604d06.html, 
#  and http://www.dbmsmag.com/9605d06.html.  The basic idea is that each category
#  has a "left" and "right" value that are boundaries around other (sub) categories.  Sub-
#  categories are those with smaller left and right values than the current category.  This
#  offers many advantages in recursive operations.
CREATE TABLE [dbname].Categories (
catId     INT NOT NULL PRIMARY KEY UNIQUE AUTO_INCREMENT,
catName   VARCHAR(36) NOT NULL,
lft       INT NOT NULL DEFAULT '0',
rgt       INT NOT NULL DEFAULT '0'
);

[more tables]

#  Add data to the categories table
[a bunch of insert statements into the categories table]

How to repeat:
Start up a 4.1.1a-alpha server on a windows 2000 machine.  On the same machine, start a command-line mysql client using an account appropriate to create new databases.

Source the above file (some names have to be changed and some lines commented out).

Type show tables;
You will get a list of tables.  Now source the file again.  Type show tables;  

You will get:
mysql> show tables;
ERROR 1046 (3D000): No Database Selected

If you try to use the "use" command on database [dbname], you will get:
mysql> use [dbname];
Database changed
mysql> show tables;
ERROR 1046 (3D000): No Database Selected

If you do a use mysql; then a use [dbname]; then show tables; you will get a list of tables.
[7 Jan 2004 1:26] Georg Richter
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see #2205