[rt-users] MySQL schema upgrade recipe for 3.6.1->3.8.1

[Travis C. Brooks]

Spurred on by Jesse's excellent class last week, I'm upgrading to 3.8.1,
and wanted to report the issues I found with the schema upgrade script.
I managed to solve most of the stumbling blocks, and I document them
here for others.  

Here was my procedure (working on a test sandbox...obviously):

Using  RHEL4  MySQL4.1.22 (on a non-local server)

install RT code -> make upgrade

(no problems here except for GnuPG::Interface as noted here
http://www.gossamer-threads.com/lists/rt/users/79410 )


rt-setup-database --action upgrade

(no problems here...though I did go all the way to 3.8.1, while the docs
might have meant for me to stop at 3.8.0 or were they just older?)

Now I have to upgrade my copy of DBD::mysql, since RHEL4 is woefully out
of date. Would be nice to note this in the UPGRADING.mysql doc, since it
isn't covered by testdeps/fixdeps (and it shouldn't be in testdeps,
since it isn't a dependency of RT, just of the schema upgrade script)

perl -MCPAN -eshell
cpan> install CAPTTOFU/DBD-mysql-4.005.tar.gz

(as noted elsewhere on the list one has to do something akin to:

 sudo service mysqld start
 mysql -uroot -p
 mysql> grant all privileges on test.* to 'root'@'localhost' identified by ''

in order to allow CPAN to run tests on DBD::mysql
Note also that 4.010 still wouldn't pass tests (as noted elsewhere)
hanging on test 76 or so.  So I just backed down to 4.005 which worked
fine.  Probably I could have upgraded the DBI version instead but this
downgrade seemed to work fine.  Then you might like to remove the very
permissive privs you granted on your test db.)


schema.mysql-4.0-4.1.pl DB USER PASS > sql.queries

but this failed because my db is remote so I trivially modified the
script with attached patch (probably not the best way, but it works, not
needed if your db is local, or if you just run the script on your DB
server.)

schema.mysql-4.0-4.1.pl DB USER PASS HOST > sql.queries
Use of uninitialized value in numeric gt (>) at etc/upgrade/schema.mysql-4.0-4.1_tcb.pl line 310.
Use of uninitialized value in numeric gt (>) at etc/upgrade/schema.mysql-4.0-4.1_tcb.pl line 310.
Use of uninitialized value in numeric gt (>) at etc/upgrade/schema.mysql-4.0-4.1_tcb.pl line 310.
Use of uninitialized value in numeric gt (>) at etc/upgrade/schema.mysql-4.0-4.1_tcb.pl line 310

now my sql.queries contains many lines, including, for example:

ALTER TABLE Templates MODIFY Content CHAR NULL DEFAULT NULL;
ALTER TABLE Templates MODIFY Content BLOB CHARACTER SET utf8 NULL DEFAULT NULL;

As noted elsewhere on the list, this appears to convert the content
column of my Templates table to 1 CHAR and then make it a BLOB that has
utf8 encoding.  (Also there are 4 such pairs...matching the 4 errors
above perhaps?)  So, I am not a MySQL expert, and it's a sandbox, so
let's see what MySQL says:

ERROR 1064 (42000) at line 131: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8 NULL DEFAULT NULL' at line 1

This is

http://rt3.fsck.com//Ticket/Display.html?id=12278

Which suggests to change BLOB to TEXT and CHAR in above to BLOB (or just
delete the line(s), since in my orig. schema it is already a BLOB)
and this fix is what I will try once restored from backup (and report
success or failure here)

Hope this helps some others.

Best,
Travis

-- 
Travis C. Brooks
Manager of Information Systems & SPIRES
Stanford Linear Accelerator Center Library
http://www.slac.stanford.edu/spires/
-------------- next part --------------
--- schema.mysql-4.0-4.1.pl	2008-10-28 11:50:35.000000000 -0700
+++ schema.mysql-4.0-4.1_tcb.pl	2008-10-28 16:29:34.000000000 -0700
@@ -7,7 +7,7 @@
 use DBD::mysql 4.002;
 
 unless (@ARGV) {
-    print STDERR "usage: $0 db_name db_user db_password\n";
+    print STDERR "usage: $0 db_name db_user db_password db_host\n";
     exit 1;
 }
 
@@ -211,8 +211,8 @@
 
 my @sql_commands;
 
-my ($db_name, $db_user, $db_pass) = (shift, shift, shift);
-my $dbh = DBI->connect("dbi:mysql:$db_name", $db_user, $db_pass, { RaiseError => 1 });
+my ($db_name, $db_user, $db_pass, $db_host) = (shift, shift, shift, shift);
+my $dbh = DBI->connect("dbi:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { RaiseError => 1 });
 
 
 push @sql_commands, qq{ALTER DATABASE $db_name DEFAULT CHARACTER SET utf8};