Moving or copying the Yellowfin DB

Nick Tierney shared this problem 2 years ago
Resolved

I have an existing mysql config db setup on my server, but no application data. I did the following:


  1. Tried the old guide https://portal.yellowfin.bi/YFForum-Moving-or-Copying-the-Yellowfin-DB-?thread=105089 with HSQLDB
  2. Started the server to generate the root
  3. Stopped the server
  4. Changed the web.xml config as per http://portal.yellowfin.bi/YFForum-Connecting-to-the-Yellowfin-Repository-DB-?thread=105090 (with and without an encrypted password)
  5. Started the server

All I got was tomcat posting a 404 error.

My jdbc log says that it has successfully created the 2 connections.

Files: logs.zip

Comments (13)

photo
1

Hi Nick,


Thanks for reaching out with the issue you're experiencing. It would be helpful if you could provide some additional information so I can determine how best to proceed in getting you up and running.


Are you attempting to migrate an existing HSQLDB Yellowfin Configuration DB to a new MySQL Yellowfin Configuration DB?

Please attach copies of your old web.xml and your new web.xml.

Also include a zipped copy of your logs folder, located at <YellowfinInstall>/appserver/logs, it looks like something went wrong with the one you posted.


This information will get us started on moving forward with your migration.


Thanks,

Ryan

photo
1

Yep, I am attempting to migrate an existing HSQLDB Yellowfin Configuration DB to a new MySQL Yellowfin Configuration DB just like you said.


The attachment should contain the old xml file, new one and the logs.


Thanks.

Files: Data.zip
photo
1

Hi Nick,


Thanks for the quick reply and for including the additional information. In order to migrate your existing reports from the HSQLDB instance to your new MySQL instance you'll have to use the import / export function from inside the Admin Console. More information on that can be found here.


  1. Boot up your existing HSQLDB Yellowfin Instance.
  2. Navigate to Administration > Export.
  3. Select the elements you want to export, or select "Add All" to export all elements (probably easiest depending on the amount of reports you have).
  4. You will be prompted to include any dependencies that weren't resolved, click "Add" if so.
  5. Click "Export."
  6. Download the YellowfinExport.xml.
  7. At this point you are safe to shut down your HSQLDB instance of Yellowfin.
  8. Start up a fresh Yellowfin instance connected to your new MySQL Configuration Database.
  9. Log in and navigate to Administration > Import.
  10. Click "Browse" and select your YellowfinExport.xml file you downloaded in step 6.
  11. Inspect each item before clicking "Next." You will be prompted whether you'd like to import each item. In cases where the item exists in your new instance, you can decide whether to Add new or Replace existing.
  12. After approving each item you want to import, you'll be presented with the "Import Summary" page. This will show you any errors in your importing (let me know if you have trouble here).
  13. Click "Import" to finish the importing process.
  14. Browse and test your imported content.


Give this a go and let me know your results.


Thanks,

Ryan

photo
1

Sorry I think I was a bit unclear, we don't need to migrate reports or anything.

