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

[Ruslan Zakirov]

On Wed, Apr 10, 2013 at 1:38 AM, Ken Johnson <kjohnson at eclypse.org> wrote:

>
> >On Fri, Apr 05, 2013 at 05:43:41PM -0500, Ken Johnson wrote:
> >> I'm trying to upgrade from RT 3.4.5 to 4.0.7 on Debian 6.0.7.
> >>
> >> I backed up the existing database and loaded it a new database
> >> according the the instructions in UPGRADING.mysql.  I reached the
> >> point where I believed it was time to perform the first stage of the
> >> database upgrades, and used a command like this:
> >>
> >> rt-setup-database-4 --action upgrade --dba user --dba-password
> >> password Enter RT version you're upgrading from: 3.4.5
> >
> >On Fri, Apr 05, 2013 Kevin Falcone wrote:
> >
> >It looks like at some point in the past - someone upgraded you to
> >3.4.5 without running the database upgrades.  Version 3.3.0 of RT
> >added the Pattern column to the CustomFields table.
> >
> >
> https://github.com/bestpractical/rt/blob/stable/etc/upgrade/3.3.0/schema.my
> sql
> >
> >I'd be really concerned and be looking to see what else between
> >your older version and 3.4.5 was never upgraded.
> >
> >-kevin
>
> 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.



> All of the other operations have no complaints.
>
> Ken
>
> ================== script to do the database updates =====================
>
> #!/bin/bash -vx
> #
> # Drop the previous RT4 database.
> #
> mysql --user=user --password=password </home/shared/sysadmin/dropRT4.sql
> #
> # Create an empty RT4 database.
> # Specify the character set default of the RT4 database.  See
> UPGRADING.mysql item 1b)
> #
> mysql --user=user --password=password </home/shared/sysadmin/createRT4.sql
> #
> # Dump the RT3 database to an .sql file.  See UPGRADING.mysql item 1a)
> #
> mysqldump -F --opt --user=user --password=password
> --default-character-set=binary rt3 >/tmp/daily/rt3.sql
> #
> # Load rt3.sql into the RT4 database.  See UPGRADING.mysql item 1c)
> #
> mysql --user=user --password=password --default-character-set=binary rt4
> </tmp/daily/rt3.sql
> #
> # Grant access for rt_user to the RT4 database
> #
> mysql --user=user --password=password rt4
> </home/shared/sysadmin/grantRT4.sql
> #
> # Apply the 3.3.0 database update subset
> #
> mysql --user=user --password=password rt4
> </home/shared/sysadmin/rt3.3.0dbpartial.sql
> #
> # Apply the 3.3.11 database update subset (nothing to do -- previously
> done.)
> #
> # mysql --user=user --password=password rt4
> </home/shared/sysadmin/rt3.3.11dbpartial.sql
> #
> # Apply the 3.3.11-3.7.87 updates with rt-setup-database-4 --action upgrade
> --dba user --dba-password password
> #
> rt-setup-database-4 --action upgrade --dba user --dba-password password
> </home/shared/sysadmin/first.txt
> #
> # Apply the RT 3.8 schema upgrades.  See UPGRADING.mysql item 5)
> #   First step, generate queries.sql file
> #   Second step, apply queries.sql file.  See UPGRADING.mysql item 7)
> #   See /usr/share/request-tracker3.8/etc/upgrade-mysql-schema.pl
> perl /usr/share/request-tracker3.8/etc/upgrade/upgrade-mysql-schema.pl rt4
> user password >/home/shared/sysadmin/queries.sql
> mysql --user=user --password=password rt4
> </home/shared/sysadmin/queries.sql
> #
> # Apply the RT 3.7.87-4.0.6 database updates.  See UPGRADING.mysql item 8)
> #
> rt-setup-database-4 --action upgrade --dba user --dba-password password
> </home/shared/sysadmin/second.txt
>
>
>
>
>


-- 
Best regards, Ruslan.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20130410/4532791e/attachment.htm>