Postgres support seems to be broken
iMats opened this issue · 4 comments
iMats commented
I tried to use PostgreSQL for persisting history, but it seems no one actually tried it before :)
I downloaded the shaded jar and postgres migrations.sql and tried to run the migration:
$ java -jar SingularityService-1.0.0-shaded.jar db migrate conf.yaml --migrations migrations.sql
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/singularity/SingularityService-1.0.0-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String)
WARNING: Please consider reporting this to the maintainers of com.fasterxml.jackson.module.afterburner.util.MyClassLoader
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
java.sql.SQLException: Unable to load class: org.postgresql.Driver from ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3;ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:292)
ERROR [2020-03-31 15:17:25,933] org.apache.tomcat.jdbc.pool.ConnectionPool: Unable to create initial connections of pool.
! java.lang.ClassNotFoundException: org.postgresql.Driver
! at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
! at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
! at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
! at java.base/java.lang.Class.forName0(Native Method)
! at java.base/java.lang.Class.forName(Unknown Source)
! at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:38)
! ... 16 common frames omitted
! Causing: java.lang.ClassNotFoundException: Unable to load class: org.postgresql.Driver from ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3;ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3
! at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:56)
! at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:281)
! ... 15 common frames omitted
! Causing: java.sql.SQLException: Unable to load class: org.postgresql.Driver from ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3;ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3
! at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:292)
! at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:212)
! at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:739)
! at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:671)
! at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:483)
! at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154)
! at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
! at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
! at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
! at io.dropwizard.migrations.AbstractLiquibaseCommand.createDatabase(AbstractLiquibaseCommand.java:94)
! at io.dropwizard.migrations.AbstractLiquibaseCommand.openLiquibase(AbstractLiquibaseCommand.java:79)
! at io.dropwizard.migrations.AbstractLiquibaseCommand.run(AbstractLiquibaseCommand.java:67)
! at io.dropwizard.cli.ConfiguredCommand.run(ConfiguredCommand.java:87)
! at io.dropwizard.cli.Cli.run(Cli.java:78)
! at io.dropwizard.Application.run(Application.java:93)
! at com.hubspot.singularity.SingularityService.main(SingularityService.java:134)
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:212)
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:739)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:671)
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:483)
at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:154)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
at io.dropwizard.migrations.AbstractLiquibaseCommand.createDatabase(AbstractLiquibaseCommand.java:94)
at io.dropwizard.migrations.AbstractLiquibaseCommand.openLiquibase(AbstractLiquibaseCommand.java:79)
at io.dropwizard.migrations.AbstractLiquibaseCommand.run(AbstractLiquibaseCommand.java:67)
at io.dropwizard.cli.ConfiguredCommand.run(ConfiguredCommand.java:87)
at io.dropwizard.cli.Cli.run(Cli.java:78)
at io.dropwizard.Application.run(Application.java:93)
at com.hubspot.singularity.SingularityService.main(SingularityService.java:134)
Caused by: java.lang.ClassNotFoundException: Unable to load class: org.postgresql.Driver from ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3;ClassLoader:jdk.internal.loader.ClassLoaders$AppClassLoader@436813f3
at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:56)
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:281)
... 15 more
Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Unknown Source)
at org.apache.tomcat.jdbc.pool.ClassLoaderUtil.loadClass(ClassLoaderUtil.java:38)
... 16 more
I managed to work around this by downloading the postgresql jdbc driver manually from here and running: $ java -cp SingularityService-1.0.0-shaded.jar:postgresql-42.2.11.jre7.jar com.hubspot.singularity.SingularityService db migrate conf.yaml --migrations migrations.sql but ran into SQL syntax problems:
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.fasterxml.jackson.module.afterburner.util.MyClassLoader (file:/opt/singularity/SingularityService-1.0.0-shaded.jar) to method java.lang.ClassLoader.findLoadedClass(java.lang.String)
WARNING: Please consider reporting this to the maintainers of com.fasterxml.jackson.module.afterburner.util.MyClassLoader
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
INFO [2020-03-31 15:20:26,972] liquibase.executor.jvm.JdbcExecutor: SELECT COUNT(*) FROM public.databasechangeloglock
INFO [2020-03-31 15:20:26,978] liquibase.executor.jvm.JdbcExecutor: SELECT COUNT(*) FROM public.databasechangeloglock
INFO [2020-03-31 15:20:26,982] liquibase.executor.jvm.JdbcExecutor: SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
INFO [2020-03-31 15:20:26,996] liquibase.lockservice.StandardLockService: Successfully acquired change log lock
INFO [2020-03-31 15:20:27,070] liquibase.executor.jvm.JdbcExecutor: SELECT MD5SUM FROM public.databasechangelog WHERE MD5SUM IS NOT NULL LIMIT 1
INFO [2020-03-31 15:20:27,071] liquibase.executor.jvm.JdbcExecutor: SELECT COUNT(*) FROM public.databasechangelog
INFO [2020-03-31 15:20:27,072] liquibase.changelog.StandardChangeLogHistoryService: Reading from public.databasechangelog
INFO [2020-03-31 15:20:27,072] liquibase.executor.jvm.JdbcExecutor: SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
INFO [2020-03-31 15:20:27,099] liquibase.executor.jvm.JdbcExecutor: ALTER TABLE taskUsage CHARACTER SET ascii COLLATE ascii_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
ERROR [2020-03-31 15:20:27,105] liquibase.changelog.ChangeSet: Change Set migrations.sql::2::ssalinas failed. Error: ERROR: syntax error at or near "CHARACTER"
Position: 23 [Failed SQL: ALTER TABLE taskUsage CHARACTER SET ascii COLLATE ascii_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8]
INFO [2020-03-31 15:20:27,110] liquibase.lockservice.StandardLockService: Successfully released change log lock
liquibase.exception.MigrationFailedException: Migration failed for change set migrations.sql::2::ssalinas:
Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or near "CHARACTER"
Position: 23 [Failed SQL: ALTER TABLE taskUsage CHARACTER SET ascii COLLATE ascii_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8]
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
at liquibase.Liquibase.update(Liquibase.java:202)
at liquibase.Liquibase.update(Liquibase.java:179)
at liquibase.Liquibase.update(Liquibase.java:175)
at liquibase.Liquibase.update(Liquibase.java:168)
at io.dropwizard.migrations.DbMigrateCommand.run(DbMigrateCommand.java:68)
at io.dropwizard.migrations.DbCommand.run(DbCommand.java:55)
at io.dropwizard.migrations.AbstractLiquibaseCommand.run(AbstractLiquibaseCommand.java:68)
at io.dropwizard.cli.ConfiguredCommand.run(ConfiguredCommand.java:87)
at io.dropwizard.cli.Cli.run(Cli.java:78)
at io.dropwizard.Application.run(Application.java:93)
at com.hubspot.singularity.SingularityService.main(SingularityService.java:134)
Caused by: liquibase.exception.DatabaseException: ERROR: syntax error at or near "CHARACTER"
Position: 23 [Failed SQL: ALTER TABLE taskUsage CHARACTER SET ascii COLLATE ascii_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8]
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:356)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1211)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)
... 13 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "CHARACTER"
Position: 23
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:268)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy37.execute(Unknown Source)
at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
... 18 more
I tried to fix the offending lines but just kept running into syntax errors, so I gave up.
ssalinas commented
Postgres support was limited and we wrote it for a specific user. So, at the moment, you have to build your own jar for SingularityService with the postgresql maven profile. That will include the correct deps + migrations file
iMats commented
That's basically what I did with this command: $ java -cp SingularityService-1.0.0-shaded.jar:postgresql-42.2.11.jre7.jar com.hubspot.singularity.SingularityService db migrate conf.yaml --migrations migrations.sql
I can get Singularity running by running $ java -cp SingularityService-1.0.0-shaded.jar:postgresql-42.2.11.jre7.jar com.hubspot.singularity.SingularityService server conf.yaml and it starts up fine and connects to postgres.
The problem is the migrations.sql file for postgres does not contain valid postgres sql syntax, but seems to be mostly copied from the mysql file, so it can't be run.
I've only tried this in a local dev environment so far where I can't run tasks, so I don't know what happens when Singularity tries to persist history to the database; maybe it works, but I'm guessing it won't.
We're running postgresql for all our other services, so it would be nice not to have the odd mysql installation, but if you're not planning on supporting it or maintaining it, maybe you should just drop support for it. After all, there doesn't seem to be that many people using it :)
ssalinas commented
It's a very small team of two maintaining this project right now, we'd be happy to review/merge/release a PR if you wanted to put one through to help fix syntax
iMats commented
Yes, I understand. I'll see if I can come up with a working migrations file and submit a PR.