django.db.utils.DataError: invalid input syntax for integer, migration generated with older Django version not processed correctly during db creation

[rubnov]

After upgrading our Django project from Django 1.11.8 to 2.2.6, along with pytest (from 2.9.2 to 5.2.0) and pytest-django (3.1.2 to 3.5.1), we started getting the following error in some of our test cases:

Traceback (most recent call last):
  File "dev/psngr-django/myapp/myapp/apps/premium/serializers.py", line 1638, in create
    OrderItem.objects.bulk_create(order_list)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/query.py", line 474, in bulk_create
    ids = self._batched_insert(objs_without_pk, fields, batch_size, ignore_conflicts=ignore_conflicts)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/query.py", line 1204, in _batched_insert
    ignore_conflicts=ignore_conflicts,
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/query.py", line 1186, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1335, in execute_sql
    cursor.execute(sql, params)
  File ".pyenv/versions/3.6.8/envs/venv368/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 ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File ".pyenv/versions/3.6.8/envs/venv368/lib/python3.6/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for integer: "7khgb4fp"
LINE 1: ...m" ("order_id", "product_id", "quantity") VALUES ('7khgb4fp'...

This is the offending code:

with transaction.atomic():
    order = Order.objects.create(
        customer=customer,
        braintree_transaction_id=result.transaction.id,
        order_datetime=result.transaction.updated_at,
        price=total_amount,
        taxamo_transaction_id=transaction_key,
        braintree_payment_token=payment_method_token,
        bt_billing_address=transaction_data['billing_address_id'],
        bt_shipping_address=transaction_data['shipping_address_id'],
        user=user
    )

    order_list = []
    for product in validated_data['order_item']:
        order_list.append(
            OrderItem(order=order, **product)
        )

    # ---> DataError raised here:  <---
    OrderItem.objects.bulk_create(order_list)
    ...

The DataError exception is thrown when attempting to create a new OrderItem which has a ForeignKey to Order. The primary key of Order is braintree_transaction_id which is a CharField. However, Order was initially created with id (integer) as the pkey, which was removed and replaced by varchar braintree_transaction_id as the pkey. This was done in a migration.

Model classes:

class OrderItem(models.Model):
    order = models.ForeignKey(
        'premium.Order',
        on_delete=models.CASCADE,
        related_name="order_item"
    )
    product = models.ForeignKey(
        'premium.Product',
        on_delete=models.PROTECT,
    )
    quantity = models.PositiveSmallIntegerField(
        validators=[validators.MinValueValidator(1)]
    )

    def get_product_price(self):
        return self.product.price * self.quantity

class Order(models.Model):
    braintree_transaction_id = models.CharField(
        max_length=36, primary_key=True, null=False,
        help_text="Braintree transaction id generate by braintree API"
    )
    ...

Initial Order and OrderItem migrations:

# -*- coding: utf-8 -*-
# Generated by Django 1.9.12 on 2018-02-19 13:57
from __future__ import unicode_literals

import django.core.validators
from django.db import migrations, models
import django.db.models.deletion

class Migration(migrations.Migration):

    dependencies = [
        ('premium', '0014_subscription_user_instance'),
    ]

    operations = [
        ...
        migrations.CreateModel(
            name='Order',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('braintree_transaction_id', models.CharField(max_length=36)),
                ('order_datetime', models.DateTimeField()),
                ('shipped_datetime', models.DateTimeField()),
                ('status', models.CharField(
                    choices=[('submitted', 'submitted'), ('settled', 'settled'), ('failed', 'failed'),
                             ('shipped', 'shipped'), ('received', 'received'), ('cancelled', 'cancelled')],
                    default='submitted', max_length=20)),
                ('customer', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='premium.Customer')),
            ],
        ),
        migrations.CreateModel(
            name='OrderItem',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                (
                'quantity', models.PositiveSmallIntegerField(validators=[django.core.validators.MinValueValidator(1)])),
                ('order', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='premium.Order')),
                ('product', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='premium.Product')),
            ],
        ),

        migrations.AlterField(
            model_name='order',
            name='shipped_datetime',
            field=models.DateTimeField(null=True),
        ),

        migrations.AddField(
            model_name='order',
            name='price',
            field=models.FloatField(default=0.0),
        ),
    ]

A following migration then alters the primary key of Order and remaps the ForeignKey from OrderItem:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.8 on 2018-04-06 08:28
from __future__ import unicode_literals

import django.core.validators
from django.db import migrations, models

map_braintree_id_by_order_id = {}

def collect_fk_order(apps, schema_editor):
    Order = apps.get_model("premium", "Order")

    qs = Order.objects.all()

    for q in qs:
        map_braintree_id_by_order_id[str(q.id)] = q.braintree_transaction_id

def rebuild_fk_order_item(apps, schema_editor):
    OrderItem = apps.get_model("premium", "OrderItem")

    for order_item in OrderItem.objects.all():
        br_id = map_braintree_id_by_order_id[order_item.order_id]

        order_item.order_id = br_id
        order_item.save(update_fields=['order_id'])

