MySql DATETIME Knime TIMESTAMP and DB Writer Duplicate entry

#1

Hi,

I have a imputation project that used to work in KNIME 3.7.x - now on 4.0.2
I use the MySQL Connector set as MySQL 8, but towards a Mariadb 10.3.12.

I get all data, for example, for a part of a day - or for a whole month and found out if any minute of data was lost, using ‘Create Date&Time Range’ node and doing linear calculations in ‘Missing Value’ node.

I get back a real good looking table…

However, when I try to write for example 60 lines of 71 data columns I get errors:
Error DB Writer … Execution failed … Duplicate entry on ‘2008-03-30 03:59:00’ for key ‘PRIMARY’
If changing DB Writer/Settings and unchecking ‘Fail on error’ I get:
“Error during preparation”

For example getting every of possible 1440 minute data between ‘2008-03-30 00:00:00’ and
‘2008-03-30 23:59:00’ and I get 1380 of them but missing between 60 minutes of data
between …02:00:00 and …02:59:00. But the results from ‘Missing Value’ node does a correct job and creates a table with the 60 minutes of data for all 71 columns. But it can’t be written!? See error messages above.

There is, as it should be one row of data with primary key of '2008-03-30 03:00:00, and I have run the ANALYSE on the table. which returns OK.

There are a few conversions of types from the database to KNIME back to Output for DB Writer:
DATETIME (Primary key) -> Local Date Time -> TIMESTAMP
DATE (key - separately indexed) - Local Date - DATE
DECIMAL -> Double -> Number (double) -> Double -> DECIMAL
VARCHAR(n) -> String -> String -> VARCHAR(n)
TINYINT -> Integer -> Number(integer) -> Interger -> TINYINT

Why would DB Writer object to try to write a line to the database, and that line is not part of what is supposed to be written… (Tested also DB Insert - same problem…

Glad for any light you can shine on this problem…

0 Likes

#2

Hi,
is guess the problem is caused by the daylight saving time which was on the 30.03.2008 between 2am and 3am in Germany. At this date 2am didn’t exists and the database automatically converts it to 3am which then causes the duplicate entry exception.
Bye
Tobias

2 Likes

#3

Yes tobias.koetter you are quite right - that is the problem. Originally that database was used on a computer with automatic DST, for 3,5 years, after that the station was put into non-DST, normal local time because of all side effects it caused. On that normal local time the timestamps were recalculated, and usually the gaps in data were filled in with linear data. Must have missed that date.

That problem now shows because some other reconstructions are done “off line” on with the database on another desktop computer with automatic DST, so then the KNIME and system found out what it ought to do in normal circumstances. I will run a test with settings using only standardtime. (Please excuse if I didn’t use correct time standard names!)
.

0 Likes

#4

Hi,
Perfect. Please close the thread if everything works as expected.
Bye
Tobias

0 Likes

closed #5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

0 Likes