OpenHab2, JPA Binding, MySQL DB

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