Configuring a Data Source in the Linux OS

Procedure

1. Obtain the source code package of unixODBC at: Currently, unixODBC-2.2.1 is not supported. unixODBC-2.3.0 is used as an example.

   https://sourceforge.net/projects/unixodbc/files/unixODBC/2.3.0/unixODBC-2.3.0.tar.gz/download

2. Prepare unixODBC.
   1. Decompress the unixODBC code file.

      tar -xvf unixODBC-2.3.0.tar.gz

   2. Compile the code file and install the driver.

      1 2 3 4

      cd unixODBC-2.3.0 ./configure --enable-gui=no make make install

      

      • After the unixODBC is compiled and installed, the *.so.2 library file will be in the installation directory. To create the *.so.1 library file, change LIB_VERSION in the configure file to 1:0:0.

        1	LIB_VERSION="1:0:0"

      • This driver dynamically loads the libodbcinst.so.* library files. If one of the library files is successfully loaded, the library file is loaded. The loading priority is libodbcinst.so > libodbcinst.so.1 > libodbcinst.so.1.0.0 > libodbcinst.so.2 > libodbcinst.so.2.0.0.

        For example, a directory can be dynamically linked to libodbcinst.so.1, libodbcinst.so.1.0.0, and libodbcinst.so.2. The driver file loads libodbcinst.so first. If libodbcinst.so cannot be found in the current environment, the driver file searches for libodbcinst.so.1, which has a lower priority. After libodbcinst.so.1 is loaded, the loading is complete.

3. Replace the GaussDB(DWS) client driver.

   Decompress dws_8.1.x_odbc_driver_for_xxx_xxx.zip to obtain the psqlodbcw.la and psqlodbcw.so files in the /dws_8.1.x_odbc_driver_for_xxx_xxx/odbc/lib directory.

4. Configure the data source.
   1. Configure the ODBC driver file.

      Add the following content to the end of 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
      [DriverName]	Driver name, corresponding to Driver in DSN.	[DRIVER_N]
      Driver64	Path of the dynamic driver library	Driver64=/xxx/odbc/lib/psqlodbcw.so
      setup	Driver installation path, which is the same as the dynamic library path in Driver64.	setup=/xxx/odbc/lib/psqlodbcw.so

   2. Configure the data source file.

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

      [MPPODBC]
      Driver=GaussMPP
      Servername=10.10.0.13 (database server IP address)
      Database=gaussdb (database name)
      Username=dbadmin (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	[MPPODBC]
      Driver	Driver name, corresponding to DriverName in odbcinst.ini	Driver=DRIVER_N
      Servername	IP address of the server	Servername=10.145.130.26
      Database	Name of the database to connect to	Database=gaussdb
      Username	Name of the database user	Username=dbadmin
      Password	Password of the database user	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.
      Port	Port ID of the server	Port=8000
      Sslmode	Whether to enable the SSL mode	Sslmode=allow
      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, the 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 the support_batch_bind parameter is set to on, the batch query protocol is enabled.	UseBatchProtocol=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 1. If this parameter is set to 0, the ODBC driver reports the name and version of the current driver to the database. If this parameter is set to 1, the ODBC driver reports the name, deployment path, and process owner of the current driver to the database and records them in the connection_info parameter (see connection_info). You can query this parameter in PG_STAT_ACTIVITY and PGXC_STAT_ACTIVITY.
      ForExtensionConnector	ETL tool performance optimization parameter. It can be used to optimize the memory and reduce the memory usage by the peer CN, to avoid system instability caused by excessive CN memory usage. The value can be 0 or 1. The default value is 0, indicating that the optimization item is disabled. Do not set this parameter for other services outside the database system. Otherwise, the service correctness may be affected.	ForExtensionConnector=1
      KeepDisallowPremature	Specifies whether the cursor in the SQL statement has the with hold attribute when the following conditions are met: UseDeclareFetch is set to 1, and the application invokes SQLNumResultCols, SQLDescribeCol, or SQLColAttribute after invoking SQLPrepare to obtain the column information of the result set. The value can be 0 or 1. 0 indicates that the with hold attribute is supported, and 1 indicates that the with hold attribute is not supported. The default value is 0.	KeepDisallowPremature=1 NOTE: When UseServerSidePrepare is set to 1, the KeepDisallowPremature parameter does not take effect. To use this parameter, set UseServerSidePrepare to 0. For example, set UseDeclareFetch to 1. KeepDisallowPremature=1 UseServerSidePrepare=0

      The valid values of sslmode are as follows.

      Table 3 sslmode options

      sslmode	Whether SSL Encryption Is Enabled	Description
      disable	No	The SSL secure connection is not used.
      allow	Probably	The SSL secure encrypted connection is used if required by the database server, but does not check the authenticity of the server.
      prefer	Probably	The SSL secure encrypted connection is used as a preferred mode if supported by the database, but does not check the authenticity of the server.
      require	Yes	The SSL secure connection must be used, but it only encrypts data and does not check the authenticity of the server.
      verify-ca	Yes	The SSL secure connection must be used, and it checks whether the database has certificates issued by a trusted CA.
      verify-full	Yes	The SSL secure connection must be used. In addition to the check scope specified by verify-ca, it checks whether the name of the host where the database resides is the same as that on the certificate. This mode is not supported.

5. Enable the SSL mode.

   To use SSL certificates for connection, decompress the certificate package contained in the GaussDB(DWS) installation package, and run source sslcert_env.sh in a shell environment to deploy certificates in the default location of the current session.

   Or manually declare the following environment variables and ensure that the permission for the client.key* series files is set to 600.

   export PGSSLCERT= "/YOUR/PATH/OF/client.crt" # Change the path to the absolute path of client.crt.
   export PGSSLKEY= "/YOUR/PATH/OF/client.key" # Change the path to the absolute path of client.key.

   In addition, change the value of Sslmode in the data source to verify-ca.

6. Add the IP address segment of the host where the client is located to the security group rules of GaussDB(DWS) to ensure that the host can communicate with GaussDB(DWS).
7. Configure environment variables.

   vim ~/.bashrc

   Add the following content to the end of 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

8. Run the following commands to validate the settings:

   source ~/.bashrc

Testing Data Source Configuration

Run the isql-v GaussODBC command (GaussODBC is the data source name).

Troubleshooting

• [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, ensuring that the psqlodbcw.so file exists and you have execution permissions on it.

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

    Run ldd to check the path in the error information. If libodbc.so.1 or other UnixODBC libraries are lacking, configure UnixODBC again following the procedure provided in this section, and add the lib directory under its installation directory to LD_LIBRARY_PATH. If other libraries are lacking, add the lib directory under 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 based on database server configurations in the procedure in this section.

  • Firewall and network gatekeeper settings are improper.

    Check firewall settings, ensuring 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 it to allow or a higher level. For more 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 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 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 machine 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, the 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.
  • In versions earlier than V100R002C80SPC300, the database stores only SHA-256 hashes and no MD5 hashes. Therefore, MD5 cannot be used for user password authentication.
  • In V100R002C80SPC300 and later, 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. If your database is upgraded from a version earlier than V100R002C80SPC300, passwords in the old version will only have SHA-256 hashes and not support MD5 authentication.

  To solve this problem, you can update the user password. Alternatively, create a user, assign the same permissions to the user, and use the new user to connect to the database.

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