search

ClickHouse / clickhouse-odbc

ODBC driver for ClickHouse
https://clickhouse.tech
Apache License 2.0
248 stars 88 forks source link

Oracle: Драйвер не работает из Oracle через GD4ODBC #138

Open bazhunov opened 5 years ago

bazhunov commented 5 years ago

При попытке выполнить SELECT из Oracle через настроенный dblink к ClickHouse, возвращается ошибка.

SQL>  select "date_event" from "connect.test_data"@CLICK;
 select "date_event" from "connect.test_data"@CLICK

ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.0.5)(PORT=1522))(CONNECT_DA
TA=(SID=CLICKHOUSE)))
ORA-02063: preceding line from CLICK

При этом в ISQL все выполняется корректно.

 $ echo  select "date_event" from "connect.test_data" | isql CLICKHOUSE data *****
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +--------------------+
| date_event         |
+--------------------+
| 2018-11-13 11:29:28|
| 2018-11-13 11:29:28|
| 2018-11-13 11:29:28|
...

В логах oracle ( $ORACLE_HOME/hs/log/CLICKHOUSE_agt_NNNN.trc) видим следующую ошибку:

...
Entered hgopoer at 2018/11/27-11:03:54
hgopoer, line 231: got native error 1 and sqlstate 07009; message follows...
Column number is out of range {07009,NativeErr = 1}
hgoulcp, line 1192: calling SQLBindCol got sqlstate 07009
Exiting hgoulcp, rc=28500 at 2018/11/27-11:03:54 with error ptr FILE:hgoulcp.c LINE:1192 FUNCTION:hgoulcp() ID:Bindcol: Type Name
...

----------------------------------------------------------------------------------- Libs:

DriverName: CLICKHOUSEODBC.DLL, 
DriverVer: 1.0.0.20181017
$ objdump -f /usr/lib/x86_64-linux-gnu/odbc/libclickhouseodbc.so

/usr/lib/x86_64-linux-gnu/odbc/libclickhouseodbc.so:     file format elf64-x86-64
architecture: i386:x86-64, flags 0x00000150:

$ isql --version
unixODBC 2.3.7

OS: 

DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.5 LTS"
$ uname -a
Linux c659382d024c 4.4.0-131-generic #157-Ubuntu SMP Thu Jul 12 15:51:36 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Orcale: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

$ORACLE_HOME/hs/admin/initCLICKHOUSE.ora:

HS_FDS_CONNECT_INFO = CLICKHOUSE
HS_FDS_TRACE_LEVEL= 255
HS_FDS_SHAREABLE_NAME = /usr/lib/x86_64-linux-gnu/libodbc.so
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_FDS_SQLLEN_INTERPRETATION = 64
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
set ODBCINI=/usr/local/etc/odbc.ini

/usr/local/etc/odbc.ini:

Description = ClickHouse driver
url = http://<IP>:8123/query?database=connect
uid = data
password = ****

$ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
...
    (SID_DESC =
      (SID_NAME = CLICKHOUSE)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = dg4odbc)
      (ENVS="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/11.2.0/xe/hs/lib:/usr/lib/x86_64-linux-gnu:/u01/app/oracle/product/11.2.0/xe/lib")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
...
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.5)(PORT = 1522))
    )
  )

DEFAULT_SERVICE_LISTENER = (central)

$ORACLE_HOME/network/admin/tnsnames.ora:

...
CLICKHOUSE=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.5)(PORT = 1522))
      (CONNECT_DATA =
        (SID = CLICKHOUSE))
      (HS = OK)
  )
...
proller commented 5 years ago

А если в odbc.ini написать trace=1
tracefile=/tmp/chlickhouse-odbc.log

то что в файле будет ?

bazhunov commented 5 years ago

Я это делал и раньше, просто забыл выложить Вот кусок касающийся ошибки:

[ODBC][188299][1543305834.769959][SQLAllocHandle.c][540]
                Entry:
                        Handle Type = 3
                        Input Handle = 0x1765270
