"Morten Gulbrandsen" <[EMAIL PROTECTED]> wrote:
> I think something is wrong with my administration,
>
> Basically I get these messages,
>
> ERROR 1005 at line 9: Can't create table '.\company\department.frm'
> (errno: 150)
>
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> YES)
>
> ERROR 1133: Can't find any matching row in the user table
>
> C:\mysql\examples\elmasri>mysql -u root -p*******
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 16 to server version: 4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> use mysql;
> Database changed
> mysql> update user set password = password('navathe') where user =
> 'elmasri';
> Query OK, 1 row affected (0.02 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> flush privileges;
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> quit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 17 to server version: 4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> quit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company <
> company_01.sql
> ERROR 1005 at line 9: Can't create table '.\company\department.frm'
> (errno: 150)
>
>
> C:\mysql\examples\elmasri>
> C:\mysql\examples\elmasri>type company_01.sql
> # mysql -u root -h localhost -p -vvv < company_01.sql
>
> USE company;
>
> # SHOW INNODB STATUS;
>
> DROP TABLE IF EXISTS DEPARTMENT;
>
> CREATE TABLE DEPARTMENT
> (
> DNAME VARCHAR(15) NOT NULL,
> DNUMBER INT NOT NULL,
> MGRSSN CHAR(9) NOT NULL,
> MGRSTARTDATE DATE,
>
> PRIMARY KEY (DNUMBER),
> UNIQUE (DNAME),
>
> INDEX (MGRSSN), # between employee and department
> FOREIGN KEY (MGRSSN) # a sort of mutually declaration
> REFERENCES EMPLOYEE(SSN) # this is declared in employee
>
> )TYPE = INNODB;
You create table DEPARTMENT with foreign key, which is referenced to the non-existent
table EMPLOYEE. So, you need to create both table without foreign keys and then add
foreign key constraints or execute SET FOREIGN_KEY_CHECKS=0 before table creation.
>
>
> DROP TABLE IF EXISTS EMPLOYEE;
>
> CREATE TABLE EMPLOYEE
> (
> FNAME VARCHAR(15) NOT NULL,
> MINIT CHAR,
> LNAME VARCHAR(15) NOT NULL,
> SSN CHAR(9) NOT NULL,
> BDATE DATE,
> ADDRESS VARCHAR(30),
> SEX CHAR,
> SALARY DECIMAL(10,2),
> SUPERSSN CHAR(9),
> DNO INT NOT NULL,
>
> PRIMARY KEY (SSN),
> INDEX (SUPERSSN, SSN),
>
> FOREIGN KEY (SUPERSSN)
> REFERENCES EMPLOYEE(SSN),
>
> INDEX (DNO, DNUMBER),
> FOREIGN KEY (DNO)
> REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
> )TYPE = INNODB;
>
>
> C:\mysql\examples\elmasri>mysql -u root -p*******
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 19 to server version: 4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> grant all on company.* to 'elmasri'@'localhost' identified by
> 'navathe';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> quit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company;
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> YES)
>
> C:\mysql\examples\elmasri>mysql -u root -p*******
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 21 to server version: 4.1.0-alpha-max-debug
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> set password for 'elmasri'@'localhost' = password('navathe');
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> set password for 'elmasri'@'%' = password('navathe');
> ERROR 1133: Can't find any matching row in the user table
> mysql>
It means the there is no any entry 'elmasri'@'%' in the table user.
> I try again with
> Manually
>
> mysql> create table employee( fname varchar(15) )type=innodb;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> DROP TABLE IF EXISTS EMPLOYEE;
> Query OK, 0 rows affected (0.00 sec)
>
> And
>
>
> mysql> CREATE TABLE EMPLOYEE
> -> (
> -> FNAME VARCHAR(15) NOT NULL,
> -> MINIT CHAR,
> -> LNAME VARCHAR(15) NOT NULL,
> -> SSN CHAR(9) NOT NULL,
> -> BDATE DATE,
> -> ADDRESS VARCHAR(30),
> -> SEX CHAR,
> -> SALARY DECIMAL(10,2),
> -> SUPERSSN CHAR(9),
> -> DNO INT NOT NULL,
> ->
> -> PRIMARY KEY (SSN),
> -> INDEX (SUPERSSN, SSN),
> ->
> -> FOREIGN KEY (SUPERSSN)
> -> REFERENCES EMPLOYEE(SSN),
> ->
> -> INDEX (DNO, DNUMBER),
> -> FOREIGN KEY (DNO)
> -> REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
> -> )TYPE = INNODB;
> ERROR 1072: Key column 'DNUMBER' doesn't exist in table
I don't see column DNUMBER in the above create table definition.
> mysql> create table employee( fname varchar(15) )type=innodb;
> Query OK, 0 rows affected (0.00 sec)
>
>
>
> mysql>
> mysql> CREATE TABLE EMPLOYEE
> -> (
> -> FNAME VARCHAR(15) NOT NULL,
> -> MINIT CHAR,
> -> LNAME VARCHAR(15) NOT NULL,
> -> SSN CHAR(9) NOT NULL,
> -> BDATE DATE,
> -> ADDRESS VARCHAR(30),
> -> SEX CHAR,
> -> SALARY DECIMAL(10,2),
> -> SUPERSSN CHAR(9),
> -> DNO INT NOT NULL,
> ->
> -> PRIMARY KEY (SSN),
> -> INDEX (SUPERSSN, SSN),
> ->
> -> FOREIGN KEY (SUPERSSN)
> -> REFERENCES EMPLOYEE(SSN),
> ->
> -> INDEX (DNO),
> -> FOREIGN KEY (DNO)
> -> REFERENCES DEPARTMENT(DNUMBER) # this is declared in department
> -> )TYPE = INNODB;
> ERROR 1005: Can't create table '.\company\employee.frm' (errnoo: 150)
> mysql> exit
> Bye
>
> C:\mysql\examples\elmasri>mysql -u elmasri -pnavathe company <
> company_01.sql
> ERROR 1005 at line 9: Can't create table '.\company\department.frm'
> (errno: 150)
>
> This shows that I actually can create a table,
>
> My question is now,
>
> Is this code ansi sql correct ?
>
> How can I Modify it to run under the latest MySQL ?
>
> Is it something wrong with the users I have created ?
>
> I expect one user and one password for entering mysql,
> And depending upon which database I'd like to use
> I expect for each database I'd like to use, one second password,
>
> This seems to be another topic,
> When I write
>
> mysql> grant all on company.* to 'elmasri'@'localhost' identified by
> 'navathe';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> grant all on company.* to 'elmasri'@'%' identified by 'navathe';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql>
>
> Then I should be able to do whatever SQL permits ?
User 'elmasri' has all privileges on the database 'company'.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
