5

Some of the tables in the Magento 2.1 DB have "MEMORY" as their engine. This is a problem for the DB cluster we are using (Percona-XtraDB-Cluster with pxc_strict_mode = ENFORCING).

Can I change the engines to InnoDB or will that create errors somewhere?

  • Don't hesitate to experiment yourself. Take a backup of your DB, and try making the required changes in your local, then test all the functionalities for any glitches. Enjoy :) – MGento Oct 27 '17 at 4:51
0

It can decrease performance but should not cause errors.

But please pay attention then some tables is created dynamically.

|improve this answer|||||
0

I haven't yet attempted this with percona, though I have done it on both AWS RDS MySQL 5.7 and also AWS RDS Aurora with success in both of those environments. I faced a similar situation where for some reason some of my DB tables were using the MyISAM storage engine though a few others were using MEMORY and some were using the InnoDB storage engines.

After a lot of digging into my source code repository it seems as though a lot of the discrepancies could be explained by the fact that the systems with the variations were all long-running installations that had started using Magento CE 1.6 or lower and had been upgraded over time to 1.9 (at least).

It appeared that some of our extensions (custom and 3rd party) weren't very diligent about specifying the storage engine to use during setup and so whatever was set as the default for the DB server (which might have been a dev or VM environment in some cases) was used and then propagated to our production environment. Also, it appears that some of the Magento upgrade code had similar lapses on occasion, though their clean install logic usually did not, so some of our installs were all InnoDB and others were a mix.

At any rate, as I outline in this other Answer, you should be able to migrate the storage engines of all your Magento 1.9.X tables over to InnoDB without any major issues. I haven't tried this on a Magento 2.X install yet, so I'm very curious to see if the solution is still valid in that environment.

That solution was primarily focused on migrating MyISAM -> InnoDB, but when I tested it, I had a few tables using the MEMORY storage engine also and I just moved everything to InnoDB and it worked like a champ (given the caveats I point out in that solution).

If you're certain you want to use InnoDB, I'd recommend modifying your Percona configurations on all of your servers (and your local dev environments) to set the default storage engine to InnoDB - there's just a lot of 3rd party (and maybe even some of your own legacy code) that doesn't specify a storage engine in the setup logic and you can probably keep the configuration drift to a minimum if you do so. NOTE You may be see unintended side effects* at least perhaps in other installation software that assumes you have a different default storage engine, but that should be relatively easy to track down during installation if you run into errors.

Please shoot us an update here when you're done - I'd like to know if PerconaDB behaves any differently than the MySQL & AWS setups I've used this technique on in the past.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.