[Bug] [dinky_task]Startup fails when using pg，Cause: org.postgresql.util.PSQLException: ERROR: column "enabled" is of type smallint but expression is of type boolean

[tchivs]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

I just configured the database as PG and initialized it, and got an error after starting it.

What you expected to happen

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
[dinky] 2024-04-24 11:34:32 CST ERROR org.springframework.boot.SpringApplication 821 reportFailure - Application run failed java.lang.IllegalStateException: Failed to execute ApplicationRunner
        at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:762) ~[spring-boot-2.7.11.jar:2.7.11]
        at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:749) ~[spring-boot-2.7.11.jar:2.7.11]
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:314) ~[spring-boot-2.7.11.jar:2.7.11]
        at org.dinky.Dinky.main(Dinky.java:47) ~[dinky-admin-1.0.1.jar:?]
Caused by: org.dinky.data.exception.DinkyException: org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column "enabled" is of type smallint but expression is of type boolean
  Hint: You will need to rewrite or cast the expression.
  Position: 167
### The error may exist in org/dinky/mapper/TaskMapper.java (best guess)
### The error may involve org.dinky.mapper.TaskMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into dinky_task (dialect, tenant_id, fragment, statement, operator, name, enabled, create_time, update_time, creator, updater) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: column "enabled" is of type smallint but expression is of type boolean
  Hint: You will need to rewrite or cast the expression.
  Position: 167
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "enabled" is of type smallint but expression is of type boolean
  Hint: You will need to rewrite or cast the expression.
  Position: 167
        at org.dinky.aop.UdfClassLoaderAspect.round(UdfClassLoaderAspect.java:58) ~[dinky-admin-1.0.1.jar:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_381]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_381]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_381]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_381]
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.27.jar:5.3.27]
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.27.jar:5.3.27]
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.dinky.service.impl.TaskServiceImpl$$EnhancerBySpringCGLIB$$c9193962.initDefaultFlinkSQLEnv(<generated>) ~[dinky-admin-1.0.1.jar:?]
        at org.dinky.init.SystemInit.run(SystemInit.java:107) ~[dinky-admin-1.0.1.jar:?]
        at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:759) ~[spring-boot-2.7.11.jar:2.7.11]
        ... 3 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column "enabled" is of type smallint but expression is of type boolean
  Hint: You will need to rewrite or cast the expression.
  Position: 167

I checked dinky-pg.sql and found that enabled fields exist in many tables. Some types are smallint and some are boolean .
According to the above exception,I try to change the enabled field of the dinky_task table in the database to boolean type.

Running again I got the following error：

### Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: boolean = integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 407
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: boolean = integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 407
        at org.dinky.aop.UdfClassLoaderAspect.round(UdfClassLoaderAspect.java:58) ~[dinky-admin-1.0.1.jar:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_381]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_381]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_381]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_381]
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708) ~[spring-aop-5.3.27.jar:5.3.27]
        at org.dinky.service.impl.TaskServiceImpl$$EnhancerBySpringCGLIB$$3ce6fe4d.getReleaseUDF(<generated>) ~[dinky-admin-1.0.1.jar:?]
        at org.dinky.init.SystemInit.registerUDF(SystemInit.java:224) ~[dinky-admin-1.0.1.jar:?]
        at org.dinky.init.SystemInit.run(SystemInit.java:111) ~[dinky-admin-1.0.1.jar:?]
        at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:759) ~[spring-boot-2.7.11.jar:2.7.11]
        ... 3 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: boolean = integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 407
### The error may exist in org/dinky/mapper/TaskMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select id, dialect, tenant_id, type, check_point, save_point_strategy, save_point_path, parallelism, fragment, statement_set, batch_model, cluster_id, cluster_configuration_id, database_id, env_id, alert_group_id, config_json, note, step, job_instance_id, version_id, statement, operator, name, enabled, create_time, update_time, creator, updater from dinky_task where (dialect in (?, ?, ?) and enabled = ? and step = ? and save_point_path is not null)

I opened the source code and found the abnormal location. I found that the SET enabled field used a boolean type, but the query construction condition used numbers. MYSQL might automatically convert it? The version of the PG library I used could not run normally, and an error occurred. For consistency reasons, Boolean should also be used when constructing query conditions for this boolean type field.

How to reproduce

My PostgreSQL version is 10.12.
Since the old version of PG does not support EXECUTE FUNCTION in the initialization script, I replaced it with EXECUTE PROCEDURE
Start the project after initializing the database

Anything else

No response

Version

1.0.1

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[github-actions]

Hello @tchivs, thank you for your feedback, but this issue has been reported before. You can search for keywords in the Issue list to view it. Issues are marked as `Duplicate`.

你好 @tchivs, 感谢你的反馈, 但是此问题已经被反馈过了，你可以在 Issue 列表中搜索关键字来查看, Issues 标注为 `Duplicate` 。

[Zzm0809]

waiting for #3306 merge