Migration Prep

Paul_O_Rorke1 · July 25, 2013, 3:36am

Hi,

I’ve been putting off upgrading my RT 3.8.4 to current because I always
got stuck. Now I can’t put it off any more. I have read lost about
upgrading but I don’t want to upgrade the current server/installation, I
want to move it to a brand new server and OS.

From what I’ve read it seems that the best approach might be to:

make a clone of the existing RT,
upgrade it to 4.0.13
do a mysqldump on the upgraded clone
restore the dump on the new server.

It’s the details of step 4 I’m concerned about. If I set up the
database on the new server and restore it from the dump do I then use
make upgrade on the new server? I don’t understand what the right
process would be to make sure the new server has mailgate, users etc and
can accept the dump.

Do I configure the whole new RT instance as though it were a new install
then try to migrate my data or should it be and ‘upgrade’ even though
it’s new?

I apologise if this is a dumb question or if it’s covered somewhere in
the docs - the whole task of upgrading from 3.8.4 has been overwhelming
and I’ve been avoiding it.

Paul O’Rorke
Tracker Software Products
paul@tracker-software.com mailto:paul.ororke@tracker-software.com

Kevin_Falcone · July 25, 2013, 4:32pm

From what I’ve read it seems that the best approach might be to:
1. make a clone of the existing RT,
2. upgrade it to 4.0.13
3. do a mysqldump on the upgraded clone
4. restore the dump on the new server.

Since you have a new server, upgrading should be easy, if something
fails, you just start again on the new server. You’re not in any way
touching production until the final cutover.

I always:

Install RT 4.0.14 on the new server
mysqldump the old server
import on the new server
run make upgrade-database
test
turn off the old server
do a final dump, import, upgrade
go forth and rejoice.

It’s the details of step 4 I’m concerned about. If I set up the database on the new server
and restore it from the dump do I then use make upgrade on the new server? I don’t understand
what the right process would be to make sure the new server has mailgate, users etc and can
accept the dump.

You have to copy things like cron jobs and your mailgate config.
I don’t understand what you mean by ‘users etc and can accept the
dump’?

If you want to create a very empty DB on the rt4 host, run

/opt/rt4/sbin/rt-setup-databas --action create,acl
then restore your dump

You don’t say what docs you’ve reviewed, but I frequently recommend to
the list our upgrading and README rather than any third party guides
as well as this quite old but still surprisingly relevant blog post I
wrote.

-kevin

Paul_O_Rorke1 · July 25, 2013, 8:47pm

Thanks for that Kevin,

I meant the README Docs in the installer archive mostly.

My concern with the migration is that I used custom statuses for queues
and I have to now use the LifeCycle set up. I wasn’t sure how the DB
restore would go because I imagine I’m going to have different fields in
my database than what 4.0.18 expects. Anyway - installing 4.0.14 on my
new server now and will get back I’m sure if I run into any troubles…

regards

Paul O’Rorke
Tracker Software Products
paul@tracker-software.com mailto:paul.ororke@tracker-software.comOn 7/25/2013 9:32 AM, Kevin Falcone wrote:

On Wed, Jul 24, 2013 at 08:36:43PM -0700, Paul O’Rorke wrote:
From what I've read it seems that the best approach might be to:

 1. make a clone of the existing RT,
 2. upgrade it to 4.0.13
 3. do a mysqldump on the upgraded clone
 4. restore the dump on the new server.
Since you have a new server, upgrading should be easy, if something
fails, you just start again on the new server. You’re not in any way
touching production until the final cutover.

I always:

Install RT 4.0.14 on the new server
mysqldump the old server
import on the new server
run make upgrade-database
test
turn off the old server
do a final dump, import, upgrade
go forth and rejoice.
It's the details of step 4 I'm concerned about.  If I set up the database on the new server
and restore it from the dump do I then use make upgrade on the new server?  I don't understand
what the right process would be to make sure the new server has mailgate, users etc and can
accept the dump.
You have to copy things like cron jobs and your mailgate config.
I don’t understand what you mean by ‘users etc and can accept the
dump’?

If you want to create a very empty DB on the rt4 host, run

/opt/rt4/sbin/rt-setup-databas --action create,acl
then restore your dump

You don’t say what docs you’ve reviewed, but I frequently recommend to
the list our upgrading and README rather than any third party guides
as well as this quite old but still surprisingly relevant blog post I
wrote.

Upgrading to RT 4 — Best Practical Solutions

-kevin

Craig_Ringer · July 26, 2013, 4:41am

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1On 07/26/2013 12:32 AM, Kevin Falcone wrote:

On Wed, Jul 24, 2013 at 08:36:43PM -0700, Paul O’Rorke wrote:

From what I’ve read it seems that the best approach might be to:

make a clone of the existing RT, 2. upgrade it to 4.0.13 3. do
a mysqldump on the upgraded clone 4. restore the dump on the new
server.

Since you have a new server, upgrading should be easy, if
something fails, you just start again on the new server. You’re
not in any way touching production until the final cutover.

I always:

