Upgrade-database 4.3.6 step needs UniqueValues & CanonicalizeClass

make upgrade-database has errors at 4.3.6 w/reference to to columns UniqueValues and CanonicalizeClass in the CustomFields table

Details:
Redhat 7.7, using perlbrew 0.87 w/perl-5.28.2, MySQL 5.7.28. Going from RT4.2.16 to 4.4.4. In my …/src/rt-4.4.4/ directory and doing make upgrade-database, and selecting 4.2.16 and blank. Copy/paste of the upgrade process below. I saw forum post references to PERL_USE_UNSAFE_INC=1 so I have that environment variable exported, just in case.

The upgrade-database throws errors at the 4.3.6 stage, when trying to insert into the table CustomFields. It refers to UniqueValues and CanoncalizeClass. But these columns don’t seem to be added until 4.4.2 (…/etc/upgrade/4.4.2/schema.mysql)

$ make upgrade-database
/opt/perl/perls/perl-5.28.2/bin/perl -I/opt/rt4/local/lib -I/opt/rt4/lib sbin/rt-setup-database --action upgrade --prompt-for-dba-password
In order to create or update your RT database, this script needs to connect to your mysql instance on localhost (port ‘’) as root
Please specify that user’s database password below. If the user has no database password, just press return.

Password:
Working with:
Type: mysql
Host: localhost
Port:
Name: rt3upgrade
User: rt3upgrade
DBA: root
Enter RT version you’re upgrading from: 4.2.16
Going to apply following upgrades:

  • 4.3.0
  • 4.3.1
  • 4.3.2
  • 4.3.3
  • 4.3.5
  • 4.3.6
  • 4.3.7
  • 4.3.8
  • 4.3.9
  • 4.3.10
  • 4.3.11
  • 4.3.12
  • 4.3.13
  • 4.4.1
  • 4.4.2

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

IT’S VERY IMPORTANT TO BACK UP BEFORE THIS STEP

