MySQL upgrade kills perl webapp?

breun · Unread post by **breun** » Sat Feb 18, 2006 10:00 pm

I've used this repository for some time now, but had mysql in my exclude list. Today I figured I would take the jump and upgrade a FC2 and a FC3 box from MySQL 3.23.x to ART's MySQL 4.1.15 (and upgraded tot PHP 4.4.2 from 4.4.1 at the same time).

All vhosts running on MySQL databases seem to work just fine, only my perl based ticket system (OTRS) now acts really weird. Sometimes when I load the login page I just get a blank page, but when I get the login screen and login I always get 'Connection closed by remote host'. When I use elinks on the server via ssh I get a lot of 'Error reading socket' errors and when I login I see (I put in x's for a hex number):

dbih_getcom handle DBI::db=HASH(0xxxxxxxx) is not a valid DBI handle at /usr/li

Which makes me think it is a database (MySQL) problem. Does anyone have the faintest idea what could be the problem? Could the mysql-compat package be confusing maybe?

Unread post by **scott** » Sun Feb 19, 2006 11:18 am

perl is going to be linked against the older mysql 3 client library, which is what mysql-compat contains. So as long as that is on the system, the basics are in there for connecting to the database. What I've found in php at least, is that with mysql 4.1's newer password format, it was sometimes failing to authenticate when it is linked it against the mysql 3 libraries, so to resolve that in 4.4.1-2 I started relinking php against the mysql 4.1. Its possible that perl is doing the same thing in a mixed mysql 3/4.1 environment.

It definitely warrants more debugging, you may need to try rebuilding perl-DBD-Mysql against mysql 4.1 to see if it fixes it.

breun · Unread post by **breun** » Sun Feb 19, 2006 11:28 am

mysql-compat is on the system. Also, the cronjobs ran by the application do seem to work correctly with the database: e-mails are fetched from the POP3 box using a fetchmail cronjob and then put into the db by a perl script that is part of the webapp. So that script seems to be able to work with the MySQL upgrade.

Could it be that the web frontend uses a different way of connecting to the db which fails while the backend scripts do work? I believe they're both using this DBI/DBD thing.

I put old-passwords in /etc/my.cnf, because I read Plesk needs that.

I'm not familiar with rebuilding, how would I rebuild perl-DBD-MySQL against mysql 4.1?

P.S. 3es.atomicrocketturtle.com seems to be down...

Unread post by **scott** » Sun Feb 19, 2006 1:09 pm

All you would need to do is to grab the perl-DBD-MySQL src.rpm, and rebuild it:

rpmbuild --rebuild <filename>

That would automatically link against the newer mysql library. The DBI interface is the normal way of doing it in perl, so I can't imagine they would have strayed away from that. Unless they included their own perl modules along with the package? If they werent using the version installed with the OS that could cause it too.

breun · Unread post by **breun** » Sun Feb 19, 2006 1:22 pm

Well...

# rpm -e perl-DBD-MySQL
error: Failed dependencies:
perl(DBD::mysql) is needed by (installed) psa-bu-7.5.4-fc2.build75050926.17
perl-DBD-MySQL is needed by (installed) otrs-2.0.4-01
perl-DBD-MySQL is needed by (installed) mysql-4.1.15-1.rhfc2.art

OTRS (the webapp that broke) apparently uses perl-DBD-MySQL. I'll try rebuilding the package.

breun · Unread post by **breun** » Sun Feb 19, 2006 1:51 pm

Rebuilding perl-DBD-MySQL fails. Any idea what's wrong?

# rpm -q perl-DBD-MySQL
perl-DBD-MySQL-2.9003-4
# rpmbuild --rebuild perl-DBD-MySQL-2.9003-4.src.rpm
Installing perl-DBD-MySQL-2.9003-4.src.rpm
Executing(%prep): /bin/sh -e /var/tmp/rpm-tmp.2985
+ umask 022
+ cd /usr/src/redhat/BUILD
+ cd /usr/src/redhat/BUILD
+ rm -rf DBD-mysql-2.9003
+ /usr/bin/gzip -dc /usr/src/redhat/SOURCES/DBD-mysql-2.9003.tar.gz
+ tar -xf -
+ STATUS=0
+ '[' 0 -ne 0 ']'
+ cd DBD-mysql-2.9003
++ /usr/bin/id -u
+ '[' 0 = 0 ']'
+ /bin/chown -Rhf root .
++ /usr/bin/id -u
+ '[' 0 = 0 ']'
+ /bin/chgrp -Rhf root .
+ /bin/chmod -Rf a+rX,u+w,g-w,o-w .
+ exit 0
Executing(%build): /bin/sh -e /var/tmp/rpm-tmp.7673
+ umask 022
+ cd /usr/src/redhat/BUILD
+ cd DBD-mysql-2.9003
+ CFLAGS=-O2 -g -march=i386 -mcpu=i686
+ perl Makefile.PL PREFIX=/var/tmp/perl-DBD-MySQL-2.9003-root/usr INSTALLDIRS=vendor --ssl
I will use the following settings for compiling and testing:

cflags (mysql_config) = -I/usr/include/mysql -g -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE
libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (Users choice) = 1
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testuser (default ) =

To change these settings, see 'perl Makefile.PL --help' and
'perldoc INSTALL'.

Checking if your kit is complete...
Looks good
Using DBI 1.40 (for perl 5.008003 on i386-linux-thread-multi) installed in /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi/auto/DBI
Writing Makefile for DBD::mysql
+ make
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
cp lib/Mysql.pm blib/lib/Mysql.pm
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/Mysql/Statement.pm blib/lib/Mysql/Statement.pm
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm
gcc -c -I/usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -DDBD_MYSQL_WITH_SSL -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -march=i386 -mcpu=i686 -DVERSION=\"2.9003\" -DXS_VERSION=\"2.9003\" -fPIC "-I/usr/lib/perl5/5.8.3/i386-linux-thread-multi/CORE" dbdimp.c
/usr/bin/perl -p -e "s/~DRIVER~/mysql/g" /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi/auto/DBI/Driver.xst > mysql.xsi
/usr/bin/perl /usr/lib/perl5/5.8.3/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.3/ExtUtils/typemap mysql.xs > mysql.xsc && mv mysql.xsc mysql.c
Warning: duplicate function definition 'do' detected in mysql.xs, line 192
Warning: duplicate function definition 'rows' detected in mysql.xs, line 290
gcc -c -I/usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -DDBD_MYSQL_WITH_SSL -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -march=i386 -mcpu=i686 -DVERSION=\"2.9003\" -DXS_VERSION=\"2.9003\" -fPIC "-I/usr/lib/perl5/5.8.3/i386-linux-thread-multi/CORE" mysql.c
mysql.xs: In function `XS_DBD__mysql__dr__admin_internal':
mysql.xs error: too few arguments to function `mysql_shutdown'
make: *** [mysql.o] Error 1
error: Bad exit status from /var/tmp/rpm-tmp.7673 (%build)

RPM build errors:
Bad exit status from /var/tmp/rpm-tmp.7673 (%build)

Unread post by **scott** » Sun Feb 19, 2006 2:05 pm

Looks like that version might not be compatible with the newer mysql client library to me. You'd probably have to upgrade the perl library itself to link against 4.1.

breun · Unread post by **breun** » Sun Feb 19, 2006 3:00 pm

I'm already running the latest perl for FC2 from the Fedora Legacy Updates channel: perl-5.8.3-19.3.legacy

So maybe your mysql-4.1 rpm is not entirely compatible with FC2 and its perl? I'll try if I'm able to rebuild perl-DBD-MySQL on the other server (FC3).

breun · Unread post by **breun** » Sun Feb 19, 2006 3:07 pm

I can't rebuild perl-DBD-MySQL on the FC3 (running perl-5.8.5-24) server either:

# rpmbuild --rebuild perl-DBD-MySQL-2.9003-5.src.rpm
Installing perl-DBD-MySQL-2.9003-5.src.rpm
Executing(%prep): /bin/sh -e /var/tmp/rpm-tmp.15693
+ umask 022
+ cd /usr/src/redhat/BUILD
+ cd /usr/src/redhat/BUILD
+ rm -rf DBD-mysql-2.9003
+ /usr/bin/gzip -dc /usr/src/redhat/SOURCES/DBD-mysql-2.9003.tar.gz
+ tar -xf -
+ STATUS=0
+ '[' 0 -ne 0 ']'
+ cd DBD-mysql-2.9003
++ /usr/bin/id -u
+ '[' 0 = 0 ']'
+ /bin/chown -Rhf root .
++ /usr/bin/id -u
+ '[' 0 = 0 ']'
+ /bin/chgrp -Rhf root .
+ /bin/chmod -Rf a+rX,u+w,g-w,o-w .
+ exit 0
Executing(%build): /bin/sh -e /var/tmp/rpm-tmp.15693
+ umask 022
+ cd /usr/src/redhat/BUILD
+ cd DBD-mysql-2.9003
+ CFLAGS='-O2 -g -march=i386 -mcpu=i686'
+ perl Makefile.PL PREFIX=/var/tmp/perl-DBD-MySQL-2.9003-root/usr INSTALLDIRS=vendor --ssl
I will use the following settings for compiling and testing:

cflags (mysql_config) = -I/usr/include/mysql -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE
libs (mysql_config) = -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (Users choice) = 1
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testuser (default ) =

To change these settings, see 'perl Makefile.PL --help' and
'perldoc INSTALL'.

Checking if your kit is complete...
Looks good
Using DBI 1.40 (for perl 5.008005 on i386-linux-thread-multi) installed in /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI
Writing Makefile for DBD::mysql
+ make
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
cp lib/Mysql.pm blib/lib/Mysql.pm
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/Mysql/Statement.pm blib/lib/Mysql/Statement.pm
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm
gcc -c -I/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -DDBD_MYSQL_WITH_SSL -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m32 -march=i386 -mtune=pentium4 -DVERSION=\"2.9003\" -DXS_VERSION=\"2.9003\" -fPIC "-I/usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE" dbdimp.c
/usr/bin/perl -p -e "s/~DRIVER~/mysql/g" /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI/Driver.xst > mysql.xsi
/usr/bin/perl /usr/lib/perl5/5.8.5/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.5/ExtUtils/typemap mysql.xs > mysql.xsc && mv mysql.xsc mysql.c
Warning: duplicate function definition 'do' detected in mysql.xs, line 192
Warning: duplicate function definition 'rows' detected in mysql.xs, line 290
gcc -c -I/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -DDBD_MYSQL_WITH_SSL -D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -m32 -march=i386 -mtune=pentium4 -DVERSION=\"2.9003\" -DXS_VERSION=\"2.9003\" -fPIC "-I/usr/lib/perl5/5.8.5/i386-linux-thread-multi/CORE" mysql.c
mysql.xs: In function `XS_DBD__mysql__dr__admin_internal':
mysql.xs error: too few arguments to function `mysql_shutdown'
make: *** [mysql.o] Error 1
error: Bad exit status from /var/tmp/rpm-tmp.15693 (%build)

RPM build errors:
Bad exit status from /var/tmp/rpm-tmp.15693 (%build)

breun · Unread post by **breun** » Sun Feb 19, 2006 3:30 pm

This is just a wild guess, but maybe I should be rebuilding perl-DBI instead of perl-DBD-MySQL? Or both?

Edit: I guess not, as I just read DBI is db-independent.

Unread post by **scott** » Sun Feb 19, 2006 3:54 pm

This is why I avoid the perl libraries

They're so much more work to screw around with than php. Generally what I do in this case is I work backwards from a known version of the package that works with the environment. In this case FC4 and RHEL4/CentOS4 both are linked against mysql 4.1 libraries natively. The downside here is that you could end up making a lot of work for yourself doing a backport rather than just building a new package from scratch. IE, those FC4/4ES packages might need newer versions of perl or some other perl library, so you build those, only to find out they need something else newer, and down the rabbit hole you go. I'd say 75% of the time, its just a straight single package rebuild when you're doing a backport, the other 25% of the time (apache... in case you ever wondered why I dont do newer versions) its just easier to upgrade the OS.

breun · Unread post by **breun** » Sun Feb 19, 2006 4:20 pm

Ok, I guess I can't use your mysql package on my server then. And because php-mysql needs your mysql since the 4.4.2 builds of your rpms, I'm sort of forced to go back to php 4.4.1 as well because of this. Am I right or do you see any other options?

Can I just use rpm -Uvh --oldpackage to go back to mysql 3.23.x? I believe new databases have been created since the upgrade to 4.1.x, would they still work after going back to 3.23.x?

Unread post by **scott** » Sun Feb 19, 2006 5:16 pm

Worst case, if you go back to mysql3 and it doesnt work, just reimport your db's from /var/lib/psa/. I don't think you're going need to do that, since old_passwords keeps it from modifying the password field into the newer format.

breun · Unread post by **breun** » Sun Feb 19, 2006 5:47 pm

I guess I'll try downgrading, but unfortunately that will also mean downgrading php to 4.4.1. If I downgrade mysql, I won't be able to update php in the future using your repository?

I used to be able to use your repo for php, but keep Fedora's own mysql. Starting with 4.4.2 however php-mysql depends on libmysqlclient.so.14, while Fedora's own packages provide libmysqlclient.so.10.

breun · Unread post by **breun** » Sun Feb 19, 2006 5:49 pm

Hm, php 4.4.1 has just disappeared from your server. Now I need to go back to 4.4.0?