query that works on jdbc does not work on linux->php->odbc->teiid...

[m.ardito]

Hi,

I have a relatively simple query like

"SELECT * FROM ag LEFT JOIN personale_interno ON ag.id_utente_inserimento = personale_interno.idanagrafica WHERE ag.id = 12"

which works perfectly on jdbc using SquirrelSQL, and also with "odbc query tool" (ODBC Query Tool download | SourceForge.net)

but using that query on a linux->php->odbc web server throws an error...

Attached are logs both from teiid and the pg odbc webserver client...

(teiid is 9beta1, while the setup is the same as reported here help needed troubleshooting slow odbc usage)

as usual, more details available if needed,

I can't guess what is happening now...

Marco

[rareddy]

Looks like ODBC Web Server is implicitly starting a local transaction (like autoCommit=false) then on Teiid server side to support for that, data source need be defined as XA source. So, see if in your PHP script you are turning off autocommit or define the XA data source in Teiid.

[m.ardito]

I don't set autoCommit=false, and in php it is "on by default", but I just tried in the php script:

using "odbc_autocommit($connection)" php dumps current setting, and in my script it prints "1" so, it's TRUE

anyway I tried also with

odbc_autocommit($connection,TRUE);

sadly, it doesn't change anything...

could there be a setting at odbc level (odbc.ini or odbcinst.ini)?

since on windows odbc works, I guess it should on linux odbc too...

could it be anything else than this autocommit setting?

instead/before starting the XA datasource journey, I would prefer to keep things simple, and make this odbc on webserver client behave like windows client do... if possible.

Marco

[rareddy]

Try using "iSQL" program that comes with "unixODBC" driver manager in Linux and issue the same query, that should tell you if it is the odbc driver or the PHP script that is turning on autoCommit. You can even turn on logging in odbc.ini file to debug and see if that might give a clue as to where this may be occurring.

Ramesh..

[m.ardito]

I will try isql and report here...

btw the attached zip already contains ODBC logs...

Marco

[m.ardito]

tried isql:

as in php, until I issue a SQL query which joins only tables in the same VDB model, it works,

as soon as I try to join tables that are in different VDB models (in the same VDB), it fails...

the only output I get from iscq is

"[ISQL]ERROR: Could not SQLExecute"

as in

SQL> SELECT ag.id,al.cognome from ag left join apifdbcorsiapif.allievi as al on  ag.id=al.cod_ditta WHERE ag.id=7496;

[ISQL]ERROR: Could not SQLExecute

while this works

SQL> SELECT ag.id,s.id from ag left join sedi as s on ag.id=s.id_unica where ag.id=7496;

+---------------------+---------------------+

| id                  | id                  |

+---------------------+---------------------+

| 7496                | 119306              |

| 7496                | 119307              |

| 7496                | 119308              |

+---------------------+---------------------+

SQLRowCount returns -1

3 rows fetched

it's a mistery (to me) why in windows odbc client the above always work, instead, btw...

Marco

[rareddy]

I tried the same scenario over two JDBC sources (posgres, mysql) did not see the same behavior with ODBC driver on Linux when I used the JOIN. I used Fedora 23 and latest postgresql ODBC driver I think it is 9.2.400?

Is there a store procedure in your join anywhere?

[m.ardito]

Thanks for your try and info. Did you try just with isql?

I will try other setups on other machines... unfortunately this is a bit slower, and it could take some days.

I double checked, no stored procedures on jdbc backend (mysql in this case) or anywhere else are involved.

I tried joining real mtsql tables (each on different databases=> models) and also a table and a view (each on different databases=> models)

It always fail in the same way, it appears.

But I also tried in suirrelsql this connection:

jdbc:teiid:apifdb_apimn@mm://x.y.z.k:31000;autoCommitTxn=OFF

jdbc:teiid:apifdb_apimn@mm://x.y.z.k:31000;autoCommitTxn=OFF;disableLocalTxn=true

and the query that joins different models works, in both cases, btw...

isn't this strange? could this mean that the problem is anywhere else?

Can you share your other versions/settings on that machine?

my webserver client (ubuntu) has

> odbc-postgresql Version: 1:09.00.0310-2

> unixodbc Version: 2.2.14p2-5ubuntu3

> odbcinst.ini

[PostgreSQL Unicode]

Description     = PostgreSQL ODBC driver (Unicode version)

Driver          = psqlodbcw.so

Setup           = libodbcpsqlS.so

Debug           = 1

CommLog         = 1

UsageCount      = 1

> odbc.ini (teiid dsn) <=== do you spot any strange setting that could cause that?

[teiid]

Description     = PostgreSQL Unicode

Driver          = /usr/lib/i386-linux-gnu/odbc/psqlodbcw.so

Database        = apifdb_apimn

Servername      = x.y.z.k

Username        = user

Password        = pass

Port            = 35432

Protocol        = 7.4-1

ServerType      = Postgres

ReadOnly        = No

ShowSystemTables= No

ShowOidColumn   = No

FakeOidIndex    = No

UseServerSidePrepare    =1

ByteaAsLongVarBinary    =1

UpdatableCursors=0

ConnSettings    =

Optimizer       =0

Ksqo            =0

Debug           =0

Fetch           = 10000

UseDeclareFetch =1

Trace = 1

TraceFile = PostgreSQL_test_trace.log

Debug = 1

DebugFile = PostgreSQL_test_debug.log

Thanks for any hint

Marco

[m.ardito]

Is there any way on teiid server side, to see clearly if the connected client is (or the issued query happens) "implicitly starting a local transaction"?

From the teiid logs maybe? Should I activate some particular trace/level?

This would help me to be more aware of what's really happening, or not, and when...

[edit]

Tried the same php script on windows/apache/php/odbc => teiid, and it works...

odbc driver there is 8.04.

[/edit]

Marco

[m.ardito]

Update: I installed a new virtual machine with basic ubuntu server 16.04 and standard LAMP stack.

then installed

- unixodbc (Version: 2.3.1-4.1)

- odbc-postgresql (Version: 1:09.03.0300-1)

and kept same unixodbc odbc.ini as above...

Description     = PostgreSQL Unicode

Driver          = /usr/lib/i386-linux-gnu/odbc/psqlodbcw.so

Database        = apifdb_apimn_gforms

Servername      = x.y.z.k

Username        = user

Password        = pass

Port            = 35432

Protocol        = 7.4-1

ServerType      = Postgres

ReadOnly        = No

ShowSystemTables= No

ShowOidColumn   = No

FakeOidIndex    = No

UseServerSidePrepare    =1

ByteaAsLongVarBinary    =1

UpdatableCursors=0

ConnSettings    =

Optimizer       =0

Ksqo            =0

Debug           =0

Fetch           = 10000

UseDeclareFetch =1

Trace = 1

TraceFile = PostgreSQL_test_trace.log

Debug = 1

DebugFile = PostgreSQL_test_debug.log

Then copied the same php script and.... doesn't work.

As before, joins local to the model work, but it doesn't work when join in inter-model...

If you still think it's useful, I could try fedora, even if it's not my battleground, but imho, it's something else...

anyway, I would love to know your install/conig details in order to be able to replicate exactly your setup on fedora....

Marco

[rareddy]

unixODBC 2.3.4

Postgesql 09.05.0200

$cat ~/.odbc.ini

[loopy]
  #Driver=/usr/lib64/libodbcpsqlS.so
  Driver=/usr/lib64/psqlodbc.so
  Description=PostgreSQL Data Source
  Servername=127.0.0.1
  Port=35432
  Protocol=7.4-1
  UserName=user
  Password=redhat1!
  Database=test
  ReadOnly=no
  ServerType=Postgres
  ConnSettings =
  UseServerSidePrepare=0
  ByteaAsLongVarBinary=1
  Optimizer=0
  Ksqo=0
  Debug=0
  Fetch=10000
  Trace=Yes
  Tracefile=sql.log
  Debug=1
  Debug=debug.log
  # enable below when dealing large resultsets to enable cursoring(note prepared stmts will not work)
  UseDeclareFetch=0

As per the logging you can open up "org.teiid.ODBC" in TRACE mode in server's standalone-teiid.xml file.

As per PHP script, the implementations of PHP and/or driver could be different, and we already established that it was the driver as iSQL also exhibited the same symptom. 

Ramesh..

[m.ardito]

Found!!! it was

UseDeclareFetch=1

just setting  UseDeclareFetch=0 as in your .ini makes both servers work... it seems, I'll test more and report here....

Thanks,

Marco

[shawkins]

Thanks Marco.  Hopefully the issue was something that was already addressed with the recent changes, but if not we'll get it addressed in 9.0/8.13.x.