Operator does not exist exception when using search field in webinterface and postgresql

[piotrekkr]

I get exception when trying to use "command or args" search field in web interface:

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing 'SELECT j0_.id AS id_0, j0_.state AS state_1, j0_.queue AS queue_2, j0_.priority AS priority_3, j0_.createdAt AS createdat_4, j0_.startedAt AS startedat_5, j0_.checkedAt AS checkedat_6, j0_.workerName AS workername_7, j0_.executeAfter AS executeafter_8, j0_.closedAt AS closedat_9, j0_.command AS command_10, j0_.args AS args_11, j0_.output AS output_12, j0_.errorOutput AS erroroutput_13, j0_.exitCode AS exitcode_14, j0_.maxRuntime AS maxruntime_15, j0_.maxRetries AS maxretries_16, j0_.stackTrace AS stacktrace_17, j0_.runtime AS runtime_18, j0_.memoryUsage AS memoryusage_19, j0_.memoryUsageReal AS memoryusagereal_20, j0_.originalJob_id AS originaljob_id_21 FROM jms_jobs j0_ WHERE j0_.originalJob_id IS NULL AND (j0_.command LIKE ? OR j0_.args LIKE ?) ORDER BY j0_.id DESC LIMIT 51 OFFSET 0' with params ["%test%", "%test%"]:

SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: json ~~ unknown
LINE 1: ...b_id IS NULL AND (j0_.command LIKE $1 OR j0_.args LIKE $2) O...
                                                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:96
at Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception occurred while executing \'SELECT j0_.id AS id_0, j0_.state AS state_1, j0_.queue AS queue_2, j0_.priority AS priority_3, j0_.createdAt AS createdat_4, j0_.startedAt AS startedat_5, j0_.checkedAt AS checkedat_6, j0_.workerName AS workername_7, j0_.executeAfter AS executeafter_8, j0_.closedAt AS closedat_9, j0_.command AS command_10, j0_.args AS args_11, j0_.output AS output_12, j0_.errorOutput AS erroroutput_13, j0_.exitCode AS exitcode_14, j0_.maxRuntime AS maxruntime_15, j0_.maxRetries AS maxretries_16, j0_.stackTrace AS stacktrace_17, j0_.runtime AS runtime_18, j0_.memoryUsage AS memoryusage_19, j0_.memoryUsageReal AS memoryusagereal_20, j0_.originalJob_id AS originaljob_id_21 FROM jms_jobs j0_ WHERE j0_.originalJob_id IS NULL AND (j0_.command LIKE ? OR j0_.args LIKE ?) ORDER BY j0_.id DESC LIMIT 51 OFFSET 0\' with params ["%test%", "%test%"]:SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: json ~~ unknownLINE 1: ...b_id IS NULL AND (j0_.command LIKE $1 OR j0_.args LIKE $2) O...                                                             ^HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.', object(PDOException))
    (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:176)
at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(PDOException), 'An exception occurred while executing \'SELECT j0_.id AS id_0, j0_.state AS state_1, j0_.queue AS queue_2, j0_.priority AS priority_3, j0_.createdAt AS createdat_4, j0_.startedAt AS startedat_5, j0_.checkedAt AS checkedat_6, j0_.workerName AS workername_7, j0_.executeAfter AS executeafter_8, j0_.closedAt AS closedat_9, j0_.command AS command_10, j0_.args AS args_11, j0_.output AS output_12, j0_.errorOutput AS erroroutput_13, j0_.exitCode AS exitcode_14, j0_.maxRuntime AS maxruntime_15, j0_.maxRetries AS maxretries_16, j0_.stackTrace AS stacktrace_17, j0_.runtime AS runtime_18, j0_.memoryUsage AS memoryusage_19, j0_.memoryUsageReal AS memoryusagereal_20, j0_.originalJob_id AS originaljob_id_21 FROM jms_jobs j0_ WHERE j0_.originalJob_id IS NULL AND (j0_.command LIKE ? OR j0_.args LIKE ?) ORDER BY j0_.id DESC LIMIT 51 OFFSET 0\' with params ["%test%", "%test%"]:SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: json ~~ unknownLINE 1: ...b_id IS NULL AND (j0_.command LIKE $1 OR j0_.args LIKE $2) O...                                                             ^HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.')
    (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:150)
at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'SELECT j0_.id AS id_0, j0_.state AS state_1, j0_.queue AS queue_2, j0_.priority AS priority_3, j0_.createdAt AS createdat_4, j0_.startedAt AS startedat_5, j0_.checkedAt AS checkedat_6, j0_.workerName AS workername_7, j0_.executeAfter AS executeafter_8, j0_.closedAt AS closedat_9, j0_.command AS command_10, j0_.args AS args_11, j0_.output AS output_12, j0_.errorOutput AS erroroutput_13, j0_.exitCode AS exitcode_14, j0_.maxRuntime AS maxruntime_15, j0_.maxRetries AS maxretries_16, j0_.stackTrace AS stacktrace_17, j0_.runtime AS runtime_18, j0_.memoryUsage AS memoryusage_19, j0_.memoryUsageReal AS memoryusagereal_20, j0_.originalJob_id AS originaljob_id_21 FROM jms_jobs j0_ WHERE j0_.originalJob_id IS NULL AND (j0_.command LIKE ? OR j0_.args LIKE ?) ORDER BY j0_.id DESC LIMIT 51 OFFSET 0', array('%test%', '%test%'))
    (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:928)
at Doctrine\DBAL\Connection->executeQuery('SELECT j0_.id AS id_0, j0_.state AS state_1, j0_.queue AS queue_2, j0_.priority AS priority_3, j0_.createdAt AS createdat_4, j0_.startedAt AS startedat_5, j0_.checkedAt AS checkedat_6, j0_.workerName AS workername_7, j0_.executeAfter AS executeafter_8, j0_.closedAt AS closedat_9, j0_.command AS command_10, j0_.args AS args_11, j0_.output AS output_12, j0_.errorOutput AS erroroutput_13, j0_.exitCode AS exitcode_14, j0_.maxRuntime AS maxruntime_15, j0_.maxRetries AS maxretries_16, j0_.stackTrace AS stacktrace_17, j0_.runtime AS runtime_18, j0_.memoryUsage AS memoryusage_19, j0_.memoryUsageReal AS memoryusagereal_20, j0_.originalJob_id AS originaljob_id_21 FROM jms_jobs j0_ WHERE j0_.originalJob_id IS NULL AND (j0_.command LIKE ? OR j0_.args LIKE ?) ORDER BY j0_.id DESC LIMIT 51 OFFSET 0', array('%test%', '%test%'), array(2, 2), null)
    (vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php:50)
at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array('%test%', '%test%'), array(2, 2))
    (vendor/doctrine/orm/lib/Doctrine/ORM/Query.php:321)
at Doctrine\ORM\Query->_doExecute()
    (vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php:962)
at Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(null, 1)
    (vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php:917)
at Doctrine\ORM\AbstractQuery->execute(null, 1)
    (vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php:720)
at Doctrine\ORM\AbstractQuery->getResult()
    (vendor/jms/job-queue-bundle/JMS/JobQueueBundle/Controller/JobController.php:64)
at JMS\JobQueueBundle\Controller\JobController->overviewAction(object(Request))
at call_user_func_array(array(object(JobController), 'overviewAction'), array(object(Request)))
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:153)
at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php:68)
at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php:169)
at Symfony\Component\HttpKernel\Kernel->handle(object(Request), 1, true)
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpCache/HttpCache.php:460)
at Symfony\Component\HttpKernel\HttpCache\HttpCache->forward(object(Request), true, null)
    (vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/HttpCache/HttpCache.php:57)
at Symfony\Bundle\FrameworkBundle\HttpCache\HttpCache->forward(object(Request), true)
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpCache/HttpCache.php:414)
at Symfony\Component\HttpKernel\HttpCache\HttpCache->fetch(object(Request), true)
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpCache/HttpCache.php:311)
at Symfony\Component\HttpKernel\HttpCache\HttpCache->lookup(object(Request), true)
    (vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpCache/HttpCache.php:186)
at Symfony\Component\HttpKernel\HttpCache\HttpCache->handle(object(Request))
    (web/app_dev.php:53)

It seems that it cannot use LIKE with json field in postgresql and in Job entity there is json_array (which is deprecated in doctrine) as args column type. I'm using doctrine 2.6 and postgresql 10.

[scaytrase]

@piotrekkr we have the same isuse. You have to override the Job mappings to be

args:
  type: json
  options:
      jsonb: true

and migrate the table field to be JSONB in order to make the bundle work

[piotrekkr]

@scaytrase I'm not really sure how to overwrite those mappings since they are in docblocks in JMS\JobQueueBundle\Entity\Job. Any suggestions how to do this without changing vendor code?

[scaytrase]

As a simple way (since the bundle releases not so often) you can just convert annotation mapping into yaml one using console command, fix and configure it instead of original via mapping config

symfony/symfony-docs#7076