I also had some issues running Mendix on an external database. I got mine to work with MySQL (this was our preferred database of choice). I have a mysql database running locally (important to note Mendix 7.23.7 and down only supports MySQL 5.7 and 7.23.7 and up (8.0.0) support MySQL 5.7 and 8.
Step 1:
Create an empty schema.
Step 2:
In mendix studio pro, open your project settings and go to the ‘Configuration’ tab and click on new. Add one like below:
Name: <unique name> // any config name you want
Type: MySQL/Maria DB // database type
URL: localhost:3306 // if you have a local instance of mysql running, you can use localhost or 127.0.0.1
Database name: <database name> // database name
Username: <user> // create this user with their privileges beforehand
Password: <user password> // password for above user
Step 3 <optional>:
If you want your data from your build-in database carried over to the external one you need to add the following options in the ‘Custom’ tab in the ‘Configuration’ window:
SourceDatabaseName: default // database you want to copy the data from
SourceDatabasePassword: // password for the database, the built-in one does not use a password
SourceDatabaseType: HSQLDB // build-in database uses HSQLDB
SourceDatabaseUserName: SA // don’t know if all build-in databases uses the same one, but you can open your build-in one in mendix and check
Step 4:
Make sure that your MySQL instance is always running before you run your app and make sure to set the new database as the ‘Active’ configuration. Now, run your project locally. Your data will be copied over to your external database (might take some time). After your first startup, it is best practice to remove all the settings under the ‘Custom’ tab.
Hope this helps! You can message me if you need help.
Edit:
Privileges looks like this:
‘admin’ user’s privileges.
Constants and Customs tab contains no settings.
I am getting below error when i tried to connect to MYSQL 5.7 , mine studio version is 9.0.5
com.mendix.m2ee.api.AdminException: An error occurred while initializing the Runtime: java.sql.SQLSyntaxErrorException: (conn=836) Unknown column 'Value' in 'where clause'
at com.mendix.basis.impl.MxRuntimeImplBase.start(MxRuntimeImplBase.java:334)
Caused by: com.mendix.connectionbus.ConnectionBusRuntimeException: java.sql.SQLSyntaxErrorException: (conn=836) Unknown column 'Value' in 'where clause'
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.liftedTree1$1(MySqlDataStoreConfigurator.scala:96)
Caused by: java.sql.SQLSyntaxErrorException: (conn=836) Unknown column 'Value' in 'where clause'
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)
at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:612)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.$anonfun$withResultSet$1(MySqlDataStoreConfigurator.scala:128)
at scala.util.Using$.resource(Using.scala:261)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.withResultSet(MySqlDataStoreConfigurator.scala:127)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.checkHasStatementBinlogFormat(MySqlDataStoreConfigurator.scala:115)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.$anonfun$getInitialDatabaseCommands$1(MySqlDataStoreConfigurator.scala:93)
at scala.util.Using$.resource(Using.scala:261)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.liftedTree1$1(MySqlDataStoreConfigurator.scala:92)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.getInitialDatabaseCommands(MySqlDataStoreConfigurator.scala:90)
at com.mendix.connectionbus.ConnectionBusImpl.getInitialDatabaseCommands(ConnectionBusImpl.java:361)
at com.mendix.basis.impl.MxRuntimeImplBase.initializeConnectionBusAnyway(MxRuntimeImplBase.java:417)
at com.mendix.basis.impl.MxRuntimeImplBase.start(MxRuntimeImplBase.java:313)
at com.mendix.m2ee.appcontainer.actions.StartAction.startRuntime(StartAction.java:48)
at com.mendix.m2ee.appcontainer.actions.StartAction.execute(StartAction.java:30)
at com.mendix.m2ee.appcontainer.server.handler.HttpAdminHandler.handle(HttpAdminHandler.java:134)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:279)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:383)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:882)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1036)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Unknown column 'Value' in 'where clause'
at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:192)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:260)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:612)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.$anonfun$withResultSet$1(MySqlDataStoreConfigurator.scala:128)
at scala.util.Using$.resource(Using.scala:261)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.withResultSet(MySqlDataStoreConfigurator.scala:127)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.checkHasStatementBinlogFormat(MySqlDataStoreConfigurator.scala:115)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.$anonfun$getInitialDatabaseCommands$1(MySqlDataStoreConfigurator.scala:93)
at scala.util.Using$.resource(Using.scala:261)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.liftedTree1$1(MySqlDataStoreConfigurator.scala:92)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.getInitialDatabaseCommands(MySqlDataStoreConfigurator.scala:90)
at com.mendix.connectionbus.ConnectionBusImpl.getInitialDatabaseCommands(ConnectionBusImpl.java:361)
at com.mendix.basis.impl.MxRuntimeImplBase.initializeConnectionBusAnyway(MxRuntimeImplBase.java:417)
at com.mendix.basis.impl.MxRuntimeImplBase.start(MxRuntimeImplBase.java:313)
at com.mendix.m2ee.appcontainer.actions.StartAction.startRuntime(StartAction.java:48)
at com.mendix.m2ee.appcontainer.actions.StartAction.execute(StartAction.java:30)
at com.mendix.m2ee.appcontainer.server.handler.HttpAdminHandler.handle(HttpAdminHandler.java:134)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:279)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:383)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:882)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1036)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLException: Unknown column 'Value' in 'where clause'
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1681)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1543)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1506)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:254)
at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:612)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.$anonfun$withResultSet$1(MySqlDataStoreConfigurator.scala:128)
at scala.util.Using$.resource(Using.scala:261)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.withResultSet(MySqlDataStoreConfigurator.scala:127)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.checkHasStatementBinlogFormat(MySqlDataStoreConfigurator.scala:115)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.$anonfun$getInitialDatabaseCommands$1(MySqlDataStoreConfigurator.scala:93)
at scala.util.Using$.resource(Using.scala:261)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.liftedTree1$1(MySqlDataStoreConfigurator.scala:92)
at com.mendix.connectionbus.connections.jdbc.implementations.mysql.MySqlDataStoreConfigurator.getInitialDatabaseCommands(MySqlDataStoreConfigurator.scala:90)
at com.mendix.connectionbus.ConnectionBusImpl.getInitialDatabaseCommands(ConnectionBusImpl.java:361)
at com.mendix.basis.impl.MxRuntimeImplBase.initializeConnectionBusAnyway(MxRuntimeImplBase.java:417)
at com.mendix.basis.impl.MxRuntimeImplBase.start(MxRuntimeImplBase.java:313)
at com.mendix.m2ee.appcontainer.actions.StartAction.startRuntime(StartAction.java:48)
at com.mendix.m2ee.appcontainer.actions.StartAction.execute(StartAction.java:30)
at com.mendix.m2ee.appcontainer.server.handler.HttpAdminHandler.handle(HttpAdminHandler.java:134)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:516)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:388)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:633)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:380)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:279)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:383)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:882)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1036)
at java.base/java.lang.Thread.run(Thread.java:834)