Michael,
----- Original Message -----
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, December 24, 2003 1:45 AM
Subject: Foreign Keys in CREATE TABLEs produced by mysqldump
> --=_alternative 008277CD88256E05_=
> Content-Type: text/plain; charset="US-ASCII"
>
> First let me state that this is not a question where a valid answer is to
> SET FOREIGN_KEY_CHECKS=0... (no, its not *that* question..)
>
> I have noticed that mysqldump includes the database name in foreign key
> specifications within CREATE TABLEs. This is causing a bit of grief as I
> would like to reimport such a dumped data set with a *different* database
> name (multiple instances of a data set being created for development, qa,
> and ua purposes..) Is there any way to modify this behaviour? I would
> prefer not to have to modify a mysqldump'ed file to have it reimported (at
> all!)
this is the question where the valid answer is: upgrade!
InnoDB no longer prints the database name in SHOW CREATE TABLE:
[EMAIL PROTECTED]:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.17-debug
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.08 sec)
mysql> CREATE TABLE child(id INT, parent_id INT,
-> INDEX par_ind (parent_id),
-> FOREIGN KEY (parent_id)
-> REFERENCES parent(id)
-> ON DELETE CASCADE) TYPE=INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table child;
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
--------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
--------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `0_15` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON
DELE
TE CASCADE
) TYPE=InnoDB |
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
--------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql>
> In addition, is there a known bug with 3.23.58 where reading bulk queries
> is very slow? (i.e., mysql FOODB < foodb.dump.) Ever since upgrading (on
> FreeBSD 5.2-RC1) this is horrendously slow. Almost NO cpu time is consumed
> by any process involved, and both server/client processes are often in
> state 'S' (sleeping for less than 20 seconds.) This also occurs when using
> a client running on a linux machine to feed data to the aforementioned
> mysqld on the FreeBSD host. I'm about to break up my dumps into
> table-specific files so that I can use LOAD DATA INFILE to help work
> around this problem.
The following may explain this:
"
MySQL/InnoDB-3.23.57, June 20, 2003
Changed the default value of innodb_flush_log_at_trx_commit from 0
to 1. If you have not specified it explicitly in your my.cnf, and your
application runs much slower with this new release, it is because
the value 1 causes a log flush to disk at each transaction commit.
"
> -mike
Merry Christmas!
Heikki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