Proceed [y/N]:y
Processing 4.3.0
Now inserting data.
Processing 4.3.1
Now populating database schema.
Now inserting data.
Processing 4.3.2
Now populating database schema.
Now inserting data.
Processing 4.3.3
Now populating database schema.
Processing 4.3.5
Now inserting data.
Processing 4.3.6
Now inserting data.
[4490] [Mon Dec 30 23:44:18 2019] [warning]: Found CF ‘Category_ProcureHelp’ with categories: Category (./etc/upgrade/4.3.6/content:25)
[4490] [Mon Dec 30 23:44:18 2019] [warning]: Going to create CF Category_ProcureHelp category for it (./etc/upgrade/4.3.6/content:27)
[4490] [Mon Dec 30 23:44:18 2019] [warning]: DBD::mysql::st execute failed: Unknown column ‘UniqueValues’ in ‘field list’ at /opt/perl/perls/perl-5.28.2/lib/site_perl/5.28.2/DBIx/SearchBuilder/Handle.pm line 586. (/opt/perl/perls/perl-5.28.2/lib/site_perl/5.28.2/DBIx/SearchBuilder/Handle.pm:586)
[4490] [Mon Dec 30 23:44:18 2019] [warning]: RT::Handle=HASH(0x69d0da8) couldn’t execute the query ‘INSERT INTO CustomFields (Description, Disabled, Type, UniqueValues, LookupType, Created, ValuesClass, BasedOn, LastUpdated, CanonicalizeClass, RenderType, Pattern, Name, LastUpdatedBy, Creator, MaxValues) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’ at /opt/perl/perls/perl-5.28.2/lib/site_perl/5.28.2/DBIx/SearchBuilder/Handle.pm line 599.
DBIx::SearchBuilder::Handle::SimpleQuery(RT::Handle=HASH(0x69d0da8), “INSERT INTO CustomFields (Description, Disabled, Type, Unique”…, “”, 0, “Select”, 0, “RT::Queue-RT::Ticket”, “2019-12-30 23:44:18”, …) called at /opt/perl/perls/perl-5.28.2/lib/site_perl/5.28.2/DBIx/SearchBuilder/Handle.pm line 352
DBIx::SearchBuilder::Handle::Insert(RT::Handle=HASH(0x69d0da8), “CustomFields”, “Description”, “”, “Disabled”, 0, “Type”, “Select”, …) called at /opt/perl/perls/perl-5.28.2/lib/site_perl/5.28.2/DBIx/SearchBuilder/Handle/mysql.pm line 36
DBIx::SearchBuilder::Handle::mysql::Insert(RT::Handle=HASH(0x69d0da8), “CustomFields”, “Description”, “”, “Disabled”, 0, “Type”, “Select”, …) called at /opt/perl/perls/perl-5.28.2/lib/site_perl/5.28.2/DBIx/SearchBuilder/Record.pm line 1320
DBIx::SearchBuilder::Record::Create(RT::CustomField=HASH(0xb346438), “Created”, “2019-12-30 23:44:18”, “RenderType”, undef, “CanonicalizeClass”, undef, “LastUpdated”, …) called at /opt/apache/rt-4.4.4/sbin/…/lib/RT/Record.pm line 316
RT::Record::Create(RT::CustomField=HASH(0xb346438), “Name”, “Category_ProcureHelp category”, “Type”, “Select”, “RenderType”, undef, “MaxValues”, …) called at /opt/apache/rt-4.4.4/sbin/…/lib/RT/CustomField.pm line 366
RT::CustomField::Create(RT::CustomField=HASH(0xb346438), “Name”, “Category_ProcureHelp category”, “Type”, “Select”, “MaxValues”, 1, “LookupType”, …) called at ./etc/upgrade/4.3.6/content line 34
RT::Handle::ANON() called at /opt/apache/rt-4.4.4/sbin/…/lib/RT/Handle.pm line 929
eval {…} called at /opt/apache/rt-4.4.4/sbin/…/lib/RT/Handle.pm line 929
RT::Handle::InsertData(RT::Handle=HASH(0x69d0da8), “./etc/upgrade/4.3.6/content”, undef) called at sbin/rt-setup-database line 388
main::ANON() called at sbin/rt-setup-database line 401
main::action_insert(“datafile”, undef, “prompt-for-dba-password”, 1, “action”, “upgrade”, “backcompat”, ARRAY(0x282a608), …) called at sbin/rt-setup-database line 569
main::action_upgrade(“package”, “RT”, “prompt-for-dba-password”, 1, “action”, “upgrade”) called at sbin/rt-setup-database line 210 (/opt/perl/perls/perl-5.28.2/lib/5.28.2/Carp.pm:294)

… lots of these …
Processing 4.3.7
Now populating database schema.
Processing 4.3.8
Now populating database schema.
Now inserting data.
Processing 4.3.9
Now inserting data.
Processing 4.3.10
Now populating database schema.
Now inserting data.
Processing 4.3.11
Now inserting data.
Processing 4.3.12
Now populating database schema.
Processing 4.3.13
Now populating database schema.
Processing 4.4.1
Now inserting data.
Processing 4.4.2
Now inserting database indexes.
Now inserting data.
[5123] [Tue Dec 31 00:57:54 2019] [warning]: The owner of ticket #594562 is inconsistent. The denormalized owner is user #814813, but there exists a normalized owner role group member, user #31473. Going to update normalized owner role group members to user #814813. (./etc/upgrade/4.4.2/content:38)
[5123] [Tue Dec 31 00:57:55 2019] [warning]: The owner of ticket #611126 is inconsistent. The denormalized owner is user #31473, but there exists a normalized owner role group member, user #3753. Going to update normalized owner role group members to user #31473. (./etc/upgrade/4.4.2/content:38)
[5123] [Tue Dec 31 00:57:55 2019] [warning]: The owner of ticket #623590 is inconsistent. The denormalized owner is user #11256, but there exists a normalized owner role group member, user #31473. Going to update normalized owner role group members to user #11256. (./etc/upgrade/4.4.2/content:38)
[5123] [Tue Dec 31 00:57:55 2019] [warning]: The owner of ticket #623592 is inconsistent. The denormalized owner is user #11256, but there exists a normalized owner role group member, user #31473. Going to update normalized owner role group members to user #11256. (./etc/upgrade/4.4.2/content:38)
[5123] [Tue Dec 31 00:57:55 2019] [warning]: Unable to load scrip On TimeWorked Change Update User TimeWorked: Couldn’t find row. If you renamed this scrip, you can manually disable it as it is no longer needed. (./etc/upgrade/4.4.2/content:66)
Done.

The post-error-upgrade-database of CustomFields:
mysql (rt3upgrade)>desc 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 | |
| MaxValues | int(11) | YES | | NULL | |
| Pattern | text | YES | | NULL | |
| Description | varchar(255) | YES | | NULL | |
| SortOrder | int(11) | NO | | 0 | |
| LookupType | varchar(255) | NO | MUL | NULL | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
| Disabled | smallint(6) | NO | | 0 | |
| BasedOn | int(11) | YES | | NULL | |
| RenderType | varchar(64) | YES | | NULL | |
| ValuesClass | varchar(64) | YES | | NULL | |
| EntryHint | varchar(255) | YES | | NULL | |
±--------------±-------------±-----±----±--------±---------------+
17 rows in set (0.00 sec)

The only difference in the CustomFields table pre-upgrade and post-(failed)-upgrade is the addition of the EntryHint column, which appears to have been added at the 4.3.1/schema.mysql step.

Just as a trial, I moved the …/etc/upgrade/4.4.2/schema.mysql to …/etc/upgrade/4.3.5/schema.mysql and then re-ran the upgrade-database (after restoring a known-good 4.2.16 database) and while I still had warnings, I didn’t get the unknown-column error. I’m not saying this is the correct fix, but it was something I just wanted to try. While I admit, I don’t understand how the …/upgrade/###/content files work, I didn’t see any conflicting changes in any of the …/schema.mysql files between 4.3.5 and 4.4.2 related to UniqueValues and CanonicalizeClass, so I figured it might be safe to move that file. Notice the “Now populating database schema.” in step 4.3.5 that didn’t exist before.

-bash-4.2$ make upgrade-database

IT’S VERY IMPORTANT TO BACK UP BEFORE THIS STEP

Proceed [y/N]:y
Processing 4.3.0
Now inserting data.
Processing 4.3.1
Now populating database schema.
Now inserting data.
Processing 4.3.2
Now populating database schema.
Now inserting data.
Processing 4.3.3
Now populating database schema.
Processing 4.3.5
Now populating database schema.
Now inserting data.
Processing 4.3.6
Now inserting data.
[5123] [Tue Dec 31 00:29:52 2019] [warning]: Found CF ‘Category_ProcureHelp’ with categories: Category (./etc/upgrade/4.3.6/content:25)
[5123] [Tue Dec 31 00:29:52 2019] [warning]: Going to create CF Category_ProcureHelp category for it (./etc/upgrade/4.3.6/content:27)
[5123] [Tue Dec 31 00:30:16 2019] [warning]: Found CF ‘Department’ with categories: All, A&FS, UCDHS, Campus (./etc/upgrade/4.3.6/content:25)
[5123] [Tue Dec 31 00:30:16 2019] [warning]: Going to create CF Department category for it (./etc/upgrade/4.3.6/content:27)
[5123] [Tue Dec 31 00:30:21 2019] [warning]: Found CF ‘EQHelp Issue Type’ with categories: - (./etc/upgrade/4.3.6/content:25)
[5123] [Tue Dec 31 00:30:21 2019] [warning]: Going to create CF EQHelp Issue Type category for it (./etc/upgrade/4.3.6/content:27)
[5123] [Tue Dec 31 00:30:22 2019] [warning]: Found CF ‘Invoice Exception Reason’ with categories: Reason (./etc/upgrade/4.3.6/content:25)
[5123] [Tue Dec 31 00:30:22 2019] [warning]: Going to create CF Invoice Exception Reason category for it (./etc/upgrade/4.3.6/content:27)

Processing 4.3.7
Now populating database schema.

The rt3upgrade user (and database) is a carried-over database I’m testing with to work out the kinks of the upgrade process, so please don’t let the name fool you :slight_smile: The rt3upgrade user was created with grant all on rt3upgrade.* command, so it should have full access to the rt3upgrade database, as of course, does the root user.

Any ideas on the this CustomFields table UniqueVaules & CanonicalizeClass columns appreciated.

Thanks all,
PH

This sounds similar to this thread. See if the same fix works for you. Looks like at some point the upgrade progress doesn’t add those two new columns in place.