1

I encounter a really weird issue. I'm moving my CiviCRM install from an Ubuntu 14.04 server to an Ubuntu 16.04 server. Mysql is now 5.7.21.

CiviCRM version is the latest 4.7.30

The default SQL mode only_full_group_by is disabled.

If I connect using the terminal from the same localhost using the same username/password (I triple triple triple checked), it runs like a charm.

The CiviCRM page always shows a DB Error: unknown error (duh!). Enabling the backtrace gives me more info:

[callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => 
SELECT  v.name as name ,v.value as value, v.grouping as grouping
FROM   civicrm_option_value_en_US v,
       civicrm_option_group_en_US g
WHERE  v.option_group_id = g.id
  AND  g.name            = 'wysiwyg_presets'
  AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviMember","CiviMail","CiviReport")) )  ORDER BY v.weight [nativecode=1045 ** Access denied for user 'civietui'@'localhost' (using password: YES)]

Any help much appreciated

Update

If I deliberately use a wrong username or password, I have a different error:

Initialization Error

It shows that somehow, Civi connects to the DB up to a certain point

update 2

mysql> show grants for username@localhost;

| Grants for username@localhost |

| GRANT USAGE ON . TO 'username'@'localhost' | | GRANT ALL PRIVILEGES ON civicrm_DB.* TO 'username'@'localhost' WITH GRANT OPTION |

  • Two questions: First - are you using different MySQL users for your CMS and CRM? Second - could you edit your question to show the results of SHOW GRANTS FOR <username>? – Jon G - Megaphone Tech Feb 27 '18 at 17:52
  • 1. I do have different users for both databases 2. I've added the result of the query in the 'update 2' part of the initial question. Thanks! – Damien Mar 1 '18 at 13:05
1

MySQL 5.7 has "pluggable authentication", which means that you're not always logging in by password from your terminal. You'll want to ensure that the mysql.user table shows a value of mysql_native_password for all users in the plugin column.

If you're logging in to the terminal as the root user on Ubuntu 16.04, then by default you're not using mysql_native_password, you're using auth_socket. This could lead to misleading results where you can log in from the command line with what SEEMS like a good password! If this is your problem, you may need to install the mysql_native_password plugin before you can set the password.

  • I confirm that all involved users have mysql_native_password in the plugin column of the mysql.user table. That's not it – Damien Feb 27 '18 at 9:09
0

Look at your imported SQL file any reference to the old site mysql user and replace it with your new site user.

CiviCRM 4.6.x has mysql user hardcoded, for exemple:

/*!50013 DEFINER=old_mysql_user@localhost SQL SECURITY DEFINER / /!50003 CREATE*/ /!50017 DEFINER=old_mysql_user@localhost/ /*!50003 TRIGGER civicrm_value_autoritzacions_16_after_update after update ON civicrm_value_autoritzacions_16 FOR EACH ROW BEGIN

This also run in an import SQL error...

After cleaning this I get ride of your error...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.