【TiDB 使用环境】测试
【TiDB 版本】V8.5.1 裸机通过tiup命令部署
通过mysql命令行，dbeaver客户端都可以通过tiproxy连接到数据库，但是python通过tiproxy连接集群数据库time out，通过tidb server可以连接。
pymysql 版本 1.1.1
SQLAlchemy 版本是 Version: 2.0.40
python 版本是 3.12
配置如下
host = ********
port = 6000
user = ********
password = ********
database = kox_videoclip
workspace = default
use_ssl = false
pool_recycle = 25200
pool_size = 10
pool_pre_ping = true
sql_log = true
def __init__(self, **kwargs):
self.host = config.get("tidb.host", str, "localhost")
self.port = config.get("tidb.port", int, 30020)
self.user = config.get("tidb.user")
# 处理密码中的特殊字符，如'@'
self.password = urllib.parse.quote_plus(config.get("tidb.password", str))
self.database = config.get("tidb.database", str)
self.use_ssl = config.get("tidb.use_ssl", bool, False)
self.timeout = config.get("tidb.timeout", int, 10)
# TODO socket读写超时配置
connection_string = (
f"mysql+pymysql://{self.user}:{self.password}@{self.host}:{self.port}/{self.database}"
f"?ssl_verify_cert={self.use_ssl}&ssl_verify_identity={self.use_ssl}&read_timeout=10&write_timeout=10"
)
self.pool_size = config.get("tidb.pool_size", int, 5) # 连接池大小
self.max_overflow = config.get("tidb.max_overflow", int, 0) # 允许溢出的连接数，设置为0表示不允许
self.pool_timeout = config.get("tidb.pool_timeout", float, 3.0) # 等待从连接池获取连接的超时时间
self.pool_recycle = config.get("tidb.pool_recycle", int, 3600) # 连接主动断开时间，参考mysql server来配置
self.pool_pre_ping = config.get("tidb.pool_pre_ping", bool, False) # 从连接池获取到连接后，是否先执行ping命令，检查连接是否可用
self.sql_log = config.get("tidb.sql_log", bool, False) # 是否打印sql日志
logger.debug(f"TiDB sql_log: {self.sql_log}")
try:
self.engine = create_engine(
connection_string,
pool_size=self.pool_size,
max_overflow=self.max_overflow,
pool_timeout=self.pool_timeout,
pool_recycle=self.pool_recycle,
pool_pre_ping=self.pool_pre_ping,
poolclass=QueuePool,
echo=self.sql_log,
)
self.make_session = sessionmaker(bind=self.engine)
logger.info(f"Connected to database[{self.database}] by connection string: {connection_string}")
except Exception as e:
logger.error(traceback.format_exc())
logger.error(f"Failed to connect to database at {self.database}, error: {e}")
raise
报错如下：
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, ‘Lost connection to MySQL server during query (timed out)’)
app/base/db/tidb.py:145: in query
response = self.db.query(sql, params)
app/base/db/tidb.py:64: in query
with self.engine.connect() as conn, conn.begin():
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3274: in connect
return self._connection_cls(self)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/base.py:148: in __init__
Connection._handle_dbapi_exception_noconnection(
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/base.py:2439: in _handle_dbapi_exception_noconnection
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/base.py:146: in __init__
self._dbapi_connection = engine.raw_connection()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/base.py:3298: in raw_connection
return self.pool.connect()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:449: in connect
return _ConnectionFairy._checkout(self)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:1264: in _checkout
fairy = _ConnectionRecord.checkout(pool)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:713: in checkout
rec = pool._do_get()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:179: in _do_get
with util.safe_reraise():
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:146: in __exit__
raise exc_value.with_traceback(exc_tb)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/impl.py:177: in _do_get
return self._create_connection()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:390: in _create_connection
return _ConnectionRecord(self)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:675: in __init__
self.__connect()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:901: in __connect
with util.safe_reraise():
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:146: in __exit__
raise exc_value.with_traceback(exc_tb)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/pool/base.py:897: in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/create.py:646: in connect
return dialect.connect(*cargs, **cparams)
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/sqlalchemy/engine/default.py:625: in connect
return self.loaded_dbapi.connect(*cargs, **cparams) # type: ignore[no-any-return] # NOQA: E501
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/pymysql/connections.py:361: in __init__
self.connect()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/pymysql/connections.py:669: in connect
self._request_authentication()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/pymysql/connections.py:957: in _request_authentication
auth_packet = self._read_packet()
/opt/anaconda3/envs/videoclip/lib/python3.10/site-packages/pymysql/connections.py:744: in _read_packet
packet_header = self._read_bytes(4)
