Trying to restore MyISAM to InnoDB

[JacobParticipant]

I’m migrating from an old server to new.
Old server is using MyISAM, and new is using InnoDB database.
I’m also adding in SSL during the migration.

I’m assuming this database change is the issue?

Processing table (MyISAM): wp_options
An error (1) occurred: – Storage engine MyISAM is disabled (Table creation is disallowed). – the database query being run was: CREATE TABLE wp_options ( option_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, option_name …
Disabling Maintenance mode…
Error message: An error occurred on the first CREATE TABLE command – aborting run
Restore failed…

[Bryle CroduaModerator]

Hi Jacob,

As the log shows, it appears that “Storage engine MyISAM is disabled”. Please ask your host/server admin if that is the case. If so, then ask them if they can enable it for you.

Kind Regards,
Bryle

[udadminKeymaster]

N.B. In modern MySQL versions, it’s not possible to disable the MyISAM engine. So, your new server must be running a pretty old version – it might be worth asking the admin to upgrade it to something modern.

[JacobParticipant]

In theory- this should be super modern.
I’m attempting to self-host on a Google Cloud instance. So *I* am the host/admin. I’ll ask around, but this server is only days old.

[JacobParticipant]

https://cloud.google.com/sql/faq

For MySQL Second Generation instances, InnoDB is the only storage engine supported. For MySQL First Generation instances, the InnoDB storage engine is recommended, because it provides stronger data consistency guarantees.
If you have a mysqldump file where all your tables are in MyISAM format, you can convert them to InnoDB format by piping the file through a sed script:

mysqldump –databases [DATABASE_NAME] \
-h [INSTANCE_IP] -u [USERNAME] -p [PASSWORD] \
–hex-blob –default-character-set=utf8 | sed ‘s/ENGINE=MyISAM/ENGINE=InnoDB/g’ > [DATABASE_FILE].sql
Warning: You should not do this if your mysqldump file contains the mysql schema. Those files must remain in MyISAM.

I am using 2nd Gen, and I am interested in the data consistency.
Is there a way I can use this script as part of my work flow using UpDraft to migrate my site?

[JacobParticipant]

Now I’m questioning the modernness of my SQL database… @udadmin?

[JacobParticipant]

Guys-
I’m a weekend warrior. I know enough to get in trouble.
But I’m frustrated that this isn’t getting more attention. I’m frustrated that it was recommended to me to “go ask my server to give me something more modern” (which would have made me look like a complete jackass)– when all evidence is that my server is SO modern, you’ve never heard of MySQL Gen2, where myISAM isn’t an option. Cool.

I figured it out myself- but I’m frustrated that no more suggestions were made or options presented.

My solution (so other people who run into this can actually get some possible help) was to upgrade my database to InnoDB on my old server first- which can be done in myPHPAdmin. You can either do it one table at a time via the GUI- or with a few lines of SQL code, and some copy and pasting.

If you don’t know what you’re doing- I recommend the GUI way. Click the table, go to the operations tab, and change the engine from myISAM to InnoDB. Repeat for each table (apologies if you have a lot)

In the future- please don’t suggest that people go ask their server for “something more modern” without having all the facts. That’s just so rude.

[Author]

Posts