Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

DB migrations fail in Docker with MariaDB 10.4.31 #1212

Closed
4 tasks done
sirhcel opened this issue Aug 17, 2023 · 2 comments
Closed
4 tasks done

DB migrations fail in Docker with MariaDB 10.4.31 #1212

sirhcel opened this issue Aug 17, 2023 · 2 comments

Comments

@sirhcel
Copy link
Contributor

sirhcel commented Aug 17, 2023 •
edited
Loading

Checklist

  • I checked other issues already, but found no answer/solution
  • I checked the documentation and wiki, but found no answer/solution
  • I am running the latest version and the issue still occurs
  • I am sure that this issue is about SteVe (and not about the charging station software or something unrelated to SteVe)

Specifications

SteVe Version     : release 3.6 and the current head at 0a2e75b3
Operating system  : Docker
JDK               : ...
Database          : MariaDB 10.4 as selected by Dockerfile

Expected Behavior

Starting up application and database containers succeeds.

Actual Behavior

Starting up the application container fails at running database migrations.

Steps to Reproduce the Problem

  1. Stop and remove eventually running instances and volumes

    $ docker stop steve_app_1 steve_db_1 
$ docker rm steve_app_1 steve_db_1
$ docker volume rm steve_db-data
$ docker image rm mariadb:10.4 steve_app

  2. Clone repository

    $ git clone https://github.com/steve-community/steve.git
$ cd steve
steve$ docker-compose up

  3. Running database fails and there are multiple types of issues (so far)

    1. Presence of foreign keys in an early stage of the migration

      app_1  | [INFO] Flyway Community Edition 7.15.0 by Redgate
