search

MEDIARITHMICS / liquibase-clickhouse

Apache License 2.0
43 stars 39 forks source link

How to use project with Liquibase? #8

Closed john-tipper closed 3 years ago

john-tipper commented 3 years ago

I'm currently learning about Clickhouse and I am trying to get Liquibase to play nicely with Clickhouse with the simplest possible set-up, but without success. I'm assuming that this project is a Liquibase extension for Clickhouse, meaning I can run Liquibase commands against Clickhouse - is this the case?

I've started Clickhouse using Docker:

docker run --ulimit nofile=262144:262144 --volume=$(pwd)/clickhouse_data:/var/lib/clickhouse -p 8123:8123 yandex/clickhouse-server I'm now wanting to run Liquibase against Clickhouse, so I also run that in Docker:

docker run -it --rm -v $(pwd)/external-resources/clickhouse/liquibase/:/liquibase/changelog liquibase/liquibase:4.2.2 --defaultsFile=/liquibase/changelog/liquibase.docker.properties --logLevel=debug update My liquibase properties file looks like this:

changeLogFile: /liquibase/changelog/root-changelog.xml
url: jdbc:clickhouse://192.168.0.16:8123
username: default
password:
classpath: /liquibase/changelog/drivers/liquibase-clickhouse-0.5.3-SNAPSHOT-shaded.jar
driver: shaded.liquibase.ru.yandex.clickhouse.ClickHouseDriver

I've built a shaded version of your jar so that all dependencies are in it and added it to the classpath for Liquibase. However, I am currently seeing an exception which is causing the process to fail:

SEVERE [liquibase.integration] Unexpected error running Liquibase: 'liquibase.executor.ExecutorService'
liquibase.executor.ExecutorService.getInstance()'
java.lang.NoSuchMethodError: 'liquibase.executor.ExecutorService liquibase.executor.ExecutorService.getInstance()'
at liquibase.ext.clickhouse.lockservice.ClickHouseLockService.getExecutor(ClickHouseLockService.java:89)
at liquibase.ext.clickhouse.lockservice.ClickHouseLockService.hasDatabaseChangeLogLockTable(ClickHouseLockService.java:78)
at liquibase.lockservice.StandardLockService.init(StandardLockService.java:98)
at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:251)
at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:212)
at liquibase.Liquibase.lambda$update$1(Liquibase.java:218)
at liquibase.Scope.lambda$child$0(Scope.java:160)
at liquibase.Scope.child(Scope.java:169)
at liquibase.Scope.child(Scope.java:159)
at liquibase.Scope.child(Scope.java:138)
at liquibase.Liquibase.runInScope(Liquibase.java:2277)
at liquibase.Liquibase.update(Liquibase.java:215)
at liquibase.Liquibase.update(Liquibase.java:201)
at liquibase.integration.commandline.Main.doMigration(Main.java:1761)
at liquibase.integration.commandline.Main$1.lambda$run$0(Main.java:361)
at liquibase.Scope.lambda$child$0(Scope.java:160)
at liquibase.Scope.child(Scope.java:169)
at liquibase.Scope.child(Scope.java:159)
at liquibase.Scope.child(Scope.java:138)
at liquibase.Scope.child(Scope.java:222)
at liquibase.Scope.child(Scope.java:226)
at liquibase.integration.commandline.Main$1.run(Main.java:360)
at liquibase.integration.commandline.Main$1.run(Main.java:193)
at liquibase.Scope.child(Scope.java:169)
at liquibase.Scope.child(Scope.java:145)
at liquibase.integration.commandline.Main.run(Main.java:193)
at liquibase.integration.commandline.Main.main(Main.java:156)

Is this an error in the project? Or am I supposed to use this project in a different way please?

john-tipper commented 3 years ago

Actually, I can see that this particular exceptions is caused by a version mismatch, in that the version of Clickhouse in the pom.xml is different to that declared in the Docker command to run Liquibsae. Changing the Docker command to run Liquibase to this (to match the major version):

docker run -it --rm -v $(pwd)/external-resources/clickhouse/liquibase/:/liquibase/changelog liquibase/liquibase:3.10 --defaultsFile=/liquibase/changelog/liquibase.docker.properties --logLevel=debug update now gives the following exception:

