[rt-users] Database upgrade from 3.4.5 to 3.7.87 fails with Unknown Column

[Ken Johnson]

> On Wednesday, 10 Apr 2012, Ruslan wrote:
> > Thank you for the pointer to github.
> >
> > I looked. Based on what tables and columns are present, I conclude
> > that the database updates for 3.3.0 and 3.3.11 were run, but for
> > reasons unknown, the very last part of the 3.3.0 database update,
> > beginning with 'ALTER TABLE CustomFields ...' and including 'UPDATE
CustomFields...'
> > did not occur. On the other hand, dropping the Current column from
> > the TicketCustomFieldValues table in the 3.3.11 update did occur.
> >
> >
> The only idea is that intermediate step failed and this was not noticed by
administrator, so he just continued further.
> >
> >
> > My best idea is to apply the missed portion of the updates and
> > proceed, along the lines of the shell script below. Not ideal, but
> > I don't know a better approach that does not involve time travel.
> > That approach generates one warning during the generation of the
queries.sql file:
> >
> > Use of uninitialized value in join or string at
> > /usr/share/request-tracker3.8/etc/upgrade/upgrade-mysql-schema.pl line
261.
> > .Groups.Instance has type VARCHAR however mapping is missing.
> >
> >
> In upgrade scripts find ALTER TABLE that changes Instance from VARCHAR to
INTEGER.

Ruslan,

Thank you for this helpful suggestion. In the rt-3-0-8/UPGRADING file, found
at http://download.bestpractical.com/pub/rt/release/rt-3-0-8.tar.gz, I found
these recommended changes for Postgres:

Postgres
--------
If you have a Postgres database, the following changes to your database can
improve performance:

ALTER TABLE groups rename instance to instance1;
ALTER TABLE groups add instance int;
UPDATE groups SET instance = instance1::text::int where btrim(instance1) <>
'';
ALTER TABLE groups drop column instance1;

Modifying these for MySQL as shown below, and executing these changes prior
to the partial updates for 3.3.0 removed the warning during the generation
of the queries.sql file.

ALTER TABLE Groups CHANGE Instance Instance1 varchar(64);
ALTER TABLE Groups ADD COLUMN Instance integer;
UPDATE Groups SET instance = convert(Instance1, unsigned) WHERE
trim(Instance1) <> '';
ALTER TABLE Groups DROP COLUMN Instance1;

Thanks,

Ken

> >
> > All of the other operations have no complaints.
> >
> > Ken
> >
> >
> Best regards, Ruslan.



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20130410/674cdba8/attachment.htm>