Re: MySQL v. PostgreSQL

Torsten Bronger writes:
 > > I'd like to see your RefDB setup (run refdb-bug to create an
 > > overview of your setup)
 > 
 > I can't find this program, but my config file says

Weird. Should be installed in /usr/local/bin/refdb-bug. Anyway, the
following config file is the one we need to look at, and it looks ok.

 > 
 > refdblib /usr/local/share/refdb
 > main_db	refdb
 > serverip	localhost
 > dbsport		5432
 > dbserver	pgsql
 > dbpath		/usr/local/var/lib/refdb/db
 > port	9734
 > logfile /usr/local/var/log/refdbd.log
 > logdest file
 > loglevel info
 > pidfile		/usr/local/var/run/refdbd.pid
 > remoteadmin f
 > remoteconnect f
 > keep_pnames t
 > keyword_scan t
 > dbi_driverdir /usr/lib/dbd
 > db_encoding	UTF-8
 > in_encoding	UTF-8

 > http://www-users.rwth-aachen.de/Torsten.Bronger/mysql.log
 > http://www-users.rwth-aachen.de/Torsten.Bronger/pgsql.log
 > 

Thanks for providing these logs. I've compared them with each other,
and I've compared them to what my setup produces. The main difference
that I see in your logs is, strange enough, in the pgsql log:

input encoding is:
UTF-8
database encoding is:
UTF8

See the missing dash? This does not happen on my installation. The
reason appears to be a change in PostgreSQL's character encoding
support between 8.0.19 (which I run) and 8.1 or later (which I bet you
are running). 8.0.x used "UNICODE" as the encoding name for UTF-8
data. 8.1 changed this to "UTF8" ("UNICODE" is still recognized as an
alias). The pgsql driver did not know about this change until about
five minutes ago. All libdbi drivers contain routines to translage the
database engine-specific encoding names to IANA names. If a databae
encoding name is not recognized, no error is thrown but the name is
passed as is. This is why "UTF8" shows up in your logs. The weird
thing is that this should cause an iconv error, as "UTF8" is not a
valid encoding name supported by iconv. What does

iconv -l|grep -i utf8

return on your system?

In any case, if that were a problem, I'd expect PostgreSQL to cause unicode
problems instead of MySQL.

Your MySQL log looks perfectly ok though:

no character encoding conversion required
database encoding is:
UTF-8

This shows that refdbd does not convert the encoding, as it expects
incoming data as UTF-8 and inserts them into the database engine as
is.

Did you check those datasets which you inserted when creating the
above logs? Is it possible that you changed your setup at some point,
and only datasets fail which you added before that point?

I've also checked the variables used by my MySQL setup. I don't see a
difference to your setup, especially character-set-server and
collation-server are identical.

regards,
Markus

-- 
Markus Hoenicka
[email protected]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de

------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H