Re: MYSQL error with IBMDB2 engine

Hi Scott,

Thanks for your feedback! Motivated by your comments, I checked the i5/OS
specific installation documentation in the MySQL reference and found that
it is, in fact, not current. It was supposed to be updated back when the
storage engine GA'ed, so I will be contacting MySQL to understand why that
has not been done yet. I would also suggest that you open a Documentation
bug at http://bugs.mysql.com about any issues you encounter in which you
have determined that the MySQL documentation does not accurately represent
the process that you are seeing. I'll push where I can, but it helps to
have users' specific issues backing me up.

You mentioned that the install fails saying that /etc/my.cnf already
exists. I suspect you are using INSMYQL to install on top of an existing
installation, and as I read back over my instructions to Dale, I think I
am responsible for that misunderstanding. Please revise my instructions to
Dale by inserting steps 2.5 and 3.5: "If using INSMYSQL to install the new
version, temorarily rename /etc/my.cnf" and then "Replace the newly
created /etc/my.cnf with the one you renamed in step 2.5".

I would also like to understand your statement that "the upgrade
instructions in MySQL's site don't work." Are you referring to the
platform-generic upgrade instructions I pointed Dale to earlier, or are
you describing some part of the i5/OS specific instructions? With a quick
glance at the docs I couldn't find anything about the existence of
/etc/my.cnf influencing whether an install or upgrade happens, but I may
have missed something and I'd like to understand where the problem lies.
If there are ways we can improve the documentation (particularly once the
updated content gets published), I'd really like to see that happen. But
this part of the conversation may be better suited for an offline
discussion, so please feel free to email me directly if you prefer.

Thanks,
Tim

Hi Tim,

The upgrade instructions in MySQL's site don't work, and as far as I
can tell, they never have. They state that if you already have an
/etc/my.cnf file, MySQL will perform an upgrade rather than an
install, and that you essentially don't have to do anything else.
(Except make a backup beforehand.)

However, the install fails saying that /etc/my.cnf already exists,
and then aborts the installation.

I've noticed that there's an UPGMYSQL command included in the
MYSQLINST library, but I've never found a document telling me to use
it. From cursory glances at the source code, it would appear that in
order for an upgrade to be successful, you'd have to use UPGMYSQL
and fill in the CURINST parameter (despite that this parameter is
not marked as manditory). But, again, I've found absolutely no
documentation that discusses this, and I'm certainly not so
comfortable with the source code that I'd say that I know what I'm
talking about.

But, something needs to improve here, if MySQL is to remain viable on i.

Timothy P Clark wrote:

Hi Dale,

If you haven't upgraded MySQL, then I'm stumped as to where that error

is

coming from. Since it was working for a while, something must have

changed

since your initial installation. Perhaps the CCSIDs of some columns

were

manually modified? In any case, a fresh installation of MySQL and

SugarCRM

should get you back on track.

I agree that the documentation about upgrading MySQL is lacking. The
difficulty is that there are so many possible configurations of MySQL

that

writing any sort of detailed descriptions of steps is sure to be wrong

for

any particular user scenario. Regretfully, that's the way it is across

all

MySQL platforms. The MySQL reference does have some information at
<http://dev.mysql.com/doc/refman/5.1/en/upgrade.html>. Assuming you've

got

a reasonably straightforward existing installation and that your data
directory is not inside of the existing MySQL server directory tree

(i.e.

not inside mysql-5.1.34-i5os-powerXX), the basic summary is:
(1) as a saftey measure, back up your existing data (using mysqldump

or

similar tool);
(2) shut down the old version of MySQL;
(3) install the new version beside the old version using normal
installation procedures;
(4) run bin/mysql_upgrade in the new version;
(5) start up the new version of MySQL.
Note that these steps only work for 5.1.x to 5.1.y upgrades! Moving

from

5.0 or earlier is a bit more complicated.

That being said, if you installed MySQL using the INSMYSQL command,

you

may find helpful an UPGMYSQL command included in the savefile package

of

MySQL. It was written by the MySQL support team and will upgrade an
existing 5.1.x MySQL installation using basically the same process

that I

outlined above. However, it will only run if no modifications have

been

made to the /etc/my.cnf file since installation. Usage looks something

like
MYSQLINST/UPGMYSQL DIR('/QOpenSys/usr/local/')
DATADIR('/QOpenSys/mysql/data')
USERPRF(MYSQL) MYSQLUSR('root') PASSWORD('root')
CURINST('/QOpenSys/usr/local/mysql-5.1.34-i5os-power64')

Please let me know if you have any questions on the above. Like I said

earlier, its very hard to produce a comprehensive list of upgrade

steps

that will work for everyone. But I'd be happy to help you resolve any
questions that arise from your particular situation.

Hope that helps!
Tim Clark

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