mgu

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),
-> *** CHAR,
-> SALARY DECIMAL(10,2),
-> SUPERSSN CHAR(9),
-> DNO INT NOT NULL DEFAULT 1,
-> PRIMARY KEY (SSN),
-> INDEX (SUPERSSN),
-> INDEX (DNO)
-> )TYPE =3D 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),
*** CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT 1,
PRIMARY KEY (SSN),
INDEX (SUPERSSN),
INDEX (DNO)
)TYPE =3D 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 |
|
| *** | 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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
030814 10:37:13 INNODB MONITOR OUTPUT
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 < 0 5408 undo n < 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


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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

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 =3D 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 =3D 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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
030814 10:44:10 INNODB MONITOR OUTPUT
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 < 0 5424 undo n < 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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
030814 10:45:56 INNODB MONITOR OUTPUT
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 < 0 5439 undo n < 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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

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:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
030814 10:48:29 INNODB MONITOR OUTPUT
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 < 0 5439 undo n < 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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

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>


=3D=3D=3D=3D=3D

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,

=3D=3D
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.
=3D=3D=20


According to my general SQL experience it is all correctly=20
ANSI SQL-92 coded.

Please help me,

What else can be done ?

Yours Sincerely

Morten Gulbrandsen


-----Urspr=FCngliche Nachricht-----
Von: Fred van Engen [mailto:fred.van.engen@xbn.nl]=20
Gesendet: Mittwoch, 13. August 2003 15:36
An: Morten Gulbrandsen
Cc: 'Victoria Reznichenko'; mysql@lists.mysql.com
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:

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.

--=20
Fred van Engen XB Networks B.V.
email: fred.van.engen@xbn.nl Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dmgu@owi-aachen.de


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmysql@freebsd.csie.nctu.edu.tw