After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 645216 - Wrong permissions on server result in bogus error message
Wrong permissions on server result in bogus error message
Status: RESOLVED FIXED
Product: GnuCash
Classification: Other
Component: Backend - SQL
2.4.x
Other Windows
: Normal normal
: ---
Assigned To: Phil Longstaff
Geert Janssens
: 645554 (view as bug list)
Depends on:
Blocks:
 
 
Reported: 2011-03-19 10:55 UTC by Neko Chang
Modified: 2018-06-29 22:55 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
Distinguish test failure from failure to test in conn_test_dbi_library() (7.30 KB, patch)
2011-09-17 22:50 UTC, John W. O'Brien
needs-work Details | Review
Distinguish test failure from failure to test in conn_test_dbi_library() (12.82 KB, patch)
2011-09-18 01:19 UTC, John W. O'Brien
committed Details | Review

Description Neko Chang 2011-03-19 10:55:06 UTC
Hi everyone.

I installed GnuCash 2.4.4 in windows vista 32bit and GnuCash 2.4.3 in FreeBSD 8.2 64bit and MySQL server 5.5.9 in FreeBSD 8.2 32bit server on internet.

I had been save transactions in GnuCash windows vista 32bit with sqlite3 back end.
I wish access GnuCash anywhere and windows/FreeBSD, So I plan move transactions to MySQL server.
But I can't find any procedure about my wish, So I tried it myself.
Under is my procedure.
1. Create a empty database on MySQL server.
2. Open GnuCash 2.4.4 in windows.(Open exist sqlite3 database automatic.)
3. Choose File->Save As->choose Data format:MySQL & host & database & password
4. Got error message
   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 had been view  https://bugzilla.gnome.org/show_bug.cgi?id=611936 but it said resolved?

Later, I use Gnucash 2.4.3 on FreeBSD, Create a new account and "Save As" to MySQL server same windows, Got same error message also.
FreeBSD had been install dependence library libdbi 0.8.4 and libdbi-drivers 0.8.3-1

Is it bug? or mistake procedure?
Please help us.
Thanks a lot.
Comment 1 John Ralls 2011-03-19 14:29:35 UTC
The fix for Bug 611936 is the test that is now failing for you: It attempts to store 128-bit numbers into the database and read them back out. Although it's intended to find a problem with libdbi, that shouldn't be the case on MSWin because the bug doesn't manifest there. It's also strange that the check passes for SQLite3 but not for MySal. 

Your FreeBSD machine may well have the libdbi problem, or there may be a MySql problem. Can you "save as" to SQLite3 on the BSD system?

One other possible source of your problem is MySql's case sensitivity: It doesn't like uppercase characters in object names, so if you named your database (for example) Gnucash instead of gnucash, it will fail.
Comment 2 Neko Chang 2011-03-19 17:39:42 UTC
Thanks you very much.
About FreeBSD machine libdbi issue.
Work fine.
1. Create new account and add some transactions and save as to SQLite3,
   And close GnuCash, Reopen GnuCash, All transactions is OK.
2. Copy exist (current a lot of transactions) sqlite3 file from windows to FreeBSD
   And open, add transactions, close, reopen, All transactions is OK also.

About MySQL server, I created database's name is lowercase(eg:gnucash).
 So it is not root cause I think.
 But the database is empty, Is it OK?

Note: I make sure MySQL server connect-able on windows, tested by mysqlviewer.
Thanks you a lot.
Comment 3 John Ralls 2011-03-19 18:03:55 UTC
OK. Please try again to save as your MySql database, and quit gnucash immediately after you get the libdbi dialog box. Run `tail /tmp/gnucash.trace` and post the output.
Comment 4 Neko Chang 2011-03-19 20:33:06 UTC
Thanks you very much.
I had been try it.
But not a /tmp/gnucash.trace file generated.

So I tried under procedure
Round 1: shell> gnucash --logto /tmp/gnucash.trace
  /tmp/gnucash.trace not exist, But I saw under error message.
  (gnucash:6950): gnc.backend.dbi-WARNING **: [conn_test_dbi_library()] Test_DBI_Library: Create table failed

(gnucash:6950): gnc.backend.dbi-CRITICAL **: gnc_dbi_unlock: assertion `dbi_conn_error( dcon, NULL ) == 0' failed

(gnucash:6950): gnc.engine.sx-CRITICAL **: gnc_sx_get_sxes_referencing_account: assertion `sxactions != NULL' failed