[ODBC][188299][1543305834.770084][SQLAllocHandle.c][1085]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x17bc430
[ODBC][188299][1543305834.770127][SQLBindCol.c][236]
                Entry:
                        Statement = 0x17bc430
                        Column Number = 1
                        Target Type = -8 SQL_WCHAR
                        Target Value = 0x7ffe40986b10
                        Buffer Length = 120
                        StrLen Or Ind = 0x7ffe40987058
[ODBC][188299][1543305834.770251][SQLBindCol.c][344]
                Exit:[SQL_ERROR]
[ODBC][188299][1543305834.770340][SQLGetDiagRecW.c][535]
                Entry:
                        Statement = 0x17bc430
                        Rec Number = 1
                        SQLState = 0x7ffe40986810
                        Native = 0x7ffe40986604
                        Message Text = 0x17b8d90
                        Buffer Length = 510
                        Text Len Ptr = 0x7ffe40986860
                DIAG [07009] Column number is out of range

[ODBC][188299][1543305834.770426][SQLGetDiagRecW.c][596]
                Exit:[SQL_SUCCESS]
                        SQLState = [07009]
                        Native = 0x7ffe40986604 -> 1
                        Message Text = [Column number is out of range]
[ODBC][188299][1543305834.770484][SQLGetDiagRecW.c][535]
                Entry:
                        Statement = 0x17bc430
                        Rec Number = 2
                        SQLState = 0x7ffe40986810
                        Native = 0x7ffe40986604
                        Message Text = 0x17b8d90
                        Buffer Length = 510
                        Text Len Ptr = 0x7ffe40986860
[ODBC][188299][1543305834.770518][SQLGetDiagRecW.c][596]
                Exit:[SQL_NO_DATA]
arkanovas commented 5 years ago

Аналогичная фигня. Только последних двух строчек нет.

При попытке выполнить SELECT из Oracle через настроенный dblink к ClickHouse, возвращается ошибка. При этом в ISQL все выполняется корректно. В логах oracle ( $ORACLE_HOME/hs/log/CLICKHOUSE_agt_NNNN.trc) видим следующую ошибку:

Oracle Corporation --- THURSDAY  JUN 06 2019 17:29:51.564
Heterogeneous Agent Release.
11.2.0.3.0.
Oracle Corporation --- THURSDAY  JUN 06 2019 17:29:51.563
    Version 11.2.0.3.0
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
 using arkanovas as default value for "HS_FDS_DEFAULT_OWNER"
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
 HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
hgoulcp, line 1167: calling SQLBindCol got sqlstate 07009
arkanovas commented 5 years ago

в логах самого одбс более понятно - 

1559826515891757 [140417600124736] /home/ias/clickhouse-odbc/driver/odbc.cpp:470 SQLBindCol
1559826515891839 [140417600124736] /home/ias/clickhouse-odbc/driver/utils.h:37 Exception: Column number 1 is out of range: 0

При этом не зависит от количества столбцов. Во вложении полные логи - сначала делаю успешный запрос через isql, потом стартует драйвер для оракла и там уже вываливаемся с исключением. Логи кликхауса.txt

Видимо Оракл предварительно пытается сделать какие-то дополнительные запросы касательно структуры базы и у него это не получается, и сам запрос тоже в итоге не проходит.

EgorDS commented 3 years ago

У меня такая же ошибка: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523))(CONNECT_DATA=(SID=ch)))

Есть способ ее обойти ?

arkanovas commented 3 years ago

Есть способ ее обойти ? К сожалению больше не занимались этим вопросом в виду того, что Оракл у себя больше не развиваем.

AlexGruPerm commented 10 months ago

Добрый день. Тоже столкнулся с этой ошибкой. Так и не работает. clickhouse-odbc-1.2.1 + Oracle Database 11g Enterprise Edition Release 11.2.0.4.0