skeema: AWS Aurora: unable to diff tables with multiple foreign keys

I recently migrated both my dev and prod RDS databases from MySQL to Aurora MySQL

After the migration, I was able to make schema changes on dev using skeema with no problems, however when I tried running the same changes on prod, I kept getting errors like the following:

2020-03-27 12:07:25 [WARN]  Skipping table `telemedicine_sessions_cancelled`: unable to generate DDL due to use of unsupported features. Use --debug for more information.
2020-03-27 12:07:25 [DEBUG] --- Expected CREATE
2020-03-27 12:07:25 [DEBUG] +++ MySQL-actual SHOW CREATE
2020-03-27 12:07:25 [DEBUG] @@ -9,2 +9,2 @@
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `_fk-telemedicine-sessios-cancelled-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`),
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `fk-telemedicine-session-cancelled-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`)
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `fk-telemedicine-session-cancelled-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`),
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `_fk-telemedicine-sessios-cancelled-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`)
2020-03-27 12:07:25 [WARN]  Skipping table `telemedicine_sessions_rejected`: unable to generate DDL due to use of unsupported features. Use --debug for more information.
2020-03-27 12:07:25 [DEBUG] --- Expected CREATE
2020-03-27 12:07:25 [DEBUG] +++ MySQL-actual SHOW CREATE
2020-03-27 12:07:25 [DEBUG] @@ -8,2 +8,2 @@
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `__fk-telemedicine-session-rejected-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`),
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `fk-telemedicine-sessios-rejected-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`)
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `fk-telemedicine-sessios-rejected-session` FOREIGN KEY (`session_id`) REFERENCES `telemedicine_sessions` (`id`),
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `__fk-telemedicine-session-rejected-clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`)
2020-03-27 12:07:25 [WARN]  Skipping table `telemedicine_sessions`: unable to generate DDL due to use of unsupported features. Use --debug for more information.
2020-03-27 12:07:25 [DEBUG] --- Expected CREATE
2020-03-27 12:07:25 [DEBUG] +++ MySQL-actual SHOW CREATE
2020-03-27 12:07:25 [DEBUG] @@ -39,2 +39,2 @@
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `_fk-telemedicine-session-clinics` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`),
2020-03-27 12:07:25 [DEBUG] -  CONSTRAINT `telemedicine_sessions_ibfk_1` FOREIGN KEY (`practitioner_id`) REFERENCES `users` (`id`)
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `telemedicine_sessions_ibfk_1` FOREIGN KEY (`practitioner_id`) REFERENCES `users` (`id`),
2020-03-27 12:07:25 [DEBUG] +  CONSTRAINT `_fk-telemedicine-session-clinics` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`)

I tried changing the order of the foreign keys in my CREATE TABLE statements to match what it wants, but that doesn’t seem to change anything, I still get the same errors.

The only thing that allowed me to update any of the affected tables was deleting its foreign keys entirely, but it gives me the same errors when I try to add them back in.

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 15 (11 by maintainers)

Most upvoted comments

@zylo47 thank you, but the full picture is more complicated than that. I was hoping Aurora would provide a hint in one of the variables already queried by Skeema, but it does not.

Some Skeema users are very latency-sensitive, due to running it from local machines against DBs in remote datacenters. So generally it’s important to keep the number of superfluous queries to a minimum. Since the vast majority of Skeema users are not using Aurora, I’m very hesitant to introduce an extra Aurora-specific query.

This could be mitigated by only running that query when the @@global.version matches a known value reported by Aurora (e.g. 5.6.10), but this would require a bit of testing against different Aurora clusters to build a mapping of all possible Aurora versions to purported MySQL versions. And overall, Aurora would really need its own distinct flavor in Skeema in order to model the behavioral differences between it and “real” MySQL; this is the system already used by Skeema to model MySQL vs Percona Server vs MariaDB.

However, I don’t personally use Aurora or have access to an Aurora cluster, and none of my current consulting clients use Aurora either. I’m not inclined to spend my own time and money to implement support for a proprietary commercial database. If a company who uses Aurora wants to pay for this development time to make Skeema support their use-case, then I’d be more than happy to work with them on this. But absent that, this is simply not going to happen in open source Skeema.

Alternatively, if/when a commercial edition of Skeema is developed, this can be reapproached at that time if there’s sufficient interest from potential paying customers.

evanelias on Nov 18, 2020