Skip to content

Table name 'function' not quoted in MySQL which is now a syntax error in version 8 #4348

New issue
New issue
Closed
Closed
Table name 'function' not quoted in MySQL which is now a syntax error in version 8#4348
Labels
bugSomething isn't workingmysql
Milestone
 
1.2.x
@sqlalchemy-bot

Description

@sqlalchemy-bot
sqlalchemy-bot
opened on Oct 15, 2018
Collaborator

Migrated issue, originally created by Alex Hall

I have a table named 'function', and when SQLAlchemy creates it, it sends the SQL CREATE TABLE function (...). This was valid in MySQL 5.6, but in the new MySQL 8 it's a syntax error because function is a reserved keyword. Quoting the table name with backticks works, so SQLAlchemy should probably do that.

While waiting for a fix, how can I force SQLAlchemy to always quote identifiers? I don't want to change the table name.

I'm using SQLAlchemy==1.2.12, mysql-connector==2.1.6, and MySQL versions as follows:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 8.0.12                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 8.0.12                |
| version_comment         | Homebrew              |
| version_compile_machine | x86_64                |
| version_compile_os      | osx10.13              |
| version_compile_zlib    | 1.2.11                |
+-------------------------+-----------------------+

Code to create the table:

class Base(object):
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()


Base = declarative_base(cls=Base)

class Function(Base):
    ... # column definitions

Base.metadata.create_all(engine)

Full traceback:

Traceback (most recent call last):
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'function (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	file TEXT, 
	name TEXT, 
	html' at line 1

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/runpy.py", line 170, in _run_module_as_main
    "__main__", mod_spec)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/Users/alexhall/Desktop/python/treetrace/birdseye/__main__.py", line 5, in <module>
    from birdseye.server import main
  File "/Users/alexhall/Desktop/python/treetrace/birdseye/server.py", line 37, in <module>
    db = Database()
  File "/Users/alexhall/Desktop/python/treetrace/birdseye/db.py", line 169, in __init__
    Base.metadata.create_all(engine)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all
    tables=tables)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1940, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata
    _is_metadata_operation=True)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1009, in _execute_ddl
    compiled
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/Users/alexhall/.pyenv/versions/3.5.1/lib/python3.5/site-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): 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 'function (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	file TEXT, 
	name TEXT, 
	html' at line 1 [SQL: '\nCREATE TABLE function (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tfile TEXT, \n\tname TEXT, \n\thtml_body LONGTEXT, \n\tlineno INTEGER, \n\tdata LONGTEXT, \n\thash VARCHAR(64), \n\tbody_hash VARCHAR(64), \n\tPRIMARY KEY (id), \n\tCONSTRAINT everything_unique UNIQUE (hash)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)

Activity

sqlalchemy-bot

sqlalchemy-bot commented on Oct 15, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 15, 2018
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

feel free to send a PR like the following:

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 07eca78bb3..1175d6e0ab 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -773,7 +773,7 @@ RESERVED_WORDS = set(
      'deterministic', 'distinct', 'distinctrow', 'div', 'double', 'drop',
      'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped', 'exists',
      'exit', 'explain', 'false', 'fetch', 'float', 'float4', 'float8',
-     'for', 'force', 'foreign', 'from', 'fulltext', 'grant', 'group',
+     'for', 'force', 'foreign', 'from', 'fulltext', 'function', 'grant', 'group',
      'having', 'high_priority', 'hour_microsecond', 'hour_minute',
      'hour_second', 'if', 'ignore', 'in', 'index', 'infile', 'inner', 'inout',
      'insensitive', 'insert', 'int', 'int1', 'int2', 'int3', 'int4', 'int8',

quote your name for now as:

>>> from sqlalchemy.sql.elements import quoted_name
>>> from sqlalchemy import Table, MetaData, Column, Integer
>>> t = Table(quoted_name("function", True), MetaData(), Column('q', Integer))
>>> from sqlalchemy.schema import CreateTable
>>> print(CreateTable(t))

CREATE TABLE "function" (
	q INTEGER
)
sqlalchemy-bot

sqlalchemy-bot commented on Oct 15, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 15, 2018
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • set milestone to "1.2.x"
sqlalchemy-bot

sqlalchemy-bot commented on Oct 15, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 15, 2018
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

that is, use quoted_name("function", True) as the value of __table_name__ with declarative

sqlalchemy-bot

sqlalchemy-bot commented on Oct 15, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 15, 2018
CollaboratorAuthor

Alex Hall wrote:

Thanks for the quick response!

I've made a PR: zzzeek/sqlalchemy#481

I'm hacking around this problem for now with this:

from sqlalchemy.dialects.mysql.base import RESERVED_WORDS

RESERVED_WORDS.add('function')

This way I don't have to change my table creation code.

sqlalchemy-bot

sqlalchemy-bot commented on Oct 30, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 30, 2018
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

Add reserved word 'function' for MySQL

Added word function to the list of reserved words for MySQL, which is
now a keyword in MySQL 8.0

Fixes: #4348
Change-Id: Idd30acda7e99076810f65d0ee860055a18dc9193
Pull-request: zzzeek/sqlalchemy#481

→ 38c8132

sqlalchemy-bot

sqlalchemy-bot commented on Oct 30, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 30, 2018
CollaboratorAuthor

Michael Bayer (@zzzeek) wrote:

Add reserved word 'function' for MySQL

Added word function to the list of reserved words for MySQL, which is
now a keyword in MySQL 8.0

Fixes: #4348
Change-Id: Idd30acda7e99076810f65d0ee860055a18dc9193
Pull-request: zzzeek/sqlalchemy#481
(cherry picked from commit 38c8132)

→ 353d416

sqlalchemy-bot

sqlalchemy-bot commented on Oct 30, 2018

@sqlalchemy-bot
sqlalchemy-bot
on Oct 30, 2018
CollaboratorAuthor

Changes by Michael Bayer (@zzzeek):

  • changed status to closed
sqlalchemy-bot
closed this as completedon Oct 30, 2018
sqlalchemy-bot
added
bugSomething isn't working
mysql
on Nov 27, 2018
sqlalchemy-bot
added this to the 1.2.x milestone on Nov 27, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingmysql

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @sqlalchemy-bot

        Issue actions
          Table name 'function' not quoted in MySQL which is now a syntax error in version 8 · Issue #4348 · sqlalchemy/sqlalchemy