Database upgrade from 3.4.5 to 3.7.87 fails with Unkown Column

RT Users
1

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

Here’s what I saw:

Working with:
Type: mysql
Host: localhost
Name: /var/lib/dbconfig-common/sqlite3/request-tracker4/rtdb
User:
DBA: user
Enter RT version you’re upgrading from: 3.4.5

Going to apply following upgrades:

  • 3.5.1
  • …
  • 4.0.6

Enter RT version if you want to stop upgrade at some point,
or leave it blank if you want apply above upgrades: 3.7.87

Going to apply following upgrades:

  • 3.5.1
  • 3.7.1
  • 3.7.3
  • 3.7.10
  • 3.7.15
  • 3.7.19
  • 3.7.81
  • 3.7.82
  • 3.7.85
  • 3.7.86
  • 3.7.87

IT’S VERY IMPORTANT TO BACK UP BEFORE THIS STEP

Proceed [y/N]:y
Processing 3.5.1
Now inserting data.
Processing 3.7.1
Now inserting data.
Processing 3.7.3
Now populating database schema.
[Fri Apr 5 21:40:38 2013] [crit]: DBD::mysql::st execute failed: Unknown
column ‘Pattern’ in ‘CustomFields’ at
/usr/share/request-tracker4/lib/RT/Handle.pm line 515.
(/usr/share/request-tracker4/lib/RT.pm:351)
DBD::mysql::st execute failed: Unknown column ‘Pattern’ in ‘CustomFields’ at
/usr/share/request-tracker4/lib/RT/Handle.pm line 515.

So I thought that perhaps there was some bogus column in the old database:

mysql> use rt3
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
| Tables_in_rt3 |
| ACL |
| Attachments |
| Attributes |
| CachedGroupMembers |
| CustomFieldValues |
| CustomFields |
| GroupMembers |
| Groups |
| Links |
| ObjectCustomFieldValues |
| ObjectCustomFields |
| Principals |
| Queues |
| ScripActions |
| ScripConditions |
| Scrips |
| Templates |
| TicketCustomFieldValues |
| Tickets |
| Transactions |
| Users |
| sessions |
22 rows in set (0.00 sec)ysql> describe CustomFields;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(200) | YES | | NULL | |
| Type | varchar(200) | YES | | NULL | |
| Queue | int(11) | NO | | 0 | |
| Description | varchar(255) | YES | | NULL | |
| SortOrder | int(11) | NO | | 0 | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
| Disabled | smallint(6) | NO | MUL | 0 | |
11 rows in set (0.00 sec)

I don’t see that column in that table in the original database.

Can anyone point me in the right direction?

Ken

2

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

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.

I’d be really concerned and be looking to see what else between your
older version and 3.4.5 was never upgraded.

-kevin

3

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

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

I’d be really concerned and be looking to see what else between your older
version and 3.4.5 was never upgraded.

-kevin

±--------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(200) | YES | | NULL | |
| Type | varchar(200) | YES | | NULL | |
| Queue | int(11) | NO | | 0 | |
| Description | varchar(255) | YES | | NULL | |
| SortOrder | int(11) | NO | | 0 | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
| Disabled | smallint(6) | NO | MUL | 0 | |
±--------------±-------------±-----±----±--------±---------------+

Kevin,

Thank you for replying! I can look into the database upgrade issue.

I’m still confused, though. Why don’t I see the the Pattern field in my
3.4.5 database in the CustomFields table (shown above)? Where would I look
for it if not there? Or is it there but I don’t recognize it?

Ken

4

Thank you for replying! I can look into the database upgrade issue.

I’m still confused, though. Why don’t I see the the Pattern field in my
3.4.5 database in the CustomFields table (shown above)?

Frommy original reply:

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.

So, whoever did a 3.2 → 3.4 upgrade for you missed part of the
upgrade. The Pattern field was new as part of that upgrade.

Where would I look
for it if not there? Or is it there but I don’t recognize it?

-kevin

5

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

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

6

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

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.

7

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

.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