Install RT 4.0.14 on the new server mysqldump the old server import
on the new server run make upgrade-database test turn off the old
server do a final dump, import, upgrade go forth and rejoice.

I just followed much the same process (with 4.0.15 and PostgreSQL),
except that I used replication (Londiste) to narrow the outage window,
streaming changes from the old to the new server until the moment of
switch-over.

I also used rinetd to redirect traffic from the old server to the new
one, so there’d be no visible outage to people whose DNS hadn’t caught
up to the A record change yet. This was necessary because the DNS host
used is unfortunately not able to lower the TTL.

You can do the same thing for mail handling if your RT server receives
mail by direct SMTP, or you can change rt-mailgate in /etc/aliases to
remote-deliver over http directly to the new server and set the Apache
security rules to allow it.

With a little effort you can keep the outage window down to less than
a minute.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.13 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJR8f3iAAoJELBXNkqjr+S21hwH/jK0kIt2jZrRWDwhut+5tygJ
HbP7oS+ulzZI+MtrSehmkKPn5E/wa6v+XIgCCsBSU8dG7bXlk60k0IrICW/W1sQ8
dawmw/agyKolj30/OPz2Ac+86uePLYMkEzDKuWlsEASIK/JFM9yOPH9T/m5fYrin
xb0IFZJV3wPBfteuWH3mFxkrH/Od4G5nEKAlc22WXOz8vJZti24qW6Nn3/D5M1c3
uJPAe9/AeJznxiaTIjCfQEx/IWrwb1w35c9EIMfA9PedhCAzzV95ng1y3l5+NJal
gRpV4WeD9A05vjtq9CsuGt3MZHDgv6LI9MQi6nCU5t8CQ6Eel+afD9kxkKR7f4A=
=kmnO
-----END PGP SIGNATURE-----

Kevin_Falcone · July 26, 2013, 12:53pm

I meant the README Docs in the installer archive mostly.

My concern with the migration is that I used custom statuses for queues and I have to now use
the LifeCycle set up. I wasn’t sure how the DB restore would go because I imagine I’m going
to have different fields in my database than what 4.0.18 expects. Anyway - installing 4.0.14
on my new server now and will get back I’m sure if I run into any troubles…

To start, just port your custom statuses to LifeCycles. That should
be as easy as adding them to the active and inactive lists and
defining some transitions.

Once you’re migrated, you can deal with tweaking them to be more
complicated.

-kevin

Paul_O_Rorke1 · July 29, 2013, 9:30pm

Thanks Kevin,

I’m going through that right now. I must admit I’m a little confused by
the whole LifeCycle thing. I’m going through
Customizing/Lifecycles - RT 4.0.25 Documentation - Best Practical now to
see if I can swing this.

In the meanwhile I am finding that after running make upgrade-database
(took me through all the upgrades from 3.8.4 to 4.0.15) and then trying
to set up Apache again I’m running into a loop on the login page. If I
run Apache I don’t get a login page at all. If I stop Apache and start

/opt/rt4/sbin/rt-server

then I get:

Reason: your browser did not supply a Referrer header
(/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:397)
[Mon Jul 29 21:25:46 2013] [notice]: Marking original destination as
having side-effects before redirecting for login.
Request: /rt/NoAuth/Login.html?next=d820f5cbf943dfe116cbee257b840411
Reason: your browser did not supply a Referrer header
(/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:397)
[Mon Jul 29 21:26:00 2013] [error]: FAILED LOGIN for root from
192.168.0.254 (/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:753)
[Mon Jul 29 21:26:12 2013] [error]: FAILED LOGIN for rtuser from
192.168.0.254 (/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:753)

I’m assuming I’ve not actually managed to get my database upgraded
properly as I’m very confident of the credentials used (tried others as
well that worked in 3.8.4). After running make upgrade-database do I
still need to do the individual upgrades in each of the README files for
the various updates or does make upgrade-database effectively do the same?

Thanks

Paul O’Rorke
Tracker Software Products
paul@tracker-software.com mailto:paul.ororke@tracker-software.comOn 7/26/2013 5:53 AM, Kevin Falcone wrote:

On Thu, Jul 25, 2013 at 01:47:07PM -0700, Paul O’Rorke wrote:
I meant the README Docs in the installer archive mostly.

My concern with the migration is that I used custom statuses for queues and I have to now use
the LifeCycle set up.  I wasn't sure how the DB restore would go because I imagine I'm going
to have different fields in my database than what 4.0.18 expects.  Anyway - installing 4.0.14
on my new server now and will get back I'm sure if I run into any troubles...
To start, just port your custom statuses to LifeCycles. That should
be as easy as adding them to the active and inactive lists and
defining some transitions.

Once you’re migrated, you can deal with tweaking them to be more
complicated.

-kevin

Paul_O_Rorke1 · July 29, 2013, 9:57pm

I guess I should add that although I cannot log into RT through the WEB
I can confirm that both root and rtuser can access mysql locally via the
command line. I guess however that although the users that RT runs as
can access MySQL that does not mean that the users defined in there are
working…

