Tomasz, are you using a non-latin1 character set?
The bug fixed in 3.23.49 " MySQL/InnoDB-3.23.49, February 17, 2002 * Fixed a bug: if one defined a non-latin1 character set as the default character set, then definition of foreign key constraints could fail in an assertion failure in dict0crea.c, reporting an internal error 17. " might be the root of your problem with 4.0.1. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -----Original Message----- From: Heikki Tuuri <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, March 14, 2002 8:57 AM Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ < 4.0.1? >Tomasz, > >are you sure you are running 4.0.1? In the rpm of 4.0.0 there were no >foreign keys. > >I tested this on mysql-max-4.0.1, and it worked. > >Best regards, > >Heikki Tuuri >Innobase Oy >--- >Order technical MySQL/InnoDB support at https://order.mysql.com/ >Speed up adding of features to MySQL/InnoDB through support contracts >See http://www.innodb.com for the online manual and latest news on InnoDB > >... > >heikki@hundin:~/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bin> mysql test >Welcome to the MySQL monitor. Commands end with ; or \g. >Your MySQL connection id is 1 to server version: 4.0.1-alpha-max-log > >Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > >mysql> CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; >Query OK, 0 rows affected (0.10 sec) > >mysql> CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), > -> FOREIGN KEY (parent_id) REFERENCES parent(id)) >TYPE=INN >ODB; >Query OK, 0 rows affected (0.00 sec) > >mysql> show table status from test; >+--------+--------+------------+------+----------------+-------------+----- - >---- >-------+--------------+-----------+----------------+-------------+--------- - >---+ >------------+----------------+--------------------------------------------- - >---- >---------+ >| Name | Type | Row_format | Rows | Avg_row_length | Data_length | >Max_data_ >length | Index_length | Data_free | Auto_increment | Create_time | >Update_time | > Check_time | Create_options | Comment > | >+--------+--------+------------+------+----------------+-------------+----- - >---- >-------+--------------+-----------+----------------+-------------+--------- - >---+ >------------+----------------+--------------------------------------------- - >---- >---------+ >| child | InnoDB | Fixed | 0 | 0 | 16384 | > NULL | 16384 | 0 | NULL | NULL | NULL >| > NULL | | InnoDB free: 700416 kB; (parent_id) REFER >test/pa >rent(id) | >| parent | InnoDB | Fixed | 0 | 0 | 16384 | > NULL | 0 | 0 | NULL | NULL | NULL >| > NULL | | InnoDB free: 700416 kB > | >+--------+--------+------------+------+----------------+-------------+----- - >---- >-------+--------------+-----------+----------------+-------------+--------- - >---+ >------------+----------------+--------------------------------------------- - >---- >---------+ >2 rows in set (0.03 sec) > >mysql> >-----Original Message----- >From: Tomasz Korycki <[EMAIL PROTECTED]> >Newsgroups: mailing.database.mysql >Date: Thursday, March 14, 2002 6:53 AM >Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ < 4.0.1? > > >>At 16:57 2002-03-13, Rick Flower wrote: >>>Tomasz writes: >>> >>>>> >From section 16 of http://www.innodb.com/ibman.html you find detailed >>>>>information about every InnoDB version. For example, 4.0.1 == 3.23.47. >>>>> >>>>>Foreign keys should work in 4.0.1. >>> >>>> Hmmm... That's what I read, too. And after several unsuccesful >>>attempts >>>>to create my own tables, I did those contained on Your site, verbatim (as >>>I >>>>put in my original message). Still, no effect. I guess the question then >>>>becomes: is 4.0.1 really able to keep track of constraints but unable to >>>>show them? In which case, how can one find out what they are (if extant)? >>> >>>Are you sure that you've got a MySQL-Max server, or at least one built >with >>>InnoDB support enabled? If you didn't, you might not get an InnoDB table >>>even if you asked for one.. Unfortunately, the SQL parser is somewhat >stupid >>>and doesn't bother telling you that you did something dumb or that doesn't >>>make sense in regards to how the server was built.. I've run into things >>>like that numerous times.. >> >>SHOW TABLE STATUS sez it's InnoDB... >> >>>As for listing out the foreign key constraints, that only works if you >issue >>>a "show table status;" for MySQL 3.23.4x, and you will get something like >>>the following "REFER" statement : >> >>OK, which version, exactly, do You get following output from? Oh, never >>mind, I see it's .47. Which according to Heikki Tuuri of InnoDB is the same >>as mine... >> >>>| ITEM | InnoDB | Dynamic | 0 | 0 | >>>16384 | NULL | 0 | 0 | NULL | NULL >>>| NULL | NULL | | InnoDB free: 4901888 >kB | >>>| ITEM_DEF | InnoDB | Dynamic | 0 | 0 | >>>16384 | NULL | 16384 | 0 | NULL | NULL >>>| NULL | NULL | | InnoDB free: 4901888 >>>kB; (ITEM_NAME) REFER dbname/ITEM(ITEM_NAME) >> >>Because mine stops after "InnoDB free: <whatever> kB" >> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> snip! <<<<<<<<<<<<<<<<<<<<<<<< >>>Hopefully this might shed some light on your problem.. >> >>No, it didn't. It turns out we have (according to InnoDB) the same version >>of the DB, yet mine behaves differently than mine. Oh, well. Just so You >>needn't fish out beginning of this thread, mine is 4.0.1. I don't quite >>know what to think at this point... >> >>> Below are the samples from above that you can feed directly into MySQL >>> and see what it produces.. These work fine on our installation of 3.23.47 >>> -- with InnoDB support enabled of course.. >> >>Thanks for the statements below. Unfortunately, after I try them (word for >>word, I want to eliminate possibility of my error), SHOW TABLE STATUS still >>ends right after "InnoDB free:<>" comment. >> >>>create table if not exists ITEM >>>( >>> ITEM_NAME varchar(64) not null, >>> DESCRIPTION varchar(255), >>> primary key (ITEM_NAME) >>>) TYPE=INNODB; >>>create table if not exists ITEM_DEF >>>( >>> ITEMDEF_ID int not null, >>> ITEM_NAME varchar(64), >>> primary key (ITEMDEF_ID), >>> INDEX FK_ITEM_NAME_INDEX(ITEM_NAME), >>> FOREIGN KEY (ITEM_NAME) REFERENCES ITEM(ITEM_NAME) >>>) TYPE=INNODB; >>> >>>-- Rick >> >>Thanks! So, what's next? >>-------------------------------------------------- >>sql, query >>Tomasz Korycki [EMAIL PROTECTED] >> >> >>--------------------------------------------------------------------- >>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 >> > --------------------------------------------------------------------- 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
