JPA persistence with mysql or mariadb failing

Add-ons Persistence Services
1

Hello,

I have been trying for the past hours to set-up the JPA persistence service with mysql (or mariadb) to no avail. The difficulty I face is that everything seems to be set-up correctly but the persistence then fails without any message.

Here is my debug log:

avril 11 23:10:14 jam start.sh[29978]: 2016-04-11 23:10:14.958 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Activating jpa binding…
avril 11 23:10:14 jam start.sh[29978]: 2016-04-11 23:10:14.963 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Activating jpa binding…done
avril 11 23:10:15 jam start.sh[29978]: 2016-04-11 23:10:15.018 [DEBUG] [.jpa.internal.JpaConfiguration] - Update config…
avril 11 23:10:15 jam start.sh[29978]: 2016-04-11 23:10:15.020 [DEBUG] [.jpa.internal.JpaConfiguration] - url: jdbc:mysql://jam:3306/openhab
avril 11 23:10:15 jam start.sh[29978]: 2016-04-11 23:10:15.022 [DEBUG] [.jpa.internal.JpaConfiguration] - driver: com.mysql.jdbc.Driver
avril 11 23:10:15 jam start.sh[29978]: 2016-04-11 23:10:15.024 [INFO ] [.jpa.internal.JpaConfiguration] - syncmappings was not specified!
avril 11 23:10:15 jam start.sh[29978]: 2016-04-11 23:10:15.026 [DEBUG] [.jpa.internal.JpaConfiguration] - Update config…done

So looks fine. Then when I trigger persistence through activation of an item:

avril 11 23:10:45 jam start.sh[29978]: 2016-04-11 23:10:45.827 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Storing item: seContact_Office
avril 11 23:10:46 jam start.sh[29978]: 2016-04-11 23:10:46.002 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Stored new value: OPEN
avril 11 23:10:46 jam start.sh[29978]: 2016-04-11 23:10:46.003 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Creating EntityManagerFactory…
avril 11 23:10:47 jam start.sh[29978]: 2016-04-11 23:10:47.177 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Creating EntityManagerFactory…done

and silent. Another further activation:

avril 11 23:10:52 jam start.sh[29978]: 2016-04-11 23:10:52.477 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Storing item: seContact_Office
avril 11 23:10:52 jam start.sh[29978]: 2016-04-11 23:10:52.482 [DEBUG] [.o.p.j.i.JpaPersistenceService] - Stored new value: CLOSED

Looking at the code (JpaPersistenceService.java from line 109), something fails here:

	JpaPersistentItem pItem = new JpaPersistentItem();
	try {
		String newValue = StateHelper.toString(item.getState());
		pItem.setValue(newValue);
		logger.debug("Stored new value: {}", newValue);
	} catch (Exception e1) {
		logger.error("Error on converting state value to string: {}", e1.getMessage());
		return;
	}
	pItem.setName(name);
	pItem.setRealName(item.getName());
	pItem.setTimestamp(new Date());

	EntityManager em = getEntityManagerFactory().createEntityManager();
	try {
		logger.debug("Persisting item...");
		// In RESOURCE_LOCAL calls to EntityManager require a begin/commit			
		em.getTransaction().begin();
		em.persist(pItem);
		em.getTransaction().commit();
		logger.debug("Persisting item...done");
	} catch (Exception e) {
		logger.error("Error on persisting item! Rolling back!");
		logger.error(e.getMessage(), e);
		em.getTransaction().rollback();
	} finally {
		em.close();
	}

Indeed the logs show that OpenHab goes through “Stored new value: etc”, but never reaches the “try” after getEntityManagerFactory().createEntityManager();

I believe something fails around:

		em.getTransaction().begin();
		em.persist(pItem);
		em.getTransaction().commit();

But I don’t know how to debug that part. Any help appreciated.

L.

2

After some fiddling around, I finally manage to make this work. So I answer to my own mail, in case it can help others.

First point, don’t expect any message in the debug log if the parameters in openhab.cfg are not absolutely correct: the persistence will fail silently and no message will be logged. My errors:

  • first I put the library files in ./lib instead of ./addons -> stupid mistake, but it seemed more logical to put librairies in the… lib directory, isn’t it?
  • second I used the mysql connector, even if I am using MariaDB as a backend, I could not manage to use the mariadb one. In the end it works as well, so I stopped digging deeper into that one
  • third the binding was not creating the tables automatically and so was not storing anything. I found the exact tables to be created in the following post: OpenHab2, JPA Binding, MySQL DB I copy the tables definition here:

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;

Table names in CAPITAL letters pls ! Pls note that the above definition will trigger a warning at start-up as the binding is expecting longer varchar fields. As I considered the above was enough for my needs, I used it “as-is”.

Voilà, I hope this will help someone.

L.