Configuring a Data Source in the Linux OS

Procedure

1. Install unixODBC. (By default, the unixODBC source code package has been obtained during environment preparation.) It does not matter if unixODBC of another version has been installed.
   For example, to install unixODBC-2.3.7, run the commands below on the client.

   tar zxvf unixODBC-2.3.7.tar.gz
   cd unixODBC-2.3.7
   
   ./configure --enable-gui=no # To perform compilation on an Arm server, add the configure parameter --build=aarch64-unknown-linux-gnu.
   make
   # The installation may require root permissions.
   make install

   

   • Currently, unixODBC-2.2.1 is not supported.
   • By default, it is installed in the /usr/local directory. The data source file is generated in the /usr/local/etc directory, and the library file is generated in the /usr/local/lib directory.
   • You can compile unixODBC with the --enable-fastvalidate=yes option to achieve higher performance. However, this option may cause an application that passes an invalid handle to the ODBC API to fail instead of returning an SQL_INVALID_HANDLE error.

2. Replace the GaussDB client driver.

   Decompress GaussDB-Kernel_Database version number_OS version number_64bit_Odbc.tar.gz. After the decompression, the lib and odbc folders are generated. The odbc folder contains another lib folder. Copy all dynamic libraries in the /lib and /odbc/lib folders to the /usr/local/lib directory.

