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 anyDATA DIRECTORY
orINDEX 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…
“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.
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