Hi all,
I am running OH2.5.12 and have been running ever since a mysql persistence service attached to a MariaDB database . To prepare for migration to OH V3 I changed the persistence to jdbc-mariadb and as the old DB had different data types I have created a new database.
But as I like to keep the data in one source my wish is to move the data from the old Items### tables into the new database.
I am aware that the numbering is different and that I have to check which ItemXXX table in the old database belongs to which ItemYYY in the new database.
I was wondering if someone in the community knows a way how to "read the data from the old table and insert it into the new table as I am no SQL specialist. What I assume is that you need to read out the value pair (date and ItemValue) and insert row by row it into the new table as the data types may vary.
To those of you who have a deeper knowledge on SQL programming I would kindly ask for your support how to script such a procedure or give me some hints to a documentation where a rookie like me can find what I am looking for.
EDIT:
Here is what I found in Google and what I think should work:
INSERT INTO dest_database.destination_table (DestColumn_1, DestColumn_2) SELECT SourceColumn_1, SourceColumn_2 FROM source.database.source_table
But it does not work Hence any advise is kindly appreciated.
I did that with phpmyadmin which I had running against both MySQL/MariaDB instances (there are some file size limitation) but mysqldump and mysql (alternative howto) on commandline should be fine as well. Only thing to keep in mind is - as you said - to alter the item name in the table file. As MariaDB is a MySQL fork they are working quite similar
Hi Björn,
thanks for your reply. Even though I used the MYSQL Binding I already had a MariaDB running underneath and both of my databases are running in the same MariaDB instance. I fear that migrating the data with mysqldump won’t work as the in the old database the Item Weather_Condition is stored in different tables and in the old table has the data type e.g. VARCHAR (20000) and the new table VARCHAR (4096) - or it could be int versus long int.
And what I do not know is if can simply dump the old table with into the new table when the old one has a different data type …
I will check your given link and see if I find the answer there!!!
EDIT:
Your links only showed complete database migrations. I search the web and found this:
INSERT INTO dest_database.destination_table (DestColumn_1, DestColumn_2) SELECT SourceColumn_1, SourceColumn_2 FROM source_database.source_table;
This is what I intend to do but the command does not work
This command works and solves my question. A copy&paste mistake caused the command to fail but when typing everything manually on the mariadb command line did the job!
It has to be run as root or the user running this command needs the appropriate privileges for source_database and dest_database !!!
Hence any advise is kindly appreciated.