“Why?” of the week

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…