Debugging: Cannot add foreign key constraint

Backstory:

I was working late to get a feature “working” before heading off to sleep. Somewhere in between autocomplete and matching definitions to Magento core files I had prepped my setup script. But upon running php bin/magento setup:upgrade instead of being all set I was greeted with a foreign key constraint issue to debug.

Foreign Key Constraint Cannot Be Added Error Message

Foreign key constraint from the UpgradeSchema.php in question:
->addForeignKey(
$setup->getFkName(self::CUSTOMMODULE_STORE_PIVOT_NAME, 'store_id', 'store', 'store_id'),
'store_id',
$setup->getTable('store'),
'store_id',
\Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)

So I went back to UpgradeSchema.php to take a closer look at things. I compared what I’d written to vendor/magento/module-store/Setup/InstallSchema.php since that’s the table I was creating my constraint agaisnt and realized that I’d set store_id on my pivot table to TYPE_INTEGER where it should have been TYPE_SMALLINT.

->addColumn(
'store_id',
\Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT, //<-- I assumed this was going to be an integer, oops.
null, ['unsigned' => true, 'nullable' => false, 'primary' => true],
'Store ID'
)

Simple mistake, simple fix. I updated the type definition, re-ran setup, and was back in business. But the output was not that helpful and I was curious how to get better information about what actually went wrong in the event it wasn’t as apparent. And after a quick search here we are, log in to mysql from CLI and check the INNODB Engine Status logs.

Debugging:

ERROR 1215 (HY000): Cannot add foreign key constraint

mysql -u whomever -p
mysql> SHOW ENGINE INNODB STATUS;

The piece of the log we’re interested in for this use case is the aptly named latest foreign key error section.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-05-08 21:57:58 0x700001222000 Error in foreign key constraint of table ...

...
//The table name and query are output here but not relevant as it was output when the error was thrown during setup:upgrade. See below for the real issue:
...

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

TLDR; double check your column definitions. Make sure the data type and attributes match where appropriate. For example: This DOES mean both ID columns should be unsigned but DOES NOT mean your pivot table’s ID column should be auto incrementing. Put this in your memory bank and hopefully you’ll find it useful while debugging at some point.

SO to a stackoverflow answer from 2013 for still being helpful – https://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint

More articles/tips/tool for debugging here.

Webpack Dashboard + Laravel Mix

Adding webpack-dasboard to your Laravel project only takes a few steps whether you’re just getting started or trying to further optimize your application.  This tutorial assumes that you’ve at least gotten as far as setting up your project and installing all its dependencies.

Step 1: Add webpack-dashboard to your project as a dev dependency:

npm install webpack-dashboard --save-dev

Step 2: Next update the scripts section of your package.json by changing the following entry:

"hot": "cross-env NODE_ENV=development node_modules/webpack-dev-server/bin/webpack-dev-server.js --inline --hot --config=node_modules/laravel-mix/setup/webpack.config.js",

.. to something this (the color “`-c“` and title “`-t“` can be changed to whatever you’d like):

"hot": "webpack-dashboard -c blue -t 'Laravel Webpack Dashboard!' -- cross-env NODE_ENV=development node_modules/webpack-dev-server/bin/webpack-dev-server.js --inline --hot --progress --config=node_modules/laravel-mix/setup/webpack.config.js",

Alternatively if you need to use https with a local trusted cert:

"hot": "webpack-dashboard -c blue -t 'Laravel Webpack Dashboard!' -- cross-env NODE_ENV=development node_modules/webpack-dev-server/bin/webpack-dev-server.js --https --cert '/etc/apache2/ssl/server.crt' --key '/etc/apache2/ssl/server.key' --inline --hot --progress --config=node_modules/laravel-mix/setup/webpack.config.js",

Step 3:  Now update the Laravel Mix webpack configuration “`./webpack.mix.js“` to import the dashboard plugin, near the top, and then initialize it by adding to the configuration, located near the bottom of your file:

let DashboardPlugin = require('webpack-dashboard/plugin');
//...
mix.webpackConfig({
// ...
plugins: [
new DashboardPlugin(),
],
//...
});

Step 4:  Start up the webpack-dashboard and dev server by running:

npm run hot


Your output should look similar to the following..

Webpack dashboard output for Laravel Mix

Now visit your site as normal and continue working in your assets directory!

Some caveats:

  • Hot reloading of SASS isn’t supported though you can mix(‘stylesheet’) and refresh as necessary.   Browsersync could be an alternative but likely wouldn’t play nicely with this hot reload setup.

More info here:

M2: Composer Bash Alias

At times you may need to quickly spin up a new, clean Magento 2 install. To simplify this process I added an alias for creating a new Magento 2 environment with composer. If you’re using Oh-My-Zsh as well then its as simple as nano ~/.zshrc and adding the following to the bottom of your configuration.


alias newm2="composer create-project --repository-url=https://repo.magento.com/ magento/project-community-edition ."

newm2 bash composer alias

Once you’re all set up you can run newm2 in any folder and this will automatically grab whatever the latest version is from Magento’s repo and create a new composer based env for you to continue testing/debugging/etc.

If you use any other shell this will still work but you’ll need to locate the proper configuration file for registering your alias (eg ~/.bashrc).