Round 2: shell> gnucash --debug > tmpfile
On console, I saw under error message.
qof.engine-INFO: [qof_event_generate_internal] id=1 hi=0x80b517a80 han=0x8012f964b data=0x0
qof.session-INFO: [qof_session_load_backend]  selected GnuCash Libdbi (MYSQL) Backend
gnc.backend.dbi-INFO: [mysql_error_fn] DBI error: 1044: Access denied for user 'gnucash'@'%' to database 'gnucash'

gnc.backend.dbi-INFO: [mysql_error_fn] Note: GbcDbiSqlConnection not yet initialized. Skipping further error processing.

(gnucash:62543): gnc.backend.dbi-WARNING **: [conn_test_dbi_library()] Test_DBI_Library: Create table failed
qof.session-INFO: [qof_session_begin] Done running session_begin on backend

It look like privileges issue. So I tried.
shell> mysql -h [server] -ugnucash -p
I can connect to MySQL server by "gnucash" user.
Later, I use "GRANT ALL" turn on all privileges, and save as again.

It work !! I can save data to SQL server !!

The issue is privileges problem, Not "The library "libdbi" installed on your system doesn't correctly store large
numbers."
The error message confuse me...
Can fix the error message for real reason?

I have 2 addition question.
1.Can modify SQL connect port? Because firewall block connect(port) in company.
2.Support SSL encryption SQL connect in the future? Because secure data.

Thanks you a lot.
Comment 5 John Ralls 2011-03-19 21:44:47 UTC
Yeah, I just realized that that would be the first error one would get for permissions; I'll fix that soon so that the right error gets raised.

I'm reopening this bug and changing its summary to remind me.

I think that you can set the port by adding it after the hostname, separated by a colon (e.g., foo.myhost.com:1234). The protocol is set by mysql, not by us. If you need security, set up an ssh tunnel (which will also let you evade your company's firewall if the hostname trick doesn't work).
Comment 6 Neko Chang 2011-03-22 13:31:26 UTC
Thanks you a lot.
I had been try under...
1.foo.myhost.com:1234 and firewall forward ports:  couldn't work. I don't know why.
2.ssh tunnel work fine, I can use GnuCash in my company :D
  The idea could solve port block & security issue.

Now, MySQL & PostgreSQL native support SSL connect, If GnuCash support it also, it is good I think :p

Thanks GnuCash team a lot.
Comment 7 John Ralls 2011-03-22 20:47:47 UTC
*** Bug 645554 has been marked as a duplicate of this bug. ***
Comment 8 John Ralls 2011-03-22 21:17:11 UTC
Sorry, the library we use for sql database access (http://libdbi.sourceforge.net) doesn't support ssl AFAICT, so we're not going to be able to help with that.
Comment 9 Neko Chang 2011-03-26 08:20:00 UTC
Thansk answer.
I understood. :p
If libdbi could support SSL, GnuCash support SSL also.
I must be waiting libdbi.

Thanks you a lot :D
Comment 10 John W. O'Brien 2011-09-17 22:50:58 UTC
Created attachment 196848 [details] [review]
Distinguish test failure from failure to test in conn_test_dbi_library()

This patch applies to 2.4.7.

In gnc-backend-dbi.c: delegate responsibility for setting Qof backend error and message from gnc_dbi_sqlite3_session_begin() to conn_test_dbi_library(); use ERR_SQL_DBI_UNTESTABLE if any preliminary test steps fail, or ERR_SQL_BAD_DBI if at least one main test steps fail.

Define ERR_SQL_DBI_UNTESTABLE error code and message.
Comment 11 John Ralls 2011-09-17 23:11:57 UTC
Comment on attachment 196848 [details] [review]
Distinguish test failure from failure to test in conn_test_dbi_library()

Consider this instead:

typedef enum
{
    GNC_DBI_PASS = 0,
    GNC_DBI_FAIL_SETUP,
    GNC_DBI_FAIL_TEST
} GncDbiTestResult;

return that from conn_test_dbi_library() and send the appropriate event in a switch. No travelers and no gotos.
Comment 12 John W. O'Brien 2011-09-17 23:55:58 UTC
I considered that, but elected to use delegation and gotos as the lesser of two evils in comparison to a copy/paste switch block. The gotos seemed justifiable given prior art in gnc-backend-dbi.c.

In any case, I'm perfectly content to re-wicker the patch under house rules.
Comment 13 John Ralls 2011-09-18 00:56:28 UTC
The existing gotos are for consolidating freeing resources, which is reasonably idiomatic when you are running through a bunch of tests like that. 

One of your two labels is a no-op; just calling "return retval" where you "goto exit" does the same thing and is clearer. The "incomplete" label encloses a condition and makes the calls that use the traveling parameter, so it's clearer to return GNC_DBI_FAIL_SETUP and let the calling function send the messages -- especially since the calling functions are mostly running tests (of which this is one) and sending messages. (Another way to avoid the "goto incompletes" would be to wrap the second block in an else and then just test retval as you already do:

     if ( result == NULL )
     {
         PWARN("Test_DBI_Library: Failed to insert test row into table" );
         retval = FALSE;
     }
     else
     {
         dbi_result_free( result );
         gnc_push_locale( LC_NUMERIC, "C");
         result = dbi_conn_query( conn, "SELECT * FROM numtest" );
         if ( result == NULL )
         {
             const char *errmsg;
             dbi_conn_error( conn, &errmsg );
             PWARN("Test_DBI_Library: Failed to retrieve test row into table: %s",
             errmsg );
             result = dbi_conn_query( conn, "DROP TABLE numtest" );
             retval = FALSE;
          }
      }
      if ( retval == FALSE )
      {
          qof_backend_set_error( qbe, ERR_SQL_DBI_UNTESTABLE );
          qof_backend_set_message( qbe, "DBI library large number test incomplete" );
          gnc_pop_locale( LC_NUMERIC )
          return retval;
      }
) but I think that the three-state return is clearer.

