Make WordPress Core

Opened 6 years ago

Last modified 3 years ago

#44351 new enhancement

Fix "Foreign key constraint is incorrectly formed" when running unit tests

Reported by: conner_bw's profile conner_bw Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.9.6
Component: Build/Test Tools Keywords:
Focuses: Cc:


I kept getting "Foreign key constraint is incorrectly formed" when running tests on my plugin. This drove me nuts for a few hours. The reason is WP_UnitTestCase::start_transaction()

The WordPress testing framework changes all 'CREATE TABLE' queries to 'CREATE TEMPORARY TABLE'.

From the MYSQL manual:

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables.

Please add something like:

add_filter( 'query', [ $this, '_alter_temporary_tables' ] );
// ... snip ...
public function _alter_temporary_tables( $query ) {
    if ( 'ALTER TABLE' === substr( trim( $query ), 0, 11 ) && strpos( $query, 'ADD CONSTRAINT' ) !== false && strpos( $query, 'FOREIGN KEY' ) !== false ) {
        return 'SELECT 1'; // Replace borken foreign key query with a fake query
    return $query;

To WP_UnitTestCase::start_transaction()

Thank you for your consideration.

Change History (2)

#1 @johnjamesjacoby
4 years ago

I have yet to run into this issue myself, but it appears WooCommerce may still include at least one foreign key.

(Upgrade routines in WooCommerce make it look like they've removed at least 2 so far, so I may be misreading.)

The snippet you suggest is an interesting way to silence this nag. I'm not confident it should be, but it does work.

#2 @randompage
3 years ago

simply insert a comment betwen CREATE TABLE and DROP TABLE will do the job.


CREATE /*something*/ TABLE bla bla...
DROP /*something*/ TABLE bla bla...
Note: See TracTickets for help on using tickets.