Migration error: column "form_data" cannot be cast automatically to type JSONB
#300
Comments
|
Can you provide the failing query? Is the field changing from some other type to This might be related to cockroachdb/cockroach#82416 (comment). |
|
The field looks to have been changed from a TextField: https://github.com/coderedcorp/coderedcms/blob/8e04513572498603a6f64968bb88fd7aed7cd63c/coderedcms/migrations/0015_coderedsessionformsubmission_coderedsubmissionrevision.py#L38 As for failing query: And a much more informative stacktrace (thanks @Delgan/Loguru): Applying coderedcms.0031_wagtail3...2024-09-04 08:13:21.114 | ERROR | __main__:<module>:24 - An error has been caught in function '<module>', process 'MainProcess' (259039), thread 'MainThread' (133483868759872):
Traceback (most recent call last):
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
return self.cursor.execute(sql)
│ │ │ └ 'ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb'
│ │ └ <function Cursor.execute at 0x79671ab5b600>
│ └ <django.db.backends.postgresql.base.Cursor [closed] [BAD] at 0x796716a11a90>
└ <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x79671699c860>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
psycopg.errors.DatatypeMismatch: column "form_data" cannot be cast automatically to type JSONB
HINT: You might need to specify "USING form_data::JSONB".
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
> File "/app/manage.py", line 25, in <module>
execute_from_command_line(sys.argv)
│ │ └ ['manage.py', 'migrate']
│ └ <module 'sys' (built-in)>
└ <function execute_from_command_line at 0x796721703c40>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
utility.execute()
│ └ <function ManagementUtility.execute at 0x796721703f60>
└ <django.core.management.ManagementUtility object at 0x796721dcbd40>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
│ │ │ │ └ ['manage.py', 'migrate']
│ │ │ └ <django.core.management.ManagementUtility object at 0x796721dcbd40>
│ │ └ 'migrate'
│ └ <function ManagementUtility.fetch_command at 0x796721703e20>
└ <django.core.management.ManagementUtility object at 0x796721dcbd40>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/core/management/base.py", line 413, in run_from_argv
self.execute(*args, **cmd_options)
│ │ │ └ {'verbosity': 1, 'settings': None, 'pythonpath': None, 'traceback': False, 'no_color': False, 'force_color': False, 'skip_che...
│ │ └ ()
│ └ <function BaseCommand.execute at 0x796721703420>
└ <django.core.management.commands.migrate.Command object at 0x7967179f45c0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
output = self.handle(*args, **options)
│ │ │ └ {'verbosity': 1, 'settings': None, 'pythonpath': None, 'traceback': False, 'no_color': False, 'force_color': False, 'skip_che...
│ │ └ ()
│ └ <function no_translations.<locals>.wrapper at 0x7967177df1a0>
└ <django.core.management.commands.migrate.Command object at 0x7967179f45c0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
res = handle_func(*args, **kwargs)
│ │ └ {'verbosity': 1, 'settings': None, 'pythonpath': None, 'traceback': False, 'no_color': False, 'force_color': False, 'skip_che...
│ └ (<django.core.management.commands.migrate.Command object at 0x7967179f45c0>,)
└ <function Command.handle at 0x7967177784a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 356, in handle
post_migrate_state = executor.migrate(
│ └ <function MigrationExecutor.migrate at 0x7967177deca0>
└ <django.db.migrations.executor.MigrationExecutor object at 0x79671728d0a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
state = self._migrate_all_forwards(
│ └ <function MigrationExecutor._migrate_all_forwards at 0x7967177ded40>
└ <django.db.migrations.executor.MigrationExecutor object at 0x79671728d0a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
state = self.apply_migration(
│ └ <function MigrationExecutor.apply_migration at 0x7967177dee80>
└ <django.db.migrations.executor.MigrationExecutor object at 0x79671728d0a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
state = migration.apply(state, schema_editor)
│ │ │ └ <django_cockroachdb.schema.DatabaseSchemaEditor object at 0x7967169813a0>
│ │ └ <django.db.migrations.state.ProjectState object at 0x796716c83140>
│ └ <function Migration.apply at 0x796720e87600>
└ <Migration coderedcms.0031_wagtail3>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
operation.database_forwards(
│ └ <function AlterField.database_forwards at 0x796720e716c0>
└ <AlterField model_name='coderedsessionformsubmission', name='form_data', field=<django.db.models.fields.json.JSONField>>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/migrations/operations/fields.py", line 235, in database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
│ │ │ │ └ <django.db.models.fields.json.JSONField: form_data>
│ │ │ └ <django.db.models.fields.TextField: form_data>
│ │ └ <class '__fake__.CoderedSessionFormSubmission'>
│ └ <function BaseDatabaseSchemaEditor.alter_field at 0x79671ab0ccc0>
└ <django_cockroachdb.schema.DatabaseSchemaEditor object at 0x7967169813a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 906, in alter_field
self._alter_field(
│ └ <function DatabaseSchemaEditor._alter_field at 0x79671ab11300>
└ <django_cockroachdb.schema.DatabaseSchemaEditor object at 0x7967169813a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django_cockroachdb/schema.py", line 63, in _alter_field
BaseDatabaseSchemaEditor._alter_field(
│ └ <function BaseDatabaseSchemaEditor._alter_field at 0x79671ab0ce00>
└ <class 'django.db.backends.base.schema.BaseDatabaseSchemaEditor'>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 1163, in _alter_field
self.execute(
│ └ <function DatabaseSchemaEditor.execute at 0x79671aadd3a0>
└ <django_cockroachdb.schema.DatabaseSchemaEditor object at 0x7967169813a0>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
return super().execute(sql, None)
└ 'ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb'
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 202, in execute
cursor.execute(sql, params)
│ │ │ └ None
│ │ └ 'ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb'
│ └ <function CursorDebugWrapper.execute at 0x796721302200>
└ <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x79671699c860>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 122, in execute
return super().execute(sql, params)
│ └ None
└ 'ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb'
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
return self._execute_with_wrappers(
│ └ <function CursorWrapper._execute_with_wrappers at 0x796721302020>
└ <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x79671699c860>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
return executor(sql, params, many, context)
│ │ │ │ └ {'connection': <DatabaseWrapper vendor='cockroachdb' alias='default'>, 'cursor': <django.db.backends.postgresql.base.CursorDe...
│ │ │ └ False
│ │ └ None
│ └ 'ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb'
└ <bound method CursorWrapper._execute of <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x79671699c860>>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
with self.db.wrap_database_errors:
│ │ └ <django.db.utils.DatabaseErrorWrapper object at 0x7967173849e0>
│ └ <DatabaseWrapper vendor='cockroachdb' alias='default'>
└ <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x79671699c860>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
│ │ │ └ DatatypeMismatch('column "form_data" cannot be cast automatically to type JSONB\nHINT: You might need to specify "USING form...
│ │ └ <traceback object at 0x79671697cc80>
│ └ <method 'with_traceback' of 'BaseException' objects>
└ ProgrammingError('column "form_data" cannot be cast automatically to type JSONB\nHINT: You might need to specify "USING form...
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
return self.cursor.execute(sql)
│ │ │ └ 'ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb'
│ │ └ <function Cursor.execute at 0x79671ab5b600>
│ └ <django.db.backends.postgresql.base.Cursor [closed] [BAD] at 0x796716a11a90>
└ <django.db.backends.postgresql.base.CursorDebugWrapper object at 0x79671699c860>
File "/home/dev-user/.cache/pypoetry/virtualenvs/dtautomation-9TtSrW0h-py3.12/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
django.db.utils.ProgrammingError: column "form_data" cannot be cast automatically to type JSONB
HINT: You might need to specify "USING form_data::JSONB". |
|
I'm more than willing to try any suggested patches and rapid-fire this. Since the database is so central, I can't make much progress on my project until we resolve this. :) |
|
Confirmed modifying the query (and migration) to run this raw SQL seems to have gotten past this issue: From: migrations.AlterField(
model_name='coderedsessionformsubmission',
name='form_data',
field=models.JSONField(encoder=django.core.serializers.json.DjangoJSONEncoder),
),To: migrations.RunSQL('ALTER TABLE "coderedcms_coderedsessionformsubmission" ALTER COLUMN "form_data" TYPE jsonb USING "form_data"::JSONB'),What other unintended side effects this has, I am not sure and have yet to encounter. |
|
That should be fine, though the autodetector may try to generate another The problem for django-cockroachdb (as I described in the the cockroach issue I linked to) is that I'm unsure the appropriate query can be generated by the schema editor for all field types and field options in a sufficiently generic manner. We may have to try to do our best since my hope of getting this reverted/fixed in CockroachDB was unsuccessful. |
|
Understood. I ended up with a more "robust" solution with the help of ChatGPT (I am not smart enough for low level things like this lol). # Generated by Django 4.0.6 on 2022-07-29 19:27
import json
import coderedcms.fields
import django.core.serializers.json
from django.db import migrations, models
def migrate_text_to_json_strict(apps, schema_editor):
"""Convert text field data to JSON for each row, raising errors on failure."""
CoderedSessionFormSubmission = apps.get_model("coderedcms", "CoderedSessionFormSubmission")
for submission in CoderedSessionFormSubmission.objects.all():
# Attempt to convert the form_data text field to JSON
try:
json_data = json.loads(submission.form_data)
except json.JSONDecodeError as e:
# Raise an error and stop the migration if the data is not valid JSON
raise ValueError(f"Invalid JSON data for submission ID {submission.id}: {e}")
submission.form_data_json = json_data
submission.save()
class Migration(migrations.Migration):
dependencies = [
('coderedcms', '0030_alter_coderedtag_tag'),
]
operations = [
migrations.AlterField(
model_name='carouselslide',
name='content',
field=coderedcms.fields.CoderedStreamField(blank=True, use_json_field=True),
),
migrations.AlterField(
model_name='coderedpage',
name='content_walls',
field=coderedcms.fields.CoderedStreamField(blank=True, use_json_field=True, verbose_name='Content Walls'),
),
# Step 1: Add a new JSONField to temporarily hold the data
migrations.AddField(
model_name='coderedsessionformsubmission',
name='form_data_json',
field=models.JSONField(encoder=django.core.serializers.json.DjangoJSONEncoder, default=dict),
),
# Step 2: Migrate data from the old TextField to the new JSONField
migrations.RunPython(migrate_text_to_json_strict),
# Step 3: Remove the old TextField
migrations.RemoveField(
model_name='coderedsessionformsubmission',
name='form_data',
),
# Step 4: Rename the new JSONField to form_data
migrations.RenameField(
model_name='coderedsessionformsubmission',
old_name='form_data_json',
new_name='form_data',
),
migrations.AlterField(
model_name='contentwall',
name='content',
field=coderedcms.fields.CoderedStreamField(blank=True, use_json_field=True, verbose_name='Content'),
),
migrations.AlterField(
model_name='footer',
name='content',
field=coderedcms.fields.CoderedStreamField(blank=True, use_json_field=True, verbose_name='Content'),
),
migrations.AlterField(
model_name='navbar',
name='menu_items',
field=coderedcms.fields.CoderedStreamField(blank=True, use_json_field=True, verbose_name='Navigation links'),
),
migrations.AlterField(
model_name='reusablecontent',
name='content',
field=coderedcms.fields.CoderedStreamField(blank=True, use_json_field=True, verbose_name='content'),
),
]But there are quite a few more TextField -> JSONField conversions that exist in other packages (django-allauth and wagtail). Additionally, django-treebeard even has an issue where int / int raised an error:
# fix the depth field
# we need the WHERE to speed up postgres
sql = (
"UPDATE %s "
"SET depth=" + sql_length("path", vendor=vendor) + "/%%s "
"WHERE depth!=" + sql_length("path", vendor=vendor) + "/%%s"
) % (connection.ops.quote_name(cls._meta.db_table), )
vals = [cls.steplen, cls.steplen]
cursor.execute(sql, vals)had to be converted to # fix the depth field
# we need the WHERE to speed up postgres
sql = (
"UPDATE %s "
"SET depth=FLOOR(" + sql_length("path", vendor=vendor) + "/%%s) "
"WHERE depth!=FLOOR(" + sql_length("path", vendor=vendor) + "/%%s)"
) % (connection.ops.quote_name(cls._meta.db_table), )
vals = [cls.steplen, cls.steplen]
cursor.execute(sql, vals)Note the addition of FLOOR. Here are all of the migrations I had to modify for the TextField -> JSONField error:
django-allauth:
wagtail:
With this number of issues, I have to revert to Postgres. I have no problem submitting PRs to the other packages to address these specific problems, but I'm sure this will take time if they even decide to take them (given some of these are outside the scope of django-cockroachdb). Fixing these issues one by one in my development environment does allow me to finish migrations, but all of the other dependencies I have seem to expect Postgres (for example, Django-treebeard above explicitly calls Postgres out). Having the HA cockroach provides is really nice, but I can achieve the same with a dedicated failover and proxy setup with postgres |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Trying to migrate from PostgreSQL to CockroachDB (v24.2.0) presents a unique error. I am using
django-cockroachdb==5.0as I am unable to upgrade django to 5.1 (using django 5.0.9).This is being caused by an upstream migration see here: https://github.com/coderedcorp/coderedcms/blob/8e04513572498603a6f64968bb88fd7aed7cd63c/coderedcms/migrations/0031_wagtail3.py#L25C9-L29C11
I'm just running
python manage.py migrateas I would with Postgres.Full traceback:
The text was updated successfully, but these errors were encountered: