Cms: Issues with applyFieldSave using Digital Ocean DBaaS MySQL (Managed Database)

[mrunkel]

Description

I've encountered this problem twice now. Once while trying to rename a field in a MatrixField, and now while trying to rename a field in SproutForms. The problem only happens with the Digital Ocean MySQL server. Downloading the database and running it locally in our dev environment works without issue.

Looking at the error page below, you can see that the stack trace points at line 1492 in applyFieldSave, however that SQL statement should be an ALTER TABLE 'content' RENAME COLUMN ... statement, not the ALTER TABLE 'content' CHANGE ... that is causing the error.

The error page looks like this:

errorpage.txt

Steps to reproduce

1. Create a form with sproutForms
2. Rename the form.

Additional info

• Craft version: Craft Pro 3.4.9
• PHP version: 7.4.1
• Database driver & version: MySQL 8.0.18 (Where the error occurs) MySQL 8.0.19 (locally)
  Attached is a diff of the settings between servers
• Plugins & versions: SproutForms newest

[mrunkel]

I can provide access to our server if you'd like. Addtionally: https://github.com/craftcms/commerce/issues/1210 Seems to be related.

[brandonkelly]

CHANGE is in fact proper syntax for renaming a column (see https://dev.mysql.com/doc/refman/8.0/en/alter-table.html); sounds like DO DBaaS just doesn’t support it. The actual SQL there is being generated by yii\db\mysql\QueryBuilder::renameColumn(), so you could try requesting that they change it or submit a PR, but it looks like at least in some cases, they are taking advantage of the CHANGE features that aren’t available to RENAME COLUMN.

[mrunkel]

Hi Brandon. Thanks for taking a look at my ticket. However, I don't think it's quite that simple.

A) Yes, ALTER TABLE 'foo' CHANGE is totally valid SQL, however it requires three "parameters" fromColumn toColumn and type. Type is missing from the SQL query.
B) It's not being generated by renameColumn(). renameColumn() generates ALTER TABLE 'foo' RENAME COLUMN 'old' TO 'new'

It may very well be that the problem lies in the yii2 framework, but I'm not convinced of that.

[brandonkelly]

B) It's not being generated by renameColumn(). renameColumn() generates ALTER TABLE 'foo' RENAME COLUMN 'old' TO 'new'

It may very well be that the problem lies in the yii2 framework, but I'm not convinced of that.

Your stack trace shows that the error is coming from this code:

https://github.com/craftcms/cms/blob/fa28541499591b458e11f61c21016365da7120f5/src/services/Fields.php#L1490-L1492

That renameColumn() method is yii\db\Command::renameColumn(), which internally calls yii\db\mysql\QueryBuilder::renameColumn(), which is where the actual SQL is being generated.

So yes, definitely a Yii thing.

A) Yes, ALTER TABLE 'foo' CHANGE is totally valid SQL, however it requires three "parameters" fromColumn toColumn and type. Type is missing from the SQL query.

Ah you are correct. Looking at the QueryBuilder::renameColumn() code, it looks like it generally will include a type definition, and will only omit it if it couldn’t find the current column in the SHOW CREATE TABLE query result, which means the original column didn’t exist in the first place, despite Craft’s previous columnExists() check returning true. So even if renomeColumn() had returned the correct RENAME COLUMN SQL, you would have ended up with a different SQL error about how the column field_defaultField1 doesn’t exist.

Seems like maybe the root issue is that the schema cache wasn’t cleared at some point when it should have been.

Can you tell me a bit more of what happened leading up to this error? Did you have another field with the handle defaultField1 before the error occurred?

[jakepm]

Found this issue after receiving the same SQL error for field renames during the project config sync.

I believe this is a result of differences in MySQL server config, specifically for those using Digital Ocean managed database servers. More specifically, the STRICT_ALL_TABLES setting that is applied to these servers as part of the global SQL mode in Digital Ocean. Note that it can be removed in the Digital Ocean control panel via the database settings UI.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_strict_all_tables
"Enable strict SQL mode for all storage engines. Invalid data values are rejected. For details, see Strict SQL Mode."

This seems to be the issue being described (+ https://github.com/craftcms/commerce/issues/1210) in different areas of Craft, with this MySQL config setting being the common ground. MySQL wants the data type in the ALTER TABLE query.

On the one hand, I don't really want to remove this setting which will impact many sites in our hosting ecosystem. It seems like a good rule to have. On the other hand, this appears to be part of the Yii framework dependency, so is there anything you guys can actually do? @brandonkelly

[jakepm]

Just read properly and can see that the reason behind the reason, is actually the field name not existing in the content table. That's why there is no data type being handed down to the query builder in Yii, and would be the error if these MySQL settings didn't apply.

So Digital Ocean isn't the problem... data integrity is. This has happened too many time for there not to be a root cause with project config itself. There is a mismatch between the project config and database, which cannot be resolved by simply rebuilding the project config from the master database/environment.

[angrybrad]

Tracked this down to this bug in Yii. Will look into getting a PR made soon for it.

@jakepm you were close, but ANSI_QUOTES is what you were looking for.

[brandonkelly]

Just submitted https://github.com/yiisoft/yii2/pull/18500 which, combined with #7474, will put this issue to rest.

[brandonkelly]

https://github.com/yiisoft/yii2/pull/18500 was just merged, so now we’re just waiting on Yii 2.0.41 to be released.

[mrunkel]

This is awesome! Thanks for the work to fix this!

[benje]

Just wanted to chime in and say I'm also experiencing this issue, when renaming a field and running php craft project-config/apply --force on a Craft CMS installation using Digital Ocean DBaaS MySQL 8.

Thank you for the work in fixing it @brandonkelly @angrybrad.

Here is the error in case it's useful

error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
website1 | 15:05:54 The SQL being executed was: ALTER TABLE `content` CHANGE `field_visibleInMenus` `field_imageRightAligned`
website1 | 15:05:54 building: exit status 1

[brandonkelly]

Yii 2.0.41 was just released, and I’ve pulled it in for the next Craft release 🎉

[brandonkelly]

Craft 3.6.9 is out now with this fix.