RBAC DB issue #87
Description
What steps will reproduce the problem?
/** @var CurrentUser $currentUser */
$result = $currentUser->can('permission');What is the expected result?
$result is a boolean
What do you get instead?
Yiisoft\Db\Exception\IntegrityException
Additional info
| Q | A |
|---|---|
| PHP version | 8.3 |
| Operating system | Ubuntu 24.04.1 |
| Database | MariaDb 10.11.8 |
Yii3 packages:
| Package | Version |
|---|---|
| db | 1.3.0 |
| db-mysql | 1.2.0 |
| rbac | 2.0.0 |
| rbac-db | 2.0.0 |
RBAC Hierarchy
User is assigned customer role
permission is a child of customer
Exception
Yiisoft\Db\Exception\IntegrityException
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_bin,NONE) for operation 'trim'
The SQL being executed was: WITH RECURSIVE `parent_of`(`child_name`, `children`) AS ((SELECT `name`, CAST('' AS CHAR(21844)) FROM `rbac_item` WHERE `name`='itemIndex') UNION ALL ( SELECT `parent`, TRIM(',' FROM CONCAT(children, ',', item_child_recursive.child)) FROM `rbac_item_child` `item_child_recursive` INNER JOIN `parent_of` ON `item_child_recursive`.`child`=`parent_of`.`child_name` )) SELECT `item`.*, `parent_of`.`children` FROM `parent_of` LEFT JOIN `rbac_item` `item` ON `item`.`name`=`parent_of`.`child_name`
↵
Caused by: PDOException
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_bin,NONE) for operation 'trim'
in /var/www/loytyi/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php at line 210
The exception is thrown when getting the RBAC hierarchy - getHierarchy() in Yiisoft\Rbac\Db\ItemTreeTraversal\CteItemTreeTraversal
The exception message identifies an illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_bin,NONE) for operation 'trim'. I am not using either of these collations.
I have tried utf8mb4_unicode_520_ci (preferred) and utf8mb4_general_ci at the DB, table, and column level; both give the same result.
Current Workaround
I can get RBAC to work by setting the collation of the child column in the rbac_item_child table to utf8mb4_bin.
If this is a requirement, that's fine, but the requirement should be documented.