Bug 726912

I can't replicate this on either i386 or x86_64 builds... are you sure you're using an up-to-date build of libdbi and libdbi-drivers?

I checked all the installed versions:
$ rpm -qa | grep libdbi
libdbi-0.8.3-4.fc15.i686
libdbi-dbd-mysql-0.8.3-8.fc15.i686
libdbi-drivers-0.8.3-8.fc15.i686

[andy@speedy ~]$ rpm -qi libdbi-0.8.3-4.fc15.i686
Name        : libdbi
Version     : 0.8.3
Release     : 4.fc15
Architecture: i686
Install Date: Sat 02 Jul 2011 16:00:58 BST
Group       : Development/Libraries
Size        : 92176
<snip>

Plus these
gnucash-2.4.7-1.fc15.i686
mysql-5.5.14-2.fc15.i686

I'm running a clean install of 32 bit FC15, not an upgrade.

This is the newest version I can find on koji.  Do you have a newer build?

Hmph.  Those are the exact versions I tested, on an F15 x86 install that was fresh and "yum update"d as of yesterday.  Barring something really unpleasant like a vendor-specific CPU bug, it doesn't seem like this could be a version-skew issue.  What seems more likely is that I didn't do the same thing you did.  Bearing in mind that I know nothing worth mentioning about gnucash, could you walk me through the exact reproduction sequence?  Might be a good idea to work in a virgin user account, too.  And while I'm speculating, do you have any nondefault settings in /etc/my.cnf?

Hi, 

Here are the steps to reproduce on my system

I started with a completely new test user account

As root
:: install gnucash & deps
:: install mysql 
:: start mysqld
:: install libdbi-0.8.3-4.fc15.i686, libdbi-dbd-mysql-0.8.3-8.fc15.i686 &
libdbi-drivers-0.8.3-8.fc15.i686.  


create new mysql database called gnucash.  I do this using the mysql client with the commands below
----
[root@speedy ~]# mysql <optionally add password if you set one for mysql>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database gnucash;
Query OK, 1 row affected (0.00 sec)
----



Create a new user called gnucash with all permissions to access the new database gnuchash.  Again, use mysql client as admin user, and execute this
----
mysql> create user 'gnuchash'@'localhost' identified by 'gnucash';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on gnucash.* to 'gnuchash'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> <ctrl-d to exit>
----



check that the new gnucash mysql user can login to the new database correctly
---
[andy@speedy ~]$ mysql -u gnucash gnucash -p
Enter password: <gnucash>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> <ctrl-d to exit>
---



launch gnucash from Activties menu

After Tip of the Day, a "Welcome to GnuCash!" dialog appears

Select "Create a new set of accounts", OK
"New Account Hierarchy Setup" assistant appears,
select click Forward a few times to accept Default account options then Apply

gnucash displays the main Accounts tab with 5 default top level accounts 

Select File / Save As...
Choose Data Format = mysql
Host = localhost
Database = gnucash
Username = gnucash
Password = gnucash

Click "Save As" button, and the error appears
"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."



I checked my /etc/my.cnf - it's default.

Also, I checked my CPU type
---
[andy@speedy ~]$ cat /proc/cpuinfo 
processor	: 0
vendor_id	: GenuineIntel
cpu family	: 15
model		: 4
model name	: Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping	: 1
<snip>
---


I studied the bug report again.  It seems to be a 32-bit specific issue.  It looks like there are 3 patches attached to this bugzilla which seem to fix the problem
https://bugzilla.gnome.org/show_bug.cgi?id=611936

OK, that was what I did, and I just did it again to make sure, and the error doesn't happen on my somewhat-recent laptop (Core i5 processor).  So it's looking like maybe it *is* processor specific.  I do still have a Pentium 4 machine gathering dust in the corner ... I'll see if I can get it to run F15 at all.  More news later.

Well, that was a bust ... no bug visible on that machine either.  This one is a 2001-vintage Dell Pentium 4, /proc/cpuinfo showing

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 1
model name      : Intel(R) Pentium(R) 4 CPU 1.80GHz
stepping        : 2
cpu MHz         : 1794.151
cache size      : 256 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm up pebs bts
bogomips        : 3588.30
clflush size    : 64
cache_alignment : 128
address sizes   : 36 bits physical, 32 bits virtual
power management:

which is apparently a good bit older than yours, so now I'm baffled again.  Could you show the complete cpuinfo printout from yours?  What age and model is it?

Another line of thought is that the problem is triggered by some system-level setting we're not thinking about; I suddenly recall the comments about locale settings in the gnome BZ entry for instance.  I'm just using vanilla C locale, what about you?

Thanks for your tenacity Tom!

I'm not sure if this is the locale setting that you mean, but I found that root shows a US locale
[root@speedy ~]# locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
<snip>

and my test user showed the UK locale
LANG=en_GB.UTF-8
LC_CTYPE="en_GB.UTF-8"
<snip>

That got me excited!  I changed the local in the gnome control panel, and re-logged in -- unfortunately, no change.

