[jdbc] UPDATE of values in MariaDB persistence changes historic timestamp to current time about openhab-addons HOT 25 CLOSED

@jlaur I did use the REST API endpoint /persistence/items/{itemname}

from openhab-addons.

I should add this was with one of the latest 4.2 snapshots, so I should probably repeat my test on 4.1.2.

This works in 4.1.2 as well, so either we are doing something differently, or it might be isolated to MariaDB.

from openhab-addons.

I'm using MySQL Workbench for this, but didn't change the schema, just read/write data in tables.

Okay, then I'm out of ideas. If it can't be reproduced, you can close the issue.

from openhab-addons.

@binderth - I found the following documentation for MySQL:
https://dev.mysql.com/doc/refman/8.3/en/timestamp-initialization.html

I would assume the same or similar applies to MariaDB, and this could be the cause of your problem:

TIMESTAMP and DATETIME columns have no automatic properties unless they are specified explicitly, with this exception: If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

Perhaps the behavior changed in the 11.4 upgrade you mentioned. There is also this comparison table:
https://mariadb.com/kb/en/system-variable-differences-between-mariadb-10-4-and-mysql-8-0/

This is also the reason why your column remains NULL (nullable) even after fixing it:

If the explicit_defaults_for_timestamp system variable is disabled, TIMESTAMP columns by default are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL, explicitly declare it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is invalid.) If a TIMESTAMP column permits NULL values, assigning NULL sets it to NULL, not to the current timestamp.

from openhab-addons.

This issue has been mentioned on openHAB Community. There might be relevant details there:

https://community.openhab.org/t/batched-data-persistence/155285/14

from openhab-addons.

@binderth - did you test with both MySQL and MariaDB? I'm using MySQL and time series are persisted correctly with provided timestamps.

from openhab-addons.

@jlaur - to be honest, I didn't test with MySQL as it is very basic functionality I didn't expect to differ between MySQL and the fork MariaDB.

root@BinderBrix:~# mariadb --version
mariadb  Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

from openhab-addons.

@binderth - can I also ask how you reproduce it?

from openhab-addons.

@binderth - thanks. So with MySQL, I was not able to reproduce. I created a Switch item, TestSwitch, with persistence strategy everyChange. I updated it to ON to have the table created and first row inserted. Then http://openhab:8080/rest/persistence/items/TestSwitch?serviceId=jdbc&time=2024-04-01T06%3A00%3A00Z&state=OFF. Table contents after this:

2024-04-01 08:00:00.000, 'OFF'
2024-04-10 22:42:29.210, 'ON'

EDIT: Then http://openhab:8080/rest/persistence/items/TestSwitch?serviceId=jdbc&time=2024-04-01T06%3A00%3A00Z&state=ON. Table contents after this:

2024-04-01 08:00:00.000, 'ON'
2024-04-10 22:42:29.210, 'ON'

I should add this was with one of the latest 4.2 snapshots, so I should probably repeat my test on 4.1.2.

from openhab-addons.

@jlaur
It was both MariaDB and MySQL. It seems, that along the lines the CREATE-statements changed from:

