Correct. I am using the JPA binding, and all necessary tables (actually, there is only one in which all the items get persisted into) are created if they do not already exist.
Does the MYSQL Binding work with OpenHab2 ?
I saw it on the incompatible list.
Are you using Openhab1 or Openhab2 ?
Openhab2 with the daily builds from cloudbees. The “naked” MySQL binding did not work for me (because of the Issue 312 I mentioned a few posts ago), so I started using the JPA binding.
I’m personally using OH2. mySQL works with OH2 - there was a problem with some versions of most persistence services (not just mySQL) with OH2 due to something that was added to OH1, and this has now been removed.
So, I think that the current version of mySQL is now ok (although I’ve not tested it). Alternatively, a version before 1.6.1 I think is also ok (and I have tested that with OH2).
After importing the MySQL Bindung i get an error.
The import of org.openhab.core.persistence.PersistentStateRestorer can not be resolved.
Ok - that’s the problem that I thought was resolved… Looking at the issues below, it looks like it’s not merged yet…
If you use the mySQL in OH 1.6, this problem shouldn’t exist.
Chris
I cloned the Openhab2 Repo a while ago, maybe jam using a old version.
I will clone the actual version of openhab2 repo and test if the problem still exists.
Hi,
this issue IS resolved (see Removed PersistentStateRestorer by teichsta · Pull Request #3125 · openhab/openhab1-addons · GitHub). I didn’t close the issue since some users wanted to double check the solution though. So consider this issue resolved in the latest OH1 builds. I would assume the latest OH2 distribution will also contain the actual persistence service bundles.
Best, Thomas E.-E.
Thanks Thomas 
thx, i will try it tomorrow.
ftr: since i am waiting for feedback the PR has not been merged into master yet. But please feel free to build it yourself and testing it.
Thanks, Thomas E.-E.
It is finally running.
I have imported the MySQL Binding and deleted all References to PersistentStateRestorer.
After that i still had the problem that config parameter is still null.
Dictionary<String, ?> config
I think im doing something wrong by configure the binding, but i really dont know what.
For testing purpose i coded the value direct into the code and skiped that part of the code.
With this it works , even the restore seems to work.
2015-09-07 10:01:13 [DEBUG] [.p.m.i.MysqlPersistenceService:559 ] - mySQL query: item is Weather_Temperature
2015-09-07 10:01:13 [DEBUG] [.p.m.i.MysqlPersistenceService:619 ] - mySQL: query:SELECT Time, Value FROM Item1 ORDER BY Time DESC LIMIT 0,1
2015-09-07 10:01:13 [DEBUG] [.p.m.i.MysqlPersistenceService:659 ] - mySQL: query returned 1 rows in 47ms
2015-09-07 10:01:13 [DEBUG] [o.e.s.m.p.i.PersistenceManager:380 ] - Restored item state from '06.09.2015 20:03:35' for item 'Weather_Temperature' -> '14'
My weather item is properly stroed into my database.
Database Tables
Next i will try to solve the “config is null” problem.
I suspect all you can do is to remove the logging. This error is printed when the system sends invalid information, so unless you can stop the system doing this, all you can do is remove the login as Thomas suggested.
I set the MySQL confuguration like this.
public void updated(Dictionary<String, ?> config) throws ConfigurationException {
logger.debug("mySQL configuration starting");
logger.error("testpw");
// Why are you null ?
if(config == null){
logger.error("Config is null!");
} else if (config != null){
logger.error("Config is not null!");
}
logger.error("testpw - url");
url = "jdbc:mysql://localhost:3306/openhab2";
if (StringUtils.isBlank(url)) {
throw new ConfigurationException("mysql:url",
"The SQL database URL is missing - please configure the sql:url parameter in openhab.cfg");
}
logger.error("testpw - user");
user = "root";
if (StringUtils.isBlank(user)) {
throw new ConfigurationException("sql:user",
"The SQL user is missing - please configure the sql:user parameter in openhab.cfg");
}
logger.error("testpw - pw");
password = "root";
if (StringUtils.isBlank(password)) {
throw new ConfigurationException(
"mysql:password",
"The SQL password is missing. Attempting to connect without password. To specify a password configure the sql:password parameter in openhab.cfg.");
}
logger.error("testpw - reconnect");
String tmpString = "5";
if (StringUtils.isNotBlank(tmpString)) {
errReconnectThreshold = Integer.parseInt(tmpString);
}
logger.error("testpw - timeout");
tmpString = "10";
if (StringUtils.isNotBlank(tmpString)) {
waitTimeout = Integer.parseInt(tmpString);
}
logger.error("testpw connect");
// reconnect to the database in case the configuration has changed.
disconnectFromDatabase();
connectToDatabase();
logger.error("testpw");
// connection has been established ... initialization completed!
initialized = true;
logger.debug("mySQL configuration complete.");
}
Iam pretty sure, that this is not the right way.
Hello,
can someone post here or at WIKI page dump of tables which are needed by JPA? I’m trying to use it with OH2 and MySQL database but it is just not creating any tables. I’ve managed to create two of them but I see that there is another one JpaPersistentItem which structure I can’t find.
Thanks
I had the same problem with the JPA Binding and MySQL Database.
You dont have to create any table by yourself.
I have done a screenshot of my tables using MySQL Binding.
If you post your debug information, i will take a look at it.
Maybe i can help you.
JPA didn’t created any table for me. Here is startup log:
2015-09-07 09:48:42.047 DEBUG o.o.p.j.i.JpaPersistenceServiceActivator[:31]- JPA persistence bundle has been started.
2015-09-07 09:48:42.088 DEBUG o.o.p.j.i.JpaConfiguration[:41]- Update config...
2015-09-07 09:48:42.092 ERROR o.o.p.j.i.JpaConfiguration[:44]- Got a null properties object!
2015-09-07 09:49:02.141 DEBUG o.o.p.j.i.JpaConfiguration[:41]- Update config...
2015-09-07 09:49:02.146 DEBUG o.o.p.j.i.JpaConfiguration[:49]- url: jdbc:mysql://localhost:3306/openhab
2015-09-07 09:49:02.147 DEBUG o.o.p.j.i.JpaConfiguration[:61]- driver: com.mysql.jdbc.Driver
2015-09-07 09:49:02.149 DEBUG o.o.p.j.i.JpaConfiguration[:81]- Update config...done
2015-09-07 09:49:23.761 DEBUG o.o.p.j.i.JpaPersistenceService[:59]- Activating jpa binding...
2015-09-07 09:49:23.765 DEBUG o.o.p.j.i.JpaPersistenceService[:60]- Activating jpa binding...done
2015-09-07 09:50:53.998 DEBUG o.o.p.j.i.JpaPersistenceService[:92]- Storing item: ZaDomem_Ogrod1
2015-09-07 09:50:54.090 DEBUG o.o.p.j.i.JpaPersistenceService[:209]- Creating EntityManagerFactory...
2015-09-07 09:50:57.462 DEBUG o.o.p.j.i.JpaPersistenceService[:225]- Creating EntityManagerFactory...done
2015-09-07 09:51:03.720 DEBUG o.o.p.j.i.JpaPersistenceService[:92]- Storing item: ZaDomem_Ogrod2
2015-09-07 09:51:03.923 DEBUG o.o.p.j.i.JpaPersistenceService[:120]- Persisting item...
2015-09-07 09:51:04.573 ERROR o.o.p.j.i.JpaPersistenceService[:127]- Error on persisting item! Rolling back!
2015-09-07 09:51:04.635 ERROR o.o.p.j.i.JpaPersistenceService[:128]- The transaction has been rolled back. See the nested exceptions for details on the errors that occurred.
org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back. See the nested exceptions for details on the errors that occurred.
at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:594)
at org.openhab.persistence.jpa.internal.JpaPersistenceService.store(JpaPersistenceService.java:124)
at org.openhab.core.persistence.internal.PersistenceServiceDelegate.store(PersistenceServiceDelegate.java:48)
at org.eclipse.smarthome.model.persistence.internal.PersistenceManager.handleStateEvent(PersistenceManager.java:227)
at org.eclipse.smarthome.model.persistence.internal.PersistenceManager.stateUpdated(PersistenceManager.java:209)
at org.eclipse.smarthome.core.items.GenericItem.notifyListeners(GenericItem.java:197)
Caused by: org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back. See the nested exceptions for details on the errors that occurred.
at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2370)
at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2207)
at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2105)
at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:2023)
at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1528)
Caused by: org.apache.openjpa.persistence.PersistenceException: Table 'openhab.HISTORIC_ITEM' doesn't exist {prepstmnt 454115339 INSERT INTO HISTORIC_ITEM (id, name, realName, timestamp, value) VALUES (?, ?$
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4974)
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4934)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:134)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:76)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:144)
at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:79)
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Table 'openhab.HISTORIC_ITEM' doesn't exist {prepstmnt 454115339 INSERT INTO HISTORIC_ITEM (id, name, realName, timestamp, value) VALUES (?, ?, $
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:195)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:59)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1134)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:275)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1792)
I’ve managed to create tables on my own based on error queries and it looks it works now.
Indeep the problem seems to be that the tables does not exist.
If you create the tables by yourself and it is working fine.
Can you post a screenshot of your table ? I wanna try it myself.
Here you have two tables:
CREATE TABLE `HISTORIC_ITEM` (
`id` int(11) DEFAULT NULL,
`name` varchar(90) DEFAULT NULL,
`realName` varchar(90) DEFAULT NULL,
`timestamp` datetime DEFAULT NULL,
`value` varchar(90) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `OPENJPA_SEQUENCE_TABLE` (
`ID` tinyint(4) NOT NULL,
`SEQUENCE_VALUE` bigint(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Hi.
That’s weird. The JPA binding should create the table(s) itself.
I’ll file an issue. I’ll be working on this again in the near future.
Manfred