SQL persistance problems

[StrangeTcy]

I've followed the instructions and did some editing on my own, and managed to get guile -l run-chatbot.scm from not working with sql at all to

Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.

I tried to figure out what's wrong, but haven't yet.
I've probably made some simple mistake and some of my ODBC-related files are wrong. The Postgres database exists and I can connect to it with psql without any issue.
All the necessary dependencies seem to be satisfied, but I'll use any double-checks offered.

[StrangeTcy]

This might be a separate issue, but for the moment I'll leave it here:
the kind people from #postgresql on irc have suggested abandoning the ODBC altogether and using this and this to work with postgres directly from Scheme.

[StrangeTcy]

Okay, the relevant piece of logs is:

CONN ERROR: func=original_CC_connect, desc='', errnum=201, errmsg='Missing database name in call to CC_connect.'
            ------------------------------------------------------------
            henv=0x3dec0d0, conn=0x3ffd280, status=0, num_stmts=16
            sock=(nil), stmts=0x3ffddd0, lobj_type=-999
            ---------------- Socket Info -------------------------------
CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect', errnum=201, errmsg='Missing database name in call to CC_connect.'
           ------------------------------------------------------------
            henv=0x3dec0d0, conn=0x3ffd280, status=0, num_stmts=16
            sock=(nil), stmts=0x3ffddd0, lobj_type=-999
            ---------------- Socket Info -------------------------------
DSN info: DSN='triples',server='',port='',dbase='',user='',passwd=''
          onlyread='',protocol='',showoid='',fakeoidindex='',showsystable=''
          conn_settings='(null)', conn_encoding='(null)'
          translation_dll='',translation_option=''
conn = 0x3fff340, PGAPI_Connect(DSN='triples', UID='opencog_user', PWD='xxxxx')
Driver Version='09.03.0300,201405140001'
Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
                disable_optimizer=0, ksqo=1, unique_index=1, use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings='(null)' conn_encoding=''

[linas]

So -- several remarks

-- first, the unit test cases for postgres pass for me, which tells me that you probably have postgress/odbc misconfigured. Please make sure that the unit test cases are passing. You will need to very carefully study the README file to do this.

-- next, at this time, there is nothing at all in the chatbot that requires the database, although this will be interesting in the future, to save/restore everything that the chatbot might learn, conversationally. But this will require a big chunk of architectural work, so unless you are willing to go there, I'm not entirely sure why you are working on this.

-- next, the people on #poostgres are giving you terrible advice-- they are clueless and don't know what they are talking about. Ignore them.

-- next, there are two drivers in the atomspace: one that uses ODBC, and one that attempts to bypass it, and go directly (natively) to postgres. The ODBC driver works, and is stable. The native-postgres driver was never completed, and I have no clue if it actually works.

So, in conclusion:

1. verify that ODBC unit tests work. If you can get them to pass, then everything else will work for you too. You have to very carefully study the README file.

2. If you wish, finish coding up the native-postgres driver. But since we already have a guaranteed-working driver, this is of lower priority.

[StrangeTcy]

Let me try to answer all of this:

• I was thinking about unit test, but was not sure they'd help pinpoint the problem. But now I have a reason to run them. And then it either goes well, or it doesn't, and then I can have another issue. which I can fail to solve for myself.
• at the moment, my vision is this: whatever instrument (sorry, that's an ad-hoc term) using the current opencog implementation I run, I wish it to contribute to an atomspace that should be available for all the other instruments. So, I set up a persistent storage for this chatbot, populate it, and then try to access it fro, say, chatbot-psi.
  That vision may be really wrong and might be discarded when I read more of the book.
• they don't know what I'm aiming at, yes, but I can't see why their advice is so terrible. Either way, I'm not going to try following it for a long time, if ever.

So, at this point I should be off to fiddling with unit tests :-) Thanks for the pointers!

[linas]

OK, again, there is a very detail HOWTO here https://github.com/opencog/atomspace/blob/master/opencog/persist/sql/README.md
that explains exactly how to configure it so that it works -- perhaps you haven't found that one yet.

I suspect that a blanket-save of everything to the database will jsut be confusing, and that the chatbot and other systems will need to be altered to be selective for what they save, and for what they fetch.

[StrangeTcy]

I've been following both the HOWTO and the test README before, but decided to double-check, hoping to find the error.
First, I've followed the HOWTO, and then it directed me to the tests/persist/sql/README, and there I got as far as

3) Run the two test cases:

   $ ./tests/persist/sql/BasicSaveUTest
   $ ./tests/persist/sql/PersistUTest

   It should print OK! at the end, if all tests passed.

In my case, both tests basically caught the same error:

:~ /home/myname/opencog/atomspace/build/tests/persist/sql/odbc/PersistUTest 