CREATE TABLE `item0001` (
  `time` timestamp(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3),
  `value` double DEFAULT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

to

CREATE TABLE `item1680` (
  `time` timestamp(3) NOT NULL,
  `value` varchar(16255) DEFAULT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

So I guess, the prolem is solved für "new" items, but for the "old" ones created in the persistence by openHAB before some version, it exists. And I think, that's true for both MySQL and MariaDB.

for reference: I recreated my current openHAB-version back in October 2023, so the current openHAB-version the persistence-items were created was 4.0.3.
If important. I updgrade my "PROD"-instance only to release-versions. So currently I'm running 4.1.2 and I upgraded from 4.0.3-release via all release-builds.

from openhab-addons.

That's strange. I went all the way back to openHAB 1.x in Git history and didn't find anything related to DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3). I have no idea how your schema was created/updated like that. My production MySQL schema also doesn't look like that, and some tables are several years old.

I'm wondering if these issues are found by the console command jdbc schema check?

from openhab-addons.

Of course I can't rule out anything related with a configuration of the MariaDB schema as I created it in the database.

I can verify, that items created from 01.03.2024 on don't have the "ON UPDATE" argument anymore.

• openHAB 4.1.1-release was released in January and I updated end of January
• openHAB 4.1.2-release was released 23.03.2024 ...

Indeed we could rule out a change within openHAB.

So, what's left:

• I can't rule out, that some MariaDB update did this. as 11.4 was released on 16.02.2024, so perhaps I did an upgrade on the days before March 1st.
• What I also can confirm, that I did not change some configuration in MariaDB that time. I only configured the database on creation, 2 years ago

So, if I'm the only one, we can close this issue.

from openhab-addons.

@binderth - have you used your db instance with any other tools/ORM's/frameworks that might have changed your schema?

from openhab-addons.

@jlaur I'm using MySQL Workbench for this, but didn't change the schema, just read/write data in tables.

from openhab-addons.

FWIW, if someone has a similar issue, I used Excel for a simple "ALTER TABLE"-creation for "item0001" until "itemxxxx":

ALTER TABLE `openHAB`.`item0001` CHANGE COLUMN `time` `time` TIMESTAMP(3) NOT NULL ;
ALTER TABLE `openHAB`.`item0002` CHANGE COLUMN `time` `time` TIMESTAMP(3) NOT NULL ;
ALTER TABLE `openHAB`.`item0003` CHANGE COLUMN `time` `time` TIMESTAMP(3) NOT NULL ;
...

just be sure to use the schema name (openHAB in my case).

from openhab-addons.

FWIW, if someone has a similar issue, I used Excel for a simple "ALTER TABLE"-creation for "item0001" until "itemxxxx":

So console command jdbc schema check didn't detect this issue?

from openhab-addons.

So console command jdbc schema check didn't detect this issue?

at least no errors. mostly those:

item0594  EV6_lastupdate                                Column 'time' expected to be NOT NULL, but is nullable
item0594  EV6_lastupdate                                Column type 'TIMESTAMP(3)' expected, but is 'VARCHAR(16255)'
item0598  EV6_odometer                                  Column 'time' expected to be NOT NULL, but is nullable
item0595  EV6_sleepModeCheck                            Column 'time' expected to be NOT NULL, but is nullable
item1364  EV6_smartKeyBatteryWarning                    Column 'time' expected to be NOT NULL, but is nullable
item0596  EV6_systemCutOffAlert                         Column 'time' expected to be NOT NULL, but is nullable
item1368  EV6_tailLampStatus                            Column 'time' expected to be NOT NULL, but is nullable
item0609  EV6_temperature                               Column 'time' expected to be NOT NULL, but is nullable
item0911  EV6_trunkOpen                                 Column 'time' expected to be NOT NULL, but is nullable
item0590  EV6_vehicleLocation                           Column 'time' expected to be NOT NULL, but is nullable
item0696  EV6_vehicleStatus                             Column 'time' expected to be NOT NULL, but is nullable
item1367  EV6_windowOpen                                Column 'time' expected to be NOT NULL, but is nullable
item1550  FRZ_Call_Deflection_0                         Column 'time' expected to be NOT NULL, but is nullable
item1554  FRZ_Call_List_5                               Column 'time' expected to be NOT NULL, but is nullable
item1564  FRZ_Device_Log                                Column 'time' expected to be NOT NULL, but is nullable
item1564  FRZ_Device_Log                                Column type 'VARCHAR(16255)' expected, but is 'LONGTEXT'
item1553  FRZ_Inbound_Calls_5                           Column 'time' expected to be NOT NULL, but is nullable

the Column 'time' expected to be NOT NULL, but is nullable is present for every single item, also those created after 01.03.2024.

I then jdbc schema fixed my tables. But they still got the CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) as default. (and still, the "Column 'time' expected to be NOT NULL, but is nullable" info is still present, even after jdbc schema fix, even though time timestamp(3) NOT NULL is on every item)

from openhab-addons.

the Column 'time' expected to be NOT NULL, but is nullable is present for every single item, also those created after 01.03.2024.

I then jdbc schema fixed my tables. But they still got the CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) as default. (and still, the "Column 'time' expected to be NOT NULL, but is nullable" info is still present, even after jdbc schema fix, even though time timestamp(3) NOT NULL is on every item)

I'll have a look at fixing these issues. Would you be willing to test a fix?

from openhab-addons.

I thought that maybe the schema information was cached and not refreshed after the repair, but actually it is. In MySQL this worked (after setting value column to NOT NULL):

openhab> jdbc schema check
Table                                       Item                                        Issue
------------------------------------------  ------------------------------------------  ----------------------------------------------------------------
TestSwitch                                  TestSwitch                                  Column 'value' expected to be nullable, but is NOT NULL
openhab> jdbc schema fix
Fixed table 'TestSwitch' for item 'TestSwitch'
openhab> jdbc schema check
Table                                       Item                                        Issue
------------------------------------------  ------------------------------------------  ----------------------------------------------------------------

Assuming item EV6_lastupdate still has issues, can you try to run this query (replacing openhab by your schema)?

SELECT column_name, column_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='openhab' AND table_name='item0594'

And also show how the table looks from MySQL Workbench?

You can then try to do another jdbc schema fix EV6_lastupdate and show the resulting logs.

from openhab-addons.

I'll set up some tests on the weekend and come back after.

from openhab-addons.

This issue has been mentioned on openHAB Community. There might be relevant details there:

https://community.openhab.org/t/batched-data-persistence/155285/15

from openhab-addons.

sorry @jlaur I was completely offline the last days due to illness.
What I did:

1. installed current MySQL
2. configured a test-openHAB to use that MySQL
3. changed the value my "NewTestItem" (String-item)
4. encountered a different error

2024-04-23 11:40:45.531 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=NewTestItem (Type=StringItem, State=stringtest, Label=New Item, Category=) state=stringtest date=null
2024-04-23 11:40:45.533 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'NewTestItem' in itemNameToTableNameMap
2024-04-23 11:40:45.534 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable
2024-04-23 11:40:45.534 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (ItemName) VALUES ('NewTestItem')
2024-04-23 11:40:45.755 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=1 itemName=NewTestItem
2024-04-23 11:40:45.756 [DEBUG] [persistence.jdbc.internal.dto.ItemVO] - JDBC:ItemVO tableName=item0001; newTableName=NewTestItem; 
2024-04-23 11:40:45.757 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable
2024-04-23 11:40:45.757 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doCreateItemTable sql=CREATE TABLE IF NOT EXISTS item0001 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time))
2024-04-23 11:40:45.778 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store: Unable to store item
org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: Error in SQL query!!!; Column length too big for column 'value' (max = 16383); use BLOB or TEXT instead Query: CREATE TABLE IF NOT EXISTS item0001 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time)) Parameters: []; Pool Name= yank-default; SQL= CREATE TABLE IF NOT EXISTS item0001 (time TIMESTAMP(3) NOT NULL, value VARCHAR(21717), PRIMARY KEY(time))
	at org.openhab.persistence.jdbc.internal.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:414) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:202) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:392) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:217) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.internalStore(JdbcPersistenceService.java:174) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.lambda$1(JdbcPersistenceService.java:146) ~[?:?]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
	at java.lang.Thread.run(Thread.java:840) [?:?]

after changing the CREATE statement to us only 16000 as VARCHAR-length, there was no Default/Expression for time as I had above.

from openhab-addons.

@binderth - I hope you are well.

Can you create a separate issue for that? Also please state your schema default character set in that issue. See:

from openhab-addons.

i also tried the same above, but instead of using current MariaDB with v11.2 (the one running on my production openHAB on October 2023) I don't get any errors, and also no Default Expression.
So it really seems, there was something I did on whatever kind of at database level...

Can you create a separate issue for that? Also please state your schema default character set in that issue. See:

i used utf8mb4 as standard (must check, but AFAIK utf8 points to utf8mb3 in current MySQL/MariaDB).

from openhab-addons.

I thought that maybe the schema information was cached and not refreshed after the repair, but actually it is. In MySQL this worked (after setting value column to NOT NULL):

So i did that:

and the schema fix on that item:

openhab> jdbc schema fix EV6_lastupdate
Failed to fix table 'item0594' for item 'EV6_lastupdate': Error in SQL query!!!; (conn=4029) Incorrect datetime value: '"2022-10-28T07:30:17.000Z"' Query: ALTER TABLE item0594 MODIFY COLUMN value TIMESTAMP(3) Parameters: []; Pool Name= yank-default; SQL= ALTER TABLE item0594 MODIFY COLUMN value TIMESTAMP(3)

and indeed:

from openhab-addons.