CRM installation on mySQL DB

SuiteCRM Forum - English Language Installation & Upgrade Help
1

The installation fails on step 2 while creating a data base table called user_feeds .

" 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 "

The reserved word ‘rank’ used as a column name might be the cause for this sql exception and therefore the installation failed . Is there a way to circumvent this situation to complete the installation ?

2

I searched the code and I can’t find any place where that field is used.

If you can run a test for us, please change that field name to something else here

metadata/user_feedsMetaData.php
Line 46

… and restart the installation. Tell us how it went. Thanks

3

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 it is letting me login to the application. And I think it is good enough now. Thanks for the tip.

4

Please tell me what is your version of MySQL? I need to get this fixed for everyone.

5

MySql community server - GPL 8.0.11

6

Ok, so that explains why you are the first person to get that error. That is a very new version of MySQL.

It is also an unsupported version (for now) in SuiteCRM:

https://docs.suitecrm.com/admin/compatibility-matrix/

Anyway I’ll open this issue on GitHub so we can start fixing things for future compatibility.

If you run into more database problems you should downgrade MySQL to a supported version.

1 Like
7

How to solve this issue for custom modules? Do you still need to downgrade mysql version or it has been fixed?

8

Hello! I am trying to do an installation on my development machine and I am facing the same error.
Even though my package versions are in accordance with the compatibility matrix.

  • MySQL 8.0.19.x
  • PHP 7.3.x
  • Apache 2.4.41

Even changing the default behavior of MySQL authentication I was unable to complete the installation successfully.

CREATE USER ‘suitecrm’ @ ‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘XXXX’;

Log content:

root@populoso /v/w/h/suitecrm# cat suitecrm.log
Fri Jan 31 19:47:41 2020 [29530][-none-][FATAL] Mysqli_query failed.
Fri Jan 31 19:47:41 2020 [29530][-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
Fri Jan 31 19:52:45 2020 [29530][-none-][FATAL] Mysqli_query failed.
Fri Jan 31 19:52:45 2020 [29530][-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

9

MySQL v8 is not in the Compatibility matrix…

There’s an issue here for future work on that error you mention:

10

Anyone wanting a workaround. The field name has to be in backticks
include\database\DBManager.php
Look for the method oneColumnSQLRep

replace
$name = $fieldDef[‘name’]
$name = “’”.$fieldDef[‘name’]."’";

^^formatting doesnt let me show backticks but you get the idea…

You’ll then hit a problem where primary key (id) cannot be null so edit the bit below where id should be not null but add the backticks to id so it matches the new field name…

11

What worked for me was what compsmart showed. find–

$name = $fieldDef[‘name’]
Replace with $name = $fieldDef[XX’name’XX];
Substituting the XX for a backtick.

I was stuck at 7.7.8 and the update wouldn’t work. MySQL 8.0.22