search

aafemt / magpie

Public part of Magpie project
0 stars 0 forks source link

Connection via Magpie and ODBC driver to Firebird 6 server hangs #2

Open vasiliy-yashkov opened 3 hours ago

vasiliy-yashkov commented 3 hours ago

OS: Debian GNU/Linux trixie/sid x86_64 Kernel: 6.11.10-x64v3-xanmod1 GCC: gcc (Debian 14.2.0-8) 14.2.0.

Firebird server is built with commands: ./autogen.sh --enable-binreloc --prefix=/home/vasiliy/dev/github/firebird/gen/Release/firebird/ && make all -j7

Magpie library added to plugins directory, plugin added to server providers configuration: Providers = Engine14,Magpie,Remote,Loopback

ODBC driver v3-0-0-release was installed for Firebird.

/etc/odbcinst.ini:

[OdbcFb]
Description=InterBase/Firebird ODBC Driver
Driver=/usr/lib64/libOdbcFb.so
Setup=
QuotedIdentifier=
Threading=1
FileUsage=1
CPTimeout=
CPReuse=

/etc/odbc.ini:

[FBMODBCTEST]
Description=Firebird
Driver=OdbcFb
Dbname=127.0.0.1:/tmp/test.fdb
User=SYSDBA
Password=masterkey
Role=
CharacterSet=
ReadOnly=
NoWait=

Executing the following script hangs:

> isql localhost:employee  -user SYSDBA -password masterkey
Database: localhost:employee, User: SYSDBA
set term ^ ;
execute block returns (id bigint) as
begin
 for execute statement
  'select current_connection from rdb$database'
  on external 'odbc://FBMODBCTEST' as user 'SYSDBA' password 'masterkey'
  into :id DO
   suspend;
end^

The debug version of the Magpie library prints logs:

magpie_29521.log ``` ******* Log 1.0.3.0 started at Fri Oct 18 12:33:51 2024 0x7e387f2e1300 ODBCFactory::createPlugin(0x7e3890cd3c78) Unicode charset is UTF16LE 0x7e387f2e1300 Default config = 0x7e3890cd4858 License string "JwtS1UJzq6" 0x60e860864aa0 ODBCProvider created 0x60e860864aa0 ODBCProvider::attachDatabase("localhost:employee") 0x60e860864aa0 ODBCProvider::release 0x60e860864aa0 ODBCProvider destructed (counter 0) License expiration time: Tue Oct 22 18:51:53 2024 owner Public, status 129 ******* Log ended at Fri Oct 18 12:34:51 2024 ```
magpie_29517.log ``` ******* Log 1.0.3.0 started at Fri Oct 18 12:34:06 2024 0x7ad6244e1300 ODBCFactory::createPlugin(0x7ad638c69278) Unicode charset is UTF16LE 0x7ad6244e1300 Default config = 0x7ad638c69b38 License string "JwtS1UJzq6" 0x7ad618012680 ODBCProvider created 0x7ad618012680 ODBCProvider::setDbCryptCallback(...) 0x7ad618012680 ODBCProvider::attachDatabase("odbc://test") DPB version 2: isc_dpb_auth_block Auth block 0, length 99 AUTH_TYPE: Seen AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/examples/empbuild/employee.fdb Auth block 1, length 82 AUTH_TYPE: Seen AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/security6.fdb Auth block 2, length 104 AUTH_TYPE: USER AUTH_NAME: SYSDBA AUTH_PLUGIN: Srp256 AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/security6.fdb isc_dpb_lc_ctype NONE found NONE, id 0, bytes per char 1, code page 0, iconv name isc_dpb_utf8_filename isc_dpb_session_time_zone Europe/Moscow isc_dpb_ext_call_depth 1 isc_dpb_ext_call_depth 1 isc_dpb_user_name SYSDBA isc_dpb_password masterkey Create environment handle... License expiration time: Tue Oct 22 18:51:53 2024 owner Public, status 129 Check for license... 0x7ad618012680 Attaching to test as user "SYSDBA" password "masterkey" Error line 1 (len 168): [unixODBC][Driver Manager]Data source name not found and no default driver specified SQL state: IM002 attachDatabase caught exception std::exception 0x7ad618012680 ODBCProvider::release 0x7ad618012680 ODBCProvider destructed (counter 0) 0x7ad6244e1300 ODBCFactory::createPlugin(0x7ad638c69278) Unicode charset is UTF16LE 0x7ad6244e1300 Default config = 0x7ad638c69b38 License string "JwtS1UJzq6" 0x7ad618012680 ODBCProvider created 0x7ad618012680 ODBCProvider::setDbCryptCallback(...) 0x7ad618012680 ODBCProvider::attachDatabase("odbc://FBMODBCTEST") DPB version 2: isc_dpb_auth_block Auth block 0, length 99 AUTH_TYPE: Seen AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/examples/empbuild/employee.fdb Auth block 1, length 82 AUTH_TYPE: Seen AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/security6.fdb Auth block 2, length 104 AUTH_TYPE: USER AUTH_NAME: SYSDBA AUTH_PLUGIN: Srp256 AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/security6.fdb isc_dpb_lc_ctype NONE found NONE, id 0, bytes per char 1, code page 0, iconv name isc_dpb_utf8_filename isc_dpb_session_time_zone Europe/Moscow isc_dpb_ext_call_depth 1 isc_dpb_ext_call_depth 1 isc_dpb_user_name SYSDBA isc_dpb_password masterkey Create environment handle... Check for license... 0x7ad618012680 Attaching to FBMODBCTEST as user "SYSDBA" password "masterkey" ```

If using the ODBC driver from here, the script execution fails with an error:

> isql localhost:employee  -user SYSDBA -password masterkey
Database: localhost:employee, User: SYSDBA
SQL> set term ^ ;
SQL> 
SQL> execute block returns (id bigint) as
CON> begin
CON>  for execute statement
CON>   'select current_connection from rdb$database'
CON>   on external 'odbc://FBMODBCTEST' as user 'SYSDBA' password 'masterkey'
CON>   into :id DO
CON>    suspend;
CON> end^

                   ID 
===================== 
Statement failed, SQLSTATE = 42000
Execute statement error at attach :
335544421 : connection rejected by remote interface
126211509003568 : [unixODBC][
Data source : Firebird::odbc://FBMODBCTEST
-At block line: 3, col: 2

Magpie logs in this case:

magpie_31596.log ``` ******* Log 1.0.3.0 started at Fri Oct 18 13:26:56 2024 0x7a5ba0ee1300 ODBCFactory::createPlugin(0x7a5bb2953c78) Unicode charset is UTF16LE 0x7a5ba0ee1300 Default config = 0x7a5bb2954858 License string "JwtS1UJzq6" 0x607d57519aa0 ODBCProvider created 0x607d57519aa0 ODBCProvider::attachDatabase("localhost:employee") 0x607d57519aa0 ODBCProvider::release 0x607d57519aa0 ODBCProvider destructed (counter 0) License expiration time: Tue Oct 22 18:51:53 2024 owner Public, status 129 ******* Log ended at Fri Oct 18 13:27:04 2024 ```
magpie_31592.log ``` ******* Log 1.0.3.0 started at Fri Oct 18 13:27:02 2024 0x72c9e1ae1300 ODBCFactory::createPlugin(0x72c9fc51fa18) Unicode charset is UTF16LE 0x72c9e1ae1300 Default config = 0x72c9fc51f4a8 License string "JwtS1UJzq6" 0x72c9dc012680 ODBCProvider created 0x72c9dc012680 ODBCProvider::setDbCryptCallback(...) 0x72c9dc012680 ODBCProvider::attachDatabase("odbc://FBMODBCTEST") DPB version 2: isc_dpb_auth_block Auth block 0, length 99 AUTH_TYPE: Seen AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/examples/empbuild/employee.fdb Auth block 1, length 82 AUTH_TYPE: Seen AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/security6.fdb Auth block 2, length 104 AUTH_TYPE: USER AUTH_NAME: SYSDBA AUTH_PLUGIN: Srp256 AUTH_SECURE_DB: /home/vasiliy/dev/github/firebird/gen/Release/firebird/security6.fdb isc_dpb_lc_ctype NONE found NONE, id 0, bytes per char 1, code page 0, iconv name isc_dpb_utf8_filename isc_dpb_session_time_zone Europe/Moscow isc_dpb_ext_call_depth 1 isc_dpb_ext_call_depth 1 isc_dpb_user_name SYSDBA isc_dpb_password masterkey Create environment handle... License expiration time: Tue Oct 22 18:51:53 2024 owner Public, status 129 Check for license... 0x72c9dc012680 Attaching to FBMODBCTEST as user "SYSDBA" password "masterkey" Error line 1 (len 22): [unixODBC][ SQL state: 0 attachDatabase caught exception std::exception 0x72c9dc012680 ODBCProvider::release 0x72c9dc012680 ODBCProvider destructed (counter 0) ******* Log ended at Fri Oct 18 13:27:05 2024 ```

The server log in both cases contains errors:

Authentication error
Dynamic SQL Error
SQL error code = -303
Malformed string
aafemt commented 3 hours ago

Did you check using UnixODBC's uisql that problem is not in the ODBC driver?

BTW, the driver in my repository is for Windows only.

vasiliy-yashkov commented 3 hours ago

Did you check using UnixODBC's uisql that problem is not in the ODBC driver?

Sure:

isql -v FBMODBCTEST
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help RDB$DATABASE ``` +--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------+--------------------------------+------------+--------------+---------------+---------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------------+------------------+-----------------+------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE| TYPE_NAME | COLUMN_SIZE| BUFFER_LENGTH| DECIMAL_DIGITS| NUM_PREC_RADIX| NULLABLE| REMARKS | COLUMN_DEF | SQL_DATA_TYPE| SQL_DATETIME_SUB| CHAR_OCTET_LENGTH| ORDINAL_POSITION| IS_NULLABLE| +--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------+--------------------------------+------------+--------------+---------------+---------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------------+------------------+-----------------+------------+ | | | RDB$DATABASE | RDB$DESCRIPTION | -1 | BLOB SUB_TYPE TEXT | 2147483647 | 2147483647 | | | 1 | | NULL | -1 | | | 1 | YES | | | | RDB$DATABASE | RDB$RELATION_ID | 5 | SMALLINT | 5 | 2 | 0 | 10 | 1 | | NULL | 5 | | | 2 | YES | | | | RDB$DATABASE | RDB$SECURITY_CLASS | -8 | CHAR | 63 | 252 | | | 1 | | NULL | -8 | | 252 | 3 | YES | | | | RDB$DATABASE | RDB$CHARACTER_SET_NAME | -8 | CHAR | 63 | 252 | | | 1 | | NULL | -8 | | 252 | 4 | YES | | | | RDB$DATABASE | RDB$LINGER | 4 | INTEGER | 10 | 4 | 0 | 10 | 1 | | NULL | 4 | | | 5 | YES | | | | RDB$DATABASE | RDB$SQL_SECURITY | -7 | BOOLEAN | 1 | 1 | 0 | 10 | 1 | | NULL | -7 | | | 6 | YES | +--------------------------------+--------------------------------+--------------------------------+--------------------------------+----------+--------------------------------+------------+--------------+---------------+---------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-----------------+------------------+-----------------+------------+ SQLRowCount returns 6 6 rows fetched ```
aafemt commented 3 hours ago

This is isql, I asked about uisql. These two are different utilities.

vasiliy-yashkov commented 2 hours ago

This is isql, I asked about uisql. These two are different utilities.

I can't find any mention of uisql. Only isql or iusql:

>iusql -v FBMODBCTEST 
[
[
[ISQL]ERROR: Could not SQLDriverConnect
aafemt commented 2 hours ago

Oops, my bad, it is iusql indeed. This failure shows that Firebird ODBC driver doesn't support Unicode API required for Magpie (while transliteration is known to be broken in UnixODBC Manager Library).

vasiliy-yashkov commented 2 hours ago

Thanks for clarification :)