Bu sayfa henüz yerel dilinizde mevcut değildir. Daha fazla dil seçeneği eklemek için yoğun bir şekilde çalışıyoruz. Desteğiniz için teşekkür ederiz.

Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
Software Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Configuring a Data Source in the Linux OS

Updated on 2024-12-18 GMT+08:00

The ODBC DRIVER (psqlodbcw.so) provided by GaussDB(DWS) can be used after it has been configured in the data source. To set up a data source, configure the odbc.ini and odbcinst.ini files on the server. The two files are generated during the unixODBC compilation and installation, and are saved in the etc directory by default.

Procedure

  1. Obtain the source code package of unixODBC at:

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

  2. Currently, unixODBC-2.2.1 is not supported. Assume you are to install unixODBC-2.3.0. Run the following commands. When installing, you can use --prefix=[your_path] to specify the installation directory. The data source file will be created in [your_path]/etc, and the library file will be generated in [your_path]/lib.

    tar zxvf unixODBC-2.3.0.tar.gz
    cd unixODBC-2.3.0
    # Open the configure file. If it does not exist, open the configure.ac file. 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 --prefix=[your_path] # To perform the compilation on a TaiShan server, add the configure parameter --build=aarch64-unknown-linux-gnu.
    make
    make install

    Install unixODBC. If another version of unixODBC has been installed, it will be overwritten after installation.

  3. Replace the GaussDB(DWS) client driver.

    Decompress dws_8.x.x_odbc_driver_for_xxx_xxx.zip to obtain the psqlodbcw.la and psqlodbcw.so files in the /dws_8.x.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 [your_path]/etc/odbcinst.ini file:

      [GaussMPP]
      Driver64=[your_path]/lib/odbc/psqlodbcw.so
      setup=[your_path]/lib/odbc/psqlodbcw.so

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

      Table 1 odbcinst.ini configuration parameters

      Parameter

      Description

      Examples

      [DriverName]

      Driver name, corresponding to Driver in DSN.

      [DRIVER_N]

      Driver64

      Path of the dynamic driver library

      Driver64=/xxx/odbc/lib/odbc/psqlodbcw.so

      setup

      Driver installation path, which is the same as the dynamic library path in Driver64.

      setup=/xxx/odbc/lib/odbc/psqlodbcw.so

    2. Configure the data source file.

      Add the following content to the [your_path]/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

      Examples

      [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

      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=[your_path]/lib/:$LD_LIBRARY_PATH
    export ODBCSYSINI=[your_path]/etc
    export ODBCINI=[your_path]/etc/odbc.ini
    NOTE:

    It is not recommended to add LD_LIBRARY_PATH in the Kylin OS, as it may cause conflicts with the libssl.so dynamic library. In the latest version of cluster 9.1.0, the rpath mechanism has been added, so the dependency can be located without LD_LIBRARY_PATH.

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

  • If the following information is displayed, the configuration is correct and the connection succeeds.
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL> 
  • If error information is displayed, the configuration is incorrect. Check the configuration.

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. Alternatively, you can place the dependency library of psqlodbcw.so in the path corresponding to rpath of psqlodbcw.so. To view rpath, you can use the readelf -d command.

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

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

Sitemizi ve deneyiminizi iyileştirmek için çerezleri kullanırız. Sitemizde tarama yapmaya devam ederek çerez politikamızı kabul etmiş olursunuz. Daha fazla bilgi edinin

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback