Cannot safely type bigint column as int when used with dbal 3.x #7442

New Issue

admin · 2026-01-22T15:51:43+01:00

admin commented

2026-01-22 15:51:43 +01:00

Originally created by @acoulton on GitHub (Nov 22, 2024).

Bug Report

Q	A
Version	2.19.4

Summary

When using any version of ORM with DBAL 3.x, an entity with a column type bigint and a property type int is incorrectly treated as changed every time it is used. This causes high volumes of unexpected database writes (and listener invocations).

Prior to 2.19.4, orm:validate-schema would report that the column type and property type were incompatible. That was changed in #11414 to fix #11377 - as a result doctrine no longer warns about this issue.

I reported this at the time, but I think as we were discussing on a closed PR (and I didn't get a chance to follow up) it has been overlooked.

Current behavior

Given an entity like:

use Doctrine\ORM\Mapping\Column;
use Doctrine\ORM\Mapping\Entity;
use Doctrine\DBAL\Types\Types;

#[Entity]
class MyEntity {

  #[Column(type: Types::INTEGER)]
  private ?int $id = null;

  #[Column(type: Types::BIGINT)]
  private int $number = 20;
}

When I run doctrine orm:validate-schema the schema validates successfully.

If I then run code like:

// assuming an entity in the database with an id=1, number=20
$entity = $entity_manager->getRepository(MyEntity::class)->find('1');

$entity_manager->flush();

And examine the queries from my database logs, I can see two queries:

SELECT FROM my_entity WHERE id = 1
UPDATE my_entity SET number = 20 WHERE id = 1.

This happens because:

With DBAL 3.x, bigint is hydrated as a string
ORM's UnitOfWork::computeChangeSet checks whether {original value returned by dbal} === {current value of entity property} - that link is for 2.x, but the logic is the same in 3.x and 4.x
If the entity property is typed int then PHP has automatically typecast '15' to 15 when hydrating the object.
This means that the UnitOfWork checks '15' === 15, this does not match, so the entity is added to the changeset and marked for update.

Expected behavior

Either:

The UnitOfWork should not treat the entity as changed
Or the schema validation tool should report that the column and property types are incompatible.

How to reproduce

I have not created a failing test because the correct test depends on how you would prefer to solve this.

I believe that the reproduction is clear from the description and links to how the implementations cause this bug, but am happy to create an official reproduction if it would help.

Proposed fix

IMHO the best solution is probably to fix this directly in the UnitOfWork, to allow people to type properties correctly and eliminate the unnecessary writes.

The === comparison appears in both computeChangeSet and recomputeSingleEntityChangeSet.

I would suggest therefore extracting a method something like:

private function isValueChanged(mixed $original, mixed $actual): bool
{
   if (is_int($actual) && is_string($original)) {
     // it is always safe to cast int to string, it's not guaranteed safe the other way round
     return  $original === (string) $actual;
   }
   // there may be scope to support other type differences here in future

   return $original === $actual;
}

If this is acceptable I can work on a PR with implementation and tests.

Originally created by @acoulton on GitHub (Nov 22, 2024). ### Bug Report | Q | A |-------------------------------------- | ------ | Version | 2.19.4 #### Summary When using any version of ORM with DBAL 3.x, an entity with a column type `bigint` and a property type `int` is incorrectly treated as changed every time it is used. This causes high volumes of unexpected database writes (and listener invocations). Prior to 2.19.4, `orm:validate-schema` would report that the column type and property type were incompatible. That was changed in #11414 to fix #11377 - as a result doctrine no longer warns about this issue. I [reported this at the time](https://github.com/doctrine/orm/pull/11414#issuecomment-2058891320), but I think as we were discussing on a closed PR (and I didn't get a chance to follow up) it has been overlooked. #### Current behavior Given an entity like: ```php use Doctrine\ORM\Mapping\Column; use Doctrine\ORM\Mapping\Entity; use Doctrine\DBAL\Types\Types; #[Entity] class MyEntity { #[Column(type: Types::INTEGER)] private ?int $id = null; #[Column(type: Types::BIGINT)] private int $number = 20; } ``` When I run `doctrine orm:validate-schema` the schema validates successfully. If I then run code like: ```php // assuming an entity in the database with an id=1, number=20 $entity = $entity_manager->getRepository(MyEntity::class)->find('1'); $entity_manager->flush(); ``` And examine the queries from my database logs, I can see two queries: * `SELECT FROM my_entity WHERE id = 1` * `UPDATE my_entity SET number = 20 WHERE id = 1`. This happens because: * With DBAL 3.x, [`bigint` is hydrated as a string](https://github.com/doctrine/dbal/blob/088d2828f443cc8fc626a2cc350674b5dbe27860/src/Types/BigIntType.php#L48) * ORM's [UnitOfWork::computeChangeSet](https://github.com/doctrine/orm/blob/a4a15ad243cc9209943ba457dcb4d9a2fae7f883/src/UnitOfWork.php#L789-L792) checks whether `{original value returned by dbal} === {current value of entity property}` - that link is for 2.x, but the logic is the same in 3.x and 4.x * If the entity property is typed `int` then PHP has automatically typecast `'15'` to `15` when hydrating the object. * This means that the UnitOfWork checks `'15' === 15`, this does not match, so the entity is added to the changeset and marked for update. #### Expected behavior Either: * The UnitOfWork should not treat the entity as changed * Or the schema validation tool should report that the column and property types are incompatible. #### How to reproduce I have not created a failing test because the correct test depends on how you would prefer to solve this. I believe that the reproduction is clear from the description and links to how the implementations cause this bug, but am happy to create an official reproduction if it would help. #### Proposed fix IMHO the best solution is probably to fix this directly in the UnitOfWork, to allow people to type properties correctly and eliminate the unnecessary writes. The `===` comparison appears in both `computeChangeSet` and `recomputeSingleEntityChangeSet`. I would suggest therefore extracting a method something like: ```php private function isValueChanged(mixed $original, mixed $actual): bool { if (is_int($actual) && is_string($original)) { // it is always safe to cast int to string, it's not guaranteed safe the other way round return $original === (string) $actual; } // there may be scope to support other type differences here in future return $original === $actual; } ``` If this is acceptable I can work on a PR with implementation and tests.

admin commented

2026-01-22 15:51:43 +01:00

@andrei-dascalu commented on GitHub (Feb 3, 2025):

I like the idea that the validation should warn about this.
Sure, the issue with using int for db type bigint only manifests itself for very large unsigned values but it's better to just prevent the issue altogether rather than magically casting behind the scenes to bypass type issues.

You can ignore it for as long as you can in the check but assuming you're using bigint for a reason, you're going to have issues eventually.

@andrei-dascalu commented on GitHub (Feb 3, 2025): I like the idea that the validation should warn about this. Sure, the issue with using int for db type bigint only manifests itself for very large unsigned values but it's better to just prevent the issue altogether rather than magically casting behind the scenes to bypass type issues. You can ignore it for as long as you can in the check but assuming you're using bigint for a reason, you're going to have issues eventually.

admin commented

2026-01-22 15:51:43 +01:00

@acoulton commented on GitHub (Feb 7, 2025):

To be fair, generally speaking on 64 bit systems, it is generally safe to use BIGINT - the min/max for a signed bigint is (as far as I know on all database platforms) the same as PHP_INT_MIN / PHP_INT_MAX.

It's only risky if you're using BIGINT UNSIGNED. Hence why I think dbal decided to provide it as an int whenever they could, as in the vast majority of modern cases that will work as expected.

Therefore my preferred solution would be to allow usage as an int but fix the currently broken persistence behaviour.

If validation is reintroduced, it should maybe only be if you have an int property but the column is unsigned and/or your PHP_INT_(MIN/MAX) don't match the BIGINT UNSIGNED range (e.g. if you're on a 32 bit system).

@acoulton commented on GitHub (Feb 7, 2025): To be fair, generally speaking on 64 bit systems, it is generally safe to use `BIGINT` - the min/max for a signed bigint is (as far as I know on all database platforms) the same as PHP_INT_MIN / PHP_INT_MAX. It's only risky if you're using `BIGINT UNSIGNED`. Hence why I think dbal decided to provide it as an int whenever they could, as in the vast majority of modern cases that will work as expected. Therefore my preferred solution would be to allow usage as an `int` but fix the currently broken persistence behaviour. If validation is reintroduced, it should maybe only be if you have an `int` property but the column is unsigned and/or your PHP_INT_(MIN/MAX) don't match the `BIGINT UNSIGNED` range (e.g. if you're on a 32 bit system).

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#7442

Cannot safely type bigint column as int when used with dbal 3.x #7442

Bug Report

Summary

Current behavior

Expected behavior

How to reproduce

Proposed fix

Cannot safely type `bigint` column as `int` when used with dbal 3.x #7442