0

Is there a way to get better debugging information for foreign key violations in MySQL?

I'm using Phinx for database migration and I'm dealing with multiple constraints in a row and exception messages like these:

Integrity constraint violation: 1022 Can't write; duplicate key in table '#sql-1b8c_4534' in […]

This problem is obscured by a lack of foreign-key name as well as a temporary table name. Is there a way to switch to a more verbose mode or something?

Expanding the stack trace would help also, because now it looks like this, with truncated queries:

Stack trace:
#0 /home/user/work/project/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(167): PDO->exec('ALTER TABLE `us...')
#1 /home/user/w[…]
WoodrowShigeru
  • 1,418
  • 1
  • 18
  • 25
  • 1
    It is MySQL that is obscuring the table name, as when you alter a table, MySQL creates a copy of the table first (with a name like '#sql-1b8c_4534'), does all things it needs to do, and when it's finished, renames it. It's not really phinx' fault. – Solarflare Jul 25 '19 at 19:44
  • Ah, good to know. – WoodrowShigeru Jul 25 '19 at 20:02

1 Answers1

1

You can find out at least in which table the error occurred by running "show engine innodb status" in MySQL as root. It will show something like:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-02-13 11:12:26 0x70000b776000 Error in foreign key constraint of table table/#sql-7fa_247a:
 foreign key (`my_foreing_key`) references `table` (`id`)
   on delete cascade:
Cannot resolve table name close to:
 (`id`)
   on delete cascade

(the example is from here)

Andrei Kovacs
  • 171
  • 4