MYSQL error with IBMDB2 engine

Tim,

We are running MYSQL 5.1.34 that was installed back in May. I guess it's time for 5.1.39


As I recall, we developed the PHP program this month and it worked ok for a while. Then it started throwing the character set error 2501.

But it doesn't matter, a bug is a bug. If it's fixed in 5.1.39, I will install the new MYSQL.
The redbooks and papers are all great for installing the first time, but there is not as much about upgrading to a newer version.
I will follow the redbook "Using IBM DB2 for i as a Storage Engine of MySQL" and hope for the best


Since I have not started working with SUGARCRM, I will see if I can figure out how to uninstall, and then start over after the MYSQL upgrade.

Thanks also for clarifying when to call the IBM support line. A CPF error or job log error. That makes sense.

---Dale

I'm one of the storage engine developers, and I think I understand what you're seeing. There was a bug in early releases of the storage engine such that certain Unicode character sets were not being created with the preferred CCSID (utf8 was created as CCSID 1200 instead of 1208, for example.) This worked fine, if somewhat slowly, but we chose to fix this behavior in later MySQL releases. Unfortunately, one side effect was that tables that had been created with the previous behavior looked incorrect to later versions, generating the 2501 error that you're seeing. You didn't say that you had upgraded MySQL, but based on the time frames indicated in your post, I'm guessing you were caught in this situation.

To work around this, you will need to use the older version of MySQL (5.1.36 or earlier) to get the data out of any affected IBMDB2I tables and then place the data back into the IBMDB2I tables using a current release of MySQL. One way to do this is to use the older version of MySQL to alter the tables to use MyISAM. Then upgrade to a current release of MySQL and alter the tables back to use IBMDB2I again. Or you could select the table data into an outfile under the old version and with a new version recreate the tables (i.e. reinstall SugarCRM) and select the data from the outfiles back into the tables.

As far as support goes, any failures that are clearly IBM i related (e.g. produce CPF or MCH messages in a job log) can be reported directly to IBM Support under your normal support terms. Bugs such as this one should be reported to MySQL through your respective support channels (if you have purchased the Enterprise edition of MySQL) or through the bugs.mysql.com website (for Community edition users.)
Tim Clark