database-connectionoceanbase

"Access denied" when using PyMySQL with OceanBase in transactional mode, but MySQL client connects fine


Environment

OceanBase Community Edition 4.2.1 (MySQL mode)

Python 3.10

PyMySQL 1.1.0

I'm trying to use PyMySQL to execute transactions on OceanBase, but I'm getting an unexpected "Access denied" error. The same credentials work perfectly with the MySQL command-line client.

Steps

  1. Connect using PyMySQL
import pymysql

conn = pymysql.connect(
    host='obproxy.example.com',
    port=2883,
    user='my_user@tenant',  # Explicit tenant included
    password='my_password',
    database='my_db',
    autocommit=False  # Required for transactions
)
  1. Attempt a transaction
try:
    with conn.cursor() as cursor:
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        conn.commit()  # Fails here
except pymysql.err.OperationalError as e:
    print(f"Transaction failed: {e}")
    conn.rollback()
  1. Got error message
pymysql.err.OperationalError: (1045, "Access denied for user 'my_user'@'%' to database 'my_db'")

I've verified the username includes the tenant (my_user@tenant), and explicitly granted privileges in OceanBase

GRANT ALL PRIVILEGES ON my_db.* TO 'my_user'@'%';

Question

Why does PyMySQL throw an 'Access denied' error during transactions when the MySQL client connects successfully with the same explicitly granted credentials?


Solution

  • Please do several check:

    1. make sure your user/password is correct

      you can try user/password by mysql client.

    2. directly connect observer to make sure no error on obproxy

      1. host='obproxy.example.com',    ---- please use observer's address
        port=2883,            -- observer's port is 2881
        
      2. if this can works, please check the protocol between client and oceanbase proxy.
         alter proxyconfig set enable_ob_protocol_v2 = false;
         alter proxyconfig set proxy_id=1; 
         alter proxyconfig set client_session_id_version=2;
         alter proxyconfig set enable_single_leader_node_routing=false;
      
      
    3. you can close autocommit as 0

    4. you can have a switch to another PyMySQL version 1.1.1