Suitecrm: SQL "rank" is a reserved word, can't be used as a field name in MySQL 8.0.2 and later

[pgorod]

This error is with an unsupported version of MySQL, we don't need to fix it now, I'm just adding this here for when we want to work on MySQL 8.0 compatibility somewhere in the future.

This is coming from an issue on the Forums, a user can't run the installer.

Fri Jun 15 15:34:10 2018 [3896][-none-][FATAL] Mysqli_query failed.
Fri Jun 15 15:34:10 2018 [3896][-none-][FATAL] Error creating table: users_feeds Query Failed: CREATE TABLE users_feeds (user_id varchar(36) NULL ,feed_id varchar(36) NULL ,rank int NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL , KEY idx_ud_user_id (user_id, feed_id)) CHARACTER SET utf8 COLLATE utf8_general_ci: MySQL error 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 'rank int NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL , ' at line 1 "

It seems that rank is an SQL reserved word since MySQL 8.0.2:
https://dev.mysql.com/doc/refman/8.0/en/keywords.html

And we're using it as a field name here:
https://github.com/salesagility/SuiteCRM/blob/master/metadata/user_feedsMetaData.php#L46

From my search, that is the only reference to that field, so it seems not be used at all after the initial table creation. But I might be missing something.

Note that I asked the user to change the column name and retry, and he said

I qualified the column name as rank and restarted installation. This time the installation completes with a warning MSG "Query Failed: ALTER TABLE users_feeds add COLUMN rank int(11) NULL : MySQL error 1060: Duplicate column name 'rank' ".

So I wonder where that ALTER TABLE is coming from... from the installer script? From some "Quick Repair and Rebuild" that the installer runs?

Your Environment