3. Configure a data source.
   1. Configure the ODBC driver file.

      Add the following content to the /usr/local/etc/odbcinst.ini file:

      [GaussMPP]
      Driver64=/usr/local/lib/gsqlodbcw.so
      setup=/usr/local/lib/gsqlodbcw.so

      For descriptions of the parameters in the odbcinst.ini file, see Table 1.

      Table 1 odbcinst.ini configuration parameters

      Parameter	Description	Example
      [DriverName]	Driver name, corresponding to Driver in DSN.	[GaussMPP]
      Driver64	Path of the dynamic driver library.	Driver64=/usr/local/lib/gsqlodbcw.so
      setup	Driver installation path, which is the same as the dynamic library path in Driver64.	setup=/usr/local/lib/gsqlodbcw.so

   2. Configure the data source file.

      Add the following content to the /usr/local/etc/odbc.ini file:

      [gaussdb]
      Driver=GaussMPP
      Servername=127.0.0.1 # Database server IP address
      Database=db1  # Database name
      Username=omm  # Database username
      Password=  # Database user password
      Port=8000  # Database listening port
      Sslmode = allow

      For descriptions of the parameters in the odbc.ini file, see Table 2.

      Table 2 odbc.ini configuration parameters

      Parameter	Description	Example
      [DSN]	Data source name.	[gaussdb]
      Driver	Driver name, corresponding to DriverName in odbcinst.ini.	Driver=GaussMPP
      Servername	Server IP address. Multiple IP addresses can be configured. Both IPv4 and IPv6 are supported.	Servername=127.0.0.1
      Database	Name of the database to connect.	Database=db1
      Username	Database username.	Username = omm
      Password	Database user password. NOTE: After a user establishes a connection, the ODBC driver automatically clears their password stored in memory. However, if this parameter is configured, unixODBC will cache data source files, which may cause the password to be stored in the memory for a long time. When you connect to an application, you are advised to send your password through an API instead of writing it in a data source configuration file. After the connection has been established, immediately clear the memory segment where your password is stored. NOTICE: The password in the configuration file must comply with the following HTTP rules: Characters must comply with the URL encoding specifications. For example, the exclamation mark (!) must be written as %21, and the percent sign (%) must be written as %25. Therefore, pay attention to the characters. A plus sign (+) will be replaced by a space.	Password=********
      Port	Port number of the server.	Port = 8000
      Sslmode	Specifies whether to enable the SSL connection. NOTE: Table 3 Sslmode options describes the valid values of Sslmode.	Sslmode = allow
      Debug	Specifies whether to enable the debugging mode. Value range: 0 to INT_MAX. The value 0 means disabled. If this parameter is set to a value greater than 0, the mylog file of the gsqlODBC driver will be printed. The directory generated for storing logs is /tmp/. The default value is 0.	Debug = 1
      UseServerSidePrepare	Specifies whether to enable the extended query protocol for the database. Value range: 0 and 1 The value 0 means disabled. The value 1 means enabled. The default value is 1.	UseServerSidePrepare=1
      UseBatchProtocol	Specifies whether to enable the batch query protocol. If it is enabled, DML performance can be improved. Value range: 0 and 1 If this parameter is set to 0, the batch query protocol is disabled (mainly for communication with earlier database versions). If this parameter is set to 1 and support_batch_bind is set to on, the batch query protocol is enabled. The default value is 1.	UseBatchProtocol=1
      ForExtensionConnector	Specifies whether the savepoint is sent. Value range: 0 and 1 If this parameter is set to 0, the savepoint is sent. If this parameter is set to 1, the savepoint is not sent. The default value is 1.	ForExtensionConnector=1
      ConnectionExtraInfo	Specifies whether to display the driver deployment path and process owner in the connection_info GUC parameter. Value range: 0 and 1 If this parameter is set to 0, the information is not displayed. If this parameter is set to 1, the information is displayed. The default value is 0. NOTE: If this parameter is set to 1, the ODBC driver reports the driver deployment path and process owner to the database and displays the information in the GUC parameter connection_info. In this case, you can query the information from PG_STAT_ACTIVITY.	ConnectionExtraInfo=1
      BoolsAsChar	Specifies whether to process Boolean values as characters. Value range: 0 and 1 If this parameter is set to 0, the Boolean values are mapped to the SQL_BIT type. If this parameter is set to 1, the Boolean values are mapped to the SQL_CHAR type. The default value is 1.	BoolsAsChar = 1
      RowVersioning	Specifies whether to allow the application to check whether a row of data is modified by another user when the row of data is updated. Value range: 0 and 1 If this parameter is set to 0, application detection is not allowed. If this parameter is set to 1, application detection is allowed. The default value is 0.	RowVersioning=1
      ShowSystemTables	Specifies whether to regard the system catalog as a common SQL table by default. Value range: 0 and 1 If this parameter is set to 0, the driver does not regard the system catalog as a common SQL table by default. If this parameter is set to 1, the driver regards the system catalog as a common SQL table by default. The default value is 0.	ShowSystemTables=1
      MaxCacheQueries	Controls the number of prepared statements cached for each connection. Value range: 0 to 4096 The default value is 0. NOTE: If this parameter is set to 0, the prepared statement cache pool is disabled on the client. If this parameter is set to a value greater than 4096, the value 4096 is used. If the number of executed statements exceeds the upper limit specified by MaxCacheQueries, the least recently used statements are eliminated.	MaxCacheQueries=128
      MaxCacheSizeMiB	Controls the total size of prepared statements cached for each connection. This parameter takes effect when the value of MaxCacheQueries is greater than 0. Value range: 0 to 4096 The default value is 1. NOTE: If the total size of cached statements is greater than the value of MaxCacheSizeMiB, the least recently used statements are eliminated. If this parameter is set to a value greater than 4096, the value 4096 is used. The unit is MB.	MaxCacheSizeMiB=10
      TcpUserTimeout	Specifies the maximum duration for which transmitted data can remain unacknowledged before the TCP connection is forcibly closed on an OS that supports the TCP_USER_TIMEOUT socket option. Value range: 0 to INT_MAX. The default value is 0. NOTE: 0 indicates that the default value is used. Ignore this parameter for UDS connections. The unit is millisecond.	TcpUserTimeout=5000
      TargetServerType	Specifies the type of the host to be connected. The connection is successful only when the host type is the same as the configured value. The setting principles are as follows: primary (default value): Only the primary node in the primary/standby system can be connected. standby: Only the standby node in the primary/standby system can be connected. prefer-standby: The system first attempts to connect to the standby node in the primary/standby system. If all standby nodes in the hosts list fail to be connected, try the any mode. read-write: Only hosts that are both readable and writeable can be connected. read-only: Only read-only hosts can be connected. any: All types of hosts can be connected. cluster-primary: Only the primary node of the primary database can be connected. cluster-standby: Only the standby node of the primary database can be connected. cluster-mainnode: Only the primary database or the main standby node (primary node for DR) can be connected. NOTE: The cluster-primary, cluster-standby, and cluster-mainnode options support only streaming DR.	TargetServerType=primary
      KeepaliveTime	The number of seconds of inactivity after which TCP should send a keepalive message to the server. Ignore this parameter for UDS connections or if keepalive is disabled. Value range: 0 to INT_MAX. The default value is 0.	KeepaliveTime=2
      KeepaliveInterval	The number of seconds after which a TCP keepalive message that is not acknowledged by the server should be retransmitted. Ignore this parameter for UDS connections or if keepalive is disabled. Value range: 0 to INT_MAX. The default value is 0.	KeepaliveInterval=2
      KeepaliveCount	Controls the number of times that keepalive messages are sent through TCP. Ignore this parameter for UDS connections or if keepalive is disabled. Value range: 0 to INT_MAX. The default value is 0.	KeepaliveCount=2
      SocketTimeout	Specifies a socket read/write timeout interval after a connection is successfully established between a client and a server. The unit is second. The default value is 0. NOTE: This parameter includes the timeout interval for executing database statements. If this parameter is set to a small value, the statement execution may time out. The default value is 0. Set this parameter based on the actual situation. You are advised to set this parameter to a non-zero value.	SocketTimeout=5
      SocketTimeoutInConnect	Controls the socket read/write timeout interval during the connection establishment between a client and a server after a TCP three-way handshake is successful. The unit is second. The default value is 5. NOTE: Different from SocketTimeout, this parameter may block the connection between a client and a server after a three-way handshake is successful. Therefore, you need to set the socket read/write timeout interval to a value that does not affect the statement execution timeout.	SocketTimeoutInConnect=5
      CancelTimeout	Specifies the timeout interval for an application to send a cancel message. The unit is second. The default value is 0.	CancelTimeout=5
      TcpSYNRetries	Controls the maximum number of SYN retransmissions during the TCP three-way handshake. If the connection fails to be established after the number of SYN retransmissions exceeds the threshold, an error is reported. The default value is 0. NOTE: Specifies the number of retransmissions due to SYN transmission failures during the three-way handshake when the client establishes a connection on an OS that supports the TCP_SYNCNT socket option. The value 0 indicates that the default value is used. Ignore this parameter for UDS connections.	TcpSYNRetries=3
      TextAsLongVarchar	If TextAsLongVarchar is set to 1, the text type on the kernel side is mapped to the SQL_LONGVARCHAR type on the driver side. If TextAsLongVarchar is set to 0, the text type on the kernel side is mapped to the SQL_VARCHAR type on the driver side. The default value is 1.	TextAsLongVarchar=1
      MaxLongVarcharSize	Maximum length of the SQL_LONGVARCHAR type on the driver side. The default value is 8190.	MaxLongVarcharSize=8190
      MaxVarcharSize	Maximum length of the SQL_VARCHAR type on the driver side. The default value is 255.	MaxVarcharSize=255
      EnableUpdateCursorCache	Specifies whether to enable the updateable cursor cache capability. This capability is enabled by default. Only the A-compatible database is supported.	EnableUpdateCursorCache=1
      EnableHostCache	Specifies whether to enable the node cache function. 0 indicates disabled and 1 (default) indicates enabled.	EnableHostCache=1

      Table 3 Sslmode options

      Sslmode	Enable SSL Encryption	Description
      disable	No	SSL connection is not enabled.
      allow	Possible	If the database server requires SSL connection, SSL connection can be enabled. However, authenticity of the database server will not be verified.
      prefer	Possible	If the database supports SSL connection, SSL connection is preferred. However, authenticity of the database server will not be verified.
      require	Yes	SSL connection is required, but only data is encrypted. Authenticity of the database server will not be verified.
      verify-ca	Yes	SSL connection is required and whether the database has a trusted certificate will be verified.
      verify-full	Yes	SSL connection is required. In addition to the check scope specified by verify-ca, the system checks whether the name of the host where the database resides is the same as that in the certificate. GaussDB does not support this mode.

      

      When establishing connections to the GaussDB server using ODBC, you can enable SSL to encrypt client and server communications. To enable SSL, you must have the server certificate, client certificate, and private key files. For details on how to obtain these files, see related documents and commands of OpenSSL.