class Migration(migrations.Migration):

    dependencies = [
        ('premium', '0015_product_subscriptionadminproxy'),
    ]

    operations = [
        migrations.AddField(
            model_name='order',
            name='cancelled_datetime',
            field=models.DateTimeField(blank=True, null=True),
        ),
        migrations.AddField(
            model_name='order',
            name='exception_message',
            field=models.CharField(blank=True, help_text='Shows in the status_msg for failed status', max_length=250, null=True),
        ),
        migrations.AddField(
            model_name='order',
            name='expected_delivery_days',
            field=models.IntegerField(blank=True, help_text='Expected delivery days. Affect the status_msg for shipped status', null=True, validators=[django.core.validators.MinValueValidator(1)]),
        ),
        migrations.AddField(
            model_name='order',
            name='received_datetime',
            field=models.DateTimeField(blank=True, null=True),
        ),
        migrations.AlterField(
            model_name='order',
            name='shipped_datetime',
            field=models.DateTimeField(blank=True, null=True),
        ),
        migrations.RunPython(code=collect_fk_order, reverse_code=migrations.RunPython.noop),
        migrations.RemoveField(
            model_name='order',
            name='id',
        ),
        migrations.AlterField(
            model_name='order',
            name='braintree_transaction_id',
            field=models.CharField(max_length=36, primary_key=True, serialize=False, null=False),
        ),
        migrations.RunPython(code=rebuild_fk_order_item, reverse_code=migrations.RunPython.noop),
    ]

