On Thu, 09 Jan 2003 23:41:46 +0200
Heikki Tuuri <[EMAIL PROTECTED]> wrote:
> Rafal,
>
> I tested also with the `hotele`, and it worked ok.
>
> Please provide your my.cnf and MySQL version number. And test if you can
> repeat the error.
>
> Regards,
>
> Heikki
>
MySQL version 4.0.8, my my.cnf is as follows:
# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=64
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=8M
#set-variable = max_user_connections =2
log-bin
server-id = 1
default-character-set = latin2
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
# Uncomment the following if you are using Innobase tables
innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M:autoextend
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
and the test case:
mysql> drop table pokoje,rezerwacje,hotele;
Query OK, 0 rows affected (1.13 sec)
mysql> create table hotele (
-> nip_h int(10) not null,
-> nazwa_h varchar(100) not null,
-> miasto_h varchar(50) not null,
-> kodpocz_h int(5) not null,
-> adres_h varchar(50),
-> email_h varchar(50),
-> kontobank_h int(32),
-> wyprzedaz_h varchar(10),
-> standard_h varchar(20),
-> opis_h varchar(200),
-> primary key (nip_h)
-> )
-> type=innodb;
Query OK, 0 rows affected (0.17 sec)
mysql> create table pokoje (
-> nrpok_p char(10) not null,
-> nip_h int(10) not null,
-> lozka_p char(2),
-> tv_p char(1),
-> lazienka_p char(1),
-> cena_p int(10),
-> zaliczka_p int(10),
-> index nip_h_index (nip_h,nrpok_p),
-> foreign key (nip_h) references hotele (nip_h) on delete cascade,
-> primary key (nrpok_p, nip_h)
-> )
-> type=innodb;
Query OK, 0 rows affected (0.21 sec)
mysql> create table rezerwacje (
-> id_r int(10) not null,
-> pesel_k int(11),
-> nip_h int(10) not null,
-> nrpok_p char(10) not null,
-> data_r date,
-> od_r date,
-> do_r date,
-> cena_r int(10),
-> zaliczka_r int(5),
-> zaplac_r char(1),
-> wplaczal_r char(1),
-> index nip_h_index (nip_h,nrpok_p),
-> foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on delete
se
t null,
-> index pesel_k_index (pesel_k),
-> primary key(id_r)) type=innodb;
ERROR 1005: Can't create table './test/rezerwacje.frm' (errno: 150)
>
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 1 to server version: 4.0.8-gamma-standard-log
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> drop database test;
> Query OK, 0 rows affected (0.08 sec)
>
> mysql> create database test;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> CREATE TABLE `hotele` (
> -> `nip_h` int(10) NOT NULL default '0',
> -> `nazwa_h` varchar(100) NOT NULL default '',
> -> `miasto_h` varchar(50) NOT NULL default '',
> -> `kodpocz_h` int(5) NOT NULL default '0',
> -> `adres_h` varchar(50) default NULL,
> -> `email_h` varchar(50) default NULL,
> -> `kontobank_h` int(32) default NULL,
> -> `wyprzedaz_h` varchar(10) default NULL,
> -> `standard_h` varchar(20) default NULL,
> -> `opis_h` varchar(200) default NULL,
> -> PRIMARY KEY (`nip_h`)
> -> ) TYPE=InnoDB;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql>
> mysql> CREATE TABLE `pokoje` (
> -> `nrpok_p` char(10) NOT NULL default '',
> -> `nip_h` int(10) NOT NULL default '0',
> -> `lozka_p` char(2) default NULL,
> -> `tv_p` char(1) default NULL,
> -> `lazienka_p` char(1) default NULL,
> -> `cena_p` int(10) default NULL,
> -> `zaliczka_p` int(10) default NULL,
> -> PRIMARY KEY (`nrpok_p`,`nip_h`),
> -> FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE
> CASCADE,
> -> KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> -> ) TYPE=InnoDB;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql>
> mysql> CREATE TABLE `rezerwacje` (
> -> `id_r` int(10) NOT NULL default '0',
> -> `pesel_k` int(11) default NULL,
> -> `nip_h` int(10) NOT NULL default '0',
> -> `nrpok_p` char(10) NOT NULL default '',
> -> `data_r` date default NULL,
> -> `od_r` date default NULL,
> -> `do_r` date default NULL,
> -> `cena_r` int(10) default NULL,
> -> `zaliczka_r` int(5) default NULL,
> -> `zaplac_r` char(1) default NULL,
> -> `wplaczal_r` char(1) default NULL,
> -> PRIMARY KEY (`id_r`),
> -> KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> -> ) TYPE=InnoDB;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql>
> mysql> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> -> pokoje(nip_h,nrpok_p);
> Query OK, 0 rows affected (0.01 sec)
> Records: 0 Duplicates: 0 Warnings: 0
>
> ----- Original Message -----
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, January 09, 2003 11:04 PM
> Subject: Re: Bug in foreign keys
>
>
> > Rafal,
> >
> > ----- Original Message -----
> > From: "Rafal Jank" <[EMAIL PROTECTED]>
> > Newsgroups: mailing.database.mysql
> > Sent: Thursday, January 09, 2003 6:18 PM
> > Subject: Bug in foreign keys
> >
> >
> > > Hi!
> > > I have two tables:
> > > CREATE TABLE `pokoje` (
> > > `nrpok_p` char(10) NOT NULL default '',
> > > `nip_h` int(10) NOT NULL default '0',
> > > `lozka_p` char(2) default NULL,
> > > `tv_p` char(1) default NULL,
> > > `lazienka_p` char(1) default NULL,
> > > `cena_p` int(10) default NULL,
> > > `zaliczka_p` int(10) default NULL,
> > > PRIMARY KEY (`nrpok_p`,`nip_h`),
> > > KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> > > FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE
> > > ) TYPE=InnoDB;
> > >
> > > CREATE TABLE `rezerwacje` (
> > > `id_r` int(10) NOT NULL default '0',
> > > `pesel_k` int(11) default NULL,
> > > `nip_h` int(10) NOT NULL default '0',
> > > `nrpok_p` char(10) NOT NULL default '',
> > > `data_r` date default NULL,
> > > `od_r` date default NULL,
> > > `do_r` date default NULL,
> > > `cena_r` int(10) default NULL,
> > > `zaliczka_r` int(5) default NULL,
> > > `zaplac_r` char(1) default NULL,
> > > `wplaczal_r` char(1) default NULL,
> > > PRIMARY KEY (`id_r`),
> > > KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> > > ) TYPE=InnoDB;
> > >
> > > Now, when I try:
> > > alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> > > pokoje(nip_h,nrpok_p);
> > > I get:
> > > ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150)
> > >
> > > Why?
> >
> >
> > what is your MySQL version? What is the OS? What is the
> > default-character-set in my.cnf? What does SHOW CREATE TABLE say for every
> > table involved, also `hotel`? Can you write a script which repeats the
> > error?
> >
> > I tested a slightly more complex script in the latest MySQL-4.0 source
> tree,
> > with the latin1_de charset and it ran without errors:
> >
> >
> > CREATE TABLE `hotele` (
> > `nip_h` int(10) NOT NULL default '0',
> > `abbaguu` int(10),
> > PRIMARY KEY (`nip_h`)
> > ) TYPE = InnoDB;
> >
> > CREATE TABLE `pokoje` (
> > `nrpok_p` char(10) NOT NULL default '',
> > `nip_h` int(10) NOT NULL default '0',
> > `lozka_p` char(2) default NULL,
> > `tv_p` char(1) default NULL,
> > `lazienka_p` char(1) default NULL,
> > `cena_p` int(10) default NULL,
> > `zaliczka_p` int(10) default NULL,
> > PRIMARY KEY (`nrpok_p`,`nip_h`),
> > FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE CASCADE,
> > KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> > ) TYPE=InnoDB;
> >
> > CREATE TABLE `rezerwacje` (
> > `id_r` int(10) NOT NULL default '0',
> > `pesel_k` int(11) default NULL,
> > `nip_h` int(10) NOT NULL default '0',
> > `nrpok_p` char(10) NOT NULL default '',
> > `data_r` date default NULL,
> > `od_r` date default NULL,
> > `do_r` date default NULL,
> > `cena_r` int(10) default NULL,
> > `zaliczka_r` int(5) default NULL,
> > `zaplac_r` char(1) default NULL,
> > `wplaczal_r` char(1) default NULL,
> > PRIMARY KEY (`id_r`),
> > KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> > ) TYPE=InnoDB;
> >
> > CREATE TABLE `kukkuu` (
> > `id_r` int(10) NOT NULL default '0',
> > PRIMARY KEY (`id_r`),
> > FOREIGN KEY (`id_r`) REFERENCES `rezerwacje` (`id_r`)
> > ) TYPE=InnoDB;
> >
> > alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> > pokoje(nip_h,nrpok_p);
> >
> >
> > mysql> CREATE TABLE `hotele` (
> > -> `nip_h` int(10) NOT NULL default '0',
> > -> `abbaguu` int(10),
> > -> PRIMARY KEY (`nip_h`)
> > -> ) TYPE = InnoDB;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql>
> > mysql> CREATE TABLE `pokoje` (
> > -> `nrpok_p` char(10) NOT NULL default '',
> > -> `nip_h` int(10) NOT NULL default '0',
> > -> `lozka_p` char(2) default NULL,
> > -> `tv_p` char(1) default NULL,
> > -> `lazienka_p` char(1) default NULL,
> > -> `cena_p` int(10) default NULL,
> > -> `zaliczka_p` int(10) default NULL,
> > -> PRIMARY KEY (`nrpok_p`,`nip_h`),
> > -> FOREIGN KEY (`nip_h`) REFERENCES `hotele` (`nip_h`) ON DELETE
> > CASCADE,
> > -> KEY `nip_h_index` (`nip_h`,`nrpok_p`),
> > -> ) TYPE=InnoDB;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql>
> > mysql> CREATE TABLE `rezerwacje` (
> > -> `id_r` int(10) NOT NULL default '0',
> > -> `pesel_k` int(11) default NULL,
> > -> `nip_h` int(10) NOT NULL default '0',
> > -> `nrpok_p` char(10) NOT NULL default '',
> > -> `data_r` date default NULL,
> > -> `od_r` date default NULL,
> > -> `do_r` date default NULL,
> > -> `cena_r` int(10) default NULL,
> > -> `zaliczka_r` int(5) default NULL,
> > -> `zaplac_r` char(1) default NULL,
> > -> `wplaczal_r` char(1) default NULL,
> > -> PRIMARY KEY (`id_r`),
> > -> KEY `nip_h_index` (`nip_h`,`nrpok_p`)
> > -> ) TYPE=InnoDB;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql>
> > mysql> CREATE TABLE `kukkuu` (
> > -> `id_r` int(10) NOT NULL default '0',
> > -> PRIMARY KEY (`id_r`),
> > -> FOREIGN KEY (`id_r`) REFERENCES `rezerwacje` (`id_r`)
> > -> ) TYPE=InnoDB;
> > Query OK, 0 rows affected (0.01 sec)
> >
> > mysql>
> > mysql> alter table rezerwacje add foreign key (nip_h,nrpok_p) references
> > -> pokoje(nip_h,nrpok_p);
> > Query OK, 0 rows affected (0.01 sec)
> > Records: 0 Duplicates: 0 Warnings: 0
> >
> > mysql>
> >
> > > --
> > > _/ _/ _/_/_/ ----- Rafał Jank [EMAIL PROTECTED] -----
> > > _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl
> > > _/_/_/_/ _/_/_/ ul. Traugutta 115c, 80-237 Gdansk, tel/fax.
> > (58)5215625
> > > _/ _/ _/ --------==* http://szukaj.wp.pl *==----------
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > ---
> > Order technical MySQL/InnoDB support at https://order.mysql.com/
> > See http://www.innodb.com for the online manual and latest news on InnoDB
> >
> > sql query
> >
> >
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
--
_/ _/ _/_/_/ ----- Rafał Jank [EMAIL PROTECTED] -----
_/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl
_/_/_/_/ _/_/_/ ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625
_/ _/ _/ --------==* http://szukaj.wp.pl *==----------
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
