Database Error Code: Access denied for user 'civi'@'localhost' (using password: YES), 1045

Question

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 |

Answer 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.

Answer 2

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