Today was a big day for our Medialab. After a succeeded test we decided to port one of our applications from IIS, SQL Server and Coldfusion to Apache, MySQL and Coldfusion. The last was not very problematic. Only a couple of lines of code needed upgrading from the ancient version 4 where the application was written in to the current version 8. The most difficult part proved to be the transition from SQL Server to MySQL.
The only thing I could work with was a full database backup provided by our IT department. So I could only recreate the database in SQL Server. With the aid of MySQL Migration Toolkit part of the GUI tools I could transfer the database to MySQL. There was hwever a problem, the encoding. And no matter what I tried, there was always one token that did not translate well. So I decided to do the data by hand.
I choose PHP as the language in which I would write the procedure. First read the meta-schema of the SQL Server and with that information I generated MySQL queries which I fired on our new DB. That worked like a charm, Within 5 minutes the data was in the new database and my colleague in the mean time was doing his part on the Coldfusion side.
After one hour he pointed out a slight problem with the data, we were missing some bottom parts of long pages. I looked and confirmed the problem with a couple of entries. So I went back to my data export procedure and tried to pinpoint where I lost the data. After some counting I found that only 4K of information had been exported or 4096 bytes. That was meaningful information.
I opened the PHP Manual of mssql_query and there it was:
If you are having problems with truncated text fields from mssql queries ( at 4096 characters), try some of the following: in php.ini: mssql.textlimit = 65536 mssql.textsize = 65536
I only wonder why this ridiculous low default is there in the first place, because 4K of data is in a lot of circumstances way too low. And php.ini is not the first place where you troubleshoot your code.