app_1  | [INFO] Database: jdbc:mysql://mariadb:3306/stevedb (MySQL 5.5)
app_1  | [INFO] Successfully validated 35 migrations (execution time 00:00.013s)
app_1  | [INFO] Creating Schema History table `stevedb`.`schema_version` ...
app_1  | [INFO] Current version of schema `stevedb`: << Empty Schema >>
app_1  | [WARNING] outOfOrder mode is active. Migration of schema `stevedb` may not be reproducible.
app_1  | [INFO] Migrating schema `stevedb` to version "0.6.6 - inital"
app_1  | [WARNING] DB: Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. (SQL State: HY000 - Error Code: 1105)
app_1  | [INFO] Migrating schema `stevedb` to version "0.6.7 - update"
app_1  | [WARNING] DB: Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. (SQL State: HY000 - Error Code: 1105)
app_1  | [INFO] Migrating schema `stevedb` to version "0.6.8 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.6.9 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.7.0 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.7.1 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.7.2 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.7.3 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.7.6 - update"
app_1  | [INFO] Migrating schema `stevedb` to version "0.7.7 - update"
app_1  | [ERROR] Migration of schema `stevedb` to version "0.7.7 - update" failed! Please restore backups and roll back database and code!
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [INFO] BUILD FAILURE
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [INFO] Total time:  23.591 s
app_1  | [INFO] Finished at: 2023-08-17T20:58:24Z
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [WARNING]
app_1  | [WARNING] Plugin validation issues were detected in 2 plugin(s)
app_1  | [WARNING]
app_1  | [WARNING]  * pl.project13.maven:git-commit-id-plugin:4.9.10
app_1  | [WARNING]  * org.flywaydb:flyway-maven-plugin:7.15.0
app_1  | [WARNING]
app_1  | [WARNING] For more or less details, use 'maven.plugin.validation' property with one of the values (case insensitive): [BRIEF, DEFAULT, VERBOSE]
app_1  | [WARNING]
app_1  | [ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:7.15.0:migrate (default) on project steve: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V0_7_7__update.sql failed
app_1  | [ERROR] -----------------------------------
app_1  | [ERROR] SQL State  : HY000
app_1  | [ERROR] Error Code : 1833
app_1  | [ERROR] Message    : Cannot change column 'chargeBoxId': used in a foreign key constraint 'stevedb/FK_chargeBoxId_c' of table 'stevedb/connector'
app_1  | [ERROR] Location   : /code/src/main/resources/db/migration/V0_7_7__update.sql (/code/src/main/resources/db/migration/V0_7_7__update.sql)
app_1  | [ERROR] Line       : 1
app_1  | [ERROR] Statement  : ALTER TABLE `chargebox`
app_1  | [ERROR] CHANGE COLUMN `chargeBoxId` `chargeBoxId` VARCHAR(255) NOT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `endpoint_address` `endpoint_address` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `ocppProtocol` `ocppProtocol` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `chargePointVendor` `chargePointVendor` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `chargePointModel` `chargePointModel` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `chargePointSerialNumber` `chargePointSerialNumber` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `chargeBoxSerialNumber` `chargeBoxSerialNumber` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `fwVersion` `fwVersion` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `fwUpdateStatus` `fwUpdateStatus` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `iccid` `iccid` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `imsi` `imsi` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `meterType` `meterType` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `meterSerialNumber` `meterSerialNumber` VARCHAR(255) NULL DEFAULT NULL COMMENT '' ,
app_1  | [ERROR] CHANGE COLUMN `diagnosticsStatus` `diagnosticsStatus` VARCHAR(255) NULL DEFAULT NULL COMMENT ''
app_1  | [ERROR]
app_1  | [ERROR] -> [Help 1]
app_1  | [ERROR]
app_1  | [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
app_1  | [ERROR] Re-run Maven using the -X switch to enable full debug logging.
app_1  | [ERROR]
app_1  | [ERROR] For more information about the errors and possible solutions, please read the following articles:
app_1  | [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException
steve_app_1 exited with code 1

      which I could circumvent by reordering dropping and creating keys in V0_7_7__update.sql

    2. An issue with locking the table schema_version

      app_1  | [INFO] Migrating schema `stevedb` to version "0.8.6 - update"
app_1  | [ERROR] Migration of schema `stevedb` to version "0.8.6 - update" failed! Please restore backups and roll back database and code!
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [INFO] BUILD FAILURE
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [INFO] Total time:  30.977 s
app_1  | [INFO] Finished at: 2023-08-17T21:22:02Z
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [WARNING]
app_1  | [WARNING] Plugin validation issues were detected in 2 plugin(s)
app_1  | [WARNING]
app_1  | [WARNING]  * pl.project13.maven:git-commit-id-plugin:4.9.10
app_1  | [WARNING]  * org.flywaydb:flyway-maven-plugin:7.15.0
app_1  | [WARNING]
app_1  | [WARNING] For more or less details, use 'maven.plugin.validation' property with one of the values (case insensitive): [BRIEF, DEFAULT, VERBOSE]
app_1  | [WARNING]
app_1  | [ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:7.15.0:migrate (default) on project steve: org.flywaydb.core.internal.exception.FlywaySqlException: Error while retrieving the list of applied migrations from Schema History table `stevedb`.`schema_version`
app_1  | [ERROR] ----------------------------------------------------------------------------------------------------------
app_1  | [ERROR] SQL State  : HY000
app_1  | [ERROR] Error Code : 1100
app_1  | [ERROR] Message    : Table 'schema_version' was not locked with LOCK TABLES
app_1  | [ERROR]
app_1  | [ERROR] -> [Help 1]
app_1  | [ERROR]
app_1  | [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
app_1  | [ERROR] Re-run Maven using the -X switch to enable full debug logging.
app_1  | [ERROR]
app_1  | [ERROR] For more information about the errors and possible solutions, please read the following articles:
app_1  | [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

      which I could circumvent by locking the table schema_version for writing in V0_8_6__update.sql as well.

    3. set FOREIGN_KEY_CHECKS = 0 seems to be ignored

      app_1  | [INFO] Migrating schema `stevedb` to version "0.8.6 - update"
app_1  | [ERROR] Migration of schema `stevedb` to version "0.8.6 - update" failed! Please restore backups and roll back database and code!
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [INFO] BUILD FAILURE
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [INFO] Total time:  21.793 s
app_1  | [INFO] Finished at: 2023-08-17T21:24:54Z
app_1  | [INFO] ------------------------------------------------------------------------
app_1  | [WARNING]
app_1  | [WARNING] Plugin validation issues were detected in 2 plugin(s)
app_1  | [WARNING]
app_1  | [WARNING]  * pl.project13.maven:git-commit-id-plugin:4.9.10
app_1  | [WARNING]  * org.flywaydb:flyway-maven-plugin:7.15.0
app_1  | [WARNING]
app_1  | [WARNING] For more or less details, use 'maven.plugin.validation' property with one of the values (case insensitive): [BRIEF, DEFAULT, VERBOSE]
app_1  | [WARNING]
app_1  | [ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:7.15.0:migrate (default) on project steve: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: Migration V0_8_6__update.sql failed
app_1  | [ERROR] -----------------------------------
app_1  | [ERROR] SQL State  : HY000
app_1  | [ERROR] Error Code : 1833
app_1  | [ERROR] Message    : Cannot change column 'charge_box_id': used in a foreign key constraint 'FK_connector_charge_box_cbid' of table 'stevedb.connector'
app_1  | [ERROR] Location   : /code/src/main/resources/db/migration/V0_8_6__update.sql (/code/src/main/resources/db/migration/V0_8_6__update.sql)
app_1  | [ERROR] Line       : 27
app_1  | [ERROR] Statement  : ALTER TABLE charge_box CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci
app_1  | [ERROR]
app_1  | [ERROR] -> [Help 1]
app_1  | [ERROR]
app_1  | [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
app_1  | [ERROR] Re-run Maven using the -X switch to enable full debug logging.
app_1  | [ERROR]
app_1  | [ERROR] For more information about the errors and possible solutions, please read the following articles:
app_1  | [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

      where I gave up fixing the issues I've seen so far.

Additional context

  • Database migrations work perfectly fine when firing up Docker with MariaDB 10.4.30
sirhcel added a commit to chargeITmobility/steve that referenced this issue Aug 17, 2023
@sirhcel
Fix database migrations by pinning MariaDB version
a807743 
When running database migrations with the latest 10.4.31, they fail as
shown in issue steve-community#1212. Pinning the point release of MariaDB to the latest
known-good version maneuvers around this issue.
@sirhcel sirhcel mentioned this issue Aug 17, 2023
Merged
sirhcel added a commit to chargeITmobility/steve that referenced this issue Aug 19, 2023
@sirhcel
Fix database migrations by pinning MariaDB version
b3a9808 
When running database migrations with the latest 10.4.31, they fail as
shown in issue steve-community#1212. Pinning the point release of MariaDB to the latest
known-good version maneuvers around this issue.
sirhcel added a commit to chargeITmobility/steve that referenced this issue Aug 19, 2023
@sirhcel @goekay
Fix database migrations by pinning MariaDB version
3fd9ff6 
When running database migrations with the latest 10.4.31, they fail as
shown in issue steve-community#1212. Pinning the point release of MariaDB to the latest
known-good version maneuvers around this issue.

Co-authored-by: Sevket Gökay <sevketgokay@gmail.com>
@goekay goekay closed this as completed Aug 29, 2023
@pjjjv
Copy link

pjjjv commented Sep 6, 2023

I also ran into the 0.7.7 and 0.8.6 migration failures using the released version 3.6.0 of steve. You may want to re-release soon!

I was able to successfully finish the migrations with these files (renamed to .txt to upload to github):
V0_8_6__update.sql.txt
V0_7_7__update.sql.txt

But I may still have mistakes as I then ran into:

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  48.691 s
[INFO] Finished at: 2023-09-06T11:45:34Z
[INFO] ------------------------------------------------------------------------
[WARNING] 
[WARNING] Plugin validation issues were detected in 4 plugin(s)
[WARNING] 
[WARNING]  * org.jooq:jooq-codegen-maven:3.16.7
[WARNING]  * pl.project13.maven:git-commit-id-plugin:4.9.10
[WARNING]  * org.flywaydb:flyway-maven-plugin:7.15.0
[WARNING]  * org.apache.maven.plugins:maven-resources-plugin:3.3.0
[WARNING] 
[WARNING] For more or less details, use 'maven.plugin.validation' property with one of the values (case insensitive): [BRIEF, DEFAULT, VERBOSE]
[WARNING] 
[INFO ] 2023-09-06 11:45:35,208 de.rwth.idsg.steve.utils.PropertiesFileLoader (main) - Hint: The Java system property 'main.properties' can be set to point to an external properties file, which will be prioritized over the bundled one
[INFO ] 2023-09-06 11:45:35,298 de.rwth.idsg.steve.Application (main) - Loaded the properties. Starting with the 'PROD' profile
[INFO ] 2023-09-06 11:45:35,305 de.rwth.idsg.steve.Application (main) - Date/time zone of the application is set to UTC. Current date/time: 2023-09-06T11:45:35.300Z
Log file: Not available
Starting.[INFO ] 2023-09-06 11:45:35,427 org.eclipse.jetty.server.Server (main) - jetty-10.0.14; built: 2023-02-22T23:12:32.272Z; git: 976721d0f3e903a243584d47870ad2f2c1bf9e55; jvm 11.0.20.1+1
 FAILED!
Please refer to the log file for details
Exception in thread "main" java.util.concurrent.ExecutionException: java.lang.IllegalStateException: STOPPED
	at java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:395)
	at java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2022)
	at org.eclipse.jetty.server.Server.doStop(Server.java:490)
	at org.eclipse.jetty.util.component.AbstractLifeCycle.stop(AbstractLifeCycle.java:132)
	at de.rwth.idsg.steve.JettyServer.stop(JettyServer.java:185)
	at de.rwth.idsg.steve.SteveProdStarter.stop(SteveProdStarter.java:80)
	at de.rwth.idsg.steve.Application.stop(Application.java:85)
	at de.rwth.idsg.steve.Application.main(Application.java:68)
Caused by: java.lang.IllegalStateException: STOPPED
	at org.eclipse.jetty.server.handler.ContextHandler.shutdown(ContextHandler.java:774)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
	at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655)
	at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
	at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
	at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:550)
	at java.base/java.util.stream.AbstractPipeline.evaluateToArrayNode(AbstractPipeline.java:260)
	at java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:517)
	at org.eclipse.jetty.util.component.Graceful.shutdown(Graceful.java:146)
	... 6 more

I am running this via Docker.

@pjjjv
Copy link

pjjjv commented Sep 6, 2023

If I download the latest from git, I also get the java.lang.IllegalStateException. I'll open a seperate ticket for that.

@LydiaBenaida LydiaBenaida mentioned this issue Dec 11, 2023
Closed
4 tasks
@mahmudarslan mahmudarslan mentioned this issue Feb 26, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@pjjjv @goekay @sirhcel