SuiteCRM connection to MariaDB

Keywords: SuiteCRM - Google Cloud Platform - How to - Other
Description:
So I have my credentials from MariaDB and have gone in and changed the config file to use the host, credentials, set SSL = true and uploaded the pem file to the server and have it within the dbconfig options. I have tested connectivity from a linux instance through GCP and could authenticate back to Maria there, but I’m still getting a “cannot connect to the db” error. Am I missing something? Any suggestions?

Hi @jkortus

Thanks for using Bitnami SuiteCRM!

I have tested connectivity from a linux instance through GCP and could authenticate back to Maria there

  1. I would test connectivity from the same instance SuiteCRM is running on to the MariaDB server to discard the issue lies in both machines being unable to communicate.

  2. Once you have confirmed that (or if you did already), you may need to check if the version of MariaDB is compatible with the SuiteCRM version you are running:

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

Could you please confirm that?

Additionally, we have a Support Tool that will gather relevant information for us to analyze your configuration and logs. Could you please execute it on the machine where the stack is running by following the steps described in the guide below?

Please note that you need to paste the code ID that is shown at the end.

Best regards,
Jose Antonio Carmona


Was my answer helpful? Click on :heart:

Hello Jose,

Thank you for the detailed walk through!
I have already confirmed connectivity to MariaDB outside of the SuiteCRM instance. I created a new Linux VM that was able to connect from the same location, also had a php file installed in the SuiteCRM root folder that was able to confirm connectivity back to MariaDB. Just keep running into a wall with the SuiteCRM connecting. I think we’ve narrowed it down to potentially the MySQLiManager configurations, but we’re testing through those as well. Likely related to the SSL/pem file.

I ran the bitnami support tool. Code is d691c5f2-0529-a67d-4823-d91580aa59fb

Thank you again for all of the help!

I have tried to reproduce the issue by deploying both Bitnami SuiteCRM and Bitnami MariaDB (version 10.3) instances.

According to the compatibility matrix I mentioned before, MariaDB 10.3 is said to be the most recent version this app works with (assert that the one you are trying to use is compatible too :slight_smile: ). I was able to change the settings without problems and both instances are working together now.

In order to do that, I simply updated the /opt/bitnami/suitecrm/config.php options referring to the DB:

...
'dbconfig' => 
  array (
    'db_host_name' => '52.XX.XX.XXX',
    'db_host_instance' => 'SQLEXPRESS',
    'db_user_name' => 'my_user',
    'db_password' => 'my_pass',
    'db_name' => 'my_suitecrm',
    'db_type' => 'mysql',
    'db_port' => '',
    'db_manager' => 'MysqliManager',
    'collation' => 'utf8_general_ci',
    'charset' => 'utf8',
  ),
...

Could you please take a look and share the contents of the /opt/bitnami/suitecrm/suitecrm.log file? There you should be able to see a more detailed list of errors the application is experiencing

Best regards,
Jose Antonio Carmona


Was my answer helpful? Click on :heart:

Hi Jose,

So my config file matches that with our host, user, password, port, etc configured correctly. However the problem it seems is related to the SSL / pem file. I have also added in
ssl => true and the path to the pem file (uploaded to the server) under dbconfigoption but we are still running into the same issue of “can’t connect to the database”. I have also gone into the MysqliManager.php file and made changes there related to SSL configurations but still no luck. Is it possible still that there is an issue on the MariaDB side that I’m not able to see? Everything in this config file seems to be pointing at the correct settings.

Hi @jkortus

Is it possible still that there is an issue on the MariaDB side that I’m not able to see?

I highly doubt that this lies on the MariaDB side. In case this is a software error, I’d point to SuiteCRM or the PHP that handles the connection.

In any case, could you please take a look and share the contents of the /opt/bitnami/suitecrm/suitecrm.log file? There you should be able to see a more detailed list of errors the application is experiencing and it could give us more clues :slightly_smiling_face:

Best regards,
Jose Antonio Carmona


Was my answer helpful? Click on :heart:

Hello Jose,

So I believe I was able to get the configuration set up. Required some changes in the MysqliManager.php file as well. However now I’m having an issue actually getting logged in. I am able to get to the login screen, ran a backup/restore of the SuiteCRM database to Maria… but none of the users/passwords/emails are being accepted.

I did post a new post on this one:
https://stackoverflow.com/questions/67259516/next-issue-suitecrm

Hi @jkortus

As you have modified the original code for the MysqliManager.php the issues you are experiencing might be related to that. If you believe those changes are needed for the external DB to work, I’d reach out to the developers and create a new issue so as they can review or provide further technical assistance.

Again, I think the /opt/bitnami/suitecrm/suitecrm.log comes very handy in here as it provides hints of what is happening behind the scenes, and they will more than likely ask for it too. Without this information, I cannot differentiate either if this is an issue connecting to the database or not.

On my side, I have re-launched both a new Bitnami MariaDB 10.3 and a SuiteCRM one. I have modified the aforementioned files to point to the external DB and performed a backup/restoration process so as to have the exact same information. The restoration has been performed using a brand new user, password and database name, but the data should be the same. I can log into the site and navigate and it seems to be working:

Best regards,
Jose Antonio Carmona


Was my answer helpful? Click on :heart:

Hey Jose,

I am starting to wonder if the changes that were made to the iManager file are causing the issue. What developer are you suggesting I reach out to in order to discuss further and go over these changes?

For your back up, can you do a quick review on the steps you took to backup/restore yours? The external DB through Maria that I’m using also uses an SSL (pem file) and port 5001 instead of the standard SQL port so it seemed to cause further issues.

Looking over the log files it does look like none of the SQL are executing which further makes me think that the DB isn’t connected properly as well.

 Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Retrieve User : SELECT users.* FROM users  WHERE users.id = '' AND users.deleted=0
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Limit Query:SELECT users.* FROM users  WHERE users.id = '' AND users.deleted=0 Start: 0 count: 1
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query:SELECT users.* FROM users  WHERE users.id = '' AND users.deleted=0 LIMIT 0,1
Tue Apr 27 13:53:01 2021 [30017][-none-][FATAL] Mysqli_query failed.
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query Execution Time:2.6941299438477E-5
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Retrieve User: SELECT users.* FROM users  WHERE status = '' AND is_admin = '' AND deleted=0
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Limit Query:SELECT users.* FROM users  WHERE status = '' AND is_admin = '' AND deleted=0 Start: 0 count: 1
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query:SELECT users.* FROM users  WHERE status = '' AND is_admin = '' AND deleted=0 LIMIT 0,1
Tue Apr 27 13:53:01 2021 [30017][-none-][FATAL] Mysqli_query failed.
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query Execution Time:2.9802322387695E-5
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] --------------------------------------------> at cron.php <--------------------------------------------
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Using SugarCronJobs as CRON driver
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query:SELECT id FROM job_queue WHERE status='running' AND date_modified <= ''
Tue Apr 27 13:53:01 2021 [30017][-none-][FATAL] Mysqli_query failed.
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query Execution Time:3.504753112793E-5
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] get_full_list:  order_by = '' and where = 'schedulers.status='Active' AND NOT EXISTS(SELECT id FROM job_queue WHERE scheduler_id=schedulers.id AND status!='done')'
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] SugarBean[Scheduler].load_relationships, Loading relationship (created_by_link).
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] SugarBean[Scheduler].load_relationships, Loading relationship (modified_user_link).
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] process_full_list_query: query is  SELECT  schedulers.*  , jt0.user_name created_by_name , jt0.created_by created_by_name_owner  , 'Users' created_by_name_mod , jt1.user_name modified_by_name , jt1.created_by modified_by_name_owner  , 'Users' modified_by_name_mod FROM schedulers   LEFT JOIN  users jt0 ON jt0.id=schedulers.created_by AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  users jt1 ON schedulers.modified_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 where (schedulers.status='Active' AND NOT EXISTS(SELECT id FROM job_queue WHERE scheduler_id=schedulers.id AND status!='done')) AND schedulers.deleted=0
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query: SELECT  schedulers.*  , jt0.user_name created_by_name , jt0.created_by created_by_name_owner  , 'Users' created_by_name_mod , jt1.user_name modified_by_name , jt1.created_by modified_by_name_owner  , 'Users' modified_by_name_mod FROM schedulers   LEFT JOIN  users jt0 ON jt0.id=schedulers.created_by AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  users jt1 ON schedulers.modified_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 where (schedulers.status='Active' AND NOT EXISTS(SELECT id FROM job_queue WHERE scheduler_id=schedulers.id AND status!='done')) AND schedulers.deleted=0
Tue Apr 27 13:53:01 2021 [30017][-none-][FATAL] Mysqli_query failed.
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query Execution Time:4.4107437133789E-5
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] process_full_list_query: result is 
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] -----> Scheduler found [ 0 ] ACTIVE jobs
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] ----->No Schedulers found
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Get One: |SELECT id FROM job_queue WHERE execute_time <= '' AND status = 'queued' ORDER BY date_entered ASC|
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Limit Query:SELECT id FROM job_queue WHERE execute_time <= '' AND status = 'queued' ORDER BY date_entered ASC Start: 0 count: 1
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query:SELECT id FROM job_queue WHERE execute_time <= '' AND status = 'queued' ORDER BY date_entered ASC LIMIT 0,1
Tue Apr 27 13:53:01 2021 [30017][-none-][FATAL] Mysqli_query failed.
Tue Apr 27 13:53:01 2021 [30017][-none-][INFO] Query Execution Time:1.8835067749023E-5
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Hook called: ::server_round_trip
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Calling MySQLi::disconnect()
Tue Apr 27 13:53:01 2021 [30017][-none-][DEBUG] Calling MySQLi::disconnect()

Hi @jkortus

I am starting to wonder if the changes that were made to the iManager file are causing the issue. What developer are you suggesting I reach out to in order to discuss further and go over these changes?

I am talking about SuiteCRM. According to this piece of official documentation, you should reach out to the official GitHub page and create a new issue if you think you have discovered a new bug:

https://github.com/salesagility/SuiteCRM/issues

For your back up, can you do a quick review on the steps you took to backup/restore yours? The external DB through Maria that I’m using also uses an SSL (pem file) and port 5001 instead of the standard SQL port so it seemed to cause further issues.

The process I followed is the one documented in the Bitnami guides:

https://docs.bitnami.com/google/apps/suitecrm/administration/backup-restore-mysql-mariadb/

I’d suggest you restore the changes you have performed to the MysqliManager.php file and try to connect both instance without SSL first. Once you’ve done that, you can proceed investigating about the SSL configuration.

Additionally, as you have modified the MysqliManager.php file (the ones that handles the connections), we can’t be sure if those errors in the log are actually caused by your modifications.

Best regards,
Jose Antonio Carmona


Was my answer helpful? Click on :heart:

1 Like

Hey Jose,

I was able to get it to connect finally yesterday!!! Most of it was actually related to restoring to an older version of the MysqliManager.php file as you suggested so you were certainly on the correct path. Now onto all the fun of rebuilding our current system into SuiteCRM!

Thank you for all of the help.

I am very glad to see you were able to solve your issue! We are marking the previous answer as “Solution” and this topic as “Closed”.

If you have any other questions, please do not hesitate to let us know. Feel free to create a new topic referencing this one if necessary.

Best regards,
Jose Antonio Carmona


Was my answer helpful? Click on :heart: