upgrade 4.1.0 to 4.2.0 cause access to wrong table

[caesarleong]

I am using silk on a system which does not managed the table, its migration model like this

migrations.CreateModel(
            name='User',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('password', models.CharField(max_length=128, verbose_name='password')),
                ('last_login', models.DateTimeField(blank=True, null=True, verbose_name='last login')),
                ('is_superuser', models.BooleanField(default=False, help_text='Designates that this user has all permissions without explicitly assigning them.', verbose_name='superuser status')),
                ('username', models.CharField(error_messages={'unique': 'A user with that username already exists.'}, help_text='Required. 150 characters or fewer. Letters, digits and @/./+/-/_ only.', max_length=150, unique=True, validators=[django.contrib.auth.validators.UnicodeUsernameValidator()], verbose_name='username')),
                ('first_name', models.CharField(blank=True, max_length=150, verbose_name='first name')),
                ('last_name', models.CharField(blank=True, max_length=150, verbose_name='last name')),
                ('email', models.EmailField(blank=True, max_length=254, verbose_name='email address')),
                ('is_staff', models.BooleanField(default=False, help_text='Designates whether the user can log into this admin site.', verbose_name='staff status')),
                ('is_active', models.BooleanField(default=True, help_text='Designates whether this user should be treated as active. Unselect this instead of deleting accounts.', verbose_name='active')),
                ('date_joined', models.DateTimeField(default=django.utils.timezone.now, verbose_name='date joined')),
                ('display_name', models.CharField(blank=True, max_length=255, null=True)),
                ('role', models.IntegerField(choices=[(0, 'System'), (1, 'Admin'), (2, 'Operator'), (3, 'CustomerService'), (100, 'Merchant'), (200, 'Platform'), (300, 'Agent'), (1000, 'Player')], default=2)),
                ('google2fa_secret', models.CharField(max_length=100)),
                ('update_time', models.DateTimeField(auto_now=True)),
            ],
            options={
                'db_table': 'backend_user',
                'managed': False,
            },
            managers=[
                ('objects', django.contrib.auth.models.UserManager()),
            ],
        )

it is work fine on 4.1.0, but I encounter below issue on 4.2.0

2021-11-02 23:17:19,237 [WARNING][unicorn_common.middleware:34] - get_jwt_user() UnicornAuthentication failed. message: (1146, "Table 'olympus.backend_user' doesn't exist")
2021-11-02 23:17:19,296 [ERROR][django.request:224] - Internal Server Error: /games/games/
Traceback (most recent call last):
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/home/venv_olympus/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/venv_olympus/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/venv_olympus/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1146, "Table 'olympus.backend_user' doesn't exist")

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

Traceback (most recent call last):
  File "/home/venv_olympus/lib/python3.8/site-packages/asgiref/sync.py", line 482, in thread_handler
    raise exc_info[1]
  File "/home/venv_olympus/lib/python3.8/site-packages/django/core/handlers/exception.py", line 38, in inner
    response = await get_response(request)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/core/handlers/base.py", line 231, in _get_response_async
    response = await wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/asgiref/sync.py", line 444, in __call__
    ret = await asyncio.wait_for(future, timeout=None)
  File "/usr/lib/python3.8/asyncio/tasks.py", line 455, in wait_for
    return await fut
  File "/home/venv_olympus/lib/python3.8/site-packages/asgiref/current_thread_executor.py", line 22, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/asgiref/sync.py", line 486, in thread_handler
    return func(*args, **kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/viewsets.py", line 125, in view
    return self.dispatch(request, *args, **kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/views.py", line 497, in dispatch
    self.initial(request, *args, **kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/views.py", line 414, in initial
    self.perform_authentication(request)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/views.py", line 324, in perform_authentication
    request.user
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/request.py", line 227, in user
    self._authenticate()
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework/request.py", line 380, in _authenticate
    user_auth_tuple = authenticator.authenticate(self)
  File "/home/venv_olympus/lib/python3.8/site-packages/rest_framework_simplejwt/authentication.py", line 42, in authenticate
    return self.get_user(validated_token), validated_token
  File "/home/venv_olympus/lib/python3.8/site-packages/unicorn_common/authentication.py", line 25, in get_user
    user = auth_model.objects.get(pk=_id)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/models/query.py", line 425, in get
    num = len(clone)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/models/query.py", line 269, in __len__
    self._fetch_all()
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/models/query.py", line 1308, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/venv_olympus/lib/python3.8/site-packages/silk/sql.py", line 94, in execute_sql
    query_dict['analysis'] = _explain_query(q, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/silk/sql.py", line 56, in _explain_query
    cur.execute(prefixed_query, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/home/venv_olympus/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/venv_olympus/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/venv_olympus/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.ProgrammingError: (1146, "Table 'olympus.backend_user' doesn't exist")

it seems something change from 4.1.0 to 4.2.0 at below function

  File "/home/venv_olympus/lib/python3.8/site-packages/silk/sql.py", line 94, in execute_sql
    query_dict['analysis'] = _explain_query(q, params)
  File "/home/venv_olympus/lib/python3.8/site-packages/silk/sql.py", line 56, in _explain_query
    cur.execute(prefixed_query, params)

I am not sure what change cause this issue, but I think it should work the same way like 4.1.0 without error.

[auvipy]

can you please check commit history to track it down?

[j-zaballa]

I am experiencing a similar error in a multidatabase environment. Some relations from non default database with custom routing don't seem to exist. Works on v4.1.0. Haven't had the time to dig in more thoroughly. As @auvipy suggests, I'll try and track the error through the commit history and report back.

Thank you!

[rafou]

Hey there, we are experiencing the same issue when upgrading from 4.1.0 to 4.2.0 and using multiple database.

I think the problem comes from the new explaining of queries in this version which uses raw SQL to craft the explain SQL statement. The _explain_query function uses the default connection cursor, which is not always the right one to use, in a multi database configuration. You can see that line 55:
with connection.cursor() as cur:

As stated in the Django documentation, when using raw cursors with multiple databases, you have to manually select the right database to target your SQL query (cf. https://docs.djangoproject.com/en/3.2/topics/db/multi-db/#using-raw-cursors-with-multiple-databases).

I don't really know how this can be managed automatically to fix the issue (to find the right db conf based on the query itself), but I'm pretty confident this is the source of the problem for multiple database configuration (as I and @j-zaballa are experiencing)

I hope it's enough to help fix the issue @auvipy

[auvipy]

MySQLdb._exceptions.ProgrammingError: (1146, "Table 'olympus.backend_user' doesn't exist") says otherwise

[rafou]

Hum 🤔 ? It says otherwise about what ?

If the wrong database is used (which is the case for the default cursor) then yes in the first DB there is a good chance the table olympus.backend_user doesn't exists.

[glujan]

I have the same problem with my project that's using multiple databases.

As @rafou described, the bug is with the cursor not using a correct database. For now I'm monkey patching the library (I really like the row view of requests added in 4.2.0!) like this:

import silk.sql
silk.sql._explain_query = lambda q, param: None

I'm happy to work on the issue, will create a PR if I create a sensible fix for it.

[auvipy]

I will be looking forward to a proper PR.

[glujan]

@caesarleong @rafou @j-zaballa: Please see if #538 fixes the issue for you.

[j-zaballa]

@glujan thank you so much for the PR. It does indeed solve the issue :)

[rafou]

Same here, thanks a lot for the fix ! @auvipy would it be possible to release a minor version with the fix please ? 🙏

[ZacharyThomas]

@auvipy Making a minor release for this would be awesome. I have to patch silk in my repo to get it to run tests which is quite annoying