Issue with rbac-db

Yeti · December 10, 2024, 7:03pm

Hi,
I’m trying to use the rbac and rbac-db packages for authorization and I’m getting an error I can’t figure out. Please let me know if this an issue with my database or Yii.

There are currently two items:

customer (role)
userView (permission)

userView is the child of customer

The error I am getting is:

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:210
Stack trace:
#0 /var/www/loytyi/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php(210): PDOStatement->execute()
#1 /var/www/loytyi/vendor/yiisoft/db/src/Command/AbstractCommand.php(580): Yiisoft\Db\Driver\Pdo\AbstractPdoCommand->internalExecute()
#2 /var/www/loytyi/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php(285): Yiisoft\Db\Command\AbstractCommand->queryInternal()
#3 /var/www/loytyi/vendor/yiisoft/db/src/Command/AbstractCommand.php(434): Yiisoft\Db\Driver\Pdo\AbstractPdoCommand->queryInternal()
#4 /var/www/loytyi/vendor/yiisoft/db/src/Query/Query.php(225): Yiisoft\Db\Command\AbstractCommand->queryAll()
#5 /var/www/loytyi/vendor/yiisoft/rbac-db/src/ItemTreeTraversal/CteItemTreeTraversal.php(80): Yiisoft\Db\Query\Query->all()
#6 /var/www/loytyi/vendor/yiisoft/rbac-db/src/ItemsStorage.php(293): Yiisoft\Rbac\Db\ItemTreeTraversal\CteItemTreeTraversal->getHierarchy()
#7 /var/www/loytyi/vendor/yiisoft/rbac/src/Manager.php(72): Yiisoft\Rbac\Db\ItemsStorage->getHierarchy()
#8 /var/www/loytyi/vendor/yiisoft/user/src/CurrentUser.php(170): Yiisoft\Rbac\Manager->userHasPermission()
#9 /var/www/loytyi/src/User/UserController.php(30): Yiisoft\User\CurrentUser->can()
#10 [internal function]: App\User\UserController->view()
#11 /var/www/loytyi/vendor/yiisoft/injector/src/Injector.php(86): ReflectionFunction->invokeArgs()
#12 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareFactory.php(271): Yiisoft\Injector\Injector->invoke()
#13 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Psr\Http\Server\MiddlewareInterface@anonymous->process()
#14 /var/www/loytyi/vendor/yiisoft/data-response/src/Middleware/FormatDataResponse.php(27): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#15 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\DataResponse\Middleware\FormatDataResponse->process()
#16 /var/www/loytyi/vendor/yiisoft/csrf/src/CsrfMiddleware.php(55): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#17 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Csrf\CsrfMiddleware->process()
#18 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(49): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#19 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareDispatcher.php(48): Yiisoft\Middleware\Dispatcher\MiddlewareStack->handle()
#20 /var/www/loytyi/vendor/yiisoft/router/src/MatchingResult.php(112): Yiisoft\Middleware\Dispatcher\MiddlewareDispatcher->dispatch()
#21 /var/www/loytyi/vendor/yiisoft/router/src/Middleware/Router.php(59): Yiisoft\Router\MatchingResult->process()
#22 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Router\Middleware\Router->process()
#23 /var/www/loytyi/vendor/yiisoft/user/src/Login/Cookie/CookieLoginMiddleware.php(60): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#24 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\User\Login\Cookie\CookieLoginMiddleware->process()
#25 /var/www/loytyi/vendor/yiisoft/cookies/src/CookieMiddleware.php(84): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#26 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Cookies\CookieMiddleware->process()
#27 /var/www/loytyi/vendor/yiisoft/session/src/SessionMiddleware.php(33): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#28 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Session\SessionMiddleware->process()
#29 /var/www/loytyi/vendor/yiisoft/error-handler/src/Middleware/ErrorCatcher.php(133): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#30 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\ErrorHandler\Middleware\ErrorCatcher->process()
#31 /var/www/loytyi/vendor/yiisoft/request-provider/src/RequestCatcherMiddleware.php(29): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#32 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\RequestProvider\RequestCatcherMiddleware->process()
#33 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(49): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#34 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareDispatcher.php(48): Yiisoft\Middleware\Dispatcher\MiddlewareStack->handle()
#35 /var/www/loytyi/vendor/yiisoft/yii-http/src/Application.php(77): Yiisoft\Middleware\Dispatcher\MiddlewareDispatcher->dispatch()
#36 /var/www/loytyi/vendor/yiisoft/yii-runner-http/src/HttpApplicationRunner.php(153): Yiisoft\Yii\Http\Application->handle()
#37 /var/www/loytyi/public/index.php(42): Yiisoft\Yii\Runner\Http\HttpApplicationRunner->run()
#38 {main}

Next 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`='userView') 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` in /var/www/loytyi/vendor/yiisoft/db/src/Exception/ConvertException.php:42
Stack trace:
#0 /var/www/loytyi/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php(215): Yiisoft\Db\Exception\ConvertException->run()
#1 /var/www/loytyi/vendor/yiisoft/db/src/Command/AbstractCommand.php(580): Yiisoft\Db\Driver\Pdo\AbstractPdoCommand->internalExecute()
#2 /var/www/loytyi/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php(285): Yiisoft\Db\Command\AbstractCommand->queryInternal()
#3 /var/www/loytyi/vendor/yiisoft/db/src/Command/AbstractCommand.php(434): Yiisoft\Db\Driver\Pdo\AbstractPdoCommand->queryInternal()
#4 /var/www/loytyi/vendor/yiisoft/db/src/Query/Query.php(225): Yiisoft\Db\Command\AbstractCommand->queryAll()
#5 /var/www/loytyi/vendor/yiisoft/rbac-db/src/ItemTreeTraversal/CteItemTreeTraversal.php(80): Yiisoft\Db\Query\Query->all()
#6 /var/www/loytyi/vendor/yiisoft/rbac-db/src/ItemsStorage.php(293): Yiisoft\Rbac\Db\ItemTreeTraversal\CteItemTreeTraversal->getHierarchy()
#7 /var/www/loytyi/vendor/yiisoft/rbac/src/Manager.php(72): Yiisoft\Rbac\Db\ItemsStorage->getHierarchy()
#8 /var/www/loytyi/vendor/yiisoft/user/src/CurrentUser.php(170): Yiisoft\Rbac\Manager->userHasPermission()
#9 /var/www/loytyi/src/User/UserController.php(30): Yiisoft\User\CurrentUser->can()
#10 [internal function]: App\User\UserController->view()
#11 /var/www/loytyi/vendor/yiisoft/injector/src/Injector.php(86): ReflectionFunction->invokeArgs()
#12 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareFactory.php(271): Yiisoft\Injector\Injector->invoke()
#13 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Psr\Http\Server\MiddlewareInterface@anonymous->process()
#14 /var/www/loytyi/vendor/yiisoft/data-response/src/Middleware/FormatDataResponse.php(27): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#15 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\DataResponse\Middleware\FormatDataResponse->process()
#16 /var/www/loytyi/vendor/yiisoft/csrf/src/CsrfMiddleware.php(55): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#17 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Csrf\CsrfMiddleware->process()
#18 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(49): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#19 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareDispatcher.php(48): Yiisoft\Middleware\Dispatcher\MiddlewareStack->handle()
#20 /var/www/loytyi/vendor/yiisoft/router/src/MatchingResult.php(112): Yiisoft\Middleware\Dispatcher\MiddlewareDispatcher->dispatch()
#21 /var/www/loytyi/vendor/yiisoft/router/src/Middleware/Router.php(59): Yiisoft\Router\MatchingResult->process()
#22 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Router\Middleware\Router->process()
#23 /var/www/loytyi/vendor/yiisoft/user/src/Login/Cookie/CookieLoginMiddleware.php(60): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#24 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\User\Login\Cookie\CookieLoginMiddleware->process()
#25 /var/www/loytyi/vendor/yiisoft/cookies/src/CookieMiddleware.php(84): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#26 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Cookies\CookieMiddleware->process()
#27 /var/www/loytyi/vendor/yiisoft/session/src/SessionMiddleware.php(33): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#28 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\Session\SessionMiddleware->process()
#29 /var/www/loytyi/vendor/yiisoft/error-handler/src/Middleware/ErrorCatcher.php(133): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#30 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\ErrorHandler\Middleware\ErrorCatcher->process()
#31 /var/www/loytyi/vendor/yiisoft/request-provider/src/RequestCatcherMiddleware.php(29): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#32 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(93): Yiisoft\RequestProvider\RequestCatcherMiddleware->process()
#33 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareStack.php(49): Psr\Http\Server\RequestHandlerInterface@anonymous->handle()
#34 /var/www/loytyi/vendor/yiisoft/middleware-dispatcher/src/MiddlewareDispatcher.php(48): Yiisoft\Middleware\Dispatcher\MiddlewareStack->handle()
#35 /var/www/loytyi/vendor/yiisoft/yii-http/src/Application.php(77): Yiisoft\Middleware\Dispatcher\MiddlewareDispatcher->dispatch()
#36 /var/www/loytyi/vendor/yiisoft/yii-runner-http/src/HttpApplicationRunner.php(153): Yiisoft\Yii\Http\Application->handle()
#37 /var/www/loytyi/public/index.php(42): Yiisoft\Yii\Runner\Http\HttpApplicationRunner->run()
#38 {main}
Additional Information:
Array
(
    [0] => HY000
    [1] => 1267
    [2] => Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_bin,NONE) for operation 'trim'
)