Running cxxtest tests (1 test)[INFO] Using config file found at: /home/myname/opencog/build/../lib/atomspace-test.conf
[2016-09-29 11:56:11:986] [INFO] Initializing ClassServer
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
[2016-09-29 11:56:12:223] [ERROR] sql-open: Error: Unable to connect to the database (/home/myname/opencog/atomspace/opencog/persist/sql/odbc/SQLPersistSCM.cc:120)
    Stack Trace:
    2: /usr/local/lib/libcogutil.so (opencog::Logger::logva(opencog::Logger::Level, char const*, __va_list_tag*) +0xd5) [0x7f56c7bdf085]
    3: /usr/local/lib/libcogutil.so (opencog::Logger::Error::operator()(char const*, ...) +0x92) [0x7f56c7bdf1f2]
    4: /usr/local/lib/libcogutil.so (opencog::StandardException::parse_error_message(char const*, __va_list_tag*, bool) +0x63) [0x7f56c7bcefd3]
    5: /usr/local/lib/libcogutil.so (opencog::StandardException::parse_error_message(char const*, char const*, __va_list_tag*, bool) +0x9a) [0x7f56c7bcf0aa]
    6: /usr/local/lib/libcogutil.so (opencog::RuntimeException::RuntimeException(char const*, char const*, ...) +0xb4) [0x7f56c7bcf1a4]
    7: /home/myname/opencog/atomspace/build/opencog/persist/sql/odbc/libpersist-sql.so (opencog::SQLPersistSCM::do_open(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&) +0xcb) [0x7f56c84a327b]
    8: /home/myname/opencog/atomspace/build/tests/persist/sql/odbc/PersistUTest (PersistUTest::setUp() +0xfa) [0x4160fa]
    9: /home/myname/opencog/atomspace/build/tests/persist/sql/odbc/PersistUTest (CxxTest::RealTestDescription::setUp() +0xc1) [0x411cc1]
    10: /home/myname/opencog/atomspace/build/tests/persist/sql/odbc/PersistUTest (h���V� +0x461) [0x409af1]
    11: /lib/x86_64-linux-gnu/libc.so.6 (h���V� +0xf0) [0x7f56c86d6830]
    12: /home/myname/opencog/atomspace/build/tests/persist/sql/odbc/PersistUTest (h���V� +0x29) [0x40a199]

[2016-09-29 11:56:12:228] [INFO] setUp: SQLPersistSCM cannot connect to database

In PersistUTest::test_atomspace:
/home/myname/opencog/atomspace/build/tests/persist/sql/odbc/../../../../../tests/persist/sql/odbc/PersistUTest.cxxtest:96: Error: Test failed: The PersistUTest failed.

I still think I'm missing something basic. I also think that I need some way to examine that call to CC_connect; I should be able to see where is it expecting to take the database name from, and where it doesn't find any now.
Update: Reading the logs seems to show that opencog::SQLPersistSCM::do_open() doesn't get passed some argument (I guess that's the database name). Still, I don't know why.

[linas]

The database name is taken from the odbc.ini file. This should be in the home directory of the user you are running as (and have a dot in the name i.e. ~/.odbc.ini )

Mine looks like this:

[opencog_test]
Description     = Unit-Test DB for Opencog unit tests.
Driver      = PostgreSQL
; wtf -- keeps tracing no matter what I do!?
; Trace     = No
Trace       = 0
TraceFile       = 
CommLog = No
; Database      = test-persist
Database        = opencog_test
; Servername    = localhost
Servername  = 10.70.70.2
Port            = 5432
Username        = opencog_tester
Password        = cheese
ReadOnly        = No
RowVersioning       = No
ShowSystemTables    = Yes
ShowOidColumn       = Yes
FakeOidIndex        = Yes
ConnSettings        = 

[StrangeTcy]

Ok, this file does exist on my machine, its name starts with a ., it is located at ~/.odbc.ini, and mine starts like:

 [mycogdata]
Description      = OpenCog AtomSpace Stuff (holding atomspace stuff for my machine)
Driver           = PostgreSQL_Unicode
Trace            = Yes
TraceFile        = /tmp/psql-odbc-mycogdata.log
Database         = mycogdata
Servername       = localhost
Port             = 5432
Username         = opencog_user
Password         = mypassword
ReadOnly         = No
RowVersioning    = No
ShowSystemTables = Yes
ShowOidColumn    = Yes
FakeOidIndex     = Yes
ConnSettings     = 

[opencog_test]
Description      = Unit-Test DB for Opencog unit tests
Driver           = PostgreSQL
Trace            = 0
TraceFile        = 
CommLog          = No
Database         = opencog_test
Servername       = localhost
Port             = 5432
Username         = opencog_tester
Password         = cheese
ReadOnly         = No
RowVersioning    = No
ShowSystemTables = Yes
ShowOidColumn    = Yes
FakeOidIndex     = Yes
ConnSettings     = 

[StrangeTcy]

Comments on my semi-random poking around trying to figure it out:

• There seems to be a database called triples, and for some reason the system's trying to connect to it
• Our ~/.obdc.inis look the same (unless there's a weird difference in formating or indentation), so I think it's not these files that are causing the problem.
• I've been skimming files in /home/myname/opencog/atomspace/opencog/persist/sql/odbc/ (not really reading them carefully, and also understanding them in as much as my limited knowledge of C++ permits it), and in ODBCAtomStorage.cc I've noticed a bit that says:

void ODBCAtomStorage::init(const char * dbname,
                       const char * username,
                       const char * authentication)
{
    // Create six, by default ... maybe make more?
    // There should probably be a few more here, than the number of
    // startWriterThread() calls below.
#define DEFAULT_NUM_CONNS 6

Now, when I run a cogserver and telnet to it and then sql-open mydb myuser mypassword from a different terminal, the one where cogserver's running outputs errors:

Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.
Listening on port 17001

My guess is that there are 6 threads started, each of them (in due time; I haven't really thought trough all the workflow) tries to open a database by calling opencog::SQLPersistSCM::do_open(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&), and each fails because somehow they don't get a database name.

---

Update: some more code reading makes me think that

Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.

is from odbcxx.cc; rc=-1 is from lines 77 and 91:

SQLRETURN rc;
/*...*/
sql_henv = NULL;
/*...*/
rc = SQLAllocEnv(&sql_henv);

.
(-1) is (I think) from sql.h, line 29: #define SQL_NULL_DATA (-1);
then PRINT_SQLERR (which is SQLGetDiagRec in disguise) probably outputs (201).
I also think that the rest of the line, starting from [unixODBC], is output by some other piece of code.

[linas]

On Tue, Oct 4, 2016 at 1:22 PM, StrangeTcy notifications@github.com wrote:

• There seems to be a database called triples, and for some reason the
  system's trying to connect to it

!!?? Since opencog does not create any databases, you must have created
this. There were some things called "triples" in opencog 5+ yeas ago, but
they are obsolete.

The only way that the database would try to connect to it is if it appeared
in your .odbc.ini file, and you typed that name in, by hand!

• I've been skimming files in /home/enoch/opencog/atomspace/
  opencog/persist/sql/odbc/ (not really reading them carefully, and also
  understanding them in as much as my limited knowledge of C++ permits it),
  and in ODBCAtomStorage.cc I've noticed a bit that says:

void ODBCAtomStorage::init(const char * dbname,
const char * username,
const char * authentication)
{
// Create six, by default ... maybe make more?
// There should probably be a few more here, than the number of
// startWriterThread() calls below.
#define DEFAULT_NUM_CONNS 6

Now, when I run a cogserver and telnet to it and then sql-open mydb
myuser mypassword from a different terminal, the one where cogserver's
running outputs errors:

Can't perform SQLConnect rc=-1(201) [unixODBC]Missing database name in call to CC_connect.

Is "mydb myuser mypassword" actually in your ~/.odb.ini file? I believe
that these have to match up to what is in that file. If they don't, or you
have a typo, then errors result.

For you, opening with opencog_test opencog_tester cheese should work,
assuming that you've actually created this database, and populated it with
tables.

Yes, the error messages should be tightened up to indicate the actual
problem.

--linas

[StrangeTcy]

Is "mydb myuser mypassword" actually in your ~/.odb.ini file?

Yes; it's actually mycogdata opencog_user mypassword from above.

The opencog_test sanity check is reasonable; so:

• I can psql opencog_test
• I can use flags (as the persistance HOWTO recommends) and psql opencog_test -U opencog_tester -W -h localhost
• but from the cogserver shell I can't opencog> sql-open opencog_test opencog_tester cheese. The error given is the same as before:
  sql-open: Error: Unable to connect to the database (/home/myname/opencog/atomspace/opencog/persist/sql/odbc/SQLPersistSCM.cc:120)

So, from this I take that both databases exist (were created) and are populated (as they should be after following the respective steps in the HOWTO), but somehow I can't connect to them.

---

Update: I think I've figured out the dependencies and error propagation:
In SQLPesrsistSCM.cc, line 120:

    throw RuntimeException(TRACE_INFO,
                "sql-open: Error: Unable to connect to the database");

because:
!_store->connected()
where :
_store = new ODBCAtomStorage(dbname, username, auth);

defined in ODBCAtomStorage.cc, line 482:

bool ODBCAtomStorage::connected(void)
    {
            ODBCConnection* db_conn = get_conn();
            bool have_connection = db_conn->connected();
            put_conn(db_conn);
            return have_connection;
    }

where get_conn is based on conn_pool,
defined in ODBCAtomStorage.h:
concurrent_stack<ODBCConnection*> conn_pool;

ODBCConnection is defined in odbcxx.cc
And from it I get all the "Can't perform SQLConnect"

Great. But doesn't help me understand why I get the error, yet.

[linas]

I just added a postgres driver, so that odbc can be completely avoided. That should sharply simplify configuration mistakes.

-- please try it!
-- you'll need to install libpq-dev for it to compile.
-- you'll need to make clean also
-- please make sure that the unit tests pass. There are four of them, they should pass.