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
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
)
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()
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?
Please do several check:
make sure your user/password is correct
you can try user/password by mysql client.
directly connect observer to make sure no error on obproxy
host='obproxy.example.com', ---- please use observer's address
port=2883, -- observer's port is 2881
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;
you can close autocommit as 0
you can have a switch to another PyMySQL version 1.1.1