Paul O’Rorke
Tracker Software Products
paul@tracker-software.com mailto:paul.ororke@tracker-software.comOn 7/29/2013 2:30 PM, Paul O’Rorke wrote:

Thanks Kevin,

I’m going through that right now. I must admit I’m a little confused
by the whole LifeCycle thing. I’m going through
Customizing/Lifecycles - RT 4.0.25 Documentation - Best Practical now
to see if I can swing this.

In the meanwhile I am finding that after running make
upgrade-database (took me through all the upgrades from 3.8.4 to
4.0.15) and then trying to set up Apache again I’m running into a loop
on the login page. If I run Apache I don’t get a login page at all.
If I stop Apache and start
/opt/rt4/sbin/rt-server
then I get:

Reason: your browser did not supply a Referrer header
(/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:397)
[Mon Jul 29 21:25:46 2013] [notice]: Marking original destination as
having side-effects before redirecting for login.
Request: /rt/NoAuth/Login.html?next=d820f5cbf943dfe116cbee257b840411
Reason: your browser did not supply a Referrer header
(/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:397)
[Mon Jul 29 21:26:00 2013] [error]: FAILED LOGIN for root from
192.168.0.254 (/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:753)
[Mon Jul 29 21:26:12 2013] [error]: FAILED LOGIN for rtuser from
192.168.0.254 (/opt/rt4/sbin/…/lib/RT/Interface/Web.pm:753)

I’m assuming I’ve not actually managed to get my database upgraded
properly as I’m very confident of the credentials used (tried others
as well that worked in 3.8.4). After running make upgrade-database
do I still need to do the individual upgrades in each of the README
files for the various updates or does make upgrade-database
effectively do the same?

Thanks

Paul O’Rorke
Tracker Software Products
paul@tracker-software.com mailto:paul.ororke@tracker-software.com

On 7/26/2013 5:53 AM, Kevin Falcone wrote:
On Thu, Jul 25, 2013 at 01:47:07PM -0700, Paul O’Rorke wrote:
I meant the README Docs in the installer archive mostly.

My concern with the migration is that I used custom statuses for queues and I have to now use
the LifeCycle set up.  I wasn't sure how the DB restore would go because I imagine I'm going
to have different fields in my database than what 4.0.18 expects.  Anyway - installing 4.0.14
on my new server now and will get back I'm sure if I run into any troubles...
To start, just port your custom statuses to LifeCycles. That should
be as easy as adding them to the active and inactive lists and
defining some transitions.

Once you’re migrated, you can deal with tweaking them to be more
complicated.

-kevin

Kevin_Falcone · July 30, 2013, 2:42pm

In the meanwhile I am finding that after running make upgrade-database (took me through all
the upgrades from 3.8.4 to 4.0.15) and then trying to set up Apache again I’m running into a
loop on the login page. If I run Apache I don’t get a login page at all. If I stop Apache
and start

Are you running your 3.8 Apache config or a new 4.0 config.
We can’t help debug that problem without seeing error logs or a
config.

/opt/rt4/sbin/rt-server

then I get:

You don’t say what you did to cause this. Load the login page?
Attempt to log in? There are clearly a few actions mixed in there.

Also - did you run the password updater, either as a security fix back
in 3.8 or more recently as part of the upgrade?
http://bestpractical.com/docs/rt/latest/UPGRADING-3.8.html

-kevin

Paul_O_Rorke1 · July 30, 2013, 10:17pm

Thanks for that Kevin,

not the first time - a silly little mistake had me scratching my head.
I had in my Apache config a rewrite rule for /rt and had the wrong value
in RT_Siteconfig for Set($WebPath, ‘’);

So I now get my logon screen but cannot log in.

I did run the password updater:

root@rt4:/opt/rt4# perl /opt/rt4/etc/upgrade/vulnerable-passwords --fix
Upgrading 16 users...
Done.

I am assuming that I can’t just UPDATE the passwords in MySQL because of
the hashing? I tried without success:

mysql> UPDATE rtdb.Users SET Password=PASSWORD('xxxx') WHERE
Name='jamie';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

I guess I have missed something in these upgrade README files. I do
appreciate your help. Now that I’ve the Apache thing sorted I’m feeling
like on the home stretch - if only I can sort these passwords out. Is
there another related script I missed perhaps?

Sincerely

Paul O’Rorke
Tracker Software Products Canada Limited

paul@tracker-software.com mailto:paul@tracker-software.com
tracker-software.com http://tracker-software.com

Tel: +1 (250) 324 1621
Fax: +1 (250) 324 1623

PLEASE NOTE : - If you are sending files for us to look at or assist with
these must ALWAYS be wrapped in either a ZIP/RAR or 7z FILE
or they will be removed by our Firewall/Virus management software.

Certified by Microsoft
“Works with Vista”
PDF-XChange & SDK, Image-XChange SDK,
PDF-Tools & SDK, TIFF-XChange & SDK.

Support:
http://docu-track.com/support/ http://tracker-software.com/support/
or
PDF XChange Forum - Index page
http://tracker-software.com

