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.