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

[Ken Johnson]

>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.

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.

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