Update to 7.11.10 chokes on MSSQL

SuiteCRM Forum - English Language SuiteCRM Feedback
1

Lately I received a database error while updating to 7.11.10. This is caused by the definition of “idx_user_name” in vardefs.php which contains column specifications ‘last_name (30)’ and ‘first_name (30)’. Partial indexes like this are specific to mysql and cause errors on other DBMS. Please remove this for future versions.

Speaking of mysql specifics, here are two more I came across:

In modules/Project/controller.php line 57 reads:

 $query = "SELECT max(date_finish) + INTERVAL " . (30 - $duration) . " DAY FROM project_task WHERE project_id = '{$project->id}'";

Which causes an error on MSSQL. Since there is a DBMS abstraction class I think it should read:

$query = "SELECT ". $db->convert("max(date_finish)", "add_date", (30 - $duration), "DAY") . " FROM project_task WHERE project_id = '{$project->id}'";

In modules/InboundEmail/Save.php line 222 reads:

$foldersFound = $focus->db->query('SELECT id FROM folders WHERE folders.id LIKE "'.$focus->id.'"');

In MSSQL only single quotes are accepted as string delimiters. Since this is specified in the standard single quotes should be accepted by all other DBMS as well so I suggest changing that line to:

$foldersFound = $focus->db->query('SELECT id FROM folders WHERE folders.id LIKE \''.$focus->id.'\'');

Edit: Typo “max(date_finish” corrected.

2

Hi @gerb42, thanks for those tips. We need more feedback from people using MSSQL, it’s quite valuable.

Have you checked GitHub to see if there are Issues open for this?

3

Hi @pgr, a quick skim over the issues in GitHub only shows this issue related to double quotes. However this may occur at several locations.

4

Can you please open an Issue (or several ones)? That Issue wasn’t reproducible (for some reason not quite clear to me) and didn’t go anywhere.

But if there are new Issues with complete technical information, and preferably with a PR to fix them, then it’s more likely this will ever get fixed.

Are these all new issues, you only got them after the upgrade? Which version did you have before?

5

These issues are not new. Right now we updated from 7.11.8 but I already had fixes applied locally before that version. We upgraded from Sugar 6.5 last year and I think the first version we really had in use was 7.11.4 or 7.11.5. I’ll open issues for the first two items since they are the most annoying.

1 Like