Last active
July 15, 2020 23:43
-
-
Save lpsinger/d8332ffc7d8bb5bb4fc4c0534fa9bcf7 to your computer and use it in GitHub Desktop.
SQLAlchemy example of broken index expression in MySQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| version: '3.3' | |
| services: | |
| mysql: | |
| image: mysql | |
| environment: | |
| MYSQL_USER: user | |
| MYSQL_DATABASE: mysql | |
| MYSQL_PASSWORD: password | |
| MYSQL_ROOT_PASSWORD: foobar | |
| python: | |
| build: | |
| context: . | |
| depends_on: | |
| - mysql |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| FROM python | |
| RUN pip install --no-cache-dir sqlalchemy pymysql cryptography | |
| COPY test.py / | |
| ENTRYPOINT /test.py |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env python3 | |
| from sqlalchemy import create_engine, Column, Integer, Index, func | |
| from sqlalchemy.ext.declarative import declarative_base | |
| Base = declarative_base() | |
| class Foobar(Base): | |
| foobar_id = Column(Integer, primary_key=True) | |
| xyzzy = Column(Integer) | |
| plugh = Column(Integer) | |
| __tablename__ = 'foobar' | |
| __table_args__ = ( | |
| Index('ix_foobar_xyzzy_plugh', xyzzy + plugh), | |
| ) | |
| engine = create_engine('mysql+pymysql://user:password@mysql/mysql') | |
| Base.metadata.create_all(engine) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Traceback (most recent call last): | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context | |
| self.dialect.do_execute( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute | |
| cursor.execute(statement, parameters) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 170, in execute | |
| result = self._query(query) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 328, in _query | |
| conn.query(q) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 517, in query | |
| self._affected_rows = self._read_query_result(unbuffered=unbuffered) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 732, in _read_query_result | |
| result.read() | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1075, in read | |
| first_packet = self.connection._read_packet() | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 684, in _read_packet | |
| packet.check_error() | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 220, in check_error | |
| err.raise_mysql_exception(self._data) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 109, in raise_mysql_exception | |
| raise errorclass(errno, errval) | |
| pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ plugh)' at line 1") | |
| The above exception was the direct cause of the following exception: | |
| Traceback (most recent call last): | |
| File "/test.py", line 22, in <module> | |
| Base.metadata.create_all(engine) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4546, in create_all | |
| bind._run_visitor( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2098, in _run_visitor | |
| conn._run_visitor(visitorcallable, element, **kwargs) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1657, in _run_visitor | |
| visitorcallable(self.dialect, self, **kwargs).traverse_single(element) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single | |
| return meth(obj, **kw) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 783, in visit_metadata | |
| self.traverse_single( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single | |
| return meth(obj, **kw) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 839, in visit_table | |
| self.traverse_single(index) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single | |
| return meth(obj, **kw) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 868, in visit_index | |
| self.connection.execute(CreateIndex(index)) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1014, in execute | |
| return meth(self, multiparams, params) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection | |
| return connection._execute_ddl(self, multiparams, params) | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1071, in _execute_ddl | |
| ret = self._execute_context( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context | |
| self._handle_dbapi_exception( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception | |
| util.raise_( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_ | |
| raise exception | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context | |
| self.dialect.do_execute( | |
| File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute | |
| cursor.execute(statement, parameters) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 170, in execute | |
| result = self._query(query) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 328, in _query | |
| conn.query(q) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 517, in query | |
| self._affected_rows = self._read_query_result(unbuffered=unbuffered) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 732, in _read_query_result | |
| result.read() | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1075, in read | |
| first_packet = self.connection._read_packet() | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 684, in _read_packet | |
| packet.check_error() | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 220, in check_error | |
| err.raise_mysql_exception(self._data) | |
| File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 109, in raise_mysql_exception | |
| raise errorclass(errno, errval) | |
| sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ plugh)' at line 1") | |
| [SQL: CREATE INDEX ix_foobar_xyzzy_plugh ON foobar (xyzzy + plugh)] | |
| (Background on this error at: http://sqlalche.me/e/13/f405) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment