search

spl0k / supysonic

Supysonic is a Python implementation of the Subsonic server API.
https://supysonic.readthedocs.io
GNU Affero General Public License v3.0
260 stars 57 forks source link

MySQL: "You have an error in your SQL syntax" #180

Closed chrisbeckstrom closed 4 years ago

chrisbeckstrom commented 4 years ago

Hi all, great project! I was able to get it running using sqlite and it's great. I have a pretty big music library and thought it would be better to use mysql. However, when set my config file to use it, I get a MySQL syntax error (below).

Here are some details: OS: Debian 9.5 stretch Python: 3.5.3 supysonic version: the current one from github, as well as the downloaded 0.5.0 release

Here is a portion of my config file ( /etc/supysonic):

[base]
; A database URI. See the 'schema' folder for schema creation scripts
; Default: sqlite:////tmp/supysonic/supysonic.db
;database_uri = sqlite:////var/supysonic/supysonic.db
;database_uri = mysql://supysonic:supysonic@localhost/supysonic
database_uri = mysql://root:PASSWORD@localhost/supysonic
;database_uri = postgres://supysonic:PASSWORD@localhost/supysonic

I know I shouldn't be connecting with root, but I was using that to try and troubleshoot this issue. I also have a mysql user "supysonic" that has all privs for the "supysonic" database, and I get the same error

I try to start the server with python3 cgi-bin/server.py :: and I get this error:

# python3 cgi-bin/server.py ::
/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py:170: Warning: (1050, "Table 'folder' already exists")
  result = self._query(query)
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbapiprovider.py", line 50, in wrap_dbapi_exceptions
    return func(provider, *args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbapiprovider.py", line 269, in execute
    if arguments is None: cursor.execute(sql)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, "Table 'supysonic.meta' doesn't exist")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/supysonic-0.5.0-py3.5.egg/supysonic/db.py", line 610, in init_database
    metadb.check_tables()
  File "<string>", line 2, in check_tables
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 528, in new_func
    result = func(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 1211, in check_tables
    database.schema.check_tables(database.provider, connection)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbschema.py", line 78, in check_tables
    provider.execute(cursor, sql)
  File "<string>", line 2, in execute
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbapiprovider.py", line 62, in wrap_dbapi_exceptions
    raise ProgrammingError(e)
pony.orm.dbapiprovider.ProgrammingError: (1146, "Table 'supysonic.meta' doesn't exist")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbapiprovider.py", line 50, in wrap_dbapi_exceptions
    return func(provider, *args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbapiprovider.py", line 269, in execute
    if arguments is None: cursor.execute(sql)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.5/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.5/dist-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 'IF NOT EXISTS index_folder_parent_id_fk ON folder(parent_id)' at line 1")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "cgi-bin/server.py", line 12, in <module>
    app = create_application()
  File "/usr/local/lib/python3.5/dist-packages/supysonic-0.5.0-py3.5.egg/supysonic/web.py", line 54, in create_application
    init_database(app.config["BASE"]["database_uri"])
  File "/usr/local/lib/python3.5/dist-packages/supysonic-0.5.0-py3.5.egg/supysonic/db.py", line 613, in init_database
    execute_sql_resource_script("schema/" + settings["provider"] + ".sql")
  File "/usr/local/lib/python3.5/dist-packages/supysonic-0.5.0-py3.5.egg/supysonic/db.py", line 599, in execute_sql_resource_script
    metadb.execute(statement)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 870, in execute
    return database._exec_raw_sql(sql, globals, locals, frame_depth=cut_traceback_depth+1, start_transaction=True)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 882, in _exec_raw_sql
    return database._exec_sql(adapted_sql, arguments, False, start_transaction)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 945, in _exec_sql
    connection = cache.reconnect(e)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 1779, in reconnect
    if not provider.should_reconnect(exc): reraise(*sys.exc_info())
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/utils/utils.py", line 95, in reraise
    try: raise exc.with_traceback(tb)
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/core.py", line 943, in _exec_sql
    try: new_id = provider.execute(cursor, sql, arguments, returning_id)
  File "<string>", line 2, in execute
  File "/usr/local/lib/python3.5/dist-packages/pony-0.7.11-py3.5.egg/pony/orm/dbapiprovider.py", line 62, in wrap_dbapi_exceptions
    raise ProgrammingError(e)
pony.orm.dbapiprovider.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 'IF NOT EXISTS index_folder_parent_id_fk ON folder(parent_id)' at line 1")

Thanks in advance for any hints!

spl0k commented 4 years ago

Hi! Thank you for your report, I'll take a look. In the meantime, could you please tell me if you're using MySQL or MariaDB and which version?

spl0k commented 4 years ago

Ok, this was due to some statements in the database initialization scripts that were understood by MariaDB but weren't compatible with MySQL. It should now be fixed.