mysql> USE company;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS EMPLOYEE;
--------------
DROP TABLE IF EXISTS EMPLOYEE
--------------
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 DEFAULT 1,
-> PRIMARY KEY (SSN),
-> INDEX (SUPERSSN),
-> INDEX (DNO)
-> )TYPE = INNODB;
--------------
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 DEFAULT 1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE = INNODB
--------------
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DESCRIBE EMPLOYEE;
--------------
DESCRIBE EMPLOYEE
--------------
+----------+---------------+-------------------+------+-----+---------+-
------+
| Field | Type | Collation | Null | Key | Default |
Extra |
+----------+---------------+-------------------+------+-----+---------+-
------+
| FNAME | varchar(15) | latin1_swedish_ci | | | |
|
| MINIT | char(1) | latin1_swedish_ci | YES | | NULL |
|
| LNAME | varchar(15) | latin1_swedish_ci | | | |
|
| SSN | varchar(9) | latin1_swedish_ci | | PRI | |
|
| BDATE | date | latin1_swedish_ci | YES | | NULL |
|
| ADDRESS | varchar(30) | latin1_swedish_ci | YES | | NULL |
|
| SEX | char(1) | latin1_swedish_ci | YES | | NULL |
|
| SALARY | decimal(10,2) | binary | YES | | NULL |
|
| SUPERSSN | varchar(9) | latin1_swedish_ci | YES | MUL | NULL |
|
| DNO | int(11) | binary | | MUL | 1 |
|
+----------+---------------+-------------------+------+-----+---------+-
------+
10 rows in set (0.00 sec)
mysql> SHOW INNODB STATUS \G
--------------
SHOW INNODB STATUS
--------------
*************************** 1. row ***************************
Status:
=====================================
030814 10:37:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 32, signal count 32
Mutex spin waits 10, rounds 180, OS waits 1
RW-shared spins 60, OS waits 30; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 5422
Purge done for trx's n:o < 0 5408 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 5415, not started, OS thread id 2072
MySQL thread id 8, query id 1088 localhost 127.0.0.1 root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio
I/O thread 1 state: wait Windows aio
I/O thread 2 state: wait Windows aio
I/O thread 3 state: wait Windows aio
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
62 OS file reads, 759 OS file writes, 245 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.04 writes/s, 0.04 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.87 non-hash searches/s
---
LOG
---
Log sequence number 0 880300
Log flushed up to 0 880300
Last checkpoint at 0 873305
0 pending log writes, 0 pending chkp writes
136 log i/o's done, 0.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13601916; in additional pool allocated 232832
Buffer pool size 512
Free buffers 480
Database pages 31
Modified db pages 22
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 28, created 3, written 584
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1408, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS DEPARTMENT;
--------------
DROP TABLE IF EXISTS DEPARTMENT
--------------
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE DEPARTMENT
-> (
-> DNAME VARCHAR(15) NOT NULL,
-> DNUMBER INT NOT NULL,
-> MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
-> MGRSTARTDATE DATE,
-> PRIMARY KEY (DNUMBER),
-> UNIQUE (DNAME),
-> INDEX (MGRSSN)
-> )TYPE = INNODB;
--------------
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
INDEX (MGRSSN)
)TYPE = INNODB
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> DESCRIBE DEPARTMENT;
--------------
DESCRIBE DEPARTMENT
--------------
+--------------+-------------+-------------------+------+-----+---------
--+-----
--+
| Field | Type | Collation | Null | Key | Default
| Extr
a |
+--------------+-------------+-------------------+------+-----+---------
--+-----
--+
| DNAME | varchar(15) | latin1_swedish_ci | | UNI |
|
|
| DNUMBER | int(11) | binary | | PRI | 0
|
|
| MGRSSN | varchar(9) | latin1_swedish_ci | | MUL |
888665555 |
|
| MGRSTARTDATE | date | latin1_swedish_ci | YES | | NULL
|
|
+--------------+-------------+-------------------+------+-----+---------
--+-----
--+
4 rows in set (0.00 sec)
mysql> SHOW INNODB STATUS \G
--------------
SHOW INNODB STATUS
--------------
*************************** 1. row ***************************
Status:
=====================================
030814 10:44:10 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 37, signal count 37
Mutex spin waits 15, rounds 280, OS waits 1
RW-shared spins 70, OS waits 35; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 5427
Purge done for trx's n:o < 0 5424 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 5415, not started, OS thread id 2072
MySQL thread id 8, query id 1134 localhost 127.0.0.1 root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio
I/O thread 1 state: wait Windows aio
I/O thread 2 state: wait Windows aio
I/O thread 3 state: wait Windows aio
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
62 OS file reads, 833 OS file writes, 271 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 886171
Log flushed up to 0 886171
Last checkpoint at 0 886171
0 pending log writes, 0 pending chkp writes
145 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13601916; in additional pool allocated 230528
Buffer pool size 512
Free buffers 480
Database pages 31
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 28, created 3, written 643
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool activity since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1408, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> ALTER TABLE EMPLOYEE
-> ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE;
--------------
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
ON UPDATE CASCADE
--------------
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INNODB STATUS \G
--------------
SHOW INNODB STATUS
--------------
*************************** 1. row ***************************
Status:
=====================================
030814 10:45:56 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 38, signal count 38
Mutex spin waits 15, rounds 280, OS waits 1
RW-shared spins 72, OS waits 36; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 5442
Purge done for trx's n:o < 0 5439 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 5441, not started, OS thread id 2072
MySQL thread id 8, query id 1147 localhost 127.0.0.1 root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio
I/O thread 1 state: wait Windows aio
I/O thread 2 state: wait Windows aio
I/O thread 3 state: wait Windows aio
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
62 OS file reads, 869 OS file writes, 282 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.80 writes/s, 0.24 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 2.18 non-hash searches/s
---
LOG
---
Log sequence number 0 894279
Log flushed up to 0 894279
Last checkpoint at 0 894279
0 pending log writes, 0 pending chkp writes
151 log i/o's done, 0.13 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13601916; in additional pool allocated 231296
Buffer pool size 512
Free buffers 480
Database pages 31
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 28, created 3, written 671
0.00 reads/s, 0.00 creates/s, 0.62 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1408, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> ALTER TABLE EMPLOYEE
-> ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
-> ON DELETE SET DEFAULT
-> ON UPDATE CASCADE;
--------------
ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT
ON UPDATE CASCADE
--------------
ERROR 1005: Can't create table '.\company\#sql-40c_8.frm' (errno: 150)
mysql> SHOW INNODB STATUS \G
--------------
SHOW INNODB STATUS
--------------
*************************** 1. row ***************************
Status:
=====================================
030814 10:48:29 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 30 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 39, signal count 39
Mutex spin waits 15, rounds 280, OS waits 1
RW-shared spins 74, OS waits 37; RW-excl spins 1, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 5447
Purge done for trx's n:o < 0 5439 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 5446, not started, OS thread id 2072
MySQL thread id 8, query id 1164 localhost 127.0.0.1 root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio
I/O thread 1 state: wait Windows aio
I/O thread 2 state: wait Windows aio
I/O thread 3 state: wait Windows aio
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
62 OS file reads, 885 OS file writes, 287 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 899726
Log flushed up to 0 899726
Last checkpoint at 0 899726
0 pending log writes, 0 pending chkp writes
153 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13601916; in additional pool allocated 233600
Buffer pool size 512
Free buffers 480
Database pages 31
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 28, created 3, written 684
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool activity since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 1408, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS FROM company LIKE "EMPLOYEE" \G
--------------
SHOW TABLE STATUS FROM company LIKE "EMPLOYEE"
--------------
*************************** 1. row ***************************
Name: employee
Type: InnoDB
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: NULL
Index_length: 32768
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Charset: latin1_swedish_ci
Create_options:
Comment: InnoDB free: 3072 kB; (SUPERSSN) REFER
company/employee(SSN) ON
UPDATE CASCADE
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS FROM company LIKE "DEPARTMENT" \G
--------------
SHOW TABLE STATUS FROM company LIKE "DEPARTMENT"
--------------
*************************** 1. row ***************************
Name: department
Type: InnoDB
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: NULL
Index_length: 32768
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Charset: latin1_swedish_ci
Create_options:
Comment: InnoDB free: 3072 kB
1 row in set (0.00 sec)
mysql>
=====
Hi again,
I have just tried to investigate and record what InnoDB status has
noticed,
According to the MySQL reference manual,
A foreign key constraint is in My Code erroneously coded,
==
If MySQL gives the error number 1005 from a CREATE TABLE
statement, and the error message string refers to errno 150, then
the table creation failed because a foreign key constraint was not
correctly formed. Similarly, if an ALTER TABLE fails and it refers
to errno 150, that means a foreign key definition would be incorrectly
formed for the altered table. Starting from version 4.0.13, you can
use SHOW INNODB STATUS to look at a detailed explanation of the
latest InnoDB foreign key error in the server.
==
According to my general SQL experience it is all correctly
ANSI SQL-92 coded.
Please help me,
What else can be done ?
Yours Sincerely
Morten Gulbrandsen
-----Ursprüngliche Nachricht-----
Von: Fred van Engen [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 13. August 2003 15:36
An: Morten Gulbrandsen
Cc: 'Victoria Reznichenko'; [EMAIL PROTECTED]
Betreff: Re: mutual declarations produce Error 1064
Morten,
In your extremely long mail, I think I managed to find your question and
removed all other stuff.
On Wed, Aug 13, 2003 at 03:18:26PM +0200, Morten Gulbrandsen wrote:
> mysql>
> mysql> # from now on the coding causes trouble
> mysql>
> mysql> ALTER TABLE EMPLOYEE
> -> FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
> -> ON DELETE SET NULL
> -> ON UPDATE CASCADE;
> ERROR 1064: You have an error in your SQL syntax. Check the manual
that
> corresp
> onds to your MySQL server version for the right syntax to use near
> 'FOREIGN KEY
> (SUPERSSN) REFERENCES EMPLOYEE(SSN)
> ON DELETE SET
> mysql>
6.5.4 ALTER TABLE Syntax
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification
...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
| ADD [COLUMN] (create_definition, create_definition,...)
| ADD INDEX [index_name] (index_col_name,...)
| ADD PRIMARY KEY (index_col_name,...)
| ADD UNIQUE [index_name] (index_col_name,...)
| ADD FULLTEXT [index_name] (index_col_name,...)
| ADD [CONSTRAINT symbol] FOREIGN KEY [index_name]
(index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
| MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col
| table_options
So what it clearly tells you, is to use this (note the 'ADD'):
mysql> ALTER TABLE EMPLOYEE
-> ADD FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
-> ON DELETE SET NULL
-> ON UPDATE CASCADE;
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