Download latest Releases
http://www.tracker-software.com/downloads/
http://tracker-software.com/downloadsOn 07/30/2013 07:42 AM, Kevin Falcone wrote:

On Mon, Jul 29, 2013 at 02:30:35PM -0700, Paul O’Rorke wrote:

In the meanwhile I am finding that after running make upgrade-database (took me through all
the upgrades from 3.8.4 to 4.0.15) and then trying to set up Apache again I'm running into a
loop on the login page.  If I run Apache I don't get a login page at all.  If I stop Apache
and start

Are you running your 3.8 Apache config or a new 4.0 config.
We can’t help debug that problem without seeing error logs or a
config.

/opt/rt4/sbin/rt-server
then I get:

You don’t say what you did to cause this. Load the login page?
Attempt to log in? There are clearly a few actions mixed in there.

Also - did you run the password updater, either as a security fix back
in 3.8 or more recently as part of the upgrade?
UPGRADING-3.8 - RT 5.0.5 Documentation - Best Practical

-kevin

Reason: your browser did not supply a Referrer header
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:397)
[Mon Jul 29 21:25:46 2013] [notice]: Marking original destination as having side-effects
before redirecting for login.
Request: /rt/NoAuth/Login.html?next=d820f5cbf943dfe116cbee257b840411
Reason: your browser did not supply a Referrer header
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:397)
[Mon Jul 29 21:26:00 2013] [error]: FAILED LOGIN for root from 192.168.0.254
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:753)
[Mon Jul 29 21:26:12 2013] [error]: FAILED LOGIN for rtuser from 192.168.0.254
(/opt/rt4/sbin/../lib/RT/Interface/Web.pm:753)
I'm assuming I've not actually managed to get my database upgraded properly as I'm very
confident of the credentials used (tried others as well that worked in 3.8.4).  After running
make upgrade-database do I still need to do the individual upgrades in each of the README
files for the various updates or does make upgrade-database effectively do the same?

Kevin_Falcone · July 31, 2013, 3:48pm

I am assuming that I can’t just UPDATE the passwords in MySQL because of the hashing? I tried
without success:
 mysql> UPDATE rtdb.Users SET Password=PASSWORD('xxxx') WHERE Name='jamie';
 Query OK, 1 row affected, 1 warning (0.01 sec)
 Rows matched: 1  Changed: 1  Warnings: 1

The jamie user will definitely not be able to log in after this.
That puts a MySQL password hash into RT, which is not expecting
that.

I guess I have missed something in these upgrade README files. I do appreciate your help.
Now that I’ve the Apache thing sorted I’m feeling like on the home stretch - if only I can
sort these passwords out. Is there another related script I missed perhaps?

You can safely use the techniques here. RT should update the md5 one
for you and if you change rt3 for rt4 in the perl code, it will
generate a proper password the first time out.

http://requesttracker.wikia.com/wiki/RecoverRootPassword

However, this does bring up a question. Show us
show create table Users;

-kevin

Paul_O_Rorke1 · July 31, 2013, 4:23pm

Sorry to keep bugging this list,

can anyone tell me why I can’t set the password in MySQL with the UPDATE
statement below? I have upgraded from 3.8.4 to 4.0.14 and run the
password updater. MySQL was at 5.1 so I didn’t need to do the MySQL
update stuff as far as my reading shows.

Is there something wrong with my query or is there something that RT is
doing such that the passwords are perhaps hashed differently?

I’d so love to get past this final hurdle and be able to make tickets
again…

Is there perhaps some further info that I should supply that I have not
to help understand this?

Regards

Paul O’Rorke
Tracker Software Products Canada Limited

paul@tracker-software.com mailto:paul@tracker-software.com
tracker-software.com http://tracker-software.comOn 07/30/2013 03:17 PM, Paul O’Rorke wrote:

Thanks for that Kevin,

not the first time - a silly little mistake had me scratching my
head. I had in my Apache config a rewrite rule for /rt and had the
wrong value in RT_Siteconfig for Set($WebPath, ‘’);

So I now get my logon screen but cannot log in.

I did run the password updater:
root@rt4:/opt/rt4# perl /opt/rt4/etc/upgrade/vulnerable-passwords
--fix
Upgrading 16 users...
Done.
I am assuming that I can’t just UPDATE the passwords in MySQL because
of the hashing? I tried without success:
mysql> UPDATE rtdb.Users SET Password=PASSWORD('xxxx') WHERE
Name='jamie';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1
I guess I have missed something in these upgrade README files. I do
appreciate your help. Now that I’ve the Apache thing sorted I’m
feeling like on the home stretch - if only I can sort these passwords
out. Is there another related script I missed perhaps?

Sincerely

Paul O’Rorke
Tracker Software Products Canada Limited

paul@tracker-software.com mailto:paul@tracker-software.com
tracker-software.com http://tracker-software.com

Tel: +1 (250) 324 1621
Fax: +1 (250) 324 1623

Paul_O_Rorke1 · July 31, 2013, 5:24pm

Thanks again Kevin,

