bug: Incorrect UTC time zone with mySQL backend triggers OperationalError #7918
Labels
bug
Incorrect behavior inside of ibis
Comments
cpcloud
pushed a commit
that referenced
this issue
Jan 8, 2024
## Description of changes When trying to connect to a mySQL backend, the following error can appear: ``` OperationalError: (pymysql.err.OperationalError) (1298, "Unknown or incorrect time zone: 'UTC'") (Background on this error at: https://sqlalche.me/e/20/e3q8) ``` This is due to an exception generated by PyMySQL. The exception, formerly generated by SQLAlchemy was already caught by Ibis since #6010, this PR adds a catching of the PyMySQL exception. ## Issues closed Resolves #7918
github-project-automation
bot
moved this from backlog
to done
in Ibis planning and roadmap
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
What happened?
When trying to connect to a mySQL backend, I get the following error:
This error has been already report in #6010 , but still seems to be present in Ibis 7.2.0.
Seems to be related to this issue from the PyMySQL repo #624
What version of ibis are you using?
7.2.0
What backend(s) are you using, if any?
mySQL 5.5.5
mariaDB 10.3.38
Relevant log output
connection = ibis.mysql.connect( host=server, user=user, password=pwd, port=3306, database='db_name', ) connection.list_tables() --------------------------------------------------------------------------- OperationalError Traceback (most recent call last) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:145, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin) 144 try: --> 145 self._dbapi_connection = engine.raw_connection() 146 except dialect.loaded_dbapi.Error as err: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:3292, in Engine.raw_connection(self) 3271 """Return a "raw" DBAPI connection from the connection pool. 3272 3273 The returned object is a proxied version of the DBAPI (...) 3290 3291 """ -> 3292 return self.pool.connect() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:452, in Pool.connect(self) 445 """Return a DBAPI connection from the pool. 446 447 The connection is instrumented such that when its (...) 450 451 """ --> 452 return _ConnectionFairy._checkout(self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy) 1268 if not fairy: -> 1269 fairy = _ConnectionRecord.checkout(pool) 1271 if threadconns is not None: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool) 715 else: --> 716 rec = pool._do_get() 718 try: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/impl.py:480, in StaticPool._do_get(self) 479 def _do_get(self) -> ConnectionPoolEntry: --> 480 rec = self.connection 481 if rec._is_hard_or_soft_invalidated(): File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py:1146, in _memoized_property.__get__(self, obj, cls) 1145 return self -> 1146 obj.__dict__[self.__name__] = result = self.fget(obj) 1147 return result File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/impl.py:437, in StaticPool.connection(self) 435 @util.memoized_property 436 def connection(self) -> _ConnectionRecord: --> 437 return _ConnectionRecord(self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:678, in _ConnectionRecord.__init__(self, pool, connect) 677 if connect: --> 678 self.__connect() 679 self.finalize_callback = deque() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:916, in _ConnectionRecord.__connect(self) 912 # init of the dialect now takes place within the connect 913 # event, so ensure a mutex is used on the first run 914 pool.dispatch.connect.for_modify( 915 pool.dispatch --> 916 )._exec_w_sync_on_first_run(self.dbapi_connection, self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/event/attr.py:473, in _CompoundListener._exec_w_sync_on_first_run(self, *args, **kw) 472 try: --> 473 self(*args, **kw) 474 except: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/event/attr.py:487, in _CompoundListener.__call__(self, *args, **kw) 486 for fn in self.listeners: --> 487 fn(*args, **kw) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/ibis/backends/mysql/__init__.py:114, in Backend.do_connect.<locals>.connect(dbapi_connection, connection_record) 113 try: --> 114 cur.execute("SET @@session.time_zone = 'UTC'") 115 except sa.exc.OperationalError: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/cursors.py:153, in Cursor.execute(self, query, args) 151 query = self.mogrify(query, args) --> 153 result = self._query(query) 154 self._executed = query File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/cursors.py:322, in Cursor._query(self, q) 321 self._clear_result() --> 322 conn.query(q) 323 self._do_get_result() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:558, in Connection.query(self, sql, unbuffered) 557 self._execute_command(COMMAND.COM_QUERY, sql) --> 558 self._affected_rows = self._read_query_result(unbuffered=unbuffered) 559 return self._affected_rows File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:822, in Connection._read_query_result(self, unbuffered) 821 result = MySQLResult(self) --> 822 result.read() 823 self._result = result File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:1200, in MySQLResult.read(self) 1199 try: -> 1200 first_packet = self.connection._read_packet() 1202 if first_packet.is_ok_packet(): File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:772, in Connection._read_packet(self, packet_type) 771 self._result.unbuffered_active = False --> 772 packet.raise_for_error() 773 return packet File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/protocol.py:221, in MysqlPacket.raise_for_error(self) 220 print("errno =", errno) --> 221 err.raise_mysql_exception(self._data) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/err.py:143, in raise_mysql_exception(data) 142 errorclass = InternalError if errno < 1000 else OperationalError --> 143 raise errorclass(errno, errval) OperationalError: (1298, "Unknown or incorrect time zone: 'UTC'") The above exception was the direct cause of the following exception: OperationalError Traceback (most recent call last) Cell In[48], line 1 ----> 1 connection.list_tables() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/ibis/backends/base/sql/alchemy/__init__.py:170, in BaseAlchemyBackend.list_tables(self, like, database) 169 def list_tables(self, like=None, database=None): --> 170 tables = self.inspector.get_table_names(schema=database) 171 views = self.inspector.get_view_names(schema=database) 172 return self._filter_with_like(tables + views, like) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/ibis/backends/base/sql/alchemy/__init__.py:177, in BaseAlchemyBackend.inspector(self) 174 @property 175 def inspector(self): 176 if self._inspector is None: --> 177 self._inspector = sa.inspect(self.con) 178 else: 179 self._inspector.info_cache.clear() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/inspection.py:145, in inspect(subject, raiseerr) 143 elif reg is True: 144 return subject --> 145 ret = reg(subject) 146 if ret is not None: 147 return ret File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py:303, in Inspector._engine_insp(bind) 301 @inspection._inspects(Engine) 302 def _engine_insp(bind: Engine) -> Inspector: # type: ignore[misc] --> 303 return Inspector._construct(Inspector._init_engine, bind) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py:236, in Inspector._construct(cls, init, bind) 233 cls = bind.dialect.inspector 235 self = cls.__new__(cls) --> 236 init(self, bind) 237 return self File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py:247, in Inspector._init_engine(self, engine) 245 def _init_engine(self, engine: Engine) -> None: 246 self.bind = self.engine = engine --> 247 engine.connect().close() 248 self._op_context_requires_connect = True 249 self.dialect = self.engine.dialect File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:3268, in Engine.connect(self) 3245 def connect(self) -> Connection: 3246 """Return a new :class:`_engine.Connection` object. 3247 3248 The :class:`_engine.Connection` acts as a Python context manager, so (...) 3265 3266 """ -> 3268 return self._connection_cls(self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:147, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin) 145 self._dbapi_connection = engine.raw_connection() 146 except dialect.loaded_dbapi.Error as err: --> 147 Connection._handle_dbapi_exception_noconnection( 148 err, dialect, engine 149 ) 150 raise 151 else: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2430, in Connection._handle_dbapi_exception_noconnection(cls, e, dialect, engine, is_disconnect, invalidate_pool_on_disconnect, is_pre_ping) 2428 elif should_wrap: 2429 assert sqlalchemy_exception is not None -> 2430 raise sqlalchemy_exception.with_traceback(exc_info[2]) from e 2431 else: 2432 assert exc_info[1] is not None File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:145, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin) 143 if connection is None: 144 try: --> 145 self._dbapi_connection = engine.raw_connection() 146 except dialect.loaded_dbapi.Error as err: 147 Connection._handle_dbapi_exception_noconnection( 148 err, dialect, engine 149 ) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/engine/base.py:3292, in Engine.raw_connection(self) 3270 def raw_connection(self) -> PoolProxiedConnection: 3271 """Return a "raw" DBAPI connection from the connection pool. 3272 3273 The returned object is a proxied version of the DBAPI (...) 3290 3291 """ -> 3292 return self.pool.connect() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:452, in Pool.connect(self) 444 def connect(self) -> PoolProxiedConnection: 445 """Return a DBAPI connection from the pool. 446 447 The connection is instrumented such that when its (...) 450 451 """ --> 452 return _ConnectionFairy._checkout(self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:1269, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy) 1261 @classmethod 1262 def _checkout( 1263 cls, (...) 1266 fairy: Optional[_ConnectionFairy] = None, 1267 ) -> _ConnectionFairy: 1268 if not fairy: -> 1269 fairy = _ConnectionRecord.checkout(pool) 1271 if threadconns is not None: 1272 threadconns.current = weakref.ref(fairy) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:716, in _ConnectionRecord.checkout(cls, pool) 714 rec = cast(_ConnectionRecord, pool._do_get()) 715 else: --> 716 rec = pool._do_get() 718 try: 719 dbapi_connection = rec.get_connection() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/impl.py:480, in StaticPool._do_get(self) 479 def _do_get(self) -> ConnectionPoolEntry: --> 480 rec = self.connection 481 if rec._is_hard_or_soft_invalidated(): 482 del self.__dict__["connection"] File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py:1146, in _memoized_property.__get__(self, obj, cls) 1144 if obj is None: 1145 return self -> 1146 obj.__dict__[self.__name__] = result = self.fget(obj) 1147 return result File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/impl.py:437, in StaticPool.connection(self) 435 @util.memoized_property 436 def connection(self) -> _ConnectionRecord: --> 437 return _ConnectionRecord(self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:678, in _ConnectionRecord.__init__(self, pool, connect) 676 self.__pool = pool 677 if connect: --> 678 self.__connect() 679 self.finalize_callback = deque() File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/pool/base.py:916, in _ConnectionRecord.__connect(self) 908 pool.dispatch.first_connect.for_modify( 909 pool.dispatch 910 ).exec_once_unless_exception(self.dbapi_connection, self) 912 # init of the dialect now takes place within the connect 913 # event, so ensure a mutex is used on the first run 914 pool.dispatch.connect.for_modify( 915 pool.dispatch --> 916 )._exec_w_sync_on_first_run(self.dbapi_connection, self) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/event/attr.py:473, in _CompoundListener._exec_w_sync_on_first_run(self, *args, **kw) 471 with self._exec_once_mutex: 472 try: --> 473 self(*args, **kw) 474 except: 475 raise File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/sqlalchemy/event/attr.py:487, in _CompoundListener.__call__(self, *args, **kw) 485 fn(*args, **kw) 486 for fn in self.listeners: --> 487 fn(*args, **kw) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/ibis/backends/mysql/__init__.py:114, in Backend.do_connect.<locals>.connect(dbapi_connection, connection_record) 112 with dbapi_connection.cursor() as cur: 113 try: --> 114 cur.execute("SET @@session.time_zone = 'UTC'") 115 except sa.exc.OperationalError: 116 warnings.warn("Unable to set session timezone to UTC.") File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/cursors.py:153, in Cursor.execute(self, query, args) 149 pass 151 query = self.mogrify(query, args) --> 153 result = self._query(query) 154 self._executed = query 155 return result File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/cursors.py:322, in Cursor._query(self, q) 320 conn = self._get_db() 321 self._clear_result() --> 322 conn.query(q) 323 self._do_get_result() 324 return self.rowcount File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:558, in Connection.query(self, sql, unbuffered) 556 sql = sql.encode(self.encoding, "surrogateescape") 557 self._execute_command(COMMAND.COM_QUERY, sql) --> 558 self._affected_rows = self._read_query_result(unbuffered=unbuffered) 559 return self._affected_rows File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:822, in Connection._read_query_result(self, unbuffered) 820 else: 821 result = MySQLResult(self) --> 822 result.read() 823 self._result = result 824 if result.server_status is not None: File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:1200, in MySQLResult.read(self) 1198 def read(self): 1199 try: -> 1200 first_packet = self.connection._read_packet() 1202 if first_packet.is_ok_packet(): 1203 self._read_ok_packet(first_packet) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/connections.py:772, in Connection._read_packet(self, packet_type) 770 if self._result is not None and self._result.unbuffered_active is True: 771 self._result.unbuffered_active = False --> 772 packet.raise_for_error() 773 return packet File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/protocol.py:221, in MysqlPacket.raise_for_error(self) 219 if DEBUG: 220 print("errno =", errno) --> 221 err.raise_mysql_exception(self._data) File /r1/people/maxime_borry/miniforge3/envs/speleothem/lib/python3.11/site-packages/pymysql/err.py:143, in raise_mysql_exception(data) 141 if errorclass is None: 142 errorclass = InternalError if errno < 1000 else OperationalError --> 143 raise errorclass(errno, errval) OperationalError: (pymysql.err.OperationalError) (1298, "Unknown or incorrect time zone: 'UTC'") (Background on this error at: https://sqlalche.me/e/20/e3q8)Code of Conduct
The text was updated successfully, but these errors were encountered: