Installation with MySQL 8.x cause login failling

[daweed38]

If you install TeamPass with a MySQL 8.x database (MySQL 5.7 should no longer be used as support ended in late 2023), you must adjust the sql_mode. MySQL 8.x adheres to SQL standards, particularly those governing the GROUP BY clause. All columns used in the SELECT statement must be included in the GROUP BY clause, which is not the case in TeamPass. From the very first query to identify the user, you risk not being authenticated because of the query below:

SELECT u.*,

a.value AS api_key, a.enabled AS api_enabled, a.allowed_folders as api_allowed_folders, a.allowed_to_create as api_allowed_to_create, a.allowed_to_read as api_allowed_to_read, a.allowed_to_update as api_allowed_to_update, a.allowed_to_delete as api_allowed_to_delete,

GROUP_CONCAT(DISTINCT ug.group_id ORDER BY ug.group_id SEPARATOR ";") AS groupes_visibles,

GROUP_CONCAT(DISTINCT ugf.group_id ORDER BY ugf.group_id SEPARATOR ";") AS forbidden_groups, GROUP_CONCAT(DISTINCT CASE WHEN ur.source = "manual" THEN ur.role_id END ORDER BY ur.role_id SEPARATOR ";") AS function_id, GROUP_CONCAT(DISTINCT CASE WHEN ur.source = "ad" THEN ur.role_id END ORDER BY ur.role_id SEPARATOR ";") AS roles_from_ad_groups, GROUP_CONCAT(DISTINCT uf.item_id ORDER BY uf.created_at SEPARATOR ";") AS favorites, GROUP_CONCAT(DISTINCT ul.item_id ORDER BY ul.accessed_at DESC SEPARATOR ";") AS latest_items FROM tps_users AS u LEFT JOIN tps_api AS a ON (u.id = a.user_id) LEFT JOIN tps_users_groups AS ug ON (u.id = ug.user_id) LEFT JOIN tps_users_groups_forbidden AS ugf ON (u.id = ugf.user_id) LEFT JOIN tps_users_roles AS ur ON (u.id = ur.user_id) LEFT JOIN tps_users_favorites AS uf ON (u.id = uf.user_id) LEFT JOIN tps_users_latest_items AS ul ON (u.id = ul.user_id) WHERE u.login = 'admin' AND u.deleted_at IS NULL GROUP BY u.id

which returns the error

"SELECT list is not in GROUP BY clause and contains nonaggregated column"

This prevents user authentication.

Since MariaDB is more permissive and less "clean" in terms of SQL standards, the problem doesn't occur with an installation using a MariaDB database.

Several options are available to address this issue:

• Correct the queries used in TeamPass so that they comply with SQL standards

or

• Disable the MySQL behavior by setting the sql_mode variable in the MySQL configuration file (my.cnf), for example:

sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'

This point should also be included in the documentation for those who choose to use MySQL > 5.7 (8.x and above).

[chcioni]

You need add NO_ENGINE_SUBSTITUTION option in sql_mode.