Facing issue after migration from mysql to postgressql

We have successfully migrated database from mysql to postgress but we are getting error in logs as

24-Jun-2020 13:06:19.843 SEVERE [Thread-6] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 537
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Job.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
### The error occurred while setting parameters
### SQL: select       RES.*      from ACT_RU_JOB RES      where (RES.RETRIES_ > 0)       and (                   RES.DUEDATE_ is null or                   RES.DUEDATE_ <= ?       )       and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)       and RES.SUSPENSION_STATE_ = 1                  and (RES.DEPLOYMENT_ID_ is null                     or                                                  ( RES.DEPLOYMENT_ID_ IN   (      ?      ,         ?        )   )                           )                a24-Jun-2020 13:06:19.843 SEVERE [Thread-6] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 537
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Job.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
### The error occurred while setting parameters
### SQL: select       RES.*      from ACT_RU_JOB RES      where (RES.RETRIES_ > 0)       and (                   RES.DUEDATE_ is null or                   RES.DUEDATE_ <= ?       )       and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)       and RES.SUSPENSION_STATE_ = 1                  and (RES.DEPLOYMENT_ID_ is null                     or                                                  ( RES.DEPLOYMENT_ID_ IN   (      ?      ,         ?        )   )                           )                and (      (      RES.EXCLUSIVE_ = true           and not exists(             select J2.* from ACT_RU_JOB J2             where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_                                           -- from the same proc. inst.             and (J2.EXCLUSIVE_ = true)                                                              -- also exclusive             and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?)  -- in progress             )    )     or           RES.EXCLUSIVE_ = false        )            LIMIT ? OFFSET ?
### Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 537
 org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 537
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Job.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
### The error occurred while setting parameters
### SQL: select       RES.*      from ACT_RU_JOB RES      where (RES.RETRIES_ > 0)       and (                   RES.DUEDATE_ is null or                   RES.DUEDATE_ <= ?       )       and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ < ?)       and RES.SUSPENSION_STATE_ = 1                  and (RES.DEPLOYMENT_ID_ is null                     or                                                  ( RES.DEPLOYMENT_ID_ IN   (      ?      ,         ?        )   )                           )                and (      (      RES.EXCLUSIVE_ = true           and not exists(             select J2.* from ACT_RU_JOB J2             where J2.PROCESS_INSTANCE_ID_ = RES.PROCESS_INSTANCE_ID_                                           -- from the same proc. inst.             and (J2.EXCLUSIVE_ = true)                                                              -- also exclusive             and (J2.LOCK_OWNER_ is not null and J2.LOCK_EXP_TIME_ >= ?)  -- in progress             )    )     or           RES.EXCLUSIVE_ = false        )            LIMIT ? OFFSET ?
### Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = boolean
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 537
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:97)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:184)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:176)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:172)
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:161)
at org.camunda.bpm.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:197)
at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:60)
at org.camunda.bpm.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:38)
at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28)
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:107)
at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70)
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.acquireJobs(SequentialJobAcquisitionRunnable.java:158)
at org.camunda.bpm.engine.impl.jobexecutor.SequentialJobAcquisitionRunnable.run(SequentialJobAcquisitionRunnable.java:76)
at java.lang.Thread.run(Thread.java:748)

Pls help into this

Hi @Tanmay_Naik,

from the error message, the engine runs into trouble at this piece of the SQL statement: RES.EXCLUSIVE_ = true.

A rough guess from my side: doublecheck how both databases handle boolean values. Maybe they have been corrupted during migration?

Hope this helps, Ingo

@Ingo_Richtsmeier Ok it can be. Need your help in migration from mysql to postgress without corrupted. We had used Dbeaver IDE to directly migrate. We tried with command line also but were facing issue with this ` sign on DDL Commands. So hence we did with Dbeaver software. we also tried to replace the sign but still the same