Issues with the database migrating from Rivendell 2.7.0 to 4.1.3

[lbracci]

Hi, I'm making tests to migrate the radio station where I work, from Rivendell 2.7.0 to Rivendell 4.1.3.

I installed Rivendell 4.1.3 in a new server with Linux Mint 21 Mate (based on Ubuntu 22.04). I followed the instructions on [1], in server mode. Everything worked fine, and the Rivendell applications (Rdadmin, rdairplay, etc.) ran as spected.

Then, I imported the Mysql DB from the old server with Rivendell 2.7.0, to the new server (I erased the Rivendell 4 database and imported the Rivendell 2.7.0 database into the new server).

Then, I tried to migrate the database using "rddbmgr --modify", getting this error message:

DB connection re-established
invalid SQL or failed DB connection[Unknown column 'UDP_ADDR' in 'field list' QMYSQL: Unable to execute query]: insert into `LOG_MACHINES` set `STATION_NAME`='my-new-computer',`MACHINE`=0,`START_MODE`=0,`AUTO_RESTART`='N',`LOG_NAME`='',`CURRENT_LOG`='LOG_2024_01_15',`RUNNING`='N',`LOG_ID`=-1,`LOG_LINE`=907,`NOW_CART`=0,`NEXT_CART`=0,`UDP_ADDR`='xxx.xxx.xxx.xxx',`UDP_PORT`=6666,`UDP_STRING`='%g # %t # %a # %n 
',`LOG_RML`=''

I followed the instructions in [2] to enable Strict mode in MySQL in the new server, as I understand that it is needed "to avoid errors with Rivendell", but I kept getting the error.

So I repaired the problem by myself, creating the column "UDP_ADDR" in the table LOG_MACHINES.

I ran "rddbmgr --modify" again, but I got another error message "unknown column 'UDP_PORT'". I had to manually add the columns UDP_PORT, UDP_STRING, and LOG_RML into the table LOG_MACHINES.

Then, I tried again running "rddbmgr --modify", getting another error:

invalid SQL or failed DB connection[Unknown column 'POST_POINT' in 'field list' QMYSQL: Unable to execute query]: insert into `LOG_LINES` set `LOG_NAME`='PLAYLISTALBATROPICAL',`LINE_ID`=1,`COUNT`=0,`TYPE`=0,`SOURCE`=0,`START_TIME`=0,`GRACE_TIME`=0,`CART_NUMBER`=353401,`TIME_TYPE`=0,`POST_POINT`='N',`TRANS_TYPE`=0,`START_POINT`=-1,`END_POINT`=-1,`FADEUP_POINT`=-1,`FADEUP_GAIN`=-3000,`FADEDOWN_POINT`=-1,`FADEDOWN_GAIN`=-3000,`SEGUE_START_POINT`=-1,`SEGUE_END_POINT`=-1,`SEGUE_GAIN`=-3000,`DUCK_UP_GAIN`=0,`DUCK_DOWN_GAIN`=0,`COMMENT`='',`LABEL`='',`ORIGIN_USER`='',`ORIGIN_DATETIME`=NULL,`EVENT_LENGTH`=-1,`LINK_EVENT_NAME`='',`LINK_START_TIME`=0,`LINK_LENGTH`=0,`LINK_START_SLOP`=0,`LINK_END_SLOP`=0,`LINK_ID`=-1,`LINK_EMBEDDED`='N',`EXT_START_TIME`="00:00:00",`EXT_LENGTH`=-1,`EXT_CART_NAME`='',`EXT_DATA`='',`EXT_EVENT_ID`='',`EXT_ANNC_TYPE`=''

I added then the column "POST_POINT" into LOG_LINES.

I had similar errors with columns in the tables STACK_LINES, IMPORTER_LINES, PYPAD_INSTANCES, STACK_SCHED_CODES, FEEDS, FEED_IMAGES, GPIO_EVENTS, STACK_LINES, AUDIO_INPUTS, and AUDIO_OUTPUTS.

Finally, after a couple hours running "rddbmgr --modify" and dealing with annoying SQL errors, finally I ran rddbmgr without errors! The database was migrated and Rivendell 4.1.3 is running fine with the data from the old server.

But in a few days, I must import again the old DB from the server with Rivendell 2.7.0 into the new server with Rivendell 4.1.3, and I don't want to repeat this annoying procedure.

I don't see anyone else complaining about these SQL errors, so I'm almost sure I skipped a step in the migration procedure. What do you thing?

• Mysql version in the old server: 5.7
• Old server uses Ubuntu Studio 18.04, new server uses Linux Mint 21 Mate
• Rivendell database scheme in the old server: 231
• Mysql version in the new server: 8.0.35

[1] https://software.paravelsystems.com/howtos/ubuntu/jammy [2] https://wiki.rivendellaudio.org/index.php/Legacy_Rivendell_3.6.7_on_Ubuntu18_04

[vizubeat]

I have just successfully imported a database from v1.7.2 to v4.1.3 - were any modifications made on your source database outside of any of the Rivendell applications? For example schema changes or manual deletes/updates via direct SQL queries?

[lbracci]

I have just successfully imported a database from v1.7.2 to v4.1.3 - were any modifications made on your source database outside of any of the Rivendell applications? For example schema changes or manual deletes/updates via direct SQL queries?

I have made custom scripts to generate logs, but it doesn't modify the structure of the tables; it only add or deletes records in the LOGS table, and it never affected any of the tables mentioned above.

[lbracci]

Sorry, it was my fault. I installed Rivendell 4.1.3 following the instructions as a new install (I wanted to be sure it works), and THEN I imported the database from my production server (Rivendell 2.7.0), WITHOUT erasing the tables from the 4.1.3 install. So, I made a mess.

I mistakenly believed that, when I import a database backup made with mysqldump, it deletes the pre-existent tables.

I tried again dropping the Rivendell database, creating it again, importing the data from the Rivendell 2.7.0 and then running "rddbmgr --modify", and everything worked as spected, with zero errors.

[ElvishArtisan]

Closing as resolved.