Firebird Migration: Number [ NUMERIC ] Field Data Corruption

sdritchey · October 19, 2018, 4:30am

In testing a copy of my existing database that has been migrated to Firebird, I’ve noticed some apparently random Number [ NUMERIC ] field data being corrupted. Typical entries for my Table would look something like (0.50, 3.00, 10.00, 55.00 or similar).

Post migration all entries with the previous value of 3.00 are showing -652.36, previous values of 4.00 are showing -651.36, and previous values of 5.00 are showing -650.36. All other numeric entries appear to be correct.

I plan on filing a bug report. In the meantime since this does follow a pattern, how could I go about correcting this?

Ratslinger · October 19, 2018, 4:48am

Hello,

Bug report already exists: Bug #118043. There is also one filed for transferring existing tables from another .odb containing decimal or numeric info: Bug #116893.

Have not seen any explainable pattern in this problem - gave up looking. I found only two methods to successfully move my data to Firebird Embedded - 1) via macro (real pain); 2) data transfer from Firefird Server.

This last method is noted in my answer in this post → Base: HSQLDB to Firebird migration for existing databases.

Edit 2018-12-09:

Have now discovered a third and easier method to resolve the problem. This method uses a text field as an interim in copying the data. While the process still requires a number of steps, there is nothing else to load or code to write (with the exception of an SQL statement). I have attached a document to explain the process simply because it includes many images to better explain it and therefore limiting the amount of necessary space in this answer:

Conversion document ----- FirebirdTableMigration.odt

To complete this, here is the Base file used in this process. This can be used to test the process if wanted.

Base file --------- TableMigration.odb

I am hoping this encourages others to now convert existing files. The fix noted for LO v6.2 is still not available as of this writing & nothing appears on the horizon as of yet. This is the critical piece missing in many cases - not being able to convert data correctly. And without data converted how is testing possible? Other problems exist but there are typically simple fixes to get around them. Not so for data conversion and many DB’s do use numeric or decimal types in tables.

Hopefully more people will now convert if only to test, as Firebird embedded is at least more advanced than the HSQLDB embedded now used.

sdritchey · October 19, 2018, 3:03pm

Thanks for the info, in the bug report looks like this is due to be fixed in 6.2.0. I’ll wait for the patch before fully migrating my database.

sdritchey · May 9, 2019, 3:08pm

Having finally given up on this issue being officially resolved in an upcoming release any time soon, I took a stab at ratslingers third and easier method mentioned under “Edit 2018-12-09” above.

I had dozens of table columns to recreate so it took some time, but it did indeed work (at least it appears to have so far). My databse is up and running in Firebird format and all NUMERIC values converted correctly.

I’d take a practice run on his sample files to get a handle on the steps, then take a crack at a copy of your database (not original, make a backup first).

Ratslinger · May 9, 2019, 5:28pm

@sdritchey

Don’t see any further fixes in near future either. Glad you tried and had success. Has worked for me for some time now.

nikant · March 2, 2019, 10:10am

From LO 6.0.7 stable we tried to go to 6.1.5 stable and turned on “experimental features” in order to try conversion from HSQLDB embedded to Firebird db embedded.
Unfortunately most of our DECIMAL data got corrupted and turned into -16xxxx.xx values.

This is highly critical for us since 6.1.5 was considered stable release. It is understandable that Firebird is still experimental feature.
We also tried the migration wizard in an 6.2.x portable version of LO but the problem was reproduced again and the conversion was erroneous.

EDIT 15/3/2019

This was tested in a portable version of LO (6.1.4)
Keep in mind:

I didn’t already have negative numeric values in my DB (although this I guess can be adjusted) DO NOT USE THIS YET IF YOU HAVE LEGITIMATE NEGATIVE NUMBERS IN YOUR DB (I have written a method if you already have negative numbers but I cannot guarantee your data)
It worked for me… can’t guarantee anything
For some reason the errors are in the same range specifically following one of those two numbers as a total
167772.16 and 655.36

Procedure:

