OH 2.3 on Windows 10 64bit cannot get mysql working

Setup, Configuration and Use Beginners
1

All,

I have seen may topics like this with guides for PI or Linux but nothing for windows.
I have tried to use the other guides with no luck so hoping somebody can assist this noob get going.
I have got Openhab 2.3 running nicely (except for an issue with the apple certificate) but I cannot setup MySQL.

Steps I have taken:-
Installed MySQL Persistence binding via paper
Installed MySQL Workbench 8.0 CE
Used MySQL Workbench to create a MYSQL Connection
Used MySQL Workbench to create a user openhab with password habopen
Used MySQL Workbench to create to create a database openhab
Used MySQL Workbench to give user openhab full rights to everything

In MySQL Workbench if I copy the jdbc connection to clipboard I get this

jdbc:mysql://127.0.0.1:3306/?user=openhab

Then created mysql.cfg in the services folder:-

service.pid=org.openhab.mysql
url=jdbc:mysql://127.0.0.1:3306/openhab
user=openhab
password=habopen

When I restart OpenHab I get this error in the logs

2018-08-17 17:06:08.296 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://127.0.0.1:3306/openhab, user=openhab, password=habopen
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
	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:411) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.Util.getInstance(Util.java:386) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2568) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) [248:org.openhab.persistence.mysql:1.12.0]
	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:411) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416) [248:org.openhab.persistence.mysql:1.12.0]
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346) [248:org.openhab.persistence.mysql:1.12.0]
	at java.sql.DriverManager.getConnection(DriverManager.java:664) [?:?]
	at java.sql.DriverManager.getConnection(DriverManager.java:247) [?:?]
	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521) [248:org.openhab.persistence.mysql:1.12.0]
	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.activate(MysqlPersistenceService.java:191) [248:org.openhab.persistence.mysql:1.12.0]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:?]
	at org.apache.felix.scr.impl.inject.BaseMethod.invokeMethod(BaseMethod.java:229) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.inject.BaseMethod.access$500(BaseMethod.java:39) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.inject.BaseMethod$Resolved.invoke(BaseMethod.java:650) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.inject.BaseMethod.invoke(BaseMethod.java:506) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.inject.ActivateMethod.invoke(ActivateMethod.java:307) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.inject.ActivateMethod.invoke(ActivateMethod.java:299) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.createImplementationObject(SingleComponentManager.java:298) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.createComponent(SingleComponentManager.java:109) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:906) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getServiceInternal(SingleComponentManager.java:879) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.SingleComponentManager.getService(SingleComponentManager.java:823) [39:org.apache.felix.scr:2.0.12]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse$1.run(ServiceFactoryUse.java:212) [?:?]
	at java.security.AccessController.doPrivileged(Native Method) ~[?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.factoryGetService(ServiceFactoryUse.java:210) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceFactoryUse.getService(ServiceFactoryUse.java:111) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceConsumer$2.getService(ServiceConsumer.java:45) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.getService(ServiceRegistrationImpl.java:508) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.getService(ServiceRegistry.java:461) [?:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.getService(BundleContextImpl.java:624) [?:?]
	at com.eclipsesource.jaxrs.publisher.internal.ResourceTracker.addingService(ResourceTracker.java:39) [15:com.eclipsesource.jaxrs.publisher:5.3.1.201602281253]
	at org.osgi.util.tracker.ServiceTracker$Tracked.customizerAdding(ServiceTracker.java:941) [?:?]
	at org.osgi.util.tracker.ServiceTracker$Tracked.customizerAdding(ServiceTracker.java:870) [?:?]
	at org.osgi.util.tracker.AbstractTracked.trackAdding(AbstractTracked.java:256) [?:?]
	at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:229) [?:?]
	at org.osgi.util.tracker.ServiceTracker$Tracked.serviceChanged(ServiceTracker.java:901) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109) [?:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:920) [?:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:127) [?:?]
	at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:225) [?:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:469) [?:?]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:887) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager$3.register(AbstractComponentManager.java:874) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.RegistrationManager.changeRegistration(RegistrationManager.java:132) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.registerService(AbstractComponentManager.java:941) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.activateInternal(AbstractComponentManager.java:741) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.enableInternal(AbstractComponentManager.java:675) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.AbstractComponentManager.enable(AbstractComponentManager.java:430) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.manager.ConfigurableComponentHolder.enableComponents(ConfigurableComponentHolder.java:657) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.BundleComponentActivator.initialEnable(BundleComponentActivator.java:341) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.Activator.loadComponents(Activator.java:390) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.Activator.access$200(Activator.java:54) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.scr.impl.Activator$ScrExtension.start(Activator.java:265) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.utils.extender.AbstractExtender.createExtension(AbstractExtender.java:254) [39:org.apache.felix.scr:2.0.12]
	at org.apache.felix.utils.extender.AbstractExtender.modifiedBundle(AbstractExtender.java:227) [39:org.apache.felix.scr:2.0.12]
	at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:482) [?:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.customizerModified(BundleTracker.java:415) [?:?]
	at org.osgi.util.tracker.AbstractTracked.track(AbstractTracked.java:232) [?:?]
	at org.osgi.util.tracker.BundleTracker$Tracked.bundleChanged(BundleTracker.java:444) [?:?]
	at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:908) [?:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
	at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148) [?:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEventPrivileged(EquinoxEventPublisher.java:213) [?:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:120) [?:?]
	at org.eclipse.osgi.internal.framework.EquinoxEventPublisher.publishBundleEvent(EquinoxEventPublisher.java:112) [?:?]
	at org.eclipse.osgi.internal.framework.EquinoxContainerAdaptor.publishModuleEvent(EquinoxContainerAdaptor.java:168) [?:?]
	at org.eclipse.osgi.container.Module.publishEvent(Module.java:476) [?:?]
	at org.eclipse.osgi.container.Module.start(Module.java:467) [?:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1634) [?:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.incStartLevel(ModuleContainer.java:1614) [?:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.doContainerStartLevel(ModuleContainer.java:1585) [?:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1528) [?:?]
	at org.eclipse.osgi.container.ModuleContainer$ContainerStartLevel.dispatchEvent(ModuleContainer.java:1) [?:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230) [?:?]
	at org.eclipse.osgi.framework.eventmgr.EventManager$EventThread.run(EventManager.java:340) [?:?]
Caused by: java.lang.NullPointerException
	at com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionImpl.java:3279) ~[?:?]
	at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1940) ~[?:?]
	at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1866) ~[?:?]
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252) ~[?:?]
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486) ~[?:?]
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly

Any Advice will be gratefully received.

Mick

2

I’m not entirely certain the MySQL add-on is working correctly. Just about every time I see it come up on the forum the people experiencing the problems end up switching to the JDBC add-on instead. I don’t use either so won’t be of much more help. Good luck!

3

Thanks for the quick reply,

I uninstalled MySQL and installed jdbc MySQL via paper.
I deleted MySQL.cfg
created jdbc.cfg using this guide

url=jdbc:mysql://127.0.0.1:3306/openhab
user=openhab
password=habopen
tableNamePrefix=Item
tableUseRealItemNames=false
tableIdDigitCount=0

But still no joy, hopefully somebody will see my error:)
My gut feeling now is I have done something wrong with the MYSQL side of things but everything appears correct

Error in logs is too large to post but also states could not create connection to database server:(

Many Thanks
Mick

4

Make sure the Windows Firewall isn’t blocking connections to the MySQL port.

5

I cannot find anything that blocking this in my firewall settings.
I also have tried to access mysql using putty to start a telnet connection and get the following reply.
putty
I’m guessing that this shows that there is access? (I didn’t send any login details with the connection request)
I might try installing an older version of mysql and see how that goes!?

Many Thanks

Mick

6

Hooray it works!!!
My solution was to install MySQL 5.5 using the windows installer package.
I now have a working MySQL system (I went back to using the MySQL binding as well!)
Now to work out how I’m going to display the data! the built in chart is simple to setup but cant see the configuration options I would like (ie bar chars or pie charts…)
Progress :slight_smile:
Many thanks for your help Rich, as it got me thinking about thins slightly differently :brain: