Upgrading RT 3.6.4 to RT 4.0.10

Request-Tracker (RT) upgrade steps.  This is based on http://blog.bestpractical.com/2011/07/upgrading-to-rt-4.html.  Please read all related documents and don't just follow this blindly.

Moving from a gentoo linux 2.4 box, RT 3.6.4, mysql 4.0.8 to an ubuntu 12.04 server, RT 4.0.10 and mysql 5.5

Export 3.6.4 Database
On RT 3.6.4 machine as root (obviously must know the root mysql password):

mysqldump -p --opt rt3 > rt3.sql

Copy this file to the new machine.  All further steps are on new machine.

Prepare SQL for Import
You have to edit rt3.sql with vim or sed, and remove all references to "Type=Innodb" (this is default in mysql now), and search for "timestamp(" and remove the bracketed section.. (eg. "timestamp(14)" is now just "timestamp").

Create Database for Import

mysql -p
create database rt4 character set latin1;
grant all on rt4.* to rt_user identified by 'rt_pass';
quit

NOTE: above replace rt_user and rt_pass by the user/password you wish to use for RT.  Also, it may seem counter intuitive to name database rt4, since we are importing rt3 - just remember the end target is 4.0.10.

Import 3.6.4 Database

mysql -p --default-character-set=binary rt4 < rt3.sql

Download/Configure/Install RT 4.0.10

Download RT 4.0.10 source, extract to temp dir, run .configure with needed parameters (see your 3.6.4 install), and basically follow README up to and including "make install".  This should install in /opt/rt4.  Be sure to configure RT_SiteConfig.pm and configure apache2 as needed (beyond scope of this post).

Upgrade Database
From the temp dir used above execute the following:

make upgrade-database
(From 3.6.4 to 3.7.87)

If it fails at 3.7.81, do this:
mysql -p rt4
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId, ImmediateParentId);
quit
make upgrade-database
(From 3.7.81 to 3.7.87)

perl etc/upgrade/upgrade-mysql-schema.pl rt4 rt_user rt_pass > queries.sql
(again replacing rt_user and rt_pass with user/password you wish to use for RT)
mysql -p rt4 < queries.sql
(this takes about 3 times as long as the initial import!)
mysql -p
alter database rt4 default character set utf8;
quit
make upgrade-database
(From 3.7.87 to 4.0.9)

If it fails at 3.8.2, do this:
mysql -p rt4
drop index groupmembers1 on GroupMembers;
create unique index groupmembers1 on GroupMembers (GroupId,MemberId);
quit
make upgrade-database
(From 3.8.3 to 4.0.9)

perl -I /opt/rt4/local/lib -I /opt/rt4/lib etc/upgrade/shrink_cgm_table.pl
(takes even longer than above query)
perl -I /opt/rt4/local/lib -I /opt/rt4/lib etc/upgrade/shrink_transactions_table.pl
perl etc/upgrade/vulnerable-passwords --fix

Now test, test, test... :)

Aggregating row data using user-defined aggregate in Informix IDS

Implementing mysql group_concat() aggregate in Informix IDS 11.5 - thanks to Jonathan Leffler.

As DBA do:

CREATE FUNCTION gc_init(dummy varchar(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    RETURN partial1 || ',' || partial2;
END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

Then you need to grant access to the functions to users that need it:

grant execute on function gc_init to appsql;
grant execute on function gc_iter to appsql;
grant execute on function gc_comb to appsql;
grant execute on function gc_fini to appsql;


Example Use:

select ord_id, group_concat(product)
from ord
group by ord_id

Aubit4GL: connecting to mysql and informix concurrently

Since database connections seems to be compile time, and you have to set A4GL_SQLTYPE in order to compile against database X - I wonder how you can open concurrent connections.. I cannot find any documentation on how to do this - but according to http://aubit4gl.sourceforge.net/aubit4gldoc/manual/html/features_summary.html it maybe that you have to use ODBC connections to each instead of native??

It seems that "open session" and "use session" can be used to dynamically switch at runtime. But compile time seems to be the kicker.. especially if you use "define x like table.column" in 4gl, unless it allows "define x like db.table.column" or searches both databases.

Aubit4GL: mysql connection specification

Since this is not well documented on Aubit4gl site, posting here:

To connect to mysql database:

1. Make sure your mysql server is not bound to localhost - even local connections are via hostname
2. Make sure you can connect to mysql server.. grant at least select privs using "grant select on db.* to 'test'@'hostname' identified by 'test';" NOTE: localhost privs are not enough - connection thru hostname.
   
3. Do "export MYSQL_SERVER=hostname"
   
4. Set/export the following environment variables:
   

A4GL_SQLUID=test #whatever connection user is
A4GL_SQLPWD=test #whatever connection password is
A4GL_LEXDIALECT=INFORMIX
A4GL_LEXTYPE=C
A4GL_SQLTYPE=mysql

And now take the simple.4gl program:

database test # of whatever your database name is

main
display "Hello World"
end main

Compile with : aubit 4glpc simple.4gl -o simple.4ae

If it compiles - database connection is ok!

Thanks to Mike's comment below - using .aubit4gl.acl to store database:user:pw DOES work.. just be sure to "unset A4GL_SQLUID" and "unset A4GL_SQLPWD".

mysqldump to informix DS

Problem: Need to access mysql 5.0 data from 4js BDL (I4gl)

Solution attempts:

1. generate 4js dynamic VM with mysql support.. no mysql 5.0 support (only 4.x) and not forward compatible
2. try aubit4gl with mysql support.. no go.. see this
3. call script to unload mysql table and then load into informix

On solution attempt 3 - this proved more difficult than I first imagined.

I've been spoiled with Informix's very easy "unload to". Mysql simply has no equivalent. There is the "SELECT INTO OUTFILE", but (1) you need to "grant file on *.* to 'user'@'whateverhost'" (besides select permission of course), and (2) can be run from a remote host, but the output file is local to the mysql server instance.. and while you can use NFS, its a pain.

Also there is mysqldump - which I thought might do it.. but alas, if you use the --tab option it uses "SELECT INTO OUTFILE" internally anyway - plus you need to "grant lock tables on db.* to 'user'@'whateverhost'".

Ok - so had to revise my strategy - since I am running this script on remote host... this generates ansi "insert" statements that I will try to execute against informix:

mysqldump -h mysqlhost -usomeuser -psomepassword --compatible=ansi --no-create-info --compact dbname tablename > x.sql

Kinda works.. doesn't like text fields (clob datatype in informix).. and date formating could be an issue (easily fixed with informix DBDATE environment variable).. but certainly not as easy as I imagined.

Aubit4GL and libmysqld

Had a frustrating time trying to build aubit4gl on ubuntu with mysql support.

/usr/bin/ld: /usr/lib/mysql/libmysqld.a(net_serv.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC
/usr/lib/mysql/libmysqld.a: could not read symbols: Bad value

This is from libmysqlclient15-dev package. Turns out its not even an ubuntu issue.. its actual mysql bug:

http://bugs.mysql.com/bug.php?id=39288

I did see somewhere that it was fixed in libmysqlclient16-dev - which is not available in hardy (8.04) which I need.

I stumbled upon this link and was able to build a relocatable libmysqld.. but then I ran it this:

gcc -shared -L/lib -o /opt/aubit4gl/src/plugins-1.10RC_62/libSQL_mysqldb.so db-mysql.o -L/usr/local/lib -L/usr/local/mysql -L/usr/lib/mysql -L/usr/lib -L/opt/aubit4gl/src/lib -lmysqld -laubit4gl-1.10RC_62 -Wl,-Bsymbolic-functions -L/usr/lib/mysql -lmysqld -lwrap -lrt
/usr/bin/ld: cannot find -lwrap

even though libwrap is in /lib .. arrrg ok.. so I did the following:

cd lib/libsql/mysql
gcc -shared -L/lib -o /opt/aubit4gl/src/plugins-1.10RC_62/libSQL_mysqldb.so db-mysql.o -L/usr/local/lib -L/usr/local/mysql -L/usr/lib/mysql -L/usr/lib -L/opt/aubit4gl/src/lib -lmysqld -laubit4gl-1.10RC_62 -Wl,-Bsymbolic-functions -L/usr/lib/mysql -lmysqld
cd ../../..
make

(ie. I just removed -lwrap and -lrt and it went ok) and proceeded to compile the remainder of aubit4gl. Now to test against mysql