I noticed while writing that that I missed popping the locale in the event of a retrieve failure, so I added it in to the example.

Roger the copy/paste problem. There's a lot of it in gnc-backend-dbi, mostly to customize the messages. It could be done better, but there are much bigger problems elsewhere.
Comment 14 John W. O'Brien 2011-09-18 01:19:14 UTC
Created attachment 196858 [details] [review]
Distinguish test failure from failure to test in conn_test_dbi_library()

This patch applies to 2.4.7.

In gnc-backend-dbi.c, accept enumerated result from conn_test_dbi_library(), then throw ERR_SQL_DBI_UNTESTABLE if the test setup failed, or ERR_SQL_BAD_DBI if at least one test case failed.

Define ERR_SQL_DBI_UNTESTABLE error code and message.
Define GncDbiTestResult enumerated type.
Comment 15 John W. O'Brien 2011-09-18 01:22:52 UTC
(In reply to comment #13)
> The existing gotos are for consolidating freeing resources, which is 
> reasonably idiomatic when you are running through a bunch of tests like
> that.

Fair enough.

> [patch analysis, example]

Thanks for giving me that insight into your thought process.

> I noticed while writing that that I missed popping the locale in the event of a
> retrieve failure, so I added it in to the example.

I did not include this fix in attachment 196858 [details] [review].

> Roger the copy/paste problem. There's a lot of it in gnc-backend-dbi, mostly to
> customize the messages. It could be done better, but there are much bigger
> problems elsewhere.

The feeling is a familiar one to me.
Comment 16 John Ralls 2011-09-18 18:05:59 UTC
Review of attachment 196858 [details] [review]:

Made a couple of editorial changes (GNC_DBI_PASS != result -> result != GNC_DBI_PASS, changed error messages to report the backend).
Committed to trunk and 2.4
Comment 17 mrpaul 2016-02-26 17:01:11 UTC
I want to migrate to sqlite but when i try to save as sql I am also getting this error:

GnuCash could not complete a critical test for the presence of a bug in the "libdbi" library. This may be caused by a permissions misconfiguration of your SQL database. Please see https://bugzilla.gnome.org/show_bug.cgi?id=645216 for more information.

tail /tmp/gnucash.trace
* 18:51:07  CRIT <gnc.backend.dbi> [sqlite3_error_fn()] DBI error: 13: database or disk is full
* 18:51:07  WARN <gnc.backend.dbi> [conn_test_dbi_library()] Test_DBI_Library: Create table failed

my disk is not full. in fact i have 128G available in my home dir which is where i want to save it.

arch linux
libdbi 0.9.0-2
libdbi-drivers 0.9.0-2
gnucash 2.6.11-2
Comment 18 John Ralls 2017-09-24 22:44:31 UTC
Reassign version to 2.4.x so that individual 2.4 versions can be retired.
Comment 19 John Ralls 2018-06-29 22:55:16 UTC
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=645216. Please update any external references or bookmarks.