Here is my cpu info
[root@speedy ~]# cat /proc/cpuinfo 
processor	: 0
vendor_id	: GenuineIntel
cpu family	: 15
model		: 4
model name	: Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping	: 1
cpu MHz		: 3000.000
cache size	: 1024 KB
physical id	: 0
siblings	: 2
core id		: 0
cpu cores	: 1
apicid		: 0
initial apicid	: 0
fdiv_bug	: no
hlt_bug		: no
f00f_bug	: no
coma_bug	: no
fpu		: yes
fpu_exception	: yes
cpuid level	: 5
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx constant_tsc pebs bts pni dtes64 monitor ds_cpl cid xtpr
bogomips	: 5998.05
clflush size	: 64
cache_alignment	: 128
address sizes	: 36 bits physical, 32 bits virtual
power management:



I found another PC and installed 32 bit FC15 on that - using the same package versions.  libdbi works perfectly on that other machine...

The other machine has an older cpu (cpu family=6, model=15) and the locale is set to en_US.utf8.  

So, I think you are right - this seems to be machine or local specific. 

On the problem machine, the root user and my test user had different locales.  I think I changed the display locale via the gnome control panel.  Where is the correct location to set the system locale?

Thanks

A

Hmm. I'm not sure where's the "correct" place either, but it does seem possible that the issue is a discrepancy between the locale the running mysql daemon is using and gnucash's locale.  Try doing this to check what mysqld is seeing:

1. Do
ps auxww | grep mysqld
Look to see which PID (second column) is associated with the mysqld process (don't get confused by mysqld_safe).

2. Do
sudo cat /proc/PID/environ | tr '\0' ' '    
where PID is the PID you found above.

You'll see a list of name=value pairs ... what is the value for LANG?

As an example, when I do this I get

$ sudo cat /proc/2611/environ | tr '\0' ' '
PATH=/sbin:/usr/sbin:/bin:/usr/bin PWD=/ LANG=en_US.UTF-8 SHLVL=2 MYSQL_HOME=/usr _=/usr/bin/nohup

so my mysqld is in en_US.UTF-8 locale.

(I tried that together with en_GB.UTF-8 for gnucash, btw, with no luck...)

A different line of inquiry ... I poked into what exactly gnucash is doing to test compatibility, and it seems that they issue these queries:

CREATE TEMPORARY TABLE numtest ( test_int BIGINT, test_unsigned BIGINT, test_double FLOAT8 );
INSERT INTO numtest VALUES (-9223372036854775807, 9223372036854775807, 1.7976921348623158e+307);
SELECT * FROM numtest;

When I do this by hand in the mysql command-line client, I get

mysql> SELECT * FROM numtest;
+----------------------+---------------------+------------------------+
| test_int             | test_unsigned       | test_double            |
+----------------------+---------------------+------------------------+
| -9223372036854775807 | 9223372036854775807 | 1.7976921348623158e307 |
+----------------------+---------------------+------------------------+
1 row in set (0.00 sec)

I wonder whether you see the same thing on your problematic machine ...

Another question ... after poking into the gnucash sources a bit, I realized that it ought to be emitting some log messages into /tmp/gnucash.trace when it gets this error.  Would you look there for lines mentioning "Test_DBI_Library"?

Tom, you nailed it with that one... and, I'm embarrassed...

I was chasing the wrong problem.

It turns out that my gnucash user in MySQL did not have the correct permissions on the database.  The user could login, but could not create new tables.  

When using the Save As MySQL option, GnuCash first opens the connection.  If the supplied credentials cannot login, it displays a meaningful permissions error.  If the user can login, and the schema is empty, GnuCash then tries to create the schema.  If that fails, it seems to display the wrong error message...

I found that in /tmp/gnucash.trace as you suggested.  That file contained this:
------
[andy@speedy ~]$ cat gnucash.trace 
* 23:52:03  WARN <gnc.backend.dbi> [conn_test_dbi_library()] Test_DBI_Library: Create table failed
* 23:53:19  CRIT <gnc.backend.dbi> gnc_dbi_unlock: assertion `dbi_conn_error( dcon, NULL ) == 0' failed
* 23:53:19  CRIT <gnc.engine.sx> gnc_sx_get_sxes_referencing_account: assertion `sxactions != NULL' failed
------

So the pop-up error "The library "libdbi" installed on your system doesn't correctly store large numbers...." is quite misleading - I believe it should really say "Create table failed"

I fixed the permissions, and now everything works

I am really sorry for the false alarm.  Thanks very much for your help!

(In reply to comment #11)
> [andy@speedy ~]$ cat gnucash.trace 
> * 23:52:03  WARN <gnc.backend.dbi> [conn_test_dbi_library()] Test_DBI_Library:
> Create table failed

Hah.  Well, at least we learned where to look for more info about gnucash problems.

> So the pop-up error "The library "libdbi" installed on your system doesn't
> correctly store large numbers...." is quite misleading - I believe it should
> really say "Create table failed"

Yeah, I've been less than impressed with the quality of most of gnucash's database-related error messages --- what's presented to the user contains next to no hard facts, and more often than not a mistaken claim about the origin of the problem.  But I'm not going to volunteer to fix it ...