Django MySql backend tries to evaluate MySql settings every time it uses a connection and cannot convert an empty string, that is being returned to an integer.
When it does so it throws an error as follows (in this example I am just running manage migrate command):
root@2d37585a3d96:/mealplanner/trunk# python manage.py migrate
Traceback (most recent call last):
File "manage.py", line 11, in <module>
execute_from_command_line(sys.argv)
File "/py36-default/lib/python3.6/site-packages/django/core/management/init.py", line 381, in execute_from_command_line
utility.execute()
File "/py36-default/lib/python3.6/site-packages/django/core/management/init.py", line 375, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/py36-default/lib/python3.6/site-packages/django/core/management/base.py", line 323, in run_from_argv
self.execute(*args, **cmd_options)
File "/py36-default/lib/python3.6/site-packages/django/core/management/base.py", line 361, in execute
self.check()
File "/py36-default/lib/python3.6/site-packages/django/core/management/base.py", line 390, in check
include_deployment_checks=include_deployment_checks,
File "/py36-default/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 64, in runchecks
issues = run_checks(tags=[Tags.database])
File "/py36-default/lib/python3.6/site-packages/django/core/checks/registry.py", line 72, in run_checks
new_errors = check(app_configs=app_configs)
File "/py36-default/lib/python3.6/site-packages/django/core/checks/database.py", line 10, in check_database_backends
issues.extend(conn.validation.check(**kwargs))
File "/py36-default/lib/python3.6/site-packages/django/db/backends/mysql/validation.py", line 9, in check
issues.extend(self._check_sql_mode(**kwargs))
File "/py36-default/lib/python3.6/site-packages/django/db/backends/mysql/validation.py", line 13, in checksql_mode
with self.connection.cursor() as cursor:
File "/py36-default/lib/python3.6/site-packages/django/db/backends/base/base.py", line 256, in cursor
return self._cursor()
File "/py36-default/lib/python3.6/site-packages/django/db/backends/base/base.py", line 233, in _cursor
self.ensure_connection()
File "/py36-default/lib/python3.6/site-packages/django/db/backends/base/base.py", line 217, in ensure_connection
self.connect()
File "/py36-default/lib/python3.6/site-packages/django/db/backends/base/base.py", line 197, in connect
self.init_connection_state()
File "/py36-default/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 231, in init_connection_state
if self.features.is_sql_auto_is_null_enabled:
File "/py36-default/lib/python3.6/site-packages/django/utils/functional.py", line 80, in get
res = instance.dict[self.name] = self.func(instance)
File "/py36-default/lib/python3.6/site-packages/django/db/backends/mysql/features.py", line 83, in is_sql_auto_is_null_enabled
cursor.execute('SELECT @@SQL_AUTO_IS_NULL')
File "/py36-default/lib/python3.6/site-packages/django/db/backends/utils.py", line 99, in execute
return super().execute(sql, params)
File "/py36-default/lib/python3.6/site-packages/django/db/backends/utils.py", line 67, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/py36-default/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in executewith_wrappers
return executor(sql, params, many, context)
File "/py36-default/lib/python3.6/site-packages/django/db/backends/utils.py", line 82, in _execute
return self.cursor.execute(sql)
File "/py36-default/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/py36-default/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
self.errorhandler(self, exc, value)
File "/py36-default/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
raise errorvalue
File "/py36-default/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
res = self._query(query)
File "/py36-default/lib/python3.6/site-packages/MySQLdb/cursors.py", line 413, in _query
self._post_get_result()
File "/py36-default/lib/python3.6/site-packages/MySQLdb/cursors.py", line 417, in postget_result
self._rows = self._fetch_row(0)
File "/py36-default/lib/python3.6/site-packages/MySQLdb/cursors.py", line 385, in fetchrow
return self._result.fetch_row(size, self._fetch_type)
ValueError: invalid literal for int() with base 10: ''
When I ran SELECT @@SQL_AUTO_IS_NULL in mysql client iget the following:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.2.11-id64-release (95ae9a6)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT @@SQL_AUTO_IS_NULL;
+--------------------+
| @@sql_auto_is_null |
+--------------------+
| |
+--------------------+
1 row in set (0.00 sec)
So it looks like Sphinx returns an empty string, while the backend expects something that can be converted to an integer.
Is there sphinx search configuration/setting that can force returning the value expected by MySql backend? Any other solution?