4. Configure the environment variables on the client.

   vim ~/.bashrc

   Add the following information to the configuration file:

   export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH
   export ODBCSYSINI=/usr/local/etc
   export ODBCINI=/usr/local/etc/odbc.ini

5. Run the following command to validate the addition:

   source ~/.bashrc

6. Test the connection.

   After the installation, the generated binary file is stored in the /usr/bin directory. You can run the isql -v gaussdb command (gaussdb is the data source name).

   • If the following information is displayed, the configuration is correct and the connection succeeds:

     +---------------------------------------+
     | Connected!                            |
     |                                       |
     | sql-statement                         |
     | help [tablename]                      |
     | quit                                  |
     |                                       |
     +---------------------------------------+

   • If error information is displayed, the configuration is incorrect. Check whether the preceding configuration steps are correctly performed.
   

   When the ODBC is used to connect to a database, the kernel parameters are set as follows:

   SET extra_float_digits = 2;
   SET DateStyle = 'ISO';

   These parameters may cause the ODBC and gsql clients to display inconsistent data, for example, date data display mode and floating-point precision representation. If the result is not as expected, you are advised to explicitly set these parameters in the ODBC application code.

   In M-compatible databases, the default value of extra_float_digits is 0.

FAQ

• [UnixODBC][Driver Manager]Can't open lib 'xxx/xxx/gsqlodbcw.so':file not found.

  Possible causes:

  • The path configured in the odbcinst.ini file is incorrect.

    Run ls to check the path in the error information, ensuring that the gsqlodbcw.so file exists and you have execution permissions on it.

  • The dependent library of gsqlodbcw.so does not exist or is not in system environment variables.

    Run the ldd command to check the path in the error information. If the unixODBC library such as libodbc.so.1 is missing, reconfigure unixODBC according to the procedure, ensure that the lib directory in the installation path is added to LD_LIBRARY_PATH. If other libraries do not exist, add the lib directory under the ODBC driver package to LD_LIBRARY_PATH. If other standard libraries are missing, install them.

