Trying to use Google CloudSQL

MrCaspan

So i am trying to setup Vanilla in Google CloudSQL and when I do a new install it says

"Storage engine MyISAM is disabled (Table creation is disallowed)." this is on the setup screen.

Is there a way I can use this and turn this feature on or is there a reason why Vanilla needs to use this table structure? Any ideas?

MrCaspan

I can see that the file package\library\database\class.mysqlstructure.php has this line.

Line 291:         $this->_TableStorageEngine = c('Database.DefaultStorageEngine', 'innodb');

would it be okay to change this to "MyISAM"

R_J

Some insights here

https://github.com/vanilla/vanilla/issues/6029

Try this config

Database.ForceStorageEngine

MrCaspan

Sorry I was looking for the opposite issue

I can see that the file package\library\database\class.mysqlstructure.php has this line. What is this chunk of code looking for exactly?

    // Check to see if there are any fulltext columns, otherwise use innodb.
    if (!$this->_TableStorageEngine) {
      $hasFulltext = false;
      foreach ($this->_Columns as $column) {
        $columnKeyTypes = (array)$column->KeyType;
        array_map('strtolower', $columnKeyTypes);
        if (in_array('fulltext', $columnKeyTypes)) {
          $hasFulltext = true;
          break;
        }
      }
      if ($hasFulltext) {
        $this->_TableStorageEngine = 'myisam';
      } else {
        $this->_TableStorageEngine = c('Database.DefaultStorageEngine', 'innodb');
      }

      if (!$this->hasEngine($this->_TableStorageEngine)) {
        $this->_TableStorageEngine = 'myisam';
      }
    }

MrCaspan

So i replaced the two statements

$this->_TableStorageEngine = 'myisam';

with

$this->_TableStorageEngine = c('Database.DefaultStorageEngine', 'innodb');

and I got the forum to install fine but since everything I am reading says that innob can support what needs to be done why is Vanilla Forums still using myisam?

R_J

The link I've posted above shows the issues in GitHub that deal with moving away from the MyISAM requirement. I'm not sure what happened with that issue. Since the code you have cited above is still there, the issue obviously hasn't been resolved by now :-(

R_J

I've just seen that this code is no more in the current master branch. So the next Vanilla version will not have any need for MyISAM any more.

MrCaspan

Thanks @R_J

lucas_c

Hi,

For my requirement, I would need to run my Vanilla setup on CloudSQL as well, hence I was thinking about doing the same "hack" forcing the TableStorageEngine to innodb but I am worried that doing this would create further errors... since it's a "hack".

@MrCaspan Could you give me some feedback on your setup with CloudSQL using innodb engine?

Also, would be great to get an update from a core dev about that matter, see if there is a better way to do it, thanks!

MrCaspan

Well I had mine running 100% no issues for about 4 months. Then I saw the bill for like $20 a month for SQL and hosting and was like why would I ever pay these prices. I have hosting with Valcato for like $90 a year with unlimited domains, email etc.. It was just not worth the price for using Cloud SQL.

With that said we had no issues from the database at all. And Cloud SQL is set up to be secure and solid so this is why it gives you such a hustle because this database was not designed with security first in mind. It was MAKE IT WORK then start to add security it seems. So I call it a hack but essentially you are just doing what should have been done by the developers a long time ago. This does not mean it wont break anything in the future though. Its not designed to run on Cloud SQL that don't mean it cant you just have to know what you did so if you ever have an issue you can troubleshoot it!

x00

I think people rush into cloud platforms like aws, google, and azure, without realising how much of a cost minefield it is.

The reality is they have their place, but not recommended for starting out. There are simpler cloud hosts that are more economically.

One of the myths of scalability, is you should always be optimised for maximum scalability. This is a sure way to waste money.

lucas_c

Thanks for the reply @MrCaspan and @x00 this is actually a migration project of an existing database for a big travel community with 7 million unique visitors each month.

I must agree on your feedback, this is the feel we also had when we explored solutions of running Vanilla on CloudSQL, it doesn't seem to be designed for that in its current state. I guess we will find another solution unless @charrondev tells us that this is properly possible to use InnoDB for CloudSQL compatibility 😁

x00

azure has a good mysql service and you can force engine