What we have is a mysql database setup with yellowfin 7.1. and an HSQLDB database setup with yellowfin 7.1. The HSQLDB has a yellowfin instance setup (/opt/yellowfin/*) and the mysql server does not have a yellowfin instance. What I would like to do is take the HSQLDB yellowfin instance and point it towards the mysql db.

In your instructions, the only relevant part to my problem is step 8: Start up a fresh Yellowfin instance connected to your new MySQL Configuration Database. I did this by changing the web.xml file and it only results in tomcat giving me a 404 error. I cannot even start up yellowfin to change anything. Please see logs for more info.

photo
1

Hi Nick,


Thanks for the clarification. If your HSQL installation doesn't contain any information, the easiest way to to do this is to perform a fresh install into MySQL and remove the HSQL installation. That being said, in looking at your new web.xml JDBC connection string everything looks to be in order. To break down the JDBCUrl, you will want it to say: jdbc:mysql://<serverName>:3306/<databaseName>?characterEncoding=utf8. So, provided your server name for MySQL is mysql, this should be correct. If it's on the same box as yellowfin you can insert localhost into <serverName> instead of mysql and see if that works.


Please let me know the results.


Thanks,

Ryan

photo
1

If I do a fresh install onto my MySQL, will this not wipe out my existing configuration database in MySQL?

photo
1

Hi Nick,


As you explained it, you do not have anything in your existing MySQL Configuration Database, i.e. reports, data sources, users, etc. If this is true, simply drop the database and re-create it or elect a new name for the database during the install. If you'd prefer to hop on a call or screen share let me know, I feel we may be on different pages with the end goal here.


Thanks,

Ryan

photo
1

Haha sorry just to clarify the mysql database has reports, but no yellowfin instance and the hsqldb has no reports but has a yellowfin instance. I'd like to make the hsqldb yellowfin instance point to mysql without wiping out the data in the mysql db.

photo
1

Nick,


Ok, I think I'm understanding your scenario. I presume you have reports you've built using data from a MySQL data source. I do want to make sure to clarify that any reports you create inside of Yellowfin are actually stored in the Yellowfin Configuration Database. So if you've created reports in your HSQL installation, even though the data was from MySQL the reports actually live inside the Yellowfin Configuration Database. The only way to bring those across to a new Configuration Database is to use the Import / Export method I outlined. Without performing that process, you won't have access to any of the reports you created in your original HSQLDB Yellowfin installation.


I do recommend making sure you take a backup of your entire Yellowfin installation folder before you start that process. I also want to highly recommend you ensure you switch over to MySQL, as HSQLDB isn't recommended for production.


Does this help?


Thanks,

Ryan

photo
1

Lets ignore the HSQL database and the mysql data source entirely for now. What I want to do it setup a yellowfin instance with an existing Yellowfin Configuration Database hosted on mysql. This existing Yellowfin Configuration Database does not currently have a Yellowfin instance installed but contains all of my report data, therefore I can not even log in to do any sort of export or import functionality.


I am aware the HSQLDB is not used for production, this is why it is empty. As i previously stated I was only creating it so that I could install Yellowfin to use my already existing Yellowfin Configuration Database. Please, for context, look at the previously links I used for advice on how to do this: https://portal.yellowfin.bi/YFForum-Moving-or-Copying-the-Yellowfin-DB-?thread=105089 and http://portal.yellowfin.bi/YFForum-Connecting-to-the-Yellowfin-Repository-DB-?thread=105090

photo
1

Hi Nick,


Looking closer at your jdbc.log I can see the problem here. The connection to your configuration database was not successful. I found in the logs the following:


  1. WARN: Failed to create initial connection at DBConnectionPool startup (attempt 10). java.lang.ClassNotFoundException: com.mysql.jdbcDriver

Please make sure the JDBCDriverClass contains the following value:


  1. com.mysql.jdbc.Driver

Also, please ensure that your jdbc url is correct, having mysql as the hostname of your server.


If this doesn't work, do a fresh install into MySQL, naming the config database something different from your old one, then start the new installation and let it run through initial startup, shut it down, and change the JDBCUrl.


It looks like your new web.xml is correct but it's not passing the driver parameter value properly. If you find that it is correct, doing a MySQL install instead of HSQL should make this process much easier.


Thanks,

Ryan

photo
1

Yeah the original driver I had was right. Ended up being that the &jsps; tag was missing from the new file. Thanks for your help

photo
1

Hi Nick,


Glad to hear you got it sorted. Apologies for the confusion! Please don't hesitate to let us know if you have further issues or questions.


Thanks,

Ryan

photo
1

Hi Ryan,

I am having yf application server installed on AWS cloud. Yf dB server is Oracle . Now, i want to change my dB server to new Oracle server with all data, reports, dashboard etc. without installing new yf server.

Is there any solution for it?

photo
1

Hello Amar,


The best way to get support for your issue is to submit a new Problem or a new Private Ticket to us here through the Community. That way we can work your issue on its own thread.


Thanks,

Ryan

photo