• Environment name and version: MySQL 8.0 (which we don't support yet)

[pgorod]

Rank Rant

May I add, it is really stupid in 2017 to add a reserved word like rank to MySQL, assuming none of the millions of MySQL databases out there is using that word as a column name, or assuming everyone quotes column names properly. Yeah, right... thank you, Oracle.

[sasha2002]

Still had no fix?
I renamed 'rank' to crmrank in :
metadata/user_feedsMetaData.php -- Line 46
And install is successful.
Just one non critical error : Mon Jul 30 20:00:50 2018 [49043][1][FATAL] ERROR: rmdir_recursive(): argument cache/themes/SuiteP/modules is not a file or a dir.

[Mausino]

@Dillon-Brown should be this issue which has creaded solution merged in hotfix ??? and assigned to family of issues with milestone label 7.10.12

I see that soon will stable version available https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

[Dillon-Brown]

@Mausino Yes, that PR will need to be code-reviewed + assessed before we can merge though.

[connorshea]

They also added name and description as keywords (non-reserved). Oh, and lead as a reserved keyword :D https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series

[connorshea]

Also, it should maybe be noted that you can keep the same column name by quoting the column name in backticks, per the docs: https://dev.mysql.com/doc/refman/8.0/en/keywords.html

That can lead to weird problems and errors that are unexpected, though, so it may be a better idea to rename the column instead.

[pgorod]

lead as a reserved keyword is bad news for us.

I think we should solve all this in one go, by making sure we use backticks to escape every column name in every query. But unfortunately the current state of our code doesn't let us do that in just one place (somewhere in the database driver). We have tons of scattered SQL all over the code :(

[Abuelodelanada]

Hi @Dillon-Brown

I am having this issue with 7.10.x and MySQL 8.... Did you do #6374 only in 7.8 for some special reason??

Tue Oct  8 16:42:42 2019 [10755][-none-][FATAL] Mysqli_query failed.
Tue Oct  8 16:42:42 2019 [10755][-none-][FATAL] Error creating table: users_feeds Query Failed: CREATE TABLE users_feeds (user_id varchar(36)  NULL ,feed_id varchar(36)  NULL ,rank int  NULL ,date_modified datetime  NULL ,deleted bool  DEFAULT '0' NULL  , KEY idx_ud_user_id (user_id, feed_id)) CHARACTER SET utf8 COLLATE utf8_general_ci: MySQL error 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 'rank int  NULL ,date_modified datetime  NULL ,deleted bool  DEFAULT '0' NULL  , ' at line 1

[appi147]

Try `rank` with quotes, not rank without quotes

[appi147]

CREATE TABLE users_feeds (user_id varchar(36) NULL ,feed_id varchar(36) NULL ,`rank` int NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL , KEY idx_ud_user_id (user_id, feed_id)) CHARACTER SET utf8 COLLATE utf8_general_ci

[Abuelodelanada]

@appi147 Nice workaround!

(jose@localhost) [pepe]► CREATE TABLE users_feeds (user_id varchar(36) NULL ,feed_id varchar(36) NULL ,`rank` int NULL ,date_modified datetime NULL ,deleted bool DEFAULT '0' NULL , KEY idx_ud_user_id (user_id, feed_id)) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected, 2 warnings (1.48 sec)

~ ❯❯❯ mysql --version
mysql  Ver 8.0.17-0ubuntu2 for Linux on x86_64 ((Ubuntu))

@Dillon-Brown I think this PR #8003 is useless if we quote this field when creating the table

[pgorod]

I had mentioned that above...

I think we should solve all this in one go, by making sure we use backticks to escape every column name in every query. But unfortunately the current state of our code doesn't let us do that in just one place (somewhere in the database driver). We have tons of scattered SQL all over the code :(

I guess we _could_ do it in the DBManager, even if we don't cover all cases, it would cover everything that doesn't use direct SQL access and solve the majority of potential problems.

[oc666]

It seems that in the command line that if you quote the field name as rank it will work. Probably need also to quote in the app db layer on db creation table.

[hannenule]

in 2020 and version 7.11.13 the problem is still present.

One way to solve it is to add the following line at
file \SuiteCRM\include\database\MysqlManager.php

$ref['name'] = $this->quoteIdentifier($ref['name']);

open the file, an locate the function oneColumnSQLRep,
and insert before the folowing code:

if ($return_as_array) {
    return $ref;
} else {
    return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
}

And that's it.

In the end the function should look like this:

protected function oneColumnSQLRep($fieldDef, $ignoreRequired = false, $table = '', $return_as_array = false)
{
    // always return as array for post-processing
    $ref = parent::oneColumnSQLRep($fieldDef, $ignoreRequired, $table, true); 

    if ($ref['colType'] == 'int' && !empty($fieldDef['len'])) {
        $ref['colType'] .= "(" . $fieldDef['len'] . ")";
    }
    // bug 22338 - don't set a default value on text or blob fields
    if (isset($ref['default']) &&
        in_array($ref['colBaseType'], array('text', 'blob', 'longtext', 'longblob'))
    ) {
        $ref['default'] = '';
    }

    // Quote the column name (fixes problems with names like 'open', as found in aobh_businesshours)
    $ref['name'] = $this->quoteIdentifier($ref['name']);

    if ($return_as_array) {
        return $ref;
    } else {
        return "{$ref['name']} {$ref['colType']} {$ref['default']} {$ref['required']} {$ref['auto_increment']}";
    }
}

In this way I was able to install correctly.
The software versions are
PHP 7.4.2
SuiteCRM 7.11.13
MySQL 8.0.19

[pgorod]

Thanks, that is the sort of generic fix I was looking for. The currently proposed fix is a breaking change, and solves only one problem, whereas a generic fix like this would solve _all_ problems and would not be a breaking change for any add-ons and customizations out there that depend on the current column names staying stable.

But I do see a potential problem here - if the code is sending you a query that already has quoted column names, won't this add an extra pair of backquotes and break the query?

[hannenule]

good morning.

in theory, there should be no problem because the fix is done in the functions used to dynamically create sql queries.
If you send an already created query I would not use this function.

Also, the developers already used this same solution in the MysqlManager.php file.
In that file find the same function oneColumnSQLRep, from there take the fix and even the comment.

[Abuelodelanada]

Great @hannenule !

Are you going to send a PR with this?

[hannenule]

@Abuelodelanada. Hello, I have already made the pull request.

[mattpr]

Did this resolve the installer failing issue with mysql8? Unfortunately mysql-5.7 is apparently no longer being packaged by Oracle for Ubuntu20 LTS and with 16 at EOL a lot of people are skipping 18 and going to 20.

I'm trying to track down a db-related install failure (no connection or auth problem but installer not running any queries at all). Could be related to a silent failure based on version string and mislabelled as a db connection/auth error?

https://community.suitecrm.com/t/the-provided-database-host-username-and-or-password-is-invalid/78921/4

Update

The issue was caused because mysql-server was using an auth_plugin for users that is not supported by mysqli.
This was revealed by asking mysqli to inform us of errors (@mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);) here.

Would be great if mysqli errors were reported/caught and then at least logged so users can find configuration issues faster (this could also be an issue on mysql-5.7).
After changing the auth_plugin, no more issues with mysql-8 install.

[hannenule]

Hello @mattpr
First I apologize for the delay in responding.
Since you found the cause, you know this fix won't work also this fix is already included in the current versions of the system.
I also appreciate that you shared what you found so that other people can fix their error.
Greetings.

[mattpr]

First I apologize for the delay in responding.
No worries.
Yeah I figured it out. However I wasted a lot of time because the error message was misleading.

I'd definitely recommend catching and logging mysql errors so users can see directly in the logs what the specific issue is. In this case it really had to do with the selected auth_plugin for the mysql user rather than mysql 8 vs 5.7.

Relying on a user having dependencies using specific defaults is a pretty brittle setup.
Alternatively the install guide could explicitly list all the specific requirements (mysql user using auth_plugin X, Y or Z). However keeping on top of every possible configuration would be a nightmare...so better to just actually catch/handle errors from the mysql driver and log them...then it is obvious right away to the user what the problem is...

In my case it would have been a 5 minute fix ("ah, need to set different auth_plugin when I create the mysql user") rather than hours of digging through suitecrm code to add the missing error handling just so I can see what is wrong.

I'd open a pull request but I'm not a php guy and don't know any knock-on effects of adding @mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); before suitecrm tries to connect to mysql.