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: 'e******@127.0.0.1' (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 =3D password('navathe') 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\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 =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(DNUMBER) # this is declared in department
)TYPE =3D 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: 'e******@127.0.0.1' (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' =3D password('navathe');
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'elmasri'@'%' =3D password('navathe');
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(DNUMBER) # 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(DNUMBER) # this is declared in department
-> )TYPE =3D INNODB;
ERROR 1005: Can't create table '.\company\employee.frm' (errno: 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=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'@'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 ?
Yours sincerely
Morten Gulbrandsen
-----Urspr=FCngliche Nachricht-----
Von: Victoria Reznichenko [mailto:vi******************@ensita.net]=20
Gesendet: Donnerstag, 7. August 2003 13:54
An: my***@lists.mysql.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.net
/_/ /_/\_, /___/\___\_\___/ 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
