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

[conner_bw]

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:

<?php
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.

[johnjamesjacoby]

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.

[randompage]

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

example:

CREATE /*something*/ TABLE bla bla...
DROP /*something*/ TABLE bla bla...