I think something is wrong with my administration,
Basically I get these messages,
ERROR 1005 at line 9: Can't create table '.\company\depa rtment.frm'
(errno: 150)
ERROR 1045: Access denied for user: 'e******@127.0. 0.1' (Using password:
YES)
ERROR 1133: Can't find any matching row in the user table
C:\mysql\exampl es\elmasri>mysq l -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 =3D password('navat he') where user =3D
'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\exampl es\elmasri>mysq l -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\exampl es\elmasri>mysq l -u elmasri -pnavathe company <
company_01.sql
ERROR 1005 at line 9: Can't create table '.\company\depa rtment.frm'
(errno: 150)
C:\mysql\exampl es\elmasri>
C:\mysql\exampl es\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 =3D INNODB;
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(DNUM BER) # this is declared in department
)TYPE =3D INNODB;
C:\mysql\exampl es\elmasri>mysq l -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'@'loca lhost' identified by
'navathe';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
C:\mysql\exampl es\elmasri>mysq l -u elmasri -pnavathe company;
ERROR 1045: Access denied for user: 'e******@127.0. 0.1' (Using password:
YES)
C:\mysql\exampl es\elmasri>mysq l -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'@'loca lhost' =3D password('navat he');
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'elmasri'@'%' =3D password('navat he');
ERROR 1133: Can't find any matching row in the user table
mysql>
=3D=3D=3D
what can we do now ?
I try again with=20
Manually
mysql> create table employee( fname varchar(15) )type=3Dinnodb;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS EMPLOYEE;
Query OK, 0 rows affected (0.00 sec)
And=20
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(DNUM BER) # this is declared in department
-> )TYPE =3D INNODB;
ERROR 1072: Key column 'DNUMBER' doesn't exist in table
mysql> create table employee( fname varchar(15) )type=3Dinnodb;
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(DNUM BER) # this is declared in department
-> )TYPE =3D INNODB;
ERROR 1005: Can't create table '.\company\empl oyee.frm' (errno: 150)
mysql> exit
Bye
C:\mysql\exampl es\elmasri>mysq l -u elmasri -pnavathe company <
company_01.sql
ERROR 1005 at line 9: Can't create table '.\company\depa rtment.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=20
I expect for each database I'd like to use, one second password,
This seems to be another topic,
When I write=20
mysql> grant all on company.* to 'elmasri'@'loca lhost' 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 ?
Yours sincerely
Morten Gulbrandsen
-----Urspr=FCngliche Nachricht-----
Von: Victoria Reznichenko [mailto:vi****** ************@en sita.net]=20
Gesendet: Donnerstag, 7. August 2003 13:54
An: my***@lists.mys ql.com
Betreff: Re: mutual declarations produce Error 1064
"Morten Gulbrandsen" <mg*@owi-aachen.de> wrote:
USE company;
=20
# SHOW INNODB STATUS;
=20
DROP TABLE IF EXISTS DEPARTMENT;
=20
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,
=20
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
=20
INDEX (MGRSSN), # between employee and department
FOREIGN KEY (MGRSSN), # a sort of mutually declaration=20
REFERENCES EMPLOYEE(SSN) # this is declared in employee
Remove comma before REFERENCES.
--=20
For technical support contracts, goto
https://order.mysql.com/?ref=3Densita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ Vi************* *****@ensita.ne t
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dmg*@owi-aachen.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw
