some SQL command dont work to generateChangeLog

[HomeOfTheWizard]

Hello,

I tried to build a maven project to manage my clikchouse database model. Using apache-maven 3.6.3 and Clickhouse 21.6.9

Here is my property file:

outputChangeLogFile=src/main/resources/liquibase-outputChangeLog.xml
url=jdbc:clickhouse://test:8123/default
username=xxx
password=yyy
driver=ru.yandex.clickhouse.ClickHouseDriver
databaseClass=liquibase.ext.clickhouse.database.ClickHouseDatabase
strict=true

And pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>DataModel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <build>
        <pluginManagement>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-release-plugin</artifactId>
                    <version>2.5.3</version>
                </plugin>
                <plugin>
                    <groupId>org.liquibase</groupId>
                    <artifactId>liquibase-maven-plugin</artifactId>
                    <version>4.3.5</version>
                    <configuration>
                        <propertyFile>src/main/resources/liquibase.properties</propertyFile>
                    </configuration>
                    <dependencies>
                        <dependency>
                            <groupId>com.mediarithmics</groupId>
                            <artifactId>liquibase-clickhouse</artifactId>
                            <version>0.6.1</version>
                        </dependency>
                    </dependencies>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>
</project>

when executing the following command mvn liquibase:generateChangeLog

I have the following error

[INFO] Parsing Liquibase Properties File src/main/resources/liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:clickhouse://iqoprdweb057:8123/default
[INFO] there are no resolved artifacts for the Maven project.
[INFO] Generating Change Log from database null @ jdbc:clickhouse://iqoprdweb057:8123/default (Default Schema: default)
BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  6.351 s
[INFO] Finished at: 2021-11-23T16:27:42+01:00
[INFO] ------------------------------------------------------------------------
[WARNING] The requested profile "unittest" could not be activated because it does not exist.
[ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:4.3.5:generateChangeLog (default-cli) on project DataModel:
[ERROR] Error setting up or running Liquibase:
[ERROR] liquibase.command.CommandExecutionException: liquibase.exception.DatabaseException: Error getting jdbc:clickhouse://iqoprdweb057:8123/default view with liquibase.statement.core.GetViewDefinitionStatement@740dcae3: Error executing SQL select view_definition from information_schema.views where table_name='LiquidityMetricsFacet' and table_catalog='default': ClickHouse exception, code: 81, host: iqoprdweb057, port: 8123; Code: 81, e.displayText() = DB::Exception: Database information_schema doesn't exist (version
 21.6.9.7 (official build))
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Ma          ven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

[HomeOfTheWizard]

When I run the mvn command with debug option I see that the SQL command trying to be executed is the following

select view_definition from information_schema.views where table_name='LiquidityMetricsFacet' and table_catalog='default'

When I try to execute it on clickhouse I have the same error. It seems that the syntax used by your plugin is not accepted by Clickhouse

DB::Exception: Database information_schema doesn't exist (version 21.6.9.7 (official build))

[HomeOfTheWizard]

ok it seems that the information_schema is added in the latest release of clickhouse. https://clickhouse.com/docs/en/whats-new/changelog/#clickhouse-release-21-6-2021-06-05

How can I run the driver for the version 21.6.9.7 ?

[HomeOfTheWizard]

I downgraded to the following versions, but did not work.

 <plugin>
                    <groupId>org.liquibase</groupId>
                    <artifactId>liquibase-maven-plugin</artifactId>
                    <version>3.10.1</version>
                    <configuration>
                        <propertyFile>src/main/resources/liquibase.properties</propertyFile>
                    </configuration>
                    <dependencies>
                        <dependency>
                            <groupId>com.mediarithmics</groupId>
                            <artifactId>liquibase-clickhouse</artifactId>
                            <version>0.5.2</version>
                        </dependency>
                    </dependencies>
                </plugin>

[igorepim]

The generateChangeLog command is not implemented in our plugin. Internally, we use it with the update command only.

[HomeOfTheWizard]

Thanks for the update. Is there any other feature of liquibase that are not available ? Can you please provide the list of commands that work with your plugin ? or add them in your README please ? I tried to add a TAG but seems not working either.