Copy your HSQLDB file for backup reasons
Open HSQLDB copy file and convert to Firebird → Yes
Errors appear
Close Table with errors and run the following SQL command FIRST changing TABLENAME and COLUMNNAME accordingly

UPDATE "TABLENAME" SET "TABLENAME"."COLUMNNAME" = "TABLENAME"."COLUMNNAME" + 167772.16 WHERE "TABLENAME"."COLUMNNAME" < -16000

for all your columns with problems to fix the errors in the -16xxx range

Then if you have errors in the -6xx range run again the following

UPDATE "TABLENAME" SET "TABLENAME"."COLUMNNAME" = "TABLENAME"."COLUMNNAME" + 655.36 WHERE "TABLENAME"."COLUMNNAME" < -600

Save your db file and double check your numbers

This is the file by @Ratslinger converted by the above method: TableMigration-SQLFirebird.odb

mikekaganski · March 2, 2019, 10:13am

Well - users will, of course, see it.

Ratslinger · March 2, 2019, 3:55pm

@nikant My answer in this post provides three alternative methods to convert data. The edit section provides a relatively easy method around the Decimal conversion process.

Aside from the conversion, there are a few other inconvenient situations of which all can rather easily be resolved such as Auto increment and sub forms. Links in my answer & ‘Firebird’ search in this forum should provide needed information.

Once past initial conversion situations, Firebird does prove to be much better than HSQLDB for use as an embedded database.

nikant · March 7, 2019, 6:18am

@ratslinger Yes I have read your post and thank you for the extremely useful info!
However I have to point that although I may be able to use all the tricks for a correct conversion there may be other situations that this is not advised for example offices using open source software like LO with data two valuable to be lost or even if a conversion is done thousands of records to be rechecked if everything is OK. Total waste of work hours because of a what it seems like an overflow error in the migration code.

Ratslinger · March 7, 2019, 3:46pm

@nikant It is your option to do as you see fit. I did in fact go through thousands of records in conversion and verification without much time consumed. Regardless of the process, whether it is what I have presented for converting or the LO conversion, you should always go through a verification process. Therefore, it appears it is just a matter of preference as to when the verification is to be done.

EDIT: Just want to add, even after a conversion there are several differences when using Firebird. Most are in SQL but if any macros are used, this can also be affected. A few other bugs need attention such as sub forms & possibly auto increment fields.

Ratslinger · March 15, 2019, 3:55pm

@nikant Your edited solution is not practical. In my particular personal files (not posted sample) there are a variety of different negative values erroneously appearing. This would mean running a variety of SQL statements a number of times! In my procedure, there is only one pass required and the it is not necessary to know what values you are looking for.

With that said, it appears that LO v6.2.1.2 now converts the data correctly. Still recommend verifying results.

nikant · March 15, 2019, 4:42pm

@Ratslinger as stated in my files I had a specific problem with those numbers and I already wrote that this is not for everyone. It worked for me and your posted sample file. Also the numbers agree with original poster @sdritchey
Your method did not work for me. I had popup sql errors during your method. I do not know why and not interested in investigating further.
This is not a competition whose method is better. I just posted what worked for my files and my numbers (although I would like to see if your existing negative numbers have a relation with the sums mentioned above)

Verifying results is mandatory, never said otherwise.

Ratslinger · March 15, 2019, 5:07pm

@nikant Never tried to make it a “competition” but rather a safer more efficient process.

Please ignore previous comment about conversion now working. Still generates erroneous negative figures with decimal or numeric types. Type Double seems to work OK.

Have now also tried your method on one of my tables & works fine. Although it takes multiple steps, the original data type is retained. As this is only one table/test, I would be cautious in saying (as you stated) this works in all cases.

Ratslinger · March 28, 2019, 4:13am

Have now come across another value - original value of 1 changed to -254.

nikant · March 28, 2019, 11:30am

@Ratslinger bad news then… do you have a sample file? I cannot replicate in your TESTAMOUNTS file…

Ratslinger · March 28, 2019, 2:49pm

The file is not really important. The only reason to post is to warn that other condition(s) exist and to be aware. I personally have been using the text method (in my answer) without any problems.