I’m building a fairly simple application, research, in my Django project that uses Django-CMS. (It’s my first ground-up attempt at a project/application.) Its main purpose is to store various intellectual assets (i.e article, book, etc. written by a researcher).
The problem is that when I point the browser to /research/ I get an error saying that the table 'research_journal' doesn't exist ("no such table").
I’m using Djnago 1.6.5 with a sqlite3 database.
Looking at python manage.py sql research yields:
BEGIN;
CREATE TABLE "research_researchbase" (
"id" integer NOT NULL PRIMARY KEY,
"pub_date" datetime NOT NULL,
"authors" varchar(200) NOT NULL,
"year" varchar(25) NOT NULL,
"title" varchar(200) NOT NULL,
"subtitle" varchar(200) NOT NULL,
"image_id" integer NOT NULL REFERENCES "filer_image" ("file_ptr_id"),
"link" varchar(200) NOT NULL
)
;
CREATE TABLE "research_journal" (
"researchbase_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "research_researchbase" ("id"),
"journal" varchar(200) NOT NULL,
"abstract" text NOT NULL,
"citation" varchar(200) NOT NULL
)
;
CREATE TABLE "research_encyclopedia_chapter" (
"researchbase_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "research_researchbase" ("id"),
"encyclopedia" varchar(200) NOT NULL,
"publisher" varchar(200) NOT NULL,
"summary" varchar(200) NOT NULL
)
;
CREATE TABLE "research_book" (
"researchbase_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "research_researchbase" ("id"),
"publisher" varchar(200) NOT NULL,
"summary" varchar(200) NOT NULL
)
;
COMMIT;
I’ve run python manage.py migrate research and get:
/Users/XXX/Documents/repos/sfs/env/lib/python2.7/site-packages/app_data/fields.py:2: DeprecationWarning: django.utils.simplejson is deprecated; use json instead.
from django.utils import simplejson as json
Running migrations for research:
- Nothing to migrate.
- Loading initial data for research.
Installed 0 object(s) from 0 fixture(s)
I’ve run python manage.py syncdb and get the following:
Syncing...
Creating tables ...
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)
Synced:
> djangocms_admin_style
> django.contrib.auth
> django.contrib.contenttypes
> django.contrib.sessions
> django.contrib.admin
> django.contrib.sites
> django.contrib.sitemaps
> django.contrib.staticfiles
> django.contrib.messages
> mptt
> south
> sekizai
> django_select2
> hvad
Not synced (use migrations):
- djangocms_text_ckeditor
- cms
- menus
- djangocms_style
- djangocms_column
- djangocms_file
- djangocms_flash
- djangocms_googlemap
- djangocms_inherit
- djangocms_link
- djangocms_picture
- djangocms_teaser
- djangocms_video
- reversion
- polls
- djangocms_polls
- aldryn_blog
- easy_thumbnails
- filer
- taggit
- research
(use ./manage.py migrate to migrate these)
Here’s the models.py:
from django.db import models
from django.utils import timezone
from filer.fields.image import FilerImageField
import datetime
class ResearchBase(models.Model):
pub_date = models.DateTimeField('date published')
authors = models.CharField(max_length=200)
year = models.CharField(max_length=25)
title = models.CharField(max_length=200)
subtitle = models.CharField(max_length=200, blank=True)
image = FilerImageField()
link = models.CharField(max_length=200, blank=True)
def __unicode__(self):
return self.title
def was_published_recently(self):
return self.pub_date >= timezone.now() - datetime.timedelta(days=1)
class Journal(ResearchBase):
journal = models.CharField(max_length=200)
abstract = models.TextField()
citation = models.CharField(max_length=200)
class Encyclopedia_Chapter(ResearchBase):
encyclopedia = models.CharField(max_length=200)
publisher = models.CharField(max_length=200)
summary = models.CharField(max_length=200)
class Book(ResearchBase):
publisher = models.CharField(max_length=200)
summary = models.CharField(max_length=200)
Here’s my views.py (note that I am passing two objects through render, ignore the fact that I have yet to include the class Books in the whole deal):
from django.shortcuts import render, get_object_or_404
from django.http import HttpResponse, Http404
from django.template import RequestContext, loader
from research.models import Journal, Encyclopedia_Chapter, Book
def research_index(request):
latest_journal_list = Journal.objects.order_by('-pub_date')[:5]
latest_chapter_list = Encyclopedia_Chapter.objects.order_by('-pub_date')[:5]
context = {
'latest_journal_list': latest_journal_list,
'latest_chapter_list': latest_chapter_list
}
return render(request, 'research/index.html', context)
def journal_detail(request, journal_id):
journal = get_object_or_404(Journal, pk=journal_id)
return render(request, 'research/journal_detail.html', {'journal': journal})
def chapter_detail(request, chapter_id):
chapter = get_object_or_404(Encyclopedia_Chapter, pk=chapter_id)
return render(request, 'research/chapter_detail.html', {'chapter': chapter})
Here’s the application’s url.py:
from django.conf.urls import patterns, url
from research import views
urlpatterns = patterns('',
url(r'^$', views.research_index, name='research'),
url(r'^(?P<journal_id>d+)/$', views.journal_detail, name='journal_detail'),
url(r'^(?P<chapter_id>d+)/$', views.chapter_detail, name='chapter_detail'),
)
Here’s the index.html template:
{% extends 'research/base.html' %}
{% block research_content %}
<div class="container">
<div class="row featurette">
<h3 id="research">Peer-reviewed Journal Articles</h3>
{% if latest_journal_list %}
<ul id="research">
{% for journal in latest_journal_list %}
<li id="research">
<img src="{{ journal.image.url }}" id="research">
<h4>{{ journal.journal }}</h4>
<h5>{{ journal.title }}</h5>
<a href="{% url 'research:journal_detail' journal.id %}">Read More</a>
</li>
{% endfor %}
</ul>
{% else %}
<p>No journals are available.</p>
{% endif %}
</div>
<div class="row featurette">
<h3 id="research">Encyclopedia Chapters</h3>
{% if latest_chapter_list %}
<ul id="research">
{% for chapter in latest_chapter_list %}
<li id="research">
<img src="{{ chapter.image.url }}" id="research">
<h4>{{ chapter.journal }}</h4>
<h5>{{ chapter.title }}</h5>
<a href="{% url 'research:chapter_detail' chapter.id %}">Read More</a>
</li>
{% endfor %}
</ul>
{% else %}
<p>No encyclopedia chapters are available.</p>
{% endif %}
</div>
</div>
{% endblock %}
Just in case it matters, here’s my cms_app.py:
from cms.app_base import CMSApp
from cms.apphook_pool import apphook_pool
from django.utils.translation import ugettext_lazy as _
class ResearchApp(CMSApp):
name = _("Research App")
urls = ["research.urls"]
app_name = "research"
apphook_pool.register(ResearchApp)
При выполнении manage.py makemigrations появляется ошибка о том, что таблица (на которую надо сделать миграцию) не существует:
Список вызовов
Traceback (most recent call last):
File "manage.py", line 22, in <module>
execute_from_command_line(sys.argv)
File "D:enviropmentsNewElionlibsite-packagesdjangocoremanagement__init__.py", line 367, in execute_from_command_line
utility.execute()
File "D:enviropmentsNewElionlibsite-packagesdjangocoremanagement__init__.py", line 359, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "D:enviropmentsNewElionlibsite-packagesdjangocoremanagementbase.py", line 294, in run_from_argv
self.execute(*args, **cmd_options)
File "D:enviropmentsNewElionlibsite-packagesdjangocoremanagementbase.py", line 342, in execute
self.check()
File "D:enviropmentsNewElionlibsite-packagesdjangocoremanagementbase.py", line 374, in check
include_deployment_checks=include_deployment_checks,
File "D:enviropmentsNewElionlibsite-packagesdjangocoremanagementbase.py", line 361, in _run_checks
return checks.run_checks(**kwargs)
File "D:enviropmentsNewElionlibsite-packagesdjangocorechecksregistry.py", line 81, in run_checks
new_errors = check(app_configs=app_configs)
File "D:enviropmentsNewElionlibsite-packagesdjangocorechecksurls.py", line 14, in check_url_config
return check_resolver(resolver)
File "D:enviropmentsNewElionlibsite-packagesdjangocorechecksurls.py", line 24, in check_resolver
for pattern in resolver.url_patterns:
File "D:enviropmentsNewElionlibsite-packagesdjangoutilsfunctional.py", line 35, in __get__
res = instance.__dict__[self.name] = self.func(instance)
File "D:enviropmentsNewElionlibsite-packagesdjangourlsresolvers.py", line 313, in url_patterns
patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
File "D:enviropmentsNewElionlibsite-packagesdjangoutilsfunctional.py", line 35, in __get__
res = instance.__dict__[self.name] = self.func(instance)
File "D:enviropmentsNewElionlibsite-packagesdjangourlsresolvers.py", line 306, in urlconf_module
return import_module(self.urlconf_name)
File "D:Python27Libimportlib__init__.py", line 37, in import_module
__import__(name)
File "D:YandexDiskWORK!Projects DjangotestElionElionurls.py", line 5, in <module>
from about import views as about_views
File "D:YandexDiskWORK!Projects DjangotestElionaboutviews.py", line 6, in <module>
from .forms import ContactMessageForm, SubmitApplication
File "D:YandexDiskWORK!Projects DjangotestElionaboutforms.py", line 42, in <module>
class SubmitApplication(forms.Form):
File "D:YandexDiskWORK!Projects DjangotestElionaboutforms.py", line 43, in SubmitApplication
regions = [(obj.id, obj.region) for obj in AreasWork.objects.all()]
File "D:enviropmentsNewElionlibsite-packagesdjangodbmodelsquery.py", line 256, in __iter__
self._fetch_all()
File "D:enviropmentsNewElionlibsite-packagesdjangodbmodelsquery.py", line 1087, in _fetch_all
self._result_cache = list(self.iterator())
File "D:enviropmentsNewElionlibsite-packagesdjangodbmodelsquery.py", line 54, in __iter__
results = compiler.execute_sql()
File "D:enviropmentsNewElionlibsite-packagesdjangodbmodelssqlcompiler.py", line 835, in execute_sql
cursor.execute(sql, params)
File "D:enviropmentsNewElionlibsite-packagesdjangodbbackendsutils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "D:enviropmentsNewElionlibsite-packagesdjangodbbackendsutils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "D:enviropmentsNewElionlibsite-packagesdjangodbutils.py", line 94, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "D:enviropmentsNewElionlibsite-packagesdjangodbbackendsutils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "D:enviropmentsNewElionlibsite-packagesdjangodbbackendssqlite3base.py", line 337, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such table: AreasWorkВ этом списке вызовов показано, что вызывается forms.py этого приложения в том месте где есть обращение к данной таблице:
# Elionaboutforms.py:
class SubmitApplication(forms.Form):
regions = [(obj.id, obj.region) for obj in AreasWork.objects.all()] # Ошибка в этой строке
choice_region = [(None, 'Выберите город')] + regions + [('0', 'Другой')]
region = forms.ChoiceField(label='Область', choices=choice_region,
widget=forms.Select(attrs={'class': 'form-control'}))
............
Если в этом месте убрать обращение к БД, то ошибок больше никаких не появляется. Даже после выполнения manage.py migrate и в forms.py вернуть код обратно, то всё будет работать нормально, без ошибок.
Также, я нашёл, что у кого-то была аналогичная проблема и тоже ошибка в файле forms.py при обращении к БД.
Понятно, что для решения проблемы, надо тупо убрать обращение к БД в форме, но мне интересно, почему вообще такое происходит и что я делаю не правильно?
Для нового проекта, нового приложения при миграции базы данных были выполнены следующие две команды:
python manage.py makemigrations teacher_app, первый шаг успешен
python manage.py migrate teacher_app, второй шаг завершился неудачно с django.db.utils.OperationalError: нет такой таблицы: django_content_type
Решение:
python manage.py makemigrations teacher_app
python manage.py migrateпричина:
migrate,Быть ответственным заВерныйINSTALLED_APPSПриложения вмигрировать.makemigrations, Отвечает за создание новой миграции на основе модификации вашей моделиsqlmigrate, Показать перенесенный оператор sqlshowmigrations, В котором перечислены миграция и статус проекта.
Python manger.py makemigrations: в текущем каталоге будет создана папка миграции. Содержимое папки — это содержимое, которое будет выполняться базой данных. Будут сгенерированы операторы SQL. Для просмотра определенных операторов SQL можно использовать приложение python manger.py sqlmigrate 0001.
python manager.py migrate: выполнить созданный ранее файл миграции, применить действие к файлу базы данных и сгенерировать таблицу.
python manage.py migrate app_name: выполняйте файл миграции только в приложении app_name, создайте соответствующую таблицу и не выполняйте другие файлы миграции. Но после создания нового проекта некоторые файлы миграции будут автоматически сгенерированы для нас.Только после того, как эти файлы миграции по умолчанию будут выполнены и таблицы по умолчанию (такие как: django_content_type, auth_permission и т. Д.), Мы сможем выполнить наши собственные миграции. Файл для создания нужной нам таблицы.
Конкретный процесс решения проблемы.
Моя среда: Centos6.8 + anaconda + pycharm + python3.7 + Django1.8
Это процесс создания моего проекта:
1. Создайте проект
(Django) [[email protected] Django]# django-admin startproject teacher
(Django) [[email protected] Django]# cd teacher
(Django) [[email protected] teacher]# python manage.py startapp teacher_app2. Напишите класс в файле models.py приложения teacher_app.
from django.db import models
# Create your models here.
class Teacher(models.Model):
name = models.CharField(max_length=20)
age = models.IntegerField()
address = models.CharField(max_length=20)
email = models.CharField(max_length=20)
def __str__(self):
return self.name3. Добавьте приложение в настройку
INSTALLED_APPS = (
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'teacher_app',
)4. Выполните миграцию базы данных:
(Django) [ro[email protected] teacher]# python manage.py makemigrations teacher_app
Migrations for ‘teacher_app’:
0001_initial.py:
— Create model Teacher
(Django) [[email protected] teacher]# python manage.py migrate teacher_app
Второй шаг не удался. Сообщение об ошибке: Django.db.utils.OperationalError: нет такой таблицы: django_content_type,
«Error creating new content types. Please make sure contenttypes «
RuntimeError: Error creating new content types. Please make sure contenttypes is migrated before trying to migrate apps individually.
Конкретное сообщение об ошибке:
(Django) [[email protected] teacher]# python manage.py makemigrations teacher_app
Migrations for 'teacher_app':
0001_initial.py:
- Create model Teacher
(Django) [[email protected] teacher]# python manage.py migrate teacher_app
Operations to perform:
Apply all migrations: teacher_app
Running migrations:
Rendering model states... DONE
Applying teacher_app.0001_initial... OK
Traceback (most recent call last):
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 318, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such table: django_content_type
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/contrib/contenttypes/models.py", line 65, in get_for_model
ct = self.get(app_label=opts.app_label, model=opts.model_name)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/manager.py", line 127, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 328, in get
num = len(clone)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 144, in __len__
self._fetch_all()
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 965, in _fetch_all
self._result_cache = list(self.iterator())
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 238, in iterator
results = compiler.execute_sql()
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 829, in execute_sql
cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/utils.py", line 97, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/utils/six.py", line 658, in reraise
raise value.with_traceback(tb)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 318, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such table: django_content_type
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
utility.execute()
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/__init__.py", line 330, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/base.py", line 390, in run_from_argv
self.execute(*args, **cmd_options)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/base.py", line 441, in execute
output = self.handle(*args, **options)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 225, in handle
emit_post_migrate_signal(created_models, self.verbosity, self.interactive, connection.alias)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/sql.py", line 280, in emit_post_migrate_signal
using=db)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/dispatch/dispatcher.py", line 201, in send
response = receiver(signal=self, sender=sender, **named)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/contrib/auth/management/__init__.py", line 82, in create_permissions
ctype = ContentType.objects.db_manager(using).get_for_model(klass)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/contrib/contenttypes/models.py", line 78, in get_for_model
"Error creating new content types. Please make sure contenttypes "
RuntimeError: Error creating new content types. Please make sure contenttypes is migrated before trying to migrate apps individually.В сообщении об ошибке мне предлагалось убедиться, что типы содержимого были перенесены при миграции базы данных. Затем я начал перенос типов содержимого, но по-прежнему сообщил об ошибке «django.db.utils.OperationalError: нет такой таблицы: auth_permission», а именно:
(Django) [[email protected] teacher]# python manage.py migrate contenttypes
Operations to perform:
Apply all migrations: contenttypes
Running migrations:
Rendering model states... DONE
Applying contenttypes.0001_initial... OK
Applying contenttypes.0002_remove_content_type_name... OK
Traceback (most recent call last):
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 318, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such table: auth_permission
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
utility.execute()
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/__init__.py", line 330, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/base.py", line 390, in run_from_argv
self.execute(*args, **cmd_options)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/base.py", line 441, in execute
output = self.handle(*args, **options)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 225, in handle
emit_post_migrate_signal(created_models, self.verbosity, self.interactive, connection.alias)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/core/management/sql.py", line 280, in emit_post_migrate_signal
using=db)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/dispatch/dispatcher.py", line 201, in send
response = receiver(signal=self, sender=sender, **named)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/contrib/auth/management/__init__.py", line 93, in create_permissions
"content_type", "codename"
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 162, in __iter__
self._fetch_all()
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 965, in _fetch_all
self._result_cache = list(self.iterator())
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/query.py", line 1220, in iterator
for row in compiler.results_iter():
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 783, in results_iter
results = self.execute_sql(MULTI)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 829, in execute_sql
cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/utils.py", line 97, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/utils/six.py", line 658, in reraise
raise value.with_traceback(tb)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/root/anaconda3/envs/Django/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 318, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such table: auth_permissionПозже я проверил информацию и узнал, что такие таблицы, как django_content_type, auth_permission и т. Д., Были созданы по умолчанию. Сначала я подумал, что это проблема с моей версией Django, но когда я узнал из видео раньше, все работало нормально. Похоже, что моя предыдущая операция не прошла.
Продолжаю отладку, ввожу команду: python manage.py showmigrations, эта командаПоказываетDjangoВсе в проектеmigrationsФайлы и их статус,
[x]От имени завершенногоmigrationsФайл, []Указывает на файлы, которые не были выполнены или не были выполнены.
Оказывается, я не реализовал их для нашей конфигурации по умолчанию.migrations. Далее продолжаю выполнять команду:python manage.py migrate , На этот раз без названия приложения.
Хорошо, выполнение выполнено успешно, проверьте еще раз, выполните python manage.py showmigrations, все миграции выполнены.
Далее я определю, в порядке ли база данных, запрошу данные, и дисплей в порядке.
When we tried to run django server after copied from one directory to another and deleting the cache files __pycache__ and database db.sqlite3 so that we can run the server at new location, suddenly we got an error as below,
django.db.utils.OperationalError: no such table:
This seems possibly that we deleted the database and cache manually and didn’t follow the initialisation steps properly since we just copied the code from one directory to another. So instead of doing all steps again, we can resolve this error by following steps,
Solution
$ python manage.py makemigrations
$ python manage.py migrate --run-syncdb
$ python manage.py migrate
You can refer more about Django & REST at
Installing Django and Django-REST Framework on ubuntu
November 19, 2019
Debugging an Obscure Django Problem
- Behavior
- Environment
- Known Issues
- Observed Behaviors
- Troubleshooting
- Original Python Code
- Raw SQL in Django
- Connecting Directly to MySQL
- First solution attempt
- Second solution attempt
- Third Solution Attempt
- RDS Logs
- Packet Analysis
- Wireshark Analysis
- Malformed Packets Galore
- MySQL End of File
- Django Bug Report
- Fourth Solution Attempt
- Fifth Solution Attempt
- Sixth Solution Attempt
- Solution
- Other Observations
A journey in debugging a Django issue.
Behavior
Yesterday, we started to see this crash in develop after we deployed a new backend.
error (2027, 'Malformed packet')
<class 'django.db.utils.OperationalError'> on line 513
Where line 513 executed the QuerySet.
This issue only occurs when querying two IDs, 0475776 and 0975933. All other single ID and all multiple ID queries work, even those with either or both of these IDs.
This issue also only occurred on develop, not locally, even though the database is the same.
Environment
- MySQL 5.7.12 (MySQL Community Server (GPL))
- Hosted on AWS RDS
- Amazon Linux 2
- Django 2.1.3
- mysqlclient 1.4.4
- mariadb-devel-5.5.64-1.amzn2.x86_64
Known Issues
According to MySQL bug 77298, prior to 5.7.8, a bug eerily similar to mine caused this issue:
For small values of the read_rnd_buffer_size system variable, internal caching of temporary results could fail and cause query execution failure.
Since we are using a newer version, this couldn’t be the issue.
According to a StackExchange thread, we may have max_allowed_packet set too low. However, we are able to get the data for this query by modifying some parameters, so this cannot be the issue either. Further, our Wireshark analysis shows all packets are smaller than the default value of 4194304 bytes.
Observed Behaviors
- Changing any query parameters will make them successful
- Removing any parameters will make it work as well
- Adding a
LIMIT, even if it is larger than the data returned, will make it work - Only a few
prov_iditems have this issue - Combining a broken
prov_idwith aprov_idthat has no rows works
Troubleshooting
This is a perplexing problem. I did not want to monkey patch it like this when the QuerySet gets executed:
try:
ingested_df = pd.DataFrame.from_records(data=ingested, coerce_float=True)
except Exception as e:
LOGGER.error('%s: %s, on line %s', e, e.__class__, e.__traceback__.tb_lineno)
LOGGER.error('Monkey patching query with `LIMIT = 99999999999')
ingested = ingested[:99999999999]
ingested_df = pd.DataFrame.from_records(data=ingested, coerce_float=True)
Instead, I tried to make a few changes to the QuerySet chaining steps.
Original Python Code
# Initial QuerySet, filter on dates, med status, and provider
ingested = Model.objects.filter(Q(beg_date__range=prior_range) |
Q(beg_date__range=current_range),
med_cat='N',
prov_id__in=facility_ids,
).values(
'prov_id',
'beg_date',
'method',
'category',
'code',
'allowed',
'count',
'charge'
)
# Handle removing nulls and blanks
if remove_null:
ingested = ingested.exclude(Q(category__isnull=True) | Q(code__isnull=True))
if remove_blank:
# Handle case where empty strings were stored as string literal `""`
ingested = ingested.exclude(Q(category__exact='') | Q(code__exact='') |
Q(category__exact='""') | Q(code__exact='""'))
# Handle excluding categories from api list param
ingested = ingested.exclude(category__in=excluded_categories)
# Handle payment type filter
if payment_method != 'all':
ingested = ingested.filter(method=payment_method)
Raw SQL in Django
Manually making the query by writing the same thing in raw SQL yields the same results.
SELECT `tables_ingesting`.`clm_id_svc_ln_num`,
`tables_ingesting`.`prov_id`,
`tables_ingesting`.`beg_date`,
`tables_ingesting`.`method`,
`tables_ingesting`.`category`,
`tables_ingesting`.`code`,
`tables_ingesting`.`allowed`,
`tables_ingesting`.`count`,
`tables_ingesting`.`charge`,
`tables_ingesting`.`count`
FROM `tables_ingesting`
WHERE ( ( `tables_ingesting`.`beg_date` BETWEEN '2018-10-01' AND '2018-12-31'
OR `tables_ingesting`.`beg_date` BETWEEN '2019-01-01' AND '2019-03-31' )
AND `tables_ingesting`.`prov_id` IN ( '0475776' )
AND `tables_ingesting`.`medcr_ind` = 'N'
AND NOT (( `tables_ingesting`.`category` IS NULL
OR `tables_ingesting`.`code` IS NULL ))
AND NOT (( `tables_ingesting`.`category` = '""'
OR `tables_ingesting`.`code` = '""'
OR `tables_ingesting`.`category` = ""
OR `tables_ingesting`.`code` = "" ))
);
Changing anything about the queries make them successful: for example, 2019-03-31 end date fails, but using 2019-03-30 works, even though neither of those dates have any data (thus the data is the same). Removing any of the filters will make it work as well, even when the filter does not match any rows.
Adding a LIMIT clause to the query makes it work, even if the limit is greater than the number of rows returned.
Connecting Directly to MySQL
The query Django prints out returns the correct data when sent directly to MySQL:
mysql> SELECT `tables_ingesting`.`prov_id`, `tables_ingesting`.`beg_date`, `tables_ingesting`.`method`, `tables_ingesting`.`category`, `tables_ingesting`.`code`, `tables_ingesting`.`allowed`, `tables_ingesting`.`count`, `tables_ingesting`.`charge` FROM `tables_ingesting` WHERE ((`tables_ingesting`.`beg_date` BETWEEN '2018-10-01' AND '2018-12-31' OR `tables_ingesting`.`beg_date` BETWEEN '2019-01-01' AND '2019-03-31') AND `tables_ingesting`.`prov_id` IN (0975933) AND `tables_ingesting`.`medcr_ind` = 'N' AND NOT ((`tables_ingesting`.`category` IS NULL OR `tables_ingesting`.`code` IS NULL)) AND NOT ((`tables_ingesting`.`category` = '' OR `tables_ingesting`.`code` = '' OR `tables_ingesting`.`category` = '""' OR `tables_ingesting`.`code` = '""')));
...
12587 rows in set (0.29 sec)
First solution attempt
I noticed that when I built the QuerySet I asked for the same column twice:
ingested.values('prov_id',
'beg_date',
'method',
'category',
'code',
'allowed',
'count',
'charge',
'count')
I removed the second count. Since the query started working with this change, we merged it to develop. Once we verified it was working there, we deployed the new UI/Backend this was part of to production.
After this deploy, both production and develop worked, but local no longer worked. Again, local and develop use the same backend.
Second solution attempt
In the local server, when the QuerySet got executed, we would get back no data, even though the QuerySet had preview data before it got executed. Further, the same behavior where adding a limit made it work came back. Instead of the malformed packet error, however, we now were getting this:
error (2013, 'Lost connection to MySQL server during query')
<class 'django.db.utils.OperationalError'> on line 511
Where line 511 was where the QuerySet got executed.
Again, changing any aspect of the query made it work with the exact same behavior of the previous problem in develop. Since this only happened to single providers, I thought it might have been an issue with the following code:
ingested = Model.objects.filter(Q(beg_date__range=prior_range) |
Q(beg_date__range=current_range),
med_cat='N',
prov_id__in=facility_ids,
)
The parameter prov_id__in=facility_ids generates the following SQL:
...
AND `tables_ingesting`.`prov_id` IN ( '0475776' )
...
Replacing that Model block with the following yielded a solution:
ingested = Model.objects.filter(Q(beg_date__range=prior_range) |
Q(beg_date__range=current_range),
med_cat='N')
if len(facility_ids) > 1:
ingested = ingested.filter(prov_id__in=facility_ids)
else:
ingested = ingested.filter(prov_id=facility_ids[0])
This means we now generate this SQL for a single provider:
...
AND `tables_ingesting`.`prov_id` = '0475776'
...
While this works, it does not explain either MySQL error, which worries me with regard to using this code in production.
Third Solution Attempt
This patch did not last long, as over the weekend we started to see (2027, 'Malformed packet') show up in the logs again, but only in production and develop, not locally, which is the same as before we implemented either fix.
RDS Logs
RDS Logs showed the following:
2019-10-28T17:28:05.868458Z 1308445 [Note] Aborted connection 1308445 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error reading communication packets)
2019-10-28T17:28:10.332017Z 1308446 [Note] Aborted connection 1308446 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error reading communication packets)
2019-10-28T17:28:14.701596Z 1308447 [Note] Aborted connection 1308447 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error reading communication packets)
2019-10-28T17:28:19.259719Z 1308448 [Note] Aborted connection 1308448 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error reading communication packets)
2019-10-28T17:29:25.401275Z 1308449 [Note] Aborted connection 1308449 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error reading communication packets)
2019-10-28T17:29:25.413090Z 1308451 [Note] Aborted connection 1308451 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error writing communication packets)
2019-10-28T17:29:29.053986Z 1308494 [Note] Aborted connection 1308494 to db: 'tables_ingesting' user: 'client' host: '10.x.x.x' (Got an error reading communication packets)
So, something is going wrong with the MySQL instance in reading the packets that Django sends. The Oracle reference1 tells us the following info:
Error number: 1158; Symbol: ER_NET_READ_ERROR; SQLSTATE: 08S01
Message: Got an error reading communication packets
However, making additional bad requests does not get anything logged to this file, so I am not sure that it is related.
Packet Analysis
To analyze the packets sent to and from the Django server, we can leverage tcpdump:
sudo tcpdump -i any -w ~/captures/capture_%Y-%m-%d-%H-%M-%S.cap -G 30 -n -X -Z $USER "port 8000"
- Any type of connection on port 8000, where the server runs
- Format for Wireshark
We can download this file with:
scp -i cert.pem [email protected]:captures/capture_2019-10-29-13-30-15.cap .
This will download captures/capture_2019-10-29-13-30-15.cap to our local .
Wireshark Analysis
Opening up this file in Wireshark gave us some interesting information.
Malformed Packets Galore
There are a ton of packets that look fine:
0000 31 00 00 33 07 30 39 37 35 39 33 33 0a 32 30 31 1..3.0975933.201
0010 39 2d 30 33 2d 30 38 00 01 31 05 39 33 30 31 37 9-03-08..1.93017
0020 06 33 35 33 2e 30 30 05 31 2e 30 30 30 07 33 36 .353.00.1.000.36
0030 37 30 2e 38 31 70.81
Or, more easily understood in the Wireshark tree:
MySQL Protocol
Packet Length: 49
Packet Number: 52
Catalog: 0975933
Database: 2019-03-08
Table:
Original table: 1
Name: 93306
Original name: 708
Charset number: Unknown (11825)
Length: 120598576
Type: Unknown (51)
Flags: 0x3133
.... .... .... ...1 = Not null: Set
.... .... .... ..1. = Primary key: Set
.... .... .... .0.. = Unique key: Not set
.... .... .... 0... = Multiple key: Not set
.... .... ...1 .... = Blob: Set
.... .... ..1. .... = Unsigned: Set
.... .... .0.. .... = Zero fill: Not set
.... .... 0... .... = Binary: Not set
.... ...1 .... .... = Enum: Set
.... ..0. .... .... = Auto increment: Not set
.... .0.. .... .... = Timestamp: Not set
.... 0... .... .... = Set: Not set
Decimals: 51
All of these packets were smaller (about 50 bytes each) than the max_allowed_packet of 4194304 bytes. However, these good packets were punctuated by several warnings in Wireshark for Malformed Packets, but no other information:
[Malformed Packet: MySQL]
[Expert Info (Error/Malformed): Malformed Packet (Exception occurred)]
[Malformed Packet (Exception occurred)]
[Severity level: Error]
[Group: Malformed]
MySQL End of File
The end of file packet in WireShark throws a dissector is incomplete error for broken queries as well:
MySQL Protocol
Packet Length: 7
Packet Number: 53
Response Code: EOF Packet (0xfe)
EOF marker: 254
Warnings: 0
Server Status: 0x0022
.... .... .... ...0 = In transaction: Not set
.... .... .... ..1. = AUTO_COMMIT: Set
.... .... .... .0.. = Multi query / Unused: Not set
.... .... .... 0... = More results: Not set
.... .... ...0 .... = Bad index used: Not set
.... .... ..1. .... = No index used: Set
.... .... .0.. .... = Cursor exists: Not set
.... .... 0... .... = Last row sent: Not set
.... ...0 .... .... = Database dropped: Not set
.... ..0. .... .... = No backslash escapes: Not set
.... .0.. .... .... = Metadata changed: Not set
.... 0... .... .... = Query was slow: Not set
...0 .... .... .... = PS Out Params: Not set
..0. .... .... .... = In Trans Readonly: Not set
.0.. .... .... .... = Session state changed: Not set
Payload: 0000
[Expert Info (Warning/Undecoded): FIXME - dissector is incomplete]
[FIXME - dissector is incomplete]
[Severity level: Warning]
[Group: Undecoded]
However, this may be a problem with Wireshark not understanding the end of a MySQL TCP stream when the stream contains malformed packets.
Django Bug Report
At this point, I did not know where to turn, so I filed a bug report on the Django project. They closed it as an issue in a MySQL driver, database engine, or in database schema.
Fourth Solution Attempt
The only recommendation I found online was to increase the value for max_allowed_packet2. I did not do this at first because of the reasons listed under Observed Behaviors. By default, my instance was set like:
mysql> SHOW VARIABLES LIKE '%_packet';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 4194304 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.09 sec)
Not understanding what units were used when setting the constant, I assumed 1024 would be 1Gb, so I set it to:
mysql> SHOW VARIABLES LIKE '%_packet';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 1024 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
This made all of my requests start working! However, other people using this database started reporting issues where they would get an error like (1153, "Got a packet larger than 'max_allowed_packet' bytes"). This made me realize that I entered the units wrong, so I set the value to 50mb:
mysql> SHOW VARIABLES LIKE '%_packet';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 49999872 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.04 sec)
This also worked, which confused me. How is it possible that both large and small values, but not the default, will break my queries? to test this, I set max_allowed_packet back to the default of 4194304 and all of the queries still worked, but after 10-15 minutes would start failing again. Back to the drawing board.
Unfortunately, we can’t directly access the RDS nodes, so the root cause of this issue is impossible to diagnose. Sometimes, the advice «turn it on and off again» really does fix your problem, but this case it was not the solution.
Fifth Solution Attempt
The docs for the recommended Django MySQL interface, mysqlclient, tell us to run sudo yum install python-devel mysql-devel to ensure we have all of the dependencies. This line was in the CloudFormation file, so we assumed it was working as we expected. However, when we manually tried these install steps:
[[email protected] ~]$ sudo yum install mysql-devel
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
amzn2-core | 2.4 kB 00:00:00
Package 1:mariadb-devel-5.5.64-1.amzn2.x86_64 already installed and latest version
Instead of getting mysql-devel, we actually get mariadb-devel, specifically an old version 2 full releases and a hard fork behind the database on RDS3. To fix this, we have to first uninstall the Amazon Linux repo’s version of maria-devel and install MySQL’s repository and then install the mysql-devel package from them:
To uninstall the maria-devel package:
[[email protected] ~]$ yum list installed mariadb*
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
Installed Packages
mariadb-devel.x86_64 1:5.5.64-1.amzn2 @amzn2-core
mariadb-libs.x86_64 1:5.5.64-1.amzn2 @amzn2-core
[[email protected] ~]$ sudo yum remove mariadb-libs.x86_64 mariadb-devel.x86_64
Next, install the latest MySQL RPM package from the MySQL Yum repository download page. First, we need to install the community certificate so that we can download from these repositories:
cd /etc/pki/rpm-gpg/
sudo wget https://repo.mysql.com/RPM-GPG-KEY-mysql
Next, we can install the repository itself:
wget https://dev.mysql.com/get/mysql80-community-release-el6-3.noarch.rpm
sudo yum localinstall mysql80-community-release-el6-3.noarch.rpm
We specifically need the REHL 6 repository; the REHL 7 and 8 repositories expect newer versions of system libraries and will result in errors like this:
--> Finished Dependency Resolution
Error: Package: mysql-community-libs-8.0.18-1.el8.x86_64 (mysql80-community)
Requires: libssl.so.1.1(OPENSSL_1_1_0)(64bit)
Error: Package: mysql-community-libs-8.0.18-1.el8.x86_64 (mysql80-community)
Requires: libssl.so.1.1()(64bit)
Error: Package: mysql-community-libs-8.0.18-1.el8.x86_64 (mysql80-community)
Requires: libssl.so.1.1(OPENSSL_1_1_1)(64bit)
Error: Package: mysql-community-libs-8.0.18-1.el8.x86_64 (mysql80-community)
Requires: libc.so.6(GLIBC_2.28)(64bit)
Error: Package: mysql-community-libs-8.0.18-1.el8.x86_64 (mysql80-community)
Requires: libcrypto.so.1.1()(64bit)
Error: Package: mysql-community-libs-8.0.18-1.el8.x86_64 (mysql80-community)
Requires: libcrypto.so.1.1(OPENSSL_1_1_0)(64bit)
...74 lines truncated...
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest
To verify that the repo was installed, we can run:
[[email protected] ~]$ yum repolist enabled | grep "mysql.*-community.*"
[[email protected] ~]$ yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64 MySQL Connectors Community 84+29
mysql-tools-community/x86_64 MySQL Tools Community 84
mysql80-community/x86_64 MySQL 8.0 Community Server 127
[[email protected] ~]$ yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community disabled
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community disabled
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community disabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 84+29
mysql-connectors-community-source MySQL Connectors Community disabled
mysql-tools-community/x86_64 MySQL Tools Community enabled: 84
mysql-tools-community-source MySQL Tools Community - Sou disabled
mysql-tools-preview/x86_64 MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview - Sourc disabled
mysql55-community/x86_64 MySQL 5.5 Community Server disabled
mysql55-community-source MySQL 5.5 Community Server disabled
mysql56-community/x86_64 MySQL 5.6 Community Server disabled
mysql56-community-source MySQL 5.6 Community Server disabled
mysql57-community/x86_64 MySQL 5.7 Community Server disabled
mysql57-community-source MySQL 5.7 Community Server disabled
mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 127
mysql80-community-source MySQL 8.0 Community Server disabled
This defaults to only activating the 8.x version of MySQL, which is not the match for our database version. We need to use the 5.7.x version, so we have to edit /etc/yum.repos.d/mysql-community.repo to say:
# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Once we verified the proper repositories are enabled worked, we can run sudo yum install mysql-devel to get the proper package. I ran into issues downloading this:
... (truncated)
mysql-community-devel-5.7.28-1 FAILED
http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/mysql-community-devel-5.7.28-1.el6.x86_64.rpm: [Errno 12] Timeout on http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/mysql-community-devel-5.7.28-1.el6.x86_64.rpm: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 5 seconds')
Trying other mirror.
mysql-community-devel-5.7.28-1 FAILED
http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/mysql-community-devel-5.7.28-1.el6.x86_64.rpm: [Errno 12] Timeout on http://repo.mysql.com/yum/mysql-5.7-community/el/6/x86_64/mysql-community-devel-5.7.28-1.el6.x86_64.rpm: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 5 seconds')
Trying other mirror.
Error downloading packages:
mysql-community-devel-5.7.28-1.el6.x86_64: [Errno 256] No more mirrors to try.
To solve this, I set timeout = 60 and minrate=1 in /etc/yum.conf which solved the issue.
Once MysQL 5.7 was installed, we needed to uninstall and reinstall the pip‘s instance of mysqlclient because it expects to see maria-devel and not our newer mysql-devel.
This seemed promising, as now our database version and driver version are aligned, but when we try and start Django we get this traceback:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 83, in _execute
return self.cursor.execute(sql)
File "/usr/local/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/usr/local/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
res = self._query(query)
File "/usr/local/lib64/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
db.query(q)
File "/usr/local/lib64/python3.7/site-packages/MySQLdb/connections.py", line 226, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')
Other than that, though, the server would start fine and all of our requests went through okay, so why was this crashing so hard? I reverted back to the default versions by spinning up a new instance. After redeploying, I noticed a new log message.
Sixth Solution Attempt
Once all of the modules were installed, Django would start with the following warning log:
MySQL Strict Mode is not set for database connection 'default'
The server would run fine, but this warning did not happen on local. To address it, we can add an init_command to the default database configuration
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'model_data',
'USER': get_env_variable('DBUSER'),
'PASSWORD': get_env_variable('DBPW'),
'HOST': 'dev.sql.internal.net',
'OPTIONS': {'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"}
}
Solution
The above change to the init_command solved this. SSHing in and removing that line will instantly kill the server connection and start logging the malformed packet error, and putting it back will make the queries start working again.
Other Observations
Django does not properly format the query when printing QuerySet.query(), rather it string formats the entire thing, which removes all of the quotes. The query under troubleshooting looks like this when printed 4
SELECT `tables_ingesting`.`prov_id`,
`tables_ingesting`.`beg_date`,
`tables_ingesting`.`method`,
`tables_ingesting`.`category`,
`tables_ingesting`.`code`,
`tables_ingesting`.`allowed`,
`tables_ingesting`.`count`,
`tables_ingesting`.`charge`
FROM `tables_ingesting`
WHERE ((
`tables_ingesting`.`beg_date` BETWEEN 2018-10-01 AND 2018-12-31
OR `tables_ingesting`.`beg_date` BETWEEN 2019-01-01 AND 2019-03-31)
AND `tables_ingesting`.`medcr_ind` = N
AND `tables_ingesting`.`prov_id` IN (0975933)
AND NOT ((
`tables_ingesting`.`category` IS NULL
OR `tables_ingesting`.`code` IS NULL))
AND NOT ((
`tables_ingesting`.`category` =
OR `tables_ingesting`.`code` =
OR `tables_ingesting`.`category` = ""
OR `tables_ingesting`.`code` = ""))
);
However, this is due to the way the strings are formatted when calling .query() and not what is passed to the SQL server. If this was the case, all queries with quoted parameters would fail with ERROR 1064 (42000): You have an error in your SQL syntax.