General issue with mysql persistence and DateTime Information

JensH · November 9, 2019, 11:21pm

Using Openhab latest Snapshot, but had seen this Problem since Month.

Some Items like Sunrise from Astro Binding, LastSeen from network binding allways causes errors an no SQL-Table for the persistence values can be found.

I analysed the tables and the Logs.

Every Item using the type DateTime causes the following error, only table-name is different:

2019-11-10 00:00:31.023 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: Table 'openhab2.sun_set_time_0579' doesn't exist Query: INSERT INTO sun_set_time_0579 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2019-11-10 16:46:00.0, 2019-11-10 16:46:00.0]
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.knowm.yank.Yank.execute(Yank.java:194) [250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.knowm.yank.Yank.execute(Yank.java:177) [250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:337) [250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:145) [250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:137) [250:org.openhab.persistence.jdbc:1.14.0.201911091730]
	at org.openhab.core.persistence.internal.PersistenceServiceDelegate.store(PersistenceServiceDelegate.java:59) [241:org.openhab.core.compat1x:2.5.0.201911090307]
	at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:137) [182:org.openhab.core.persistence:2.5.0.201911090304]
	at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:437) [182:org.openhab.core.persistence:2.5.0.201911090304]
	at org.eclipse.smarthome.core.items.GenericItem$1.run(GenericItem.java:261) [134:org.openhab.core:2.5.0.201911090301]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:?]
	at java.lang.Thread.run(Thread.java:748) [?:?]

Very special: If i create a table with this via phpMyAdmin with fields time and value, both type timestamp the errors go away and persistence ist doing as expected.

Why automatic creation of persistence tables doesnt work if there is a DateTime Value?

Any idea?

JensH · November 17, 2019, 8:46pm

Nobody with an idea?

shorty707 · February 8, 2020, 1:00pm

I have the same issue.
Did you open a bug already?

I think it’s defect.

JensH · February 8, 2020, 3:35pm

No, I didnt until know. Thought maybe there is a good reason for it? But I can do.

In actual Version there are some other errors too. Looks like the objects had been changed.

Schnitzelprinz · April 5, 2020, 6:47pm

Have same problem when using mysql database via jdbc with latest version of openhab. All items which are configured as DateTime items are affected. In earlier days I had MariaDB in place and not these problems but other ones why I switched to mysql

mysql --version show these output at my openhab server:
mysql Ver 8.0.19-0ubuntu0.19.10.3 for Linux on aarch64 ((Ubuntu))

mysqld --version show these output at my database server:
mysqld Ver 5.7.29-0ubuntu0.18.04.1 for Linux on aarch64 ((Ubuntu))

JensH · April 9, 2020, 3:35pm

As a workaround you can look in logs of openhab2 for exact table Name and create it with phpmyadmin or in mysql direct.

SQL-Example:

CREATE TABLE 'ITEMNAME_1000' ( 'time' timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), 'value' timestamp(3) NULL DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
ALTER TABLE 'ITEMNAME_1000' ADD PRIMARY KEY ('time'); COMMIT;

Maybe you have to change some details if using another Charset.

Schnitzelprinz · April 14, 2020, 12:01pm

Hi Jens,

thanks for the detailed SQL query try to help solving the problem, I keep in my mind for other topics,
I haven’t received an email that this topic was updated.

My solution: a big bang instead…I setup both systems from the scratch:
-using raspbian buster instead of ubuntu to get rid of starting problems with mariadb
-using again mariadb instead of mysql

This problem is now solved, but seems to be that mariadb has now problems with items of type “Location”

JensH · April 14, 2020, 1:44pm

Sounds like the choice between pst and cholera;)

Best would be to solve in jdbc binding

Schnitzelprinz · April 14, 2020, 2:10pm

I have no idea were to fix it:-(
Regarding limitations of String items inside of DB with nonworking default varchar of 65000, I configured it manually to 2048 and solved this problem. I tried to extend varchar from 30 to 128…No luck. Maybe shorter values at LON and LAT like 50.0 and 10.0 instead of 50.12345678 and 10.12345678 will help… We will see. I read somewhere else that usually a db expects LAT and LON with max 6 digits after the “.”

sqltype.CALL = VARCHAR(200)
sqltype.COLOR = VARCHAR(70)
sqltype.CONTACT = VARCHAR(6)
sqltype.DATETIME = DATETIME
sqltype.DIMMER = TINYINT
sqltype.LOCATION = VARCHAR(128) <<<default 30
sqltype.NUMBER = DOUBLE
sqltype.ROLLERSHUTTER = TINYINT
sqltype.STRING = VARCHAR(2048) <<<default 65000
sqltype.SWITCH = VARCHAR(6)

JensH · April 14, 2020, 5:26pm

I‘m no programmer, sorry

shorty707 · March 15, 2021, 8:03am

I still get those on errors on Oopenhab 3.01 until today
did anybody evel solve this?

08:43:45.898 [ERROR] [org.knowm.yank.Yank                  ] - Error in SQL query!!!
java.sql.SQLException: Table 'openhab.localweather_observerlastupdate_profile_0664' doesn't exist Query: INSERT INTO localweather_observerlastupdate_profile_0664 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2021-03-15 08:43:45.766, 2021-03-15 08:43:45.766]
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[bundleFile:?]

JensH · March 15, 2021, 8:51pm

You have to create tables for times by hand at the moment

stagf15 · June 5, 2021, 3:49am

Has anyone else had any experience with this, or come up with a solution? I have the exact same problem after upgrading from OpenHAB2.5 to 3.1.0.M5. I started with a clean mysql database and for the most part my persistence is working, except for DateTime items, and some string items that look like times or dates. (I have some String items that have a value like “2d 03:45:32”, and these also raise this sql error).

Very strange. Any help would be appreciated.