Skip to content

# “Why?” of the week

MySQL April 13, 2009April 13, 2009

As progress on oak-online-alter-table goes on, I’m encountering more and more limitations, for which I must find workarounds. Here’s two:

#### CREATE TABLE … LIKE …

It works well, but it doesn’t copy any foreign key constraints. So, if the original table is this:

>     CREATE TABLE `dept_emp` (
>       `emp_no` int(11) NOT NULL,
>       `dept_no` char(4) NOT NULL,
>       `from_date` date NOT NULL,
>       `to_date` date NOT NULL,
>       PRIMARY KEY  (`emp_no`,`dept_no`),
>       KEY `emp_no` (`emp_no`),
>       KEY `dept_no` (`dept_no`),
>       CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
>       CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
>     ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then CREATE TABLE dept_emp_shadow LIKE dept_emp results with:

>     CREATE TABLE `dept_emp_shadow` (
>       `emp_no` int(11) NOT NULL,
>       `dept_no` char(4) NOT NULL,
>       `from_date` date NOT NULL,
>       `to_date` date NOT NULL,
>       PRIMARY KEY  (`emp_no`,`dept_no`),
>       KEY `emp_no` (`emp_no`),
>       KEY `dept_no` (`dept_no`)
>     ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Why is that? Why wouldn’t the constraints be duplicated? No explanation is given in the manual, though the behavior is documented:

> `"``CREATE TABLE ... LIKE` does not preserve any `DATA DIRECTORY` or `INDEX DIRECTORY` table options that were specified for the original table, or any foreign key definitions.”

> from http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Sure, I can work it by hand, or write some automation script, but… so much fuss!

#### Changing triggers in a critical section

Say you wanted to alter a trigger. There’s no ALTER TRIGGER statement. So you must DROP the trigger first, then CREATE a new one.

Can this be done such that nothing accidentally happens in between? The following code:

>     LOCK TABLES City WRITE;
>     DROP TRIGGER IF EXISTS City_AI;
>     CREATE TRIGGER City_AI AFTER INSERT ON City
>       FOR EACH ROW SET @dummy := 17;
>     UNLOCK TABLES;

Causes a deadlock. In fact, any tampering with a trigger while the table is locked causes a deadlock. This was described in bug #23713, and has been set as fixed, although Paul DuBois notes there’s still a deadlock. (I’m running 5.0.77 on Linux x64) .

There’s an explanation ([29 Nov 2007 12:42] Bugs System) which is unsatisfactory, as I could not make of it a solution for 5.0. Perhaps a wise reader can shed light?

I’ll try to re-submit this bug. It really gets in my way…

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
3
Leave a Reply

3 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Sergei Golubchik

“Why is that? Why wouldn’t the constraints be duplicated? No explanation is given in the manual”

This happens, of course, because CREATE TABLE … LIKE duplicates a table definition as it’s know to MySQL.

MySQL doesn’t know about foreign keys, it parses and ignores FK definitions.

Author
shlomi

Sergei,

Short and fair explanation.
However, I disagree. MySQL is able to tell me about the foreign keys when asked for “SHOW CREATE TABLE“, which is exactly how I produced the above table description.
So, it is able to specify the FK, along with the constraint names. In this case, it should be able to duplicate this definition for a new table.

Regards

New and Noteworthy in openark kit | code.openark.org

[…] The table must not have any ‘AFTER’ triggers defined. At current, I do not see a solution for that, the reason being that the utility needs to create those triggers as port of its mechanism, and that MySQL does not allow for atomic alteration of triggers. Also see this post. […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.