• [UnixODBC]connect to server failed: no such file or directory

  Possible causes:

  • An incorrect or unreachable database IP address or port was configured.

    Check the Servername and Port configuration items in data sources.

  • Server monitoring is improper.

    If Servername and Port are correctly configured, ensure the proper NIC and port are monitored by following the database server configurations in the procedure in this section.

  • Firewall and network gatekeeper settings are improper.

    Check firewall settings, and ensure that the database communication port is trusted.

    Check to ensure that network gatekeeper settings are proper (if any).

• [unixODBC]The password-stored method is not supported.

  Possible causes:

  The sslmode configuration item is not configured in the data sources.

  Solution:

  Set the configuration item to allow or a higher level. For details, see Table 3.

• Server common name "xxxx" does not match host name "xxxxx"

  Possible causes:

  When verify-full is used for SSL encryption, the driver checks whether the host name in certificates is the same as the actual one.

  Solution:

  To solve this problem, use verify-ca to stop checking host names, or generate a set of CA certificates containing the actual host names.

• Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

  Possible causes:

  The executable file (such as the isql of unixODBC) and the database driver (gsqlodbcw.so) depend on different library versions of ODBC, such as libodbc.so.1 and libodbc.so.2. You can verify this problem by using the following method:

  ldd `which isql` | grep odbc
  ldd gsqlodbcw.so | grep odbc

  If the suffix digits of the outputs libodbc.so are different or indicate different physical disk files, this problem exists. Both isql and gsqlodbcw.so require libodbc.so to be loaded. If they load different physical files, two sets of function lists with the same name are generated in a visible domain (the libodbc.so.* function export lists of unixODBC are the same). This results in conflicts and the database driver cannot be loaded.

  Solution:

  Uninstall the unnecessary unixODBC, such as libodbc.so.2, and create a soft link with the same name and the .so.2 suffix for the remaining libodbc.so.1 library.

• FATAL: Forbid remote connection with trust method!

  For security purposes, the primary database node forbids access from other nodes in the database without authentication.

  To access the primary database node from inside the database, deploy the ODBC program on the host where the primary database node is located and set the server address to 127.0.0.1. It is recommended that the service system be deployed outside the database. Otherwise, the database performance may be affected.

• [unixODBC][Driver Manager]Invalid attribute value

  The unixODBC version may not be the recommended one. You are advised to run the odbcinst --version command to check the unixODBC version in the environment.

• authentication method 10 not supported.

  If this error occurs on an open-source client, the cause may be:

  The database stores only the SHA-256 hash of the password, but the open-source client supports only MD5 hashes.

  

  • The database stores the hashes of user passwords instead of actual passwords.
  • If a password is updated or a user is created, both types of hashes will be stored, compatible with open-source authentication protocols.
  • An MD5 hash can only be generated using the original password, but the password cannot be obtained by reversing its SHA-256 hash. Passwords in the source version will only have SHA-256 hashes and not support MD5 authentication.
  • The MD5 encryption algorithm has lower security and poses security risks. Therefore, you are advised to use a more secure encryption algorithm.

  To solve this problem, you can update the user password (see ALTER USER), or create a user (see CREATE USER) with the same permissions and then connect to the database as the new user.

• unsupported frontend protocol 3.51: server supports 1.0 to 3.0

  The database version is too early or the database is an open-source database.

  Use the driver of the required version to connect to the database.

• FATAL: GSS authentication method is not allowed because XXXX user password is not disabled.

  In gs_hba.conf of the target primary database node, the authentication mode is set to gss for authenticating the IP address of the current client. However, this authentication algorithm cannot authenticate clients. Change the authentication algorithm to sha256 and try again.

• isql: error while loading shared libraries:xxx

  The dynamic library does not exist in the environment. You need to install the corresponding library.