Fresh installation SQL error
Permalink
Hello,
I've recently decided to use "Cloud SQL" from Google Cloud Compute and I have completed the setup that side, however when I attempt to install Concrete5 the install fails with the following error:
The versions of software(s) that I am using are as follows:
Concrete5 - v8.3.2
MySQL - 5.6 or 5.7 (As is shown on the Google Dashboard when you create it)
Server - Debian 9
Apache - 2.4.25
PHP - 7.0.27-0+deb9u1
If there's any more information that you might require to help me, please let me know and I will get back to you as fast as possible. Thank you in advance!
I've recently decided to use "Cloud SQL" from Google Cloud Compute and I have completed the setup that side, however when I attempt to install Concrete5 the install fails with the following error:
Unable to install database: An exception occurred while executing 'CREATE TABLE PageSearchIndex (cID INT UNSIGNED DEFAULT 0 NOT NULL, content LONGTEXT DEFAULT NULL, cName VARCHAR(255) DEFAULT NULL, cDescription TEXT DEFAULT NULL, cPath TEXT DEFAULT NULL, cDatePublic DATETIME DEFAULT NULL, cDateLastIndexed DATETIME DEFAULT NULL, cDateLastSitemapped DATETIME DEFAULT NULL, cRequiresReindex TINYINT(1) DEFAULT '0', FULLTEXT INDEX cName (cName), FULLTEXT INDEX cDescription (cDescription), FULLTEXT INDEX content (content), FULLTEXT INDEX content2 (cName, cDescription, content), INDEX cDateLastIndexed (cDateLastIndexed), INDEX cDateLastSitemapped (cDateLastSitemapped), INDEX cRequiresReindex (cRequiresReindex), PRIMARY KEY(cID)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = MYISAM': SQLSTATE[HY000]: General error: 3161 Storage engine MyISAM is disabled (Table creation is disallowed)..
The versions of software(s) that I am using are as follows:
Concrete5 - v8.3.2
MySQL - 5.6 or 5.7 (As is shown on the Google Dashboard when you create it)
Server - Debian 9
Apache - 2.4.25
PHP - 7.0.27-0+deb9u1
If there's any more information that you might require to help me, please let me know and I will get back to you as fast as possible. Thank you in advance!
I think that's a MySQL/MariaDB configuration issue. It needs to be configured to be able to use MyISAM. I have no idea about how to achieve that on Cloud SQL.
This from Google Cloud Documentation
Database engines
InnoDB is the only supported storage engine for Second Generation instances because it is more resistant to table corruption than other MySQL storage engines, such as MyISAM.
By default, Cloud SQL database tables are created using the InnoDB storage engine. If your CREATE TABLE syntax includes an ENGINE option specifying a storage engine other than InnoDB, for example ENGINE = MyISAM, the table is not created and you see error messages like the following example:
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).
You can avoid this error by removing the ENGINE = MyISAM option from the CREATE TABLE command. Doing so creates the table with the InnoDB storage engine.
InnoDB is strongly recommended for First Generation instances, because of its stronger data consistency guarantees.
Database engines
InnoDB is the only supported storage engine for Second Generation instances because it is more resistant to table corruption than other MySQL storage engines, such as MyISAM.
By default, Cloud SQL database tables are created using the InnoDB storage engine. If your CREATE TABLE syntax includes an ENGINE option specifying a storage engine other than InnoDB, for example ENGINE = MyISAM, the table is not created and you see error messages like the following example:
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).
You can avoid this error by removing the ENGINE = MyISAM option from the CREATE TABLE command. Doing so creates the table with the InnoDB storage engine.
InnoDB is strongly recommended for First Generation instances, because of its stronger data consistency guarantees.
Yes, I stand corrected. c5 requires InnoDB, so it's rather strange that an alternative storage engine is being specified in that SQL.
That said, I think that search options differ between InnoDB and MyISAM so maybe there's some particular reason why that table requires InnoDB. I just checked one of my c5 installations and, lo and behold, that table does indeed use InnoDB. Ergo, you might be out of luck.
That said, I think that search options differ between InnoDB and MyISAM so maybe there's some particular reason why that table requires InnoDB. I just checked one of my c5 installations and, lo and behold, that table does indeed use InnoDB. Ergo, you might be out of luck.
Hmm I am a little confused about this since when I checked my current install with a local SQL (on the same server as my site) all of the tables are using InnoDB, which should be supported by the second generation SQL server that Google offers...
HOWEVER, when I searched deeper there is still one MyISAM being used one a single table, being "PageSearchIndex".
Is there a work around so that I can force this to use InnoDB and therefor operate it on a Gen2 SQL instance that you know of at all?
- Kind Regards.
HOWEVER, when I searched deeper there is still one MyISAM being used one a single table, being "PageSearchIndex".
Is there a work around so that I can force this to use InnoDB and therefor operate it on a Gen2 SQL instance that you know of at all?
- Kind Regards.
My guess is that there is no workaround. I assume that that particular table has to be MyISAM because MyISAM provides a particular full-text search feature that InnoDB doesn't possess; details on the differences are athttps://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html... and subsequent pages. Using InnoDB for that table would probably break the search feature in nasty ways.
If your site doesn't require search, and if that table isn't used for full-text search internally by c5, there's a REMOTE chance that creating the table in InnoDB would get a site going. But how you'd convince the installer to do that I don't know.
If your site doesn't require search, and if that table isn't used for full-text search internally by c5, there's a REMOTE chance that creating the table in InnoDB would get a site going. But how you'd convince the installer to do that I don't know.
Hmm this is an interesting one because I read that link you provided and "apparently", according to that (If I read it right, both InnoDB and MyISAM tables can use FULLTEXT indexes...
So confusing, anyway. What I will do is ask on GitHub if this is something that can be reviewed for the next release and if so, happy days! :D
So confusing, anyway. What I will do is ask on GitHub if this is something that can be reviewed for the next release and if so, happy days! :D
So I **might** have found a hacked solution but I am not sure what this might effect in the long run. Maybe you could tell me if you know?
I have downloaded a fresh copy of concrete5-8.3.2 and modified: concrete5-8.3.2\concrete\config\db.xml
Removing the line entirely from the table. From what I can tell when starting a XAMPP on a localhost the site installs without issue and PageSearchIndex is also present showing it now using InnoDB
What does this actually effect and do you think I can run a live-production site like this until changes are made in the future?
I have downloaded a fresh copy of concrete5-8.3.2 and modified: concrete5-8.3.2\concrete\config\db.xml
Removing the line
<opt platform="MYSQL">ENGINE=MYISAM</opt>
What does this actually effect and do you think I can run a live-production site like this until changes are made in the future?
Yes, InnoDB and MyISAM both support FULLTEXT searches, but their implementations aren't identical (as you can see from those links). I'd guess that some search features in c5 either won't work properly or will crash, because I assume they're using MyISAM for that table just so they can get the features it provides.
The alternative is that suitable InnoDB FULLTEXT support might not have been available in earlier versions. If c5 doesn't use MyISAM-specific features, and InnoDB now provides everything required, you might get along fine. If this is the case, it would be good for c5 to cease compatibility with old versions so it can standardise on InnoDB. This might be a good and timely suggestion for c5 9.
The alternative is that suitable InnoDB FULLTEXT support might not have been available in earlier versions. If c5 doesn't use MyISAM-specific features, and InnoDB now provides everything required, you might get along fine. If this is the case, it would be good for c5 to cease compatibility with old versions so it can standardise on InnoDB. This might be a good and timely suggestion for c5 9.