[INFO] Driver registered [INFO] Parsing Liquibase Properties File target/classes/liquibase-dev-clickhouse.properties for changeLog parameters [INFO] Executing on Database: jdbc:clickhouse://iqoprdweb057:8123/default [INFO] Successfully acquired change log lock [INFO] Successfully released change log lock [INFO] ------------------------------------------------------------------------ [INFO] BUILD FAILURE [INFO] ------------------------------ ------------------------------------------ [INFO] Total time: 2.627 s [INFO] Finished at: 2021-12-13T16:44:38+01:00 [INFO] --------------------------------------------------- --------------------- [WARNING] The requested profile "unittest" could not be activated because it does not exist. [ERROR] Failed to execute goal org.liquibase:liquibase-maven-plugin:4.6.1:tag (default-cli) on project 36mOpenseeLiquidityDataModel: [ERROR] Error setting up or running Liquibase: [ERROR] ClickHouse exception, code: 62, host: iqoprdweb057, port: 8123; Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 1 ('UP : UPDATE DATABASECHANGELOG SET TAG = '1.0.0' WHERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG). Expected one of: ALTER query, Query with output, ALTER LE, RENAME DATABASE, SHOW PRIVILEGES query, TRUNCATE, KILL, KILL QUERY query, SELECT query, possibly with UNION, list of union elements, ALTER ROLE, SELECT subquery, DESCR ery, SELECT query, subquery, possibly with UNION, SHOW GRANTS, SHOW CREATE, WATCH, CREATE SETTINGS PROFILE or ALTER SETTINGS PROFILE query, SHOW PROCESSLIST query, ALTER P ALTER USER, CREATE VIEW query, CHECK TABLE, SET ROLE, SELECT query, SELECT, REVOKE, CREATE USER, CREATE DICTIONARY, CREATE PROFILE, SET ROLE DEFAULT, EXPLAIN, ALTER SETTI OFILE, SYSTEM, ALTER LIVE VIEW, RENAME TABLE, DROP query, SHOW ACCESS, OPTIMIZE query, USE, DROP access entity query, RENAME DICTIONARY, DETACH, SET, SHOW, DESC, OPTIMIZE CREATE ROW POLICY, SET DEFAULT ROLE, EXCHANGE DICTIONARIES, CREATE POLICY, ALTER ROW POLICY, INSERT INTO, INSERT query, SHOW [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER ' [[NOT] [I]LIKE 'str'] [LIMIT expr], GRANT, RENAME query, SHOW GRANTS query, SHOW PRIVILEGES, EXISTS, DROP, SYSTEM query, CREATE LIVE VIEW query, CREATE ROW POLICY or ALT POLICY query, CREATE QUOTA or ALTER QUOTA query, SHOW PROCESSLIST, ALTER QUOTA, CREATE QUOTA, CREATE DATABASE query, SET query, Query, CREATE, WITH, CREATE ROLE or ALTER uery, EXTERNAL DDL FROM, EXCHANGE TABLES, EXISTS or SHOW CREATE query, WATCH query, REPLACE, CREATE ROLE, CREATE SETTINGS PROFILE, SET ROLE or SET DEFAULT ROLE query, CREA R or ALTER USER query, EXTERNAL DDL query, SHOW ACCESS query, SHOW CREATE QUOTA query, USE query, ATTACH, DESCRIBE, ALTER TABLE, ShowAccessEntitiesQuery, GRANT or REVOKE q CREATE TABLE or ATTACH TABLE query (version 21.6.9.7 (official build)) [ERROR] [Failed SQL: (62) UPDATE DATABASECHANGELOG SET TAG = '1.0.0' WH ERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG)] [ERROR] -> [Help 1] [ERROR] [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch. [ER ROR] Re-run Maven using the -X switch to enable full debug logging. [ERROR] [ERROR] For more information about the errors and possible solutions, please read the following articles: [ERROR]

[SergeKireev]

@homeofthewizard Thank you for your contribution on #18, it should be available in version 0.7.2