Adds a config option to disable InnoDB Strict Mode in the session of …

[hostep]

…mysql connections used by this module. Can help prevent 'Row size too large.' errors. Also explicitly setting table type to InnoDB when asked for when creating temporary tables.

This has to do with the error Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs were we can run into when importing product data with a lot of attributes.

This complements https://github.com/akeneo/magento2-connector-community/commit/6311c774c622bb5cdbc2a97da27b3c7247a8274d added by @magentix

Things done here:

1. Explicitly use InnoDB when chosen that storage engine, if we don't set it explicitly it might still use MyISAM in case MySQL is configured to not use InnoDB as default storage engine (like in MySQL 5.7)
2. Adds a new configuration option to disable InnoDB Strict Mode

The use case for number 2 is because we have a certain hosting company which uses MySQL 8 where they've chosen to explicitly disable MyISAM storage. We can't convince them to re-enable MyISAM storage, their reasoning is twofold:

• MySQL is deprecating MyISAM anyway, and will probably remove default support for it in the future: https://bugs.mysql.com/bug.php?id=78553
• They claim MyISAM tables can sometimes cause database backups to become corrupt and they don't like that.

Disabling innodb_strict_mode in the session of the current mysql connection is another way to resolve the error mentioned above in case MyISAM storage engine isn't an option. This was already suggested a while ago over here: https://github.com/akeneo/magento2-connector-community/issues/177#issuecomment-805318039

Questions:

• I've chosen to set this innodb_strict_mode flag in the getConnection method of Akeneo\Connector\Helper\Import\Entities, every other class in this module seems to use this method to get a mysql connection, so that made the most sense, is that okay?
• This query will be executed each time this method is called, which may not be needed, but I'm a bit worried if we only execute it once and if the connection times out and gets reopenend, maybe in that case the session variable may be lost (this is just in theory, I haven't tested this). Is this okay?

I've quickly tested this solution locally and it works in our case, haven't yet had the chance to test it in a production environment, but will try to do this soon-ish.

[hostep]

Small update: just tested this on a staging environment with MySQL 8.0.36 (Percona) where MyISAM is disabled, and it works good without problems. So that's a good sign!

[jogoossens]

Thx a lot for the nice patch Pieter! 😄

The ideal solution would be to change the design of the table so the error is not there in the first place all together probably?

It's just a limitation of MySQL innodb and it will bite back sooner or later :)

[hostep]

Yes, that was already mentioned on https://github.com/akeneo/magento2-connector-community/issues/177#issuecomment-805318039

But that will require a lot more work, possibly multiple days of refactoring, so I chose the quick solution for now.

[hostep]

thanks!