13:11:50.355 INFO  [liquibase.database.core.UnsupportedDatabase]: Error getting default schema
liquibase.exception.DatabaseException: Error executing SQL call current_schema: null
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:130)
    at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:170)
    at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:181)
    at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:189)
    at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:204)
    at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:199)
    at liquibase.database.AbstractJdbcDatabase.getConnectionSchemaName(AbstractJdbcDatabase.java:360)
    at liquibase.database.AbstractJdbcDatabase.getDefaultSchemaName(AbstractJdbcDatabase.java:324)
    at liquibase.database.AbstractJdbcDatabase.escapeObjectName(AbstractJdbcDatabase.java:917)
    at liquibase.database.AbstractJdbcDatabase.escapeTableName(AbstractJdbcDatabase.java:903)
    at liquibase.sqlgenerator.core.CreateTableGenerator.generateSql(CreateTableGenerator.java:39)
    at liquibase.sqlgenerator.core.CreateTableGenerator.generateSql(CreateTableGenerator.java:23)
    at liquibase.sqlgenerator.SqlGeneratorChain.generateSql(SqlGeneratorChain.java:30)
    at liquibase.sqlgenerator.SqlGeneratorFactory.generateSql(SqlGeneratorFactory.java:222)
    at liquibase.sqlgenerator.core.CreateDatabaseChangeLogLockTableGenerator.generateSql(CreateDatabaseChangeLogLockTableGenerator.java:37)
    at liquibase.sqlgenerator.core.CreateDatabaseChangeLogLockTableGenerator.generateSql(CreateDatabaseChangeLogLockTableGenerator.java:15)
    at liquibase.sqlgenerator.SqlGeneratorChain.generateSql(SqlGeneratorChain.java:30)
    at liquibase.sqlgenerator.SqlGeneratorFactory.generateSql(SqlGeneratorFactory.java:222)
    at liquibase.executor.AbstractExecutor.applyVisitors(AbstractExecutor.java:105)
    at liquibase.executor.jvm.JdbcExecutor.access$600(JdbcExecutor.java:43)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:412)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:87)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:159)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:139)
    at liquibase.lockservice.StandardLockService.init(StandardLockService.java:102)
    at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:252)
    at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:213)
    at liquibase.Liquibase.update(Liquibase.java:183)
    at liquibase.Liquibase.update(Liquibase.java:178)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1639)
    at liquibase.integration.commandline.Main.run(Main.java:303)
    at liquibase.integration.commandline.Main.main(Main.java:159)
Caused by: java.sql.SQLFeatureNotSupportedException: null
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseConnectionImpl.prepareCall(ClickHouseConnectionImpl.java:179)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
    ... 31 common frames omitted
13:11:50.359 DEBUG [liquibase.executor.jvm.JdbcExecutor]: CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
13:11:50.369 ERROR [liquibase.integration.commandline.Main]: Unexpected error running Liquibase: ClickHouse exception, code: 62, host: 192.168.0.16, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 160 ('PRIMARY'): PRIMARY KEY (ID)). Expected CHECK (version 21.1.2.15 (official build))
 [Failed SQL: (62) CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]
liquibase.exception.LockException: liquibase.exception.DatabaseException: ClickHouse exception, code: 62, host: 192.168.0.16, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 160 ('PRIMARY'): PRIMARY KEY (ID)). Expected CHECK (version 21.1.2.15 (official build))
 [Failed SQL: (62) CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]
    at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:295)
    at liquibase.lockservice.StandardLockService.waitForLock(StandardLockService.java:213)
    at liquibase.Liquibase.update(Liquibase.java:183)
    at liquibase.Liquibase.update(Liquibase.java:178)
    at liquibase.integration.commandline.Main.doMigration(Main.java:1639)
    at liquibase.integration.commandline.Main.run(Main.java:303)
    at liquibase.integration.commandline.Main.main(Main.java:159)
Caused by: liquibase.exception.DatabaseException: ClickHouse exception, code: 62, host: 192.168.0.16, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 160 ('PRIMARY'): PRIMARY KEY (ID)). Expected CHECK (version 21.1.2.15 (official build))
 [Failed SQL: (62) CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED datetime, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:430)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:87)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:159)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:139)
    at liquibase.lockservice.StandardLockService.init(StandardLockService.java:102)
    at liquibase.lockservice.StandardLockService.acquireLock(StandardLockService.java:252)
    ... 6 common frames omitted
Caused by: shaded.liquibase.ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 62, host: 192.168.0.16, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 160 ('PRIMARY'): PRIMARY KEY (ID)). Expected CHECK (version 21.1.2.15 (official build))

    at shaded.liquibase.ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:58)
    at shaded.liquibase.ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:28)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:875)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:616)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:117)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:100)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:95)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:90)
    at shaded.liquibase.ru.yandex.clickhouse.ClickHouseStatementImpl.execute(ClickHouseStatementImpl.java:226)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:426)
    ... 11 common frames omitted
Caused by: java.lang.Throwable: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 160 ('PRIMARY'): PRIMARY KEY (ID)). Expected CHECK (version 21.1.2.15 (official build))

    at shaded.liquibase.ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:53)
    ... 20 common frames omitted
Koisell commented 3 years ago

Your guess about liquibase version is right. We unfortunately only support liquibase 3.10 for now... To speak about your new error, did you connect to your docker to ensure if default database exist ?

john-tipper commented 3 years ago

Hi Nicolas, the problem ended up being the fact that Liquibase did not seem to register the classes in the shaded jar (when it was added to a location which was added to the class path) as being implementations of the Liquibase Interfaces (Database, SqlGenerator etc) and thus were not used to connect to ClickHouse. I managed to get Liquibase to recognise the contents of the jar by bind mounting the shaded jar into the default Liquibase class path directory.

I wrote up what I did to get everything working here: https://johntipper.org/how-to-execute-liquibase-changesets-against-clickhouse/