False positive in test for libdbi bug

[John W. O'Brien]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Summary
- -------

I just solved a mystery involving my use of GnuCash with a SQL backend
and this message documents that scenario in the hopes that it will help
other users.

GnuCash performs a test for a known bug in libdbi which can badly
corrupt your data, and refuses to proceed if the bug is found. The test
can succeed (or fail, depending on your perspective) for reasons
unrelated to the bug, including misconfiguration of the permissions set
in the backing database.

[Note: If the developers think it would be appropriate for me to open a
bug for this, please say so and I will.]


Details
- -------

When using the DBI backend in GnuCash 2.4.2 and later (through at least
2.4.7), you may see the following message when attempting to access your
data.

- --- snip ---
The library "libdbi" installed on your system doesn't correctly store
large numbers.  This means GnuCash cannot use SQL databases correctly.
Gnucash will not open or save to SQL databases until this is fixed by
installing a different version of "libdbi".  Please see
https://bugzilla.gnome.org/show_bug.cgi?id=611936 for more information.
- --- snip ---

In addition to detecting a buggy libdbi, this error is also triggered on
each of the these, generally unrelated cases.

1. Cannot create a temporary table in the database.
2. Cannot insert a test row into the temporary table.
3. Cannot select the test row.


Additional background (for the curious and technically inclined)
- ---------------------

I encountered this on Ubuntu 11.04 (natty) using GnuCash 2.4.6 compiled
with the following options.

   --prefix=/opt/gnucash
   --program-prefix=$(hostname)-
   --enable-ofx
   --enable-doxygen
   --enable-compile-warnings=yes
   --enable-error-on-warning
   --enable-dbi

The SQL backend is MySQL 5.1.56 running on a different computer.

The version of libdbi I have installed [0] includes a workaround for the
bug.

The test for the libdbi bug was published [1] on 2011-01-22 in response
to the bug cited in the error message.

The permissions that caused me to encounter this issue were of the form,

   GRANT ALL PRIVILEGES ON `$GNCDB`.`$GNCDB` to '$GNCUSER'@'GNCHOST'

where $GNCDB, $GNCUSER, and $GNCHOST are as you might expect. This was
evidently a typo, and the fix was to change this to,

   GRANT ALL PRIVILEGES ON `$GNCDB`.* to '$GNCUSER'@'GNCHOST'


Bonus round
- -----------

Too late to be useful in this case, I learned about the logging
capabilities [2] of GnuCash, which most likely would have saved me some
time and hair pulling.


References
- ----------

[0] https://launchpad.net/ubuntu/natty/amd64/libdbi0/0.8.3-0ubuntu2
[1] http://svn.gnucash.org/trac/changeset/20147
[2] http://wiki.gnucash.org/wiki/Logging
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJObVWXAAoJEEdKvTwaez9wregH/0RveYJn1AkdtVUaES2SmJ2g
q2wDKDPm8c4a+2UFXqKYHGF5/gYMi55udnnzUg7pvTsc69xAUoe75rGnztS8SrUk
0Vr/fHi2iJbY/JugetBOR8lOuHm8srQ30Bzp9foqSLhZNKODAACRtG0q3dr2C2Nb
SzVqklk1sEjI2X8R+HCqqqvPx8QxCek+4GpsZQjNwTd3s73x60swBoFFqd6UsDJT
vhkgKJsUVe3lnnZZITAMiiylJDLVWTRHTJwsK/17FbF9rKy3WzDctvASJtxlJMQn
H0SXvnKTQuib8k/XRTSLehSrBHnnMIw6IL7dZb8vg5EPQOoZzjmpip092HpwLys=
=bMms
-----END PGP SIGNATURE-----