Saturday, December 31, 2011

Decimal Showdown

In setting up replication between a MSSQL publisher and a MYSQL subscriber, I have come across an annoyingly maddening issue. If there is a published column that has the decimal data type, it will cause replication to fail with an obscenely obtuse error. "Multi-step OLEDB error". Yeah try searching for that.
Did you find any reference to replication between MSSQL and MYSQL?
Did you find posts about errors about data types?
Which led me to my next thought.

It came down to me doing a trial and error method going column by column changing the datatype on the MYSQL side, while leaving the MSSSQL datatype untouched. My method was, as follows:

  1. Stop replication
  2. Change data type of MYSQL column
  3. Delete all data from MYSQL table
  4. Reinitialize subscription
  5. Cross fingers

This looped for either 5 or 6 times, I have chosen to forget. Off I went searching The Google for any sort of help. I came up with a promising LiveJournal blog post. While helpful for background information of the 2006 difference between the two decimal data types in question, it's from 2006. There has been a precision change in the MYSQL decimal since then. The Books On-Line is a little thin in terms of the details of the decimal data type.

What is a novice DBA to do? Well use a different data type for MYSQL of course!

Let me forewarn, the data that is being replicated goes through no changes once it has been shipped over to the MYSQL server. All of the work of mathematics is done on the MSSQL side. The MYSQL is merely for displaying the data, not manipulating it. As such, I have elected to use the float data type. To reiterate I am doing no calculations on the MYSQL side, this must be said because using float is a really bad idea.

Don't believe me? Just ask this commentator on a StackOverflow thread. Using float to do calculations will give you a number, however you can't be sure of the exactness of it. Sure, if you using whole numbers and not worrying about accounting values, you don't have to worry about rounding. I on the other hand, do. We have gone through extensive testing in preparation for the data type change.

Our replication setup is such that the tables on the MSSQL side are just copies of the tables that are used in production. The tables on the replication server are used in no calculations, in our testing there was no change in the values on either side of the fence.

So in conclusion, don't do what I have done, do not use the float type as an equivalent data type for replication between MSSQL and MYSQL. It only works in this one situation. I am still on the search for a  resolution to my problem. Perhaps a different numeric data type will swoop in and save the day? Perhaps some DBA much smarter than myself will tell me why can't the two decimal data types get along? Until then, I'll have to deal with using float in place of decimal. If anyone out there has a solution please for the love of all that is holy, email me.