Configuring a Data Source in the Linux OS

Procedure

1. Obtain the source code package of unixODBC by the following link:

   https://sourceforge.net/projects/unixodbc/files/unixODBC

   After the download, verify the integrity based on the integrity verification algorithm provided by the community.

2. Install unixODBC. It does not matter if unixODBC of another version has been installed.
   Currently, unixODBC-2.2.1 is not supported. For example, to install unixODBC-2.3.0, run the commands below. It is installed in the /usr/local directory by default. The data source file is generated in the /usr/local/etc directory, and the library file is generated in the /usr/local/lib directory.

   tar zxvf unixODBC-2.3.0.tar.gz
   cd unixODBC-2.3.0
   # Modify the configure file and find LIB_VERSION.
   # Change the value of LIB_VERSION to 1:0:0 to compile a *.so.1 dynamic library with the same dependency on psqlodbcw.so.
   vim configure
   
   ./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

3. Replace the GaussDB client driver.

   Decompress the GaussDB-Kernel-VxxxRxxxCxx-xxxxx-64bit-Odbc.tar.gz package. 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.

4. 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/psqlodbcw.so
      setup=/usr/local/lib/psqlodbcw.so

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

      Table 1 odbcinst.ini configuration parameters

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

   2. Configure the data source file.

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

      [gaussdb]
      Driver=GaussMPP
      Servername=10.145.130.26 (IP address of the server where the database resides)
      Database=postgres  (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 Value
      [DSN]	Data source name.	[gaussdb]
      Driver	Driver name, corresponding to DriverName in odbcinst.ini.	Driver=DRIVER_N
      Servername	Server IP address. Multiple IP addresses can be configured.	Servername=10.145.130.26
      Database	Name of the database to connect.	Database=postgres
      Username	Database username.	Username=omm
      Password	Database user password.	Password= NOTE: After a user established 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. CAUTION: 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 handling of the percent sign (%). A plus sign (+) will be replaced with a space.
      Port	Port number of the server. When load balancing is enabled, multiple port numbers can be configured and must correspond to multiple IP addresses. If multiple IP addresses are configured and only one port number is configured when load balancing is enabled, all IP addresses share the same port number by default, that is, the configured port number.	Port=8000
      Sslmode	Whether to enable SSL.	Sslmode=allow
      Debug	If this parameter is set to 1, the mylog file of the PostgreSQL ODBC driver will be printed. The directory generated for storing logs is /tmp/. If this parameter is set to 0, no directory is generated.	Debug=1
      UseServerSidePrepare	Whether to enable the extended query protocol for the database. The value can be 0 or 1. The default value is 1, indicating that the extended query protocol is enabled.	UseServerSidePrepare=1
      UseBatchProtocol	Whether to enable the batch query protocol. If it is enabled, DML performance can be improved. The value can be 0 or 1. The default value is 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.	UseBatchProtocol=1
      ForExtensionConnector	This parameter specifies whether the savepoint is sent.	ForExtensionConnector=1
      ConnectionExtraInfo	Whether to display the driver deployment path and process owner in the connection_info parameter mentioned in connection_info.	ConnectionExtraInfo=1 NOTE: The default value is 0. 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 connection_info parameter (see connection_info). In this case, you can query the information from PG_STAT_ACTIVITY or PGXC_STAT_ACTIVITY.
      BoolAsChar	If this parameter is set to Yes, the Boolean value is mapped to the SQL_CHAR type. If this parameter is not set, the value is mapped to the SQL_BIT type.	BoolsAsChar = Yes
      RowVersioning	When an attempt is made to update a row of data, setting this parameter to Yes allows the application to detect whether the data has been modified by other users.	RowVersioning=Yes
      ShowSystemTables	By default, the driver regards the system table as a common SQL table.	ShowSystemTables=Yes
      AutoBalance	Specifies whether ODBC enables load balancing. The default value is 0, indicating that load balancing is disabled. The value 1 indicates that load balancing is enabled. All values except 1 indicate that load balancing is disabled.	AutoBalance=1
      RefreshCNListTime	Specifies the interval for refreshing the CN list. The value is an integer and the default value is 10. This parameter can be configured when load balancing is enabled.	RefreshCNListTime=5
      Priority	This parameter can be configured for load balancing. The default value is 0, indicating that load balancing is disabled. The value 1 indicates that load balancing is enabled. All values except 1 indicate that load balancing is disabled. When Priority is enabled, all connections initiated by applications are preferentially sent to the CNs configured in the configuration file. If all the configured CNs are unavailable, the connections are sent to the remaining CNs.	Priority=1
      UsingEip	This parameter can be configured for load balancing. The default value is 0, indicating that load balancing is disabled. The value 1 indicates that load balancing is enabled. All values except 1 indicate that load balancing is disabled. The value specifies whether to use the elastic IP address for load balancing. If UsingEip is enabled, the elastic IP address is used for load balancing. If UsingEip is disabled, the data IP address is used for load balancing.	UsingEip=1
      TcpUserTimeout	Specifies the maximum duration for which transmitted data can remain unacknowledged before the TCP connection is forcibly closed on an operating system that supports the TCP_USER_TIMEOUT socket option. The unit is millisecond. The value 0 indicates that the default value is used. Ignore this parameter for Unix-domain connections.	TcpUserTimeout=5000

      The valid values of Sslmode are as follows:

      Table 3 sslmode options and description

      sslmode	Whether SSL Encryption Is Enabled	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 and data is encrypted. However, 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 on the certificate. If they are different, modify the /etc/hosts file as user root and add the IP address and host name of the connected database node to the file.

Verifying the Data Source Configuration

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).

FAQs

• [UnixODBC][Driver Manager]Can't open lib 'xxx/xxx/psqlodbcw.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, and ensure that the psqlodbcw.so file exists and you have execute permissions on it.

  • The dependent library of psqlodbcw.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 operation procedure, ensure that the lib directory in the installation path is added to LD_LIBRARY_PATH. If the problem persists after reinstallation, manually copy the contents in the unixodbc/lib directory of the database installation package to the lib directory in the installation path of the UnixODBC. If other libraries are missing, add the lib directory in the ODBC driver package to LD_LIBRARY_PATH.

• [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 network adapter 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 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 server certificates containing the actual host names.

• Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

  Possible causes:

  The executable file (such as the isql tool of unixODBC) and the database driver (psqlodbcw.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 psqlodbcw.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 psqlodbcw.so load libodbc.so. If different physical files are loaded, different ODBC libraries with the same function list conflict with each other in a visible domain. As a result, 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 database CN forbids access from other nodes in the cluster without authentication.

  To access the CN from inside the cluster, deploy the ODBC program on the host where the CN is located and use 127.0.0.1 as the server address. It is recommended that the service system be deployed outside the cluster. If it is deployed inside, database performance may be affected.

• [unixODBC][Driver Manager]Invalid attribute value

  This problem occurs when you use SQL on other GaussDB. The possible cause is that the unixODBC version is not the recommended one. You are advised to run the odbcinst --version command to check the unixODBC version.

• 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 old 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) having the same permissions as the faulty 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.

• isql: error while loading shared libraries: xxx

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