Can you provide a complete example, starting with CREATE TABLE statements?
Hi marko - thanks much for your reply.
Hopefully these are enough (unfortunately I can't provide the exact tables):
CREATE TABLE `main_table` (
|
`uid` bigint(18) UNSIGNED NOT NULL,
|
...
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
|
|
|
ALTER TABLE `main_table`
|
ADD PRIMARY KEY (`uid`),
|
ADD UNIQUE KEY ...,
|
ADD UNIQUE KEY ...,
|
ADD KEY ...,
|
ADD KEY ...,
|
ADD KEY .;
|
|
|
|
|
-- 2145842 rows currently in `table`
|
|
|
CREATE TABLE `table` (
|
`mid` bigint(18) UNSIGNED NOT NULL,
|
`c1` bigint(18) UNSIGNED NOT NULL,
|
`c2` bigint(18) UNSIGNED NOT NULL,
|
`initiator` bigint(18) UNSIGNED DEFAULT NULL,
|
`last` bigint(18) UNSIGNED NOT NULL,
|
`time` int(10) UNSIGNED NOT NULL,
|
`c3` mediumint(8) UNSIGNED NOT NULL,
|
`c4` enum('0','1') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '1'
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
|
|
|
ALTER TABLE `table`
|
ADD PRIMARY KEY (`mid`),
|
ADD UNIQUE KEY `c1` (`c1`,`c2`),
|
ADD KEY `time` (`time`),
|
ADD KEY `c2` (`c2`),
|
ADD KEY `initiator` (`initiator`,`time`);
|
|
|
ALTER TABLE `table`
|
MODIFY `mid` bigint(18) UNSIGNED NOT NULL AUTO_INCREMENT;
|
|
|
ALTER TABLE `table`
|
ADD CONSTRAINT `table_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `main_table` (`uid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
|
ADD CONSTRAINT `table_ibfk_2` FOREIGN KEY (`c2`) REFERENCES `main_table` (`uid`) ON DELETE NO ACTION ON UPDATE NO ACTION;
|
All the constraints above are already there.
Now I'm trying to add a new constraint/FK on `initiator`.
Thanks!!
nunop, it would really help if you could provide a self-contained SQL test case. Adding a FOREIGN KEY without a table rebuild is being tested in our regression test suite, so it does work in some cases:
innodb.alter_algorithm 'COPY,innodb' w51 [ pass ] 193
|
innodb.alter_algorithm 'INPLACE,innodb' w48 [ pass ] 98
|
innodb.alter_algorithm 'NOCOPY,innodb' w38 [ pass ] 60
|
innodb.alter_algorithm 'INSTANT,innodb' w13 [ pass ] 48
|
In your case, it does not work for some reason that is not obvious to me.
The number of rows in the tables should not matter, or at most it matters that the tables contain at least 1 row. You can add explicit , ALGORITHM=INSTANT to have the operation rejected in case the table would need to be copied.
Ok marko
Thanks very much for this.
It appears that the problem must be with the UI I'm using, which is phpMyAdmin.
I've just tried directly in the shell, and ALTER ONLINE worked without any problem, and instantly!
I'll report this issue to phpMyAdmin instead 
Thank you very much!!
Issue can be closed.
Have a great day.
Thank you for the clarification.