-
-
Notifications
You must be signed in to change notification settings - Fork 481
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Dolt creates secondary indexes of columns in a foreign when you run a delete query and there are foreign key constraints #5316
Comments
Importing the dump doesn't generate the indexes. This query does:
|
It goes without saying that DELETE queries should not make schema changes. |
It turns out there were two issues. One issue is that we were incorrectly creating However, a db can only get into that state because of this second issue. Example: tmp> set foreign_key_checks=0;
tmp> create table child (j int, constraint fk1 foreign key (j) references parent (i));
tmp> show create table child;
+-------+-----------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
`j` int,
CONSTRAINT `fk1` FOREIGN KEY (`j`) REFERENCES `parent` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql: mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.0003 sec)
mysql> create table child (j int, constraint fk1 foreign key (j) references parent (i));
Query OK, 0 rows affected (0.0200 sec)
mysql> show create table child;
+-------+-------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
`j` int DEFAULT NULL,
KEY `fk` (`j`),
CONSTRAINT `fk` FOREIGN KEY (`j`) REFERENCES `parent` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------+ Consequently, committing this and then running a ddl query will create the secondary key, resulting in a schema diff. |
Merged fixes for both of the issues discovered. |
I made the HEAD version of this database using a dolt version from last week.
https://www.dolthub.com/repositories/dolthub/employees
When I import this dump over top of it:
https://drive.google.com/file/d/11EWKXEty8JJ-79thlZLGg3dwSWd98Q0k/view?usp=share_link
I get schema diffs of the form:
If I then checkout a table, make a data modification I get the following error:
The text was updated successfully, but these errors were encountered: