[jdbc] Provide console command for performing schema integrity check/repair

[jlaur]

Over time the column types in a database might no longer match the types used when creating new tables:

• JDBC persistence service has changed. Examples: [jdbc] Increased size of column type for Location Item #10542, [jdbc] Reduce size for VARCHAR columns #9394
• JDBC drivers might have changed.
• Database defaults might have changed (for example signed vs. unsigned when not specified).
• Item datatype might have changed.
• Users might have migrated from MySQL persistence to JDBC. In MySQL datetime was used for the time column, whereas this is now timestamp(3): https://github.com/openhab/openhab1-addons/blob/f7c26e5361c1cfe0382817ce7310a624e4ce89a0/bundles/persistence/org.openhab.persistence.mysql/java/org/openhab/persistence/mysql/internal/MysqlPersistenceService.java#L317

Having a tool to identify and possibly repair such inconsistencies would be helpful.

Notes

Checking columns:

SELECT * FROM information_schema.columns
WHERE table_schema = 'test' AND table_name = 'Item0001'

MySQL migration from DATETIME to TIMESTAMP(3)

See https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Verify:

• UTC vs. local time for "ALTER TABLE" statement changing to TIMESTAMP(3).

This is a follow-up to #13661.

[openhab-bot]

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

https://community.openhab.org/t/persistence-problem-item-cannot-be-cast-from-string-to-int/141166/3