The issue seems to be an illegal mix of collations; it is in the CTE query used to build the item hierarchy. I am using utf8mb4_general_ci, but the error includes a reference to utf8mb4_bin.

I am using:

PHP 8.3
MariaDb 10.11.8
Ubuntu 24.04.1

Yii3 packages:

db 1.3.0
db-mysql 1.2.0
rbac 2.0.0
rbac-db 2.0.0

The SQL for the RBAC item and item_child tables is:

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `rbac_item`;
CREATE TABLE `rbac_item` (
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `rule_name` varchar(255) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `updated_at` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `rbac_item_child`;
CREATE TABLE `rbac_item_child` (
  `parent` varchar(255) NOT NULL,
  `child` varchar(255) NOT NULL,
  PRIMARY KEY (`parent`,`child`),
  KEY `child` (`child`),
  CONSTRAINT `rbac_item_child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `rbac_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `rbac_item_child_ibfk_2` FOREIGN KEY (`child`) REFERENCES `rbac_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `rbac_item_child` (`parent`, `child`) VALUES
('customer',	'userView');

Thanks in advance

JQL · December 11, 2024, 8:15pm

The problem could be the utf8mb4_unicode_ci collation. Use utf8_unicode_ci

Instead of collate on each table use:

  public function safeUp()
  {
    $tableOptions = null;
    if ($this->db->driverName === 'mysql')
    {
      // https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
      $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
    }
...

Yeti · December 23, 2024, 1:22pm

I’ve done some more investigation on this and found a solution.

@JQL, it is collation that’s the issue, but not the solution you suggest. As a quick aside to that, MariaDb does not does not directly support utf8_unicode_ci (https://mariadb.com/kb/en/supported-character-sets-and-collations/) ; to use it requires setting OLD_MODE. It also turns out that utf8mb4_unicode_ci has been superseded utf8mb4_unicode_520_ci, which I am now using.

The Solution
I’ve found that RBAC works if collation for the child field in the rbac_item_child table is utf8mb4_bin.

Just for clarity, it is only child field in the rbac_item_child table using utf8mb4_bin; everything else, including the parent field in the rbac_item_child table, is using utf8mb4_unicode_520_ci