Using external DB instead of in-built DB

1
Hi, I am trying to build a sample app using external DB (either of SQL Server or My SQL) instead of inbuilt DB, but with both, I am getting a similar type of issue.  Following are my settings for SQL Server –  Type – Microsoft SQL Server URL – localhost\SQLEXPRESS01 Database name – learnnext Use integrated security – Yes With above setting I keep on getting following error –  Opening JDBC connection to Some(localhost\SQLEXPRESS01) failed with SQLState: 08S01 Error code: 0 Message: The connection to the host localhost, named instance SQLEXPRESS01 failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434.  For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host. Retrying...(1/4) I can connect to SQL Server on my localhost using SSMS and Command Line, using Windows Integrated Security. Can someone please help on this? Thanks,    
asked
4 answers
1

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:

 

 

answered
0

 

‘admin’ user’s privileges.

answered
0

Constants and Customs tab contains no settings.

answered
0

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)
 

answered