MyISAM tables on Cloud SQL

[Dan C.]

Hi there.  I know that the current Google documentation on Cloud SQL (second generation) says that only InnoDB engine tables are supported.  However....

A few months ago, before I knew any better, I imported an existing MyISAM dump into Google Cloud SQL.  It silently converted the storage engine into InnoDB, and when I was checking to see that everything got imported alright, that's when I first learned that Cloud SQL tables had to be using InnoDB.  So other than realizing I'm going to have to do a bit of testing to make sure the engine conversion didn't break anything, at least I knew where I stood.

A few days ago, I imported an updated dump of the same tables (from an offsite MySQL installation using MyISAM) and expected the same behaviour.  However, I got a surprise when I did a count(*) on a table with around a hundred million rows and got results immediately (NOT what one would expect from InnoDB).  And on further investigation, my import didn't "automagically" convert the engine to InnoDB this time.  Of course, I can manually convert the tables with a simple sed script prior to import, but considering that the tables were seemingly successfully created with the MyISAM engine I'm wondering if I have to.

Sure enough, if you do a "create table test (a int) engine=myISAM;" on Cloud SQL, it happily creates a table using the MyISAM engine.  But this is a odds with the Google Cloud SQL documentation which says you can'd do that.

Without getting into the debate of whether or not we *should* be using MyISAM (I know InnoDB is more reliable), does anyone know what's going on here?  Is this a new capability now offered by Cloud SQL that isn't in the documentation yet?  Or is it something that shouldn't be relied upon and may be pulled at any time?

Thanks!

Dan

[Fady (Google Cloud Platform)]

Hello Dan,

As you mentioned, Innodb is only supported on a second generation Cloud SQL instance. Trying to reproduce your results above, instead I got an error:

 

MySQL [test]> create table test (a int) engine=myISAM;
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

And that is consistent with this official troubleshooting guide where for second generation instances it throws this error. However when trying the same on a First generation Cloud SQL instance it works fine, as per this document InnoDB is only recommended and not enforced.

MySQL [firstGen]> create table test (a int) engine=myISAM;

Query OK, 0 rows affected (0.15 sec)

That said, if you are using a second generation instance, it could be an intermittent issue at the time, or a missing flag (maybe at instance creation) that allowed using “myISAM”. Hence, if you are still affected, you may open a private report (GCP Private issues component) including your instance name and project for further investigation.

[Dan C.]

OK, now that's weird.

I fired up another Cloud SQL instance, created a "testdb" database and then executed "create table test (a int) engine=myISAM;" as you did, and got the same error you did (which is what's supposed to happen, and what I believe originally happened when my original instance was created a couple of months ago).

Yet, when I do the same on the Cloud SQL instance I've had running for awhile now, the same procedure allows me to create the table with a MyISAM engine, and I don't get any errors.

In the Google Cloud Platform SQL dashboard, both instances are listed as being MySQL 2nd Gen 5.7. When I do a "select version()" on either instance, I get the same result - 5.7.14-google-log (I don't think first generation can support 5.7 at any rate).  Yet one instance lets me create a table with MyISAM and the other doesn't.  Both my original instance (where MyISAM seems to be allowed) and my test instance I just created are in the same zone, and neither had any special flags set.

I can chalk it up to just "one of those things" and ensure I don't use MyISAM in case the rug gets pulled out from the instance that allows it.  Though I'm now a bit concerned that if it has that weird behaviour, the instance itself may be flaky in other ways.

Thanks for your response.

Dan

[Fady (Google Cloud Platform)]

Hello Dan,

I found it odd that on your existing instance, you initially were not able to use myISAM, but then somehow that changed. So a possible explanation for this behavior could be that you had configured your instance for high availability, and the replica itself may had this issue and not the original master. Hence, the failover instance (due to failover) could have replaced the original master (became master and retained the original master’s ID). [Side note] The procedure also creates a new failover instance to replace the old failover.

That said, I tested this theory and found that when the failover instance replaces the master, it indeed allows using myISAM, and that is not an expected behavior. Thus, I opened this public issue for further investigation by the Cloud SQL team. If that is not the case with you, you may open a private issue per my suggestion above to inspect your instance.

[Dan C.]

Bingo - I think you definitely found the problem.  I'd neglected to mention that my original instance allowing MyISAM had a failover instance, and at one point awhile ago, I know I tested it by triggering a failover (never crossed my mind that it may be relevant).  So yes, the instance that allows MyISAM engines was once a failover instance that was promoted to a master.

At least if you were able to now duplicate this unexpected behaviour, it should be easier for the Cloud SQL team to track down and fix.  Presumably when a failover or read replica are configured, it may not be done with the same flags as a master prohibiting tables other than InnoDB (which of course, since they are read only when accessed directly, wouldn't be noticed unless a failover is promoted to master).

In any case, I'll certainly make sure my tables are manually converted to InnoDB - the behaviour of allowing a MyISAM engine is definitely unintentional, and likely won't survive when they patch this.

Thanks for helping to troubleshoot this!

Dan