Can't get MySql up and running in OH2

Hello Guys.
I can’t get the connection for my OH2 and mySQL server up and running. It’s installed on the same computer.
Basically i’ve installed the Microsoft SQL Server 2014, with an instance SQL_OH2 and created a database in that instance called OH2.

I’ve installed the MySql binding from PaperUI, mysql.cfg looks like this:

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
mysql:url=jdbc:mysql://127.0.0.1:58438/SQL_OH2/OH2

# the database user
user=OH2

# the database password
password=oh2

# the reconnection counter
reconnectCnt=100

# the connection timeout (in seconds)
waitTimeout=10

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=true

Checked that the TCP Dynamic Ports is set to 58438 in the “SQL Server Configuration Manager” and enabled the TCP/IP protocol in the SQL Server NEtwork Connection… also i’ve set the TCP Port for 3306 - just for testing.
I created the Database called OH2 and a user called OH2 in the SQL management Studio.
But when OH tries to connect it says:

2016-11-07 19:54:39.252 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://127.0.0.1:58438/SQL_OH2/OH2, user=OH2, password=oh2
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[:1.8.0_112]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)[:1.8.0_112]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)[:1.8.0_112]
	at java.lang.reflect.Constructor.newInstance(Unknown Source)[:1.8.0_112]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:675)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1086)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[:1.8.0_112]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)[:1.8.0_112]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)[:1.8.0_112]
	at java.lang.reflect.Constructor.newInstance(Unknown Source)[:1.8.0_112]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
	at java.sql.DriverManager.getConnection(Unknown Source)[:1.8.0_112]
	at java.sql.DriverManager.getConnection(Unknown Source)[:1.8.0_112]
	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521)
	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.activate(MysqlPersistenceService.java:190)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[:1.8.0_112]
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)[:1.8.0_112]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)[:1.8.0_112]
	at java.lang.reflect.Method.invoke(Unknown Source)[:1.8.0_112]
	at org.apache.felix.scr.impl.helper.BaseMethod.invokeMethod(BaseMethod.java:222)
	at org.apache.felix.scr.impl.helper.BaseMethod.access$500(BaseMethod.java:37)
	at org.apache.felix.scr.impl.helper.BaseMethod$Resolved.invoke(BaseMethod.java:615)
	at org.apache.felix.scr.impl.helper.BaseMethod.invoke(BaseMethod.java:499)
	at org.apache.felix.scr.impl.helper.ActivateMethod.invoke(ActivateMethod.java:295)
	at org.apache.felix.scr.impl.manager.SingleComponentManager.createImplementationObject(SingleComponentManager.java:302)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.createComponent(SingleComponentManager.java:113)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:866)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:833)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:774)[33:org.apache.felix.scr:2.0.2]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:212)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at java.security.AccessController.doPrivileged(Native Method)[:1.8.0_112]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:210)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:496)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:619)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at com.eclipsesource.jaxrs.publisher.internal.ResourceTracker.addingService(ResourceTracker.java:39)[11:com.eclipsesource.jaxrs.publisher:5.3.1.201602281253]
	at org.osgi.util.tracker.ServiceTracker$Tracked.customizerAdding(ServiceTracker.java:941)[org.osgi.core-6.0.0.jar:]
	at org.osgi.util.tracker.ServiceTracker$Tracked.customizerAdding(ServiceTracker.java:870)[org.osgi.core-6.0.0.jar:]
	at org.osgi.util.tracker.AbstractTracked.trackAdding(AbstractTracked.java:256)[org.osgi.core-6.0.0.jar:]
	at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:229)[org.osgi.core-6.0.0.jar:]
	at org.osgi.util.tracker.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:901)[org.osgi.core-6.0.0.jar:]
	at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:914)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:127)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:225)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:464)[org.eclipse.osgi-3.10.2.v20150203-1939.jar:]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:869)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:857)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:133)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.registerService(AbstractComponentManager.java:915)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.activateInternal(AbstractComponentManager.java:715)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:627)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.reconfigure(SingleComponentManager.java:566)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.config.ConfigurableComponentHolder.configurationUpdated(ConfigurableComponentHolder.java:419)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.scr.impl.config.ConfigurationSupport.configurationEvent(ConfigurationSupport.java:315)[33:org.apache.felix.scr:2.0.2]
	at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.sendEvent(ConfigurationManager.java:2046)[7:org.apache.felix.configadmin:1.8.8]
	at org.apache.felix.cm.impl.ConfigurationManager$FireConfigurationEvent.run(ConfigurationManager.java:2014)[7:org.apache.felix.configadmin:1.8.8]
	at org.apache.felix.cm.impl.UpdateThread.run0(UpdateThread.java:143)[7:org.apache.felix.configadmin:1.8.8]
	at org.apache.felix.cm.impl.UpdateThread.run(UpdateThread.java:110)[7:org.apache.felix.configadmin:1.8.8]
	at java.lang.Thread.run(Unknown Source)[:1.8.0_112]
Caused by: java.net.SocketException: Connection reset
	at java.net.SocketInputStream.read(Unknown Source)[:1.8.0_112]
	at java.net.SocketInputStream.read(Unknown Source)[:1.8.0_112]
	at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
	at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
	at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
	at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:599)
	... 68 more

I’m sorry to break it to you, but MS SQL Server isn’t the same as MySQL. You’ll need to download the Database software from here for MySQL https://www.mysql.com/

Hmm when you say it that way - ican see that it actually is named “Microsoft SQL” - not MySql, meaning this can’t run at all? I then need to install the MySql server instead?

Correct, these are different databases. There is no current persistence service for MS SQL for openHAB (one was attempted a while ago though).

Arh… okay, then i understand… Which version then to install from mysql.com?

Probably the free one: http://dev.mysql.com/downloads/mysql/

The 32-bit version is recommended and will work with both 32bit and 64bit Windows.

Thanks - then I need to see if that’s better :slight_smile:

After installing this and getting it up and running, i get an error saying :

"mySQL: Connection not valid"
mySQL: tableName was null
mySQL: Could not create entry for 'Temp_Outside_South' in table 'Items' with statement 'INSERT INTO Items (ItemName) VALUES (?)': No operations allowed after connection closed.

But i don’t get any connection errors anymore, first it told me that password and user wasn’t okay - but after correcting this?

Are you using a user with permission to create database objects (tables for example)

Followed guide here

Should the command:

GRANT ALL PRIVILEGES ON OpenHAB.* TO 'openhab'@'localhost';

do that?

Yep, that should be enough. I’ve not played with MySQL (I use MS SQL daily) so can’t help further I’m afraid. Have you tried creating a table manually as the openhab user?

Hmm, nope, haven’t tried :slight_smile:
But could be a good test… I’ll try to find something about how i do that from the CLI of MySql… :slight_smile:

OH has now defined 2 tables in the SQL, “ItemId” and “ItemName” - but it doesn’t put any data in them, tried changing the mysql.persist to do all items, but nothing happens.

The log doesn’t tell me that it doesn’t work - but in the PaperUI i can’t select MySql as default Persistence - only RRD4J

Same Problem here.
JDBC as default.
Connection is ready. It creates the table “items” and end.
No single entry in the table.

I’ve found another thread with this issue:

But there also no solution for this.

Deleted the sql database and recreated - then it worked for me :slight_smile: