Latest dev install: tsql2, tlubm and obackup fails + SQ200: No column t6.S.Query execution error

[fgiasson]

Hi @HughWilliams, @iv-an-ru,

I just installed the latest code as of today and the following 3 tests suites are failing:

• tlubm
• tsql2
• obackup

You can see the .output files of these suites for debugging purposes here:

https://gist.github.com/fgiasson/79132e7d5887c9dc66dc

Also, if I ignore them and run the OSF tests suites, I am eventually getting that error:

[unixODBC][OpenLink][Virtuoso iODBC Driver][Virtuoso Server]VD032: remote prepare: SQ200: No column t6.S.Query execution error

for the following SPARQL query:

                    prefix wsf: <http://purl.org/ontology/wsf#>
                    select distinct ?dataset ?title ?description ?creator ?created ?modified ?holdOntology ?contributor
                    from named <http://localhost/wsf/>
                    from named <http://localhost/wsf/datasets/>
                    where
                    {
                      graph <http://localhost/wsf/>
                      {
                        <http://localhost/wsf/users/1> a wsf:User ;
                          wsf:hasGroup ?group .

                        ?access wsf:groupAccess ?group ;
                                wsf:datasetAccess ?dataset .                      
                      }

                      graph <http://localhost/wsf/datasets/>
                      {
                        ?dataset a <http://rdfs.org/ns/void#Dataset> ;
                        <http://purl.org/dc/terms/created> ?created.

                        OPTIONAL{?dataset <http://purl.org/dc/terms/title> ?title.}
                        OPTIONAL{?dataset <http://purl.org/dc/terms/description> ?description.}
                        OPTIONAL{?dataset <http://purl.org/dc/terms/modified> ?modified.}
                        OPTIONAL{?dataset <http://purl.org/dc/terms/contributor> ?contributor.}
                        OPTIONAL{?dataset <http://purl.org/dc/terms/creator> ?creator.}
                        OPTIONAL{?dataset <http://purl.org/ontology/wsf#holdOntology> ?holdOntology.}
                      }    
                    } ORDER BY ?title

The installation steps I used are:

# Step virtuoso to take care of the DB file
/etc/init.d/virtuoso stop

# wait 20 seconds

# Backup db folder... just in case...
cd /var/lib/virtuoso/
zip -9 -r db.zip db

# Get the latest sources
mkdir /tmp/virtuoso
cd /tmp/virtuoso
wget https://github.com/openlink/virtuoso-opensource/archive/develop/7.zip
unzip 7.zip
cd /tmp/virtuoso/virtuoso-opensource-develop-7

# Install prerequired packages
aptitude install dpkg-dev build-essential
apt-get install autoconf automake libtool flex bison gperf gawk m4 make libxml2-dev libssl-dev libreadline-dev

# Compile the new Virtuoso
CFLAGS="-O2 -m64"
export CFLAGS
./autogen.sh
./configure --with-readline --program-transform-name="s/isql/isql-v/"
nice make
nice make check
nice make install

This is a 64 bits servers running Ubuntu 14.04.1 LTS. All the packages are up to date.

[HughWilliams]

@fgiasson: For me the tlubm & tsql2 tests fail on CentOS, which I will need to check with development why, but the obackup tests runs successfully. In your case the Virtuoso server seems to fail to start when explicitly killed to force a restart, thus was it in your "virtuoso.log" file in the ~/binsrc/tests/suite/obackup.ro as I assume it may have crashed or something ? Also is the "virtuoso.lck" file still in place ?

For the OSF testsuite run, which is failing , has this run successfully previously as I assume so ? I also not in your blog that in the new release of OSF ODBC is not longer used and Virtuoso is query via the sparql endpoint, so what version of OSF is being used for your test ?

[fgiasson]

Hi @HughWilliams,

Good for tlubm and tsql2, that way it will be easy to investigate. About obackup, sincce the only issue is that for some reason it doesn't restart, I am not sure it is that worry some.

About OSF, yes for OSF 3.2, but I am testing a new update that uses unixODBC instead of iODBC that would lead to OSF 3.3.

In the past it was working (with iODBC and SPARQL HTTP). I was not sure if the issue was related to the two failing tests or not.

Would that mean that the problem could be the unixODBC drivers? Seems weird considering the error that is returned (even if I am not sure what it really means).

[HughWilliams]

@fgiasson: If unixODBC as the ODBC Driver Manager is replacing iODBC, which I presume does run against the OSF test suite then is would be useful to see an ODBC trace from both ODBC Driver Managers for comparison to see where the difference in calls being made existing resulting in the error ?

[fgiasson]

Hi @HughWilliams,

How can I get such a ODBC trace with iODBC & unixODBC?

Also, do you, or @iv-an-ru, knows what the error means and what may be causing it?

[HughWilliams]

@fgiasson: An ODBC trace with iODBC or UnixODBC can be recreated as detailed at:

http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/FAQ#Tracing%20Application%20Behaviour

I have seen the error condition before last year and @iv-an-ru fixed it indicating there was some bad SQL codegen occurring, so would need a test case to diagnose. Which is why I am keen to know if this error occurs when iODBC is used or even when executed directly from isql or the /sparql endpoint ?

[fgiasson]

@HughWilliams ok good, will do that first thing tomorrow morning, thanks!

[fgiasson]

Hi @HughWilliams, @iv-an-ru,

Here is what I got so far with ODBC tracing. You can see the traces here:

https://gist.github.com/fgiasson/cd3b076010195ca7ac79

The files are:

1. odbc.trace.isql - this one has been generated after logging into virtuoso using the unixODBC isql commandline tool /usr/bin/isql. the query succeeded
2. odbc.trace.tests.suites - this one has been generated after running the query via PHP5/unixODBC (via Apache2). \ the query failed**

I couldn't get tracing working for iODBC after several attempts (adding in both odbc.ini or odbcinst.ini; restarting Virtuoso, etc) the trace was simply not generated after using iODBC's command line too (/usr/bin/isql-v).

So, where we stands now: apparently that the query fails when sent via Apach2/PHP5/unixODBC/Virtuoso. But it apparently succeed when sent via isql/unixODBC/Virtuoso.

I am really wondering why. Is it possible that /usr/bin/isql doesn't really use the same code/channel as the one in Apach2/PHP5/unixODBC/Virtuoso?

[fgiasson]

@HughWilliams, @iv-an-ru,

Since it was working on isql and not via PHP5/ODBC, I did some more testing. It turned out that the issue was with the odbc.default_cursortype default cursor type. By defaults it is 3 (which is supposed to be SQL_CURSOR_DYNAMIC) but needed to be 0 (which is supposed to be SQL_CURSOR_FORWARD_ONLY).

Setting the default cursor type to forward-only appears to be fixing this issue.

At least, that unixODBC issue is fixed. Thanks!

[fgiasson]

Re-opening since there is still the issue with the tests suites that @HughWilliams reproduced.

[kidehen]

On 1/26/15 3:40 PM, Frederick Giasson wrote:

Since it was working on isql and not via PHP5/ODBC, I did some more testing. It turned out that the issue was with the |odbc.default_cursortype| default cursor type. By defaults it is |3| (which is supposed to be |SQL_CURSOR_DYNAMIC|) but needed to be |0| (which is supposed to be |SQL_CURSOR_FORWARD_ONLY|).

This is an issue that's existed for eons, in regards to PHP5's ODBC layer [1].

As I've already stated, we can only do so much re. ODBC and ODBC Compliant application idiosyncrasies. A Dynamic Cursor != Static Cursor.

[1] http://webmail.dev411.com/t/php/php-db/019wv9xecj/problem-connecting-to-db2-via-openlink-mt

Regards,

Kingsley Idehen Founder & CEO OpenLink Software Company Web: http://www.openlinksw.com Personal Weblog 1: http://kidehen.blogspot.com Personal Weblog 2: http://www.openlinksw.com/blog/~kidehen Twitter Profile: https://twitter.com/kidehen Google+ Profile: https://plus.google.com/+KingsleyIdehen/about LinkedIn Profile: http://www.linkedin.com/in/kidehen Personal WebID: http://kingsley.idehen.net/dataspace/person/kidehen#this

[HughWilliams]

@fgiasson: Good to hear the php/unixODBC/Virtuoso issue has be resolved by changing the default cursor type.

Still need to check those test suite failures with development ...

[fgiasson]

@kidehen, yes I know, I just didn't remember and couldn't find the cause of this kind of errors. Now, this ticket may now hopefully helps others if they encounter that kind of issues.

[kidehen]

On 1/27/15 7:52 AM, Frederick Giasson wrote:

@kidehen https://github.com/kidehen, yes I know, I just didn't remember and couldn't find the cause of this kind of errors. Now, this ticket may now hopefully helps others if they encounter that kind of issues. The big headache we've had forever re., ODBC on Linux is the fact that we always end up as the instinctive source of problems when in reality all we've tried to offer since day one (many moons ago) is ability for ODBC to thrive outside Windows.

PHP's ODBC layer has been flawed for years. This simple fix hasn't been applied, and we continue to be burdened by it.

[1] http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/IODBCPHPHOWTO [2] http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/IODBCPHPHOWTO/iodbc-php5.diff -- patch which they simply haven't applied .

Regards,

Kingsley Idehen Founder & CEO OpenLink Software Company Web: http://www.openlinksw.com Personal Weblog 1: http://kidehen.blogspot.com Personal Weblog 2: http://www.openlinksw.com/blog/~kidehen Twitter Profile: https://twitter.com/kidehen Google+ Profile: https://plus.google.com/+KingsleyIdehen/about LinkedIn Profile: http://www.linkedin.com/in/kidehen Personal WebID: http://kingsley.idehen.net/dataspace/person/kidehen#this

[fgiasson]

@kidehen yeah I agree. That is why I wanted to have the possibility to use another communication channel than ODBC for OSF (so, having the option to use SPARQL HTTP). However, this is not necessarily the answer to everything since depending how the network is configured (and more particularly how many middleware that exists) it can add too much latency to the queries.

However, the problem I had with iODBC is the server maintenance overhead that it implies (recompiling of PHP5 and putting a hold on many DEB packages) was too big of a burden for many people. So this is why I am re-investigating the possibility to use unixODBC with Virtuoso for OSF 3.3.

[openlink]

Unfortunately PHP at one point decided to use a DYNAMIC cursor, which sounded cool, but about the only ODBC drivers at the time that could actually use it were OpenLink Drivers. The rest of the ODBC drivers just returned an error, so a STATIC cursor was used, which has lot less overhead. This allowed them to do simple scrolling to count records etc, not caring that most of the ODBC calls applications actually make, just read the dataset once so can use a FORWARD_ONLY cursor which has only minimal overhead.

As it was not allowed to change the existing API to add new arguments or functions, we did the next best thing and made settings that allow the programmer to change them either in the php.ini or inside their .php programs.

@fgiasson: If you want to use a forward only cursor, try adding the following to your script around the top before you make the connection or execute allocate/execute statement etc.

ini_set('odbc.default_cursortype', '0');

This will overrule whatever is in the php.ini and force a forward only cursor and will work with both iODBC and UnixODBC as far as i know.

Unless someone has broken it again, the patches as reported by @kidehen should still be in the PHP code base.

[fgiasson]

@openlink, great thanks for the background information. This is exactly that I did (use ini_set() before odbc_connect() in order to force forward only cursor to be used.

So far so good :)

[kidehen]

On 1/27/15 8:41 AM, Frederick Giasson wrote:

@kidehen https://github.com/kidehen yeah I agree. That is why I wanted to have the possibility to use another communication channel than ODBC for OSF (so, having the option to use SPARQL HTTP). However, this is not necessarily the answer to everything since depending how the network is configured (and more particularly how many middleware that exists) it can add too much latency to the queries.

However, the problem I had with iODBC is the server maintenance overhead that it implies (recompiling of PHP5 and putting a hold on many DEB packages) was too big of a burden for many people. So this is why I am re-investigating the possibility to use unixODBC with Virtuoso for OSF 3.3.

— Reply to this email directly or view it on GitHub https://github.com/openlink/virtuoso-opensource/issues/311#issuecomment-71648889.

I don't have issue with unixODBC or iODBC. I am certainly frustrated about the problems presented by this most artificial dichotomy that emerged from very tragic event. You may or may not know the history of this matter, so here is a brief update:

Richard Stallman wanted the notion of ODBC on Linux to be driven via a GPL as opposed to LGPL license. I refused point blank to that. During the flame war (deleted from the Web I've noticed) that ensued, others (lead by EasySoft) started unixODBC on the assumption that iODBC would ultimately end up being:

1. GPL
2. Some kind of OpenLink preferential ODBC implementation.

Both items above were inaccurate and misguided. ODBC on Linux was never going to be GPL or some OpenLink preferential deliverable.

You should be able to interchange between unixODBC and iODBC at the ODBC application layer, bar some implementation flaw in either.

[1] http://kidehen.typepad.com/kingsley_idehens_typepad/2004/01/mysql_41_client.html .

Regards,

Kingsley Idehen Founder & CEO OpenLink Software Company Web: http://www.openlinksw.com Personal Weblog 1: http://kidehen.blogspot.com Personal Weblog 2: http://www.openlinksw.com/blog/~kidehen Twitter Profile: https://twitter.com/kidehen Google+ Profile: https://plus.google.com/+KingsleyIdehen/about LinkedIn Profile: http://www.linkedin.com/in/kidehen Personal WebID: http://kingsley.idehen.net/dataspace/person/kidehen#this

[fgiasson]

@kidehen thanks for this additional information

[indeyets]

PHP's ODBC layer has been flawed for years. This simple fix hasn't been applied, and we continue to be burdened by it.

@kidehen linked patch is applied, as I can see (and the page you linked tells the same: "This patch has been incorporated into PHP around version 5.2.11 and above.")

am I missing something? because I can help you apply patches to PHP if you have any (I am committer)

[openlink]

On 29 January 2015 at 09:53, Alexey Zakhlestin notifications@github.com wrote:

@kidehen https://github.com/kidehen linked patch is applied, as I can see (and the page you linked tells the same: "This patch has been incorporated into PHP around version 5.2.11 and above."

am I missing something? because I can help you apply patches to PHP if you have any (I am committer)

Hi,

I checked by attempting to apply the patch to recent PHP sources, yesterday; it seemed it was committed already, hence the updated line in the document relegating the patch to history.

Regards,

~Tim

Tim Haynes Product Development Consultant OpenLink Software http://www.openlinksw.com/ http://twitter.com/openlink