I tried to copy a mysql database with several gigabytes of data and suddenly the receiving end chokes on a ‘nan’.
Nani?
Well apparently earlier versions of mysql supported ‘nan’ and ‘inf’ as valid float values but newer versions don’t as they abide by the SQL standard which states:
‘Thou shalt not NaN’
And thus it’s in the data but it won’t go into the new database.
Suckage ensues…
So how do you find all those NaNs to make them something useful again?
Magic!
SELECT * FROM `table` WHERE `value` > 3e+40
If i understood it correctly 3e+40 is larger than maxFloat but NaN wins the comparison. The same can be used to replace all those values with something valid.
Yay!
Go nuts.