Pestle 1.3.2 Released, Includes MySQL Foreign Key Integrity Check Command

There’s a new point release of pestle available for immediate download — this one’s mostly a “bug fixes and improvements” sort of release, with the exception of the new mysql:key-check command, and some early alpha work on a Magento 2 webapi/service-contract generator. If “bug fixes and improvements” makes you :eyeroll: look for a full list of changes in the closed milestone issues.

Current pestle users can grab the latest release with a

pestle.phar selfupdate

New users can find installation instructions over in the GitHub repository.

MySQL Key Check Command

The one new command is pretty straight forward — pass in some database credentials, and mysql:key-check will report on any tables that have invalid MySQL foreign key relationships

$ pestle_dev mysql:key-check
DB Server? (127.0.0.1)] 127.0.0.1
DB Port? (3306)] 3306
DB Username? (root)] root
Schema Name? ()] some_magento_database
MySQL Password: 

Invalid Foreign Key Value Counts
--------------------------------------------------
51  catalog_category_entity_text.attribute_id points to eav_attribute.attribute_id
17  catalog_category_entity_varchar.attribute_id points to eav_attribute.attribute_id
17  catalog_eav_attribute.attribute_id points to eav_attribute.attribute_id
119 eav_entity_attribute.attribute_id points to eav_attribute.attribute_id

If you’re curious about the specific rows that have problems, the --use-sql-report option will return the list of SQL queries used to identify the invalid relationships.

pestle.phar mysql:key-check --use-sql-report

What Does this Happen?

Way back in the early 00s, MySQL made a name for itself by being a fully-featured RDMS that got great performance by eschewing a lot of the data integrity checks normally associated with a RSMS (which probably makes some folks :eyeroll: at my use of “fully featured”).

This made it a great data-store for early web software that needed something more sophisticated that the file system. (The file system is actually a great data-store, with the sole exception of the file locking problem, which made it problematic for the number of writes a popular website/web application needed)

Early versions of MySQL didn’t even have foreign key relationships. An independent developer introduced foreign keys with the InnoDB extension/plugin/thing (MySQL’s architecture is not my strong suit). InnoDB eventually became a built-in feature of MySQL.

One problem with all this is a sub-section of MySQL’s user-base that didn’t really get/care about data integrity. Small teams who cared about their software used MySQL carefully — other folks banged on the keyboard until their error messages went away. MySQL chose to help this later group with a few different hammers.

One of the biggest hammers was/is the following

SET FOREIGN_KEY_CHECKS=0;

This command allows a user to turn off foreign key checks for the current database session. Users often use this when the SQL they’re trying to run gives them a Data Integrity violation error. MySQL will happily import foreign keys with values that point to nothing, and the data will sit there for the next developer to trip over.

I’ve written some version of the mysql:key-check command a handful of times over the years. I usually do this to get a handle on how borked a database is and if we’re better off trying to fix the integrity problems, or shrugging our shoulders and continuing in an awful tradition with another SET FOREIGN_KEY_CHECKS=0;. Now that I have pestle, I have a place to write the command one-last-time and come back to it again and again.

While not specifically Magento related, there’s a lot of Magento 2 migration projects that end up dealing with Magento 1 databases that have integrity constraint violations. This command should help you diagnose if the problem’s with your Magento 1 data, Magento 2 data, or if some new bit of custom code has introduced a new potential data integrity problem.