so I did try what’s on that page you sent me to:

perl -I/opt/rt4/local/lib -I/opt/rt4/lib \
     -MRT -MRT::User \
     -e'RT::LoadConfig();RT::Init(); my $u =
RT::User->new($RT::SystemUser); $u->Load("root");
$u->SetPassword("secret")'

returned to a prompt with no messages and the hash displayed in MySQL
changed so I’m thinking it worked:

mysql> SELECT * FROM Users WHERE Name='root'\G;
*************************** 1. row ***************************
                    id: 12
                  Name: root
              Password: !sha512!8MzDJesb8kr4UHIA!784B/mzwvLcUEEa
              Comments: SuperUser
             Signature: NULL
          EmailAddress: root@localhost
   FreeformContactInfo: NULL
          Organization: NULL
              RealName: Enoch Root
              NickName: NULL
                  Lang: NULL
         EmailEncoding: NULL
           WebEncoding: NULL
ExternalContactInfoId: NULL
     ContactInfoSystem: NULL
        ExternalAuthId: NULL
            AuthSystem: NULL
                 Gecos: root
             HomePhone: NULL
             WorkPhone: NULL
           MobilePhone: NULL
            PagerPhone: NULL
              Address1: NULL
              Address2: NULL
                  City: NULL
                 State: NULL
                   Zip: NULL
               Country: NULL
              Timezone: NULL
                PGPKey: NULL
               Creator: 1
               Created: 2010-03-19 21:41:50
         LastUpdatedBy: 1
           LastUpdated: 2013-07-31 17:14:02
             AuthToken: e4b79ac4754841d8
1 row in set (0.00 sec)

I still cannot login using root:secret. Here is mysql> show create
table Users;

mysql> show create table Users;
| Table | Create Table |
| Users | CREATE TABLE `Users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `Name` varchar(200) NOT NULL,
   `Password` varbinary(40) DEFAULT NULL,
   `Comments` text,
   `Signature` text,
   `EmailAddress` varchar(120) DEFAULT NULL,
   `FreeformContactInfo` text,
   `Organization` varchar(200) DEFAULT NULL,
   `RealName` varchar(120) DEFAULT NULL,
   `NickName` varchar(16) DEFAULT NULL,
   `Lang` varchar(16) DEFAULT NULL,
   `EmailEncoding` varchar(16) DEFAULT NULL,
   `WebEncoding` varchar(16) DEFAULT NULL,
   `ExternalContactInfoId` varchar(100) DEFAULT NULL,
   `ContactInfoSystem` varchar(30) DEFAULT NULL,
   `ExternalAuthId` varchar(100) DEFAULT NULL,
   `AuthSystem` varchar(30) DEFAULT NULL,
   `Gecos` varchar(16) DEFAULT NULL,
   `HomePhone` varchar(30) DEFAULT NULL,
   `WorkPhone` varchar(30) DEFAULT NULL,
   `MobilePhone` varchar(30) DEFAULT NULL,
   `PagerPhone` varchar(30) DEFAULT NULL,
   `Address1` varchar(200) DEFAULT NULL,
   `Address2` varchar(200) DEFAULT NULL,
   `City` varchar(100) DEFAULT NULL,
   `State` varchar(100) DEFAULT NULL,
   `Zip` varchar(16) DEFAULT NULL,
   `Country` varchar(50) DEFAULT NULL,
   `Timezone` varchar(50) DEFAULT NULL,
   `PGPKey` text,
   `Creator` int(11) NOT NULL DEFAULT '0',
   `Created` datetime DEFAULT NULL,
   `LastUpdatedBy` int(11) NOT NULL DEFAULT '0',
   `LastUpdated` datetime DEFAULT NULL,
   `AuthToken` varchar(16) CHARACTER SET ascii DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `Users1` (`Name`),
   KEY `Users4` (`EmailAddress`)
) ENGINE=InnoDB AUTO_INCREMENT=10612 DEFAULT CHARSET=utf8 |
1 row in set (0.01 sec)

Does that suggest anything?

Regards

Paul O’Rorke
Tracker Software Products Canada Limited

paul@tracker-software.com mailto:paul@tracker-software.com
tracker-software.com http://tracker-software.com

Tel: +1 (250) 324 1621
Fax: +1 (250) 324 1623On 07/31/2013 08:48 AM, Kevin Falcone wrote:

On Tue, Jul 30, 2013 at 03:17:11PM -0700, Paul O’Rorke wrote:
I am assuming that I can't just UPDATE the passwords in MySQL because of the hashing?  I tried
without success:

  mysql> UPDATE rtdb.Users SET Password=PASSWORD('xxxx') WHERE Name='jamie';
  Query OK, 1 row affected, 1 warning (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 1
The jamie user will definitely not be able to log in after this.
That puts a MySQL password hash into RT, which is not expecting
that.
I guess I have missed something in these upgrade README files.  I do appreciate your help.
Now that I've the Apache thing sorted I'm feeling like on the home stretch - if only I can
sort these passwords out.  Is there another related script I missed perhaps?
You can safely use the techniques here. RT should update the md5 one
for you and if you change rt3 for rt4 in the perl code, it will
generate a proper password the first time out.

http://requesttracker.wikia.com/wiki/RecoverRootPassword

However, this does bring up a question. Show us
show create table Users;

-kevin

Kevin_Falcone · July 31, 2013, 6:59pm

              Password: !sha512!8MzDJesb8kr4UHIA!784B/mzwvLcUEEa
   `Password` varbinary(40) DEFAULT NULL,

These are 3.8 versions of that table, not 4.0 versions.
Did you run all of the database upgrade steps? This was step 4.0.0rc4.
There are many other schema changes.

This will absolutely prevent you from logging in.

-kevin

Paul_O_Rorke1 · July 31, 2013, 7:31pm

OK - I thought that make upgrade-database covered those - it suggested
it was doing all those incremental updates. It asked from which version
I was update from/to and showed each step as doing something. What is
it’s purpose then?

Do I have to still do each one manually from 3.8.4 then?

Paul O’RorkeOn 07/31/2013 11:59 AM, Kevin Falcone wrote:

On Wed, Jul 31, 2013 at 10:24:29AM -0700, Paul O’Rorke wrote:
               Password: !sha512!8MzDJesb8kr4UHIA!784B/mzwvLcUEEa
    `Password` varbinary(40) DEFAULT NULL,
These are 3.8 versions of that table, not 4.0 versions.
Did you run all of the database upgrade steps? This was step 4.0.0rc4.
There are many other schema changes.

This will absolutely prevent you from logging in.

-kevin

Kevin_Falcone · August 1, 2013, 12:15am

OK - I thought that make upgrade-database covered those - it suggested it was doing all those
incremental updates. It asked from which version I was update from/to and showed each step as
doing something. What is it’s purpose then?

Do I have to still do each one manually from 3.8.4 then?

make upgrade-database runs all of the steps.
The database you’re showing clearly did not have at least one of the
steps run on it.

Did you skip past any errors?

You can also show the ‘desc TABLE’ for:
ACL
Groups
GroupMembers
CustomFieldValues
Tickets
CustomFields
Queues
and check for the existence of the Classes, Topics, Articles tables.

Your desc Users showed that at least one part of the Users table
upgrade (adding the AuthToken field) was run. Now the challenge is
figuring out what steps did not run.

-kevin

Paul_O_Rorke1 · August 1, 2013, 7:40pm

I don’t remember skipping any errors during make upgrade-database. Here
are the table descriptions you asked for. As you can see the Classes,
Topics, Articles tables do exist.

Hopefully you will be able to tell what I need to do to my DB to fix this…

mysql> describe ACL;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| PrincipalType | varchar(25) | NO | | NULL | |
| PrincipalId | int(11) | NO | | NULL | |
| RightName | varchar(25) | NO | MUL | NULL | |
| ObjectType | varchar(25) | NO | | NULL | |
| ObjectId | int(11) | NO | | 0 | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
10 rows in set (0.00 sec)

mysql> describe Groups;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(200) | YES | | NULL | |
| Description | varchar(255) | YES | | NULL | |
| Domain | varchar(64) | YES | MUL | NULL | |
| Type | varchar(64) | YES | MUL | NULL | |
| Instance | int(11) | YES | | NULL | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
10 rows in set (0.00 sec)

mysql> describe GroupMembers;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| GroupId | int(11) | NO | MUL | 0 | |
| MemberId | int(11) | NO | | 0 | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
7 rows in set (0.00 sec)

mysql> describe CustomFieldValues;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| CustomField | int(11) | NO | MUL | NULL | |
| Name | varchar(200) | YES | | NULL | |
| 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 | |
| Category | varchar(255) | YES | | NULL | |
10 rows in set (0.00 sec)

mysql> describe Tickets;
| Field | Type | Null | Key | Default |
Extra |
| id | int(11) | NO | PRI | NULL |
auto_increment |
| EffectiveId | int(11) | NO | MUL | 0
| |
| Queue | int(11) | NO | MUL | 0
| |
| Type | varchar(16) | YES | | NULL
| |
| IssueStatement | int(11) | NO | | 0
| |
| Resolution | int(11) | NO | | 0
| |
| Owner | int(11) | NO | MUL | 0
| |
| Subject | varchar(200) | YES | | [no subject]
| |
| InitialPriority | int(11) | NO | | 0
| |
| FinalPriority | int(11) | NO | | 0
| |
| Priority | int(11) | NO | | 0
| |
| TimeEstimated | int(11) | NO | | 0
| |
| TimeWorked | int(11) | NO | | 0
| |
| Status | varchar(64) | YES | | NULL
| |
| TimeLeft | int(11) | NO | | 0
| |
| Told | datetime | YES | | NULL
| |
| Starts | datetime | YES | | NULL
| |
| Started | datetime | YES | | NULL
| |
| Due | datetime | YES | | NULL
| |
| Resolved | datetime | YES | | NULL
| |
| LastUpdatedBy | int(11) | NO | | 0
| |
| LastUpdated | datetime | YES | | NULL
| |
| Creator | int(11) | NO | | 0
| |
| Created | datetime | YES | | NULL
| |
| Disabled | smallint(6) | NO | | 0
| |
25 rows in set (0.00 sec)

mysql> describe Queues;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(200) | NO | UNI | NULL | |
| Description | varchar(255) | YES | | NULL | |
| CorrespondAddress | varchar(120) | YES | | NULL | |
| CommentAddress | varchar(120) | YES | | NULL | |
| InitialPriority | int(11) | NO | | 0 | |
| FinalPriority | int(11) | NO | | 0 | |
| DefaultDueIn | 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 | |
| SubjectTag | varchar(120) | YES | | NULL | |
| Lifecycle | varchar(32) | YES | | NULL | |
15 rows in set (0.00 sec)

mysql> describe Classes;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | NO | | | |
| Description | varchar(255) | NO | | | |
| SortOrder | int(11) | NO | | 0 | |
| Disabled | int(2) | NO | | 0 | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
| HotList | int(2) | NO | | 0 | |
10 rows in set (0.00 sec)

mysql> describe Topics;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| Parent | int(11) | NO | | 0 | |
| Name | varchar(255) | NO | | | |
| Description | varchar(255) | NO | | | |
| ObjectType | varchar(64) | NO | | | |
| ObjectId | int(11) | NO | | 0 | |
6 rows in set (0.01 sec)

mysql> describe Articles;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | NO | | | |
| Summary | varchar(255) | NO | | | |
| SortOrder | int(11) | NO | | 0 | |
| Class | int(11) | NO | | 0 | |
| Parent | int(11) | NO | | 0 | |
| URI | varchar(255) | YES | | NULL | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
11 rows in set (0.00 sec)

Paul O’Rorke
Tracker Software Products
paul@tracker-software.com mailto:paul.ororke@tracker-software.comOn 7/31/2013 5:15 PM, Kevin Falcone wrote:

On Wed, Jul 31, 2013 at 12:31:26PM -0700, Paul O’Rorke wrote:
OK - I thought that make upgrade-database covered those - it suggested it was doing all those
incremental updates.  It asked from which version I was update from/to and showed each step as
doing something.  What is it's purpose then?

Do I have to still do each one manually from 3.8.4 then?
make upgrade-database runs all of the steps.
The database you’re showing clearly did not have at least one of the
steps run on it.

Did you skip past any errors?

You can also show the ‘desc TABLE’ for:
ACL
Groups
GroupMembers
CustomFieldValues
Tickets
CustomFields
Queues
and check for the existence of the Classes, Topics, Articles tables.

Your desc Users showed that at least one part of the Users table
upgrade (adding the AuthToken field) was run. Now the challenge is
figuring out what steps did not run.

-kevin
Paul O'Rorke

On 07/31/2013 11:59 AM, Kevin Falcone wrote:
On Wed, Jul 31, 2013 at 10:24:29AM -0700, Paul O’Rorke wrote:
                Password: !sha512!8MzDJesb8kr4UHIA!784B/mzwvLcUEEa
     `Password` varbinary(40) DEFAULT NULL,
These are 3.8 versions of that table, not 4.0 versions.
Did you run all of the database upgrade steps? This was step 4.0.0rc4.
There are many other schema changes.

Kevin_Falcone · August 2, 2013, 2:48pm

I don’t remember skipping any errors during make upgrade-database. Here are the table
descriptions you asked for. As you can see the Classes, Topics, Articles tables do exist.

Do you have logs of the upgrade?

Hopefully you will be able to tell what I need to do to my DB to fix this…

Really- you need to do another test upgrade and figure out what fails.
While I can tell you how to fix Users, I wouldn’t trust that other
parts of the upgrade didn’t fail.

mysql> describe ACL;
mysql> describe Groups;
mysql> describe GroupMembers;
mysql> describe CustomFieldValues;
mysql> describe Tickets;
mysql> describe CustomFields;
mysql> describe Queues;
mysql> show tables;

These all look correct. That shows that you at least ran upgrades
through 3.9.7, but the User change for Password that you’re missing
was 4.0.0rc4. Did you stop anywhere or let it run everything through
4.0.16?

You can look at ‘show create table sessions’ which should be innodb
not myisam and look at your Attributes table, which should have a
LONGBLOB for Content.

While I’ve seen errors that reset the Password field to the wrong size
on 3.6 → 4.0 upgrades, those don’t apply to a 3.8 → 4.0 upgrade.

At this point, I’d rerun a database upgrade, paying close attention to
what steps run and watching the screen and the mysql error logs for
errors.

-kevin

Asif_Iqbal2 · August 2, 2013, 3:21pm

You can look at ‘show create table sessions’ which should be innodb
not myisam and look at your Attributes table, which should have a
LONGBLOB for Content.

I just did my test upgrade from rt 3.8.2 / mysql 5.0.75 to rt 4.0.16 /
mysql 5.5.32
but session table still showing myisam.

$ mysql -e ‘use rt4; show create table sessions’
| sessions | CREATE TABLE sessions (
id varbinary(32) NOT NULL DEFAULT ‘’,
a_session longblob,
LastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

I guess I can just ALTER the session table now?

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

Kevin_Falcone · August 2, 2013, 3:32pm

 You can look at 'show create table sessions' which should be innodb
 not myisam and look at your Attributes table, which should have a
 LONGBLOB for Content.
I just did my test upgrade from rt 3.8.2 / mysql 5.0.75 to rt 4.0.16 / mysql 5.5.32
but session table still showing myisam.
$ mysql -e ‘use rt4; show create table sessions’
| sessions | CREATE TABLE sessions (
id varbinary(32) NOT NULL DEFAULT ‘’,
a_session longblob,
LastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
I guess I can just ALTER the session table now?

Please show a log of your make upgrade-database step

One of the steps explicitly drops and recreates the sessions table.

-kevin

Asif_Iqbal2 · August 2, 2013, 4:49pm

I just did my test upgrade from rt 3.8.2 / mysql 5.0.75 to rt 4.0.16
/ mysql 5.5.32
but session table still showing myisam.
$ mysql -e ‘use rt4; show create table sessions’
| sessions | CREATE TABLE sessions (
id varbinary(32) NOT NULL DEFAULT ‘’,
a_session longblob,
LastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
I guess I can just ALTER the session table now?

Please show a log of your make upgrade-database step

One of the steps explicitly drops and recreates the sessions table.

~/src/rt-4.0.16# make upgrade-database
/usr/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: rt4
User: rt_user
DBA: root
Enter RT version you’re upgrading from: 3.8.2

Going to apply following upgrades:

3.8.3
3.8.4
3.8.6
3.8.8
3.8.9
3.9.1
3.9.2
3.9.3
3.9.5
3.9.6
3.9.7
3.9.8
4.0.1
4.0.3
4.0.4
4.0.6
4.0.9
4.0.12
4.0.13

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 3.8.3
Now inserting data.
Processing 3.8.4
Now inserting data.
Processing 3.8.6
Now inserting data.
Processing 3.8.8
Now inserting data.
[Fri Aug 2 16:42:55 2013] [warning]: Couldn’t set SortOrder: That is
already the current value (./etc/upgrade/3.8.8/content:32)
[Fri Aug 2 16:42:55 2013] [warning]: Couldn’t set SortOrder: That is
already the current value (./etc/upgrade/3.8.8/content:32)
Processing 3.8.9
Now inserting data.
[Fri Aug 2 16:42:58 2013] [warning]: Use of uninitialized value in string
eq at /home/iqbala/src/rt-4.0.16/sbin/…/lib/RT/Template.pm line 652, <>
line 1. (/home/iqbala/src/rt-4.0.16/sbin/…/lib/RT/Template.pm:652)
[Fri Aug 2 16:42:58 2013] [warning]: Use of uninitialized value in string
eq at /home/iqbala/src/rt-4.0.16/sbin/…/lib/RT/Template.pm line 652, <>
line 1. (/home/iqbala/src/rt-4.0.16/sbin/…/lib/RT/Template.pm:652)
[Fri Aug 2 16:42:58 2013] [warning]: Use of uninitialized value in string
eq at /home/iqbala/src/rt-4.0.16/sbin/…/lib/RT/Template.pm line 652, <>
line 1. (/home/iqbala/src/rt-4.0.16/sbin/…/lib/RT/Template.pm:652)
Processing 3.9.1
Now inserting data.
[Fri Aug 2 16:43:03 2013] [warning]: Unable to grant ExecuteCode on
principal 685493: NSI already has that right
(./etc/upgrade/3.9.1/content:63)
Processing 3.9.2
Now inserting data.
Processing 3.9.3
Now populating database schema.
Processing 3.9.5
Now populating database schema.
Processing 3.9.6
Now populating database schema.
Processing 3.9.7
Now populating database schema.
Now inserting data.
Processing 3.9.8
Now populating database schema.
Now inserting data.
[Fri Aug 2 16:46:20 2013] [error]: You appear to be upgrading from RTFM
2.0 - We don’t support upgrading this old of an RTFM yet
(./etc/upgrade/3.9.8/content:11)
[Fri Aug 2 16:46:20 2013] [error]: We found RTFM tables in your database.
Checking for content. (./etc/upgrade/3.9.8/content:14)
[Fri Aug 2 16:46:20 2013] [error]: You appear to have RTFM Articles. You
can upgrade using the etc/upgrade/upgrade-articles script. Read more about
it in docs/UPGRADING-4.0 (./etc/upgrade/3.9.8/content:20)
Processing 4.0.1
Now inserting data.
Processing 4.0.3
Now inserting data.
Processing 4.0.4
Now inserting data.
Processing 4.0.6
Now populating database schema.
Now inserting data.
Processing 4.0.9
Now inserting data.
Processing 4.0.12
Now populating database schema.
Processing 4.0.13
Now populating database schema.
Done.
root@newwebrt:~/src/rt-4.0.16#

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?