3 Replies Latest reply on Oct 30, 2013 6:41 AM by conners

    Schema/datatype reference for database conversion from Oracle?


      Hi all,


      We have been running Web Help Desk on Oracle for a while now, but with the upgrade to version 12 I need to convert my database to MySQL as it seems Oracle is no longer a supported database.  I have a process to migrate the schema and data to MySQL (using the defunct MySQL Migration Toolkit), but found that by default it converts all Oracle NUMBER datatypes to MySQL DECIMAL.  This is a problem as according to the database datatypes appendix in the documentation (http://www.solarwinds.com/documentation/WebHelpDesk/wwh/wwhelp/wwhimpl/js/html/wwhelp.htm#href=WebHelpdeskAGDatabaseOptions.htm) columns that Oracle stored as NUMBER need to be stored as INT in MySQL, except for currency columns which should be stored as DECIMAL.


      I am looking for some reference material that I can use to identify the currency columns within the Web Help Desk 11.x database schema so that I can make sure that only those columns come across as DECIMAL while leaving the others as INT.  I'm not asking for a conversion script (though that would be very nice), but if there is some way that I can identify the Web Help Desk datatype (PK, currency, datetime, integer, pk, data, text, varchar) for each column in my database, that would allow me to finish the migration so that we can upgrade to version 12.


      (I also have the same problem with the BLOB datatype which needs to become BLOB or LONGBLOB in MySQL, depending on whether or not Web Help Desk considers the column content to be 'data' or 'pk'.  How to know the difference?)


      Has anyone else successfully migrated their Web Help Desk database from Oracle to MySQL?  I plan to go through my table creation script line by line for all 187 tables and 1595 NUMBER columns to change the datatypes to the correct ones, but I'd like to have an authoritative reference to base this on, not just trial and error.


      Thanks all!