Mysql connection not working for me

njbuch

I have tried to change my setup to use mysql for storage instead of the embedded one.

Changed the lines in configuration file to:

# MySQL settings
mcc.db.type=MYSQL
mcc.db.url=jdbc:mysql://localhost:3306/hcdb
mcc.db.username=my_user
mcc.db.password=my_password

I have created the database already which is called 'hcdb' and the user is having ultimate DBA elevated permissions.

But the jdbc connection seems to mess up the database name....here is from the log-file:

2017-04-17 10:31:24,611 ERROR [main] [org.mycontroller.standalone.db.DataBaseUtils:118] Migration exception,
org.flywaydb.core.api.FlywayException: Unable to obtain Jdbc connection from DataSource (jdbc:mysql://localhost:3306/hcdb&logger=org.mycontroller.standalone.loggers.LoggerMySql)
 for user 'njb': Unknown database 'hcdb&logger=org.mycontroller.standalone.loggers.LoggerMySql'
        at org.flywaydb.core.internal.util.jdbc.DriverDataSource.getConnectionFromDriver(DriverDataSource.java:412)
        at org.flywaydb.core.internal.util.jdbc.DriverDataSource.getConnection(DriverDataSource.java:370)
        at org.flywaydb.core.internal.util.jdbc.JdbcUtils.openConnection(JdbcUtils.java:50)
        at org.flywaydb.core.Flyway.execute(Flyway.java:1326)
        at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
        at org.mycontroller.standalone.db.DataBaseUtils.runDatabaseMigration(DataBaseUtils.java:116)
        at org.mycontroller.standalone.StartApp.startServices(StartApp.java:251)
        at org.mycontroller.standalone.StartApp.startMycontroller(StartApp.java:108)
        at org.mycontroller.standalone.StartApp.main(StartApp.java:95)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'hcdb&logger=org.mycontroller.standalone.loggers.LoggerMySql'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.Util.getInstance(Util.java:381)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
        at org.flywaydb.core.internal.util.jdbc.DriverDataSource.getConnectionFromDriver(DriverDataSource.java:409)
        ... 8 common frames omitted

jkandasa

@njbuch Could you please post MyController version? Status >> About on GUI

njbuch

As its not starting, it is kind of difficult, but it is a snapshot built by me around 3.feb 2017....

Other info you might want?

njbuch

Ok, I took the latest snapshot from your Gdrive unpacked, and only modified the MYSQL section, and then tried starting it. Working fine

But restoring from a backup into the mysql deletes the configuration changes, and restores into the default database.

How to move old data into the new database?

BTW version is now:
MyController Version 0.0.3.Final-SNAPSHOT
Database schema revision 1.03.06 - 2017 Mar 03
Database type MySQL
Database version 5.7.18
Git commit:branch a4f4e01b94a3a63566b37db97f08de957e6df2f7:development
Built on 2017-04-05T12:15:52+0530
Build JDK 1.8.0_121
Build tool Apache Maven 3.3.9

jkandasa

@njbuch Backup/restore of database backup(expect H2DB) not supported by MyController. You have to manage backup/restore of your backup manually.

If the database is other than H2DB backup/restore only configurations and scripts.

But restoring from a backup into the mysql deletes the configuration changes, and restores into the default database.

Yes, it will do. As your previous backup contains old configuration different. I request you do one backup after you do change on configuration. And use that backup from that point.

njbuch

Ok, so what you are saying is that I can

take a backup of a system running h2db.
install a new SNAPSHOT version
then I can unzip the backup file, and
copy the mycontroller.properties file AND the resources folder to a new install in the conf folder.
Modify the properties to point to a mysql database
run the script.sql file inside the zipped database file on the mysql
run the new server

jkandasa

@njbuch

take a backup of a system running h2db.

install a new SNAPSHOT version

then I can unzip the backup file, and

copy the mycontroller.properties file AND the resources folder to a new install in the conf folder.

Modify the properties to point to a mysql database

Till this level looks good.

run the script.sql file inside the zipped database file on the MySQL

You cannot run script.sql(h2 db) backup on MySql. it will return an exception. For now, there is no solution to copy data when we switch database.

run the new server

njbuch

Ok, would the approach mentioned here http://stackoverflow.com/questions/11359813/how-to-convert-h2database-database-file-to-mysql-database-sql-file using SquirrelSQL maybe work?

jkandasa

@njbuch might work. I never tried. Kindly report your experience

ragflyer

@njbuch Have you had any success?

Mysql connection not working for me

2

552

524

3.4k