These are the SQL statements generated by pystest when creating the database (I've included only the OrderItem and Order model statements):

DEBUG    django.db.backends.schema:schema.py:128 CREATE TABLE "premium_order" ("id" serial NOT NULL PRIMARY KEY, "braintree_transaction_id" varchar(36) NOT NULL, "order_datetime" timestamp with time zone NOT NULL, "shipped_datetime" timestamp with time zone NOT NULL, "status" varchar(20) NOT NULL, "customer_id" integer NOT NULL); (params None)
DEBUG    django.db.backends.schema:schema.py:128 CREATE TABLE "premium_orderitem" ("id" serial NOT NULL PRIMARY KEY, "quantity" smallint NOT NULL CHECK ("quantity" >= 0), "order_id" integer NOT NULL, "product_id" integer NOT NULL); (params None)
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ALTER COLUMN "shipped_datetime" DROP NOT NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "price" double precision DEFAULT %s NOT NULL; (params [0.0])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ALTER COLUMN "price" DROP DEFAULT; (params [])
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_productrule_product_id_8cf5a319" ON "premium_productrule" ("product_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_productrule" ADD CONSTRAINT "premium_productrule_product_id_8cf5a319_fk_premium_product_id" FOREIGN KEY ("product_id") REFERENCES "premium_product" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD CONSTRAINT "premium_order_customer_id_090ea006_fk_premium_customer_id" FOREIGN KEY ("customer_id") REFERENCES "premium_customer" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_order_customer_id_090ea006" ON "premium_order" ("customer_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_orderitem" ADD CONSTRAINT "premium_orderitem_order_id_9760db8b_fk_premium_order_id" FOREIGN KEY ("order_id") REFERENCES "premium_order" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_orderitem" ADD CONSTRAINT "premium_orderitem_product_id_b1556f49_fk_premium_product_id" FOREIGN KEY ("product_id") REFERENCES "premium_product" ("id") DEFERRABLE INITIALLY DEFERRED; (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_orderitem_order_id_9760db8b" ON "premium_orderitem" ("order_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_orderitem_product_id_b1556f49" ON "premium_orderitem" ("product_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "cancelled_datetime" timestamp with time zone NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "exception_message" varchar(250) NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "expected_delivery_days" integer NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD COLUMN "received_datetime" timestamp with time zone NULL; (params [])
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" DROP COLUMN "id" CASCADE; (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD CONSTRAINT "premium_order_braintree_transaction_id_d1e5b253_uniq" UNIQUE ("braintree_transaction_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 ALTER TABLE "premium_order" ADD CONSTRAINT "premium_order_braintree_transaction_id_d1e5b253_pk" PRIMARY KEY ("braintree_transaction_id"); (params ())
DEBUG    django.db.backends.schema:schema.py:128 CREATE INDEX "premium_order_braintree_transaction_id_d1e5b253_like" ON "premium_order" ("braintree_transaction_id" varchar_pattern_ops); (params ())

Notice this statement: CREATE TABLE "premium_orderitem" (... "order_id" integer NOT NULL,... where order_id ForeignKey is created as integer, which is according to the initial migration, but there is no statement that updates order_id to varchar, only one that updates the REFERENCE to premium_order. The ForeignKey type remains integer and is never changed to varchar (though it references a varchar field in premium_order). This results in a "broken" table structure. Here is premium_orderitem table in the test db:

> psql -d test_test

psql (11.5)
Type "help" for help.

test_test=# \d premium_orderitem
                               Table "public.premium_orderitem"
   Column   |   Type   | Collation | Nullable |                    Default
------------+----------+-----------+----------+-----------------------------------------------
 id         | integer  |           | not null | nextval('premium_orderitem_id_seq'::regclass)
 quantity   | smallint |           | not null |
 order_id   | integer  |           | not null |
 product_id | integer  |           | not null |
Indexes:
    "premium_orderitem_pkey" PRIMARY KEY, btree (id)
    "premium_orderitem_order_id_9760db8b" btree (order_id)
    "premium_orderitem_product_id_b1556f49" btree (product_id)
Check constraints:
    "premium_orderitem_quantity_check" CHECK (quantity >= 0)
Foreign-key constraints:
    "premium_orderitem_product_id_b1556f49_fk_premium_product_id" FOREIGN KEY (product_id) REFERENCES premium_product(id) DEFERRABLE INITIALLY DEFERRED

Table structure of Order is:

test_test=# \d premium_order
                             Table "public.premium_order"
          Column          |           Type           | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
 braintree_transaction_id | character varying(36)    |           | not null |
 order_datetime           | timestamp with time zone |           | not null |
 shipped_datetime         | timestamp with time zone |           |          |
 status                   | character varying(20)    |           | not null |
 customer_id              | integer                  |           | not null |
 price                    | double precision         |           | not null |
 cancelled_datetime       | timestamp with time zone |           |          |
 exception_message        | character varying(250)   |           |          |
 expected_delivery_days   | integer                  |           |          |
 received_datetime        | timestamp with time zone |           |          |
 taxamo_transaction_id    | character varying(30)    |           |          |
 track_trace              | character varying(255)   |           |          |
 braintree_payment_token  | character varying(50)    |           | not null |
 bt_billing_address       | character varying(36)    |           |          |
 bt_shipping_address      | character varying(36)    |           |          |
 braintree_refund_id      | character varying(36)    |           |          |
 user_id                  | integer                  |           |          |
Indexes:
    "premium_order_braintree_transaction_id_d1e5b253_pk" PRIMARY KEY, btree (braintree_transaction_id)
    "premium_order_braintree_transaction_id_d1e5b253_uniq" UNIQUE CONSTRAINT, btree (braintree_transaction_id)
    "premium_order_taxamo_transaction_id_key" UNIQUE CONSTRAINT, btree (taxamo_transaction_id)
    "premium_order_braintree_transaction_id_d1e5b253_like" btree (braintree_transaction_id varchar_pattern_ops)
    "premium_order_customer_id_090ea006" btree (customer_id)
    "premium_order_taxamo_transaction_id_79d6c572_like" btree (taxamo_transaction_id varchar_pattern_ops)
    "premium_order_user_id_e70bfca6" btree (user_id)
Foreign-key constraints:
    "premium_order_customer_id_090ea006_fk_premium_customer_id" FOREIGN KEY (customer_id) REFERENCES premium_customer(id) DEFERRABLE INITIALLY DEFERRED
    "premium_order_user_id_e70bfca6_fk_users_user_id" FOREIGN KEY (user_id) REFERENCES users_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "premium_ordercommunication" CONSTRAINT "premium_ordercommuni_order_id_f16be3a2_fk_premium_o" FOREIGN KEY (order_id) REFERENCES premium_order(braintree_transaction_id) DEFERRABLE INITIALLY DEFERRED

The primary key appears to be correct: braintree_transaction_id and it's a varchar.

Somehow the second migration, which updates Order's pkey, is not processed correctly and OrderItem ForeignKey to Order remains an integer. This raises an exception when trying to insert new OrderItem with a string order_id to the db (the string refers to braintree_transaction_id which is the pkey of Order).

When running pytest with --nomigrations option, the CREATE TABLE is done correctly (i.e. ForeignKey is a varchar) and no exception is thrown when creating new OrderItem.

Has anyone encountered this issue before?

[blueyed]

Sounds more like a Django issue, no?

[rubnov]

Not really. Django's manage.py migrate runs these migrations correctly and produces the correct OrderItem table structure in the main db. This issue only occurs in the test db, which is generated by pytest on every test run.

[blueyed]

Yeah, but we're calling/using only Django's methods there (IIRC). Does manage.py migrate work on a new DB?

[rubnov]

I ended up invoking py.test with --nomigrations flag in order to work around this issue.

[Ravindrakumara]

same error message
return self.cursor.execute(sql, params) django.db.utils.DataError: invalid input syntax for type double precision: "no"

[bluetech]

@rubnov If you add a print or breakpoint() call in your rebuild_fk_order_item function in the migration, does it get triggered? Trying to understand if the migration runs at all or not.

[rubnov]

@bluetech no I did not try to debug this. I found a workaround and had other priorities. It should be easy to create a test case on pytest for this. I think you should be able to reproduce this issue.

[rubnov]

@josetv91 https://github.com/pytest-dev/pytest-django/issues/767#issuecomment-597830540