Version20210428000000 causes error on first-time install

[NiklasBr]

Upon installing this bundle the first time there is an SQL integrity error, as if the migration attempts to run twice:

[warning] Migration Elements\Bundle\ProcessManagerBundle\Migrations\Version20210428000000 was executed but did not result in any SQL statements.
[error] Migration Elements\Bundle\ProcessManagerBundle\Migrations\Version20210428000000 failed during Post-Checks. Error: "An exception occurred while executing 'INSERT INTO migration_versions (`version`, `executed_at`, `execution_time`) VALUES (?, ?, ?)' with params ["Elements\\Bundle\\ProcessManagerBundle\\Migrations\\Version20210428000000", "2021-11-10 13:34:55", 7]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Elements\Bundle\ProcessManagerBundle\Migrations\Version202104280' for key 'migration_versions.PRIMARY'"

In AbstractMySQLDriver.php line 74:

  An exception occurred while executing 'INSERT INTO migration_versions (`version`, `executed_at`, `execution_time`) VALUES (?, ?, ?)' wi
  th params ["Elements\\Bundle\\ProcessManagerBundle\\Migrations\\Version20210428000000", "2021-11-10 13:34:55", 7]:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Elements\Bundle\ProcessManagerBundle\Migrations\Version202104280
  ' for key 'migration_versions.PRIMARY'

In Exception.php line 18:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Elements\Bundle\ProcessManagerBundle\Migrations\Version202104280
  ' for key 'migration_versions.PRIMARY'

In PDOStatement.php line 117:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Elements\Bundle\ProcessManagerBundle\Migrations\Version202104280
  ' for key 'migration_versions.PRIMARY'

doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration CONFIGURATION] [--em EM] [--conn CONN] [--prefix [PREFIX]] [--] [<version>]

Removing this part fixes the issue and allows the installation to complete: https://github.com/elements-at/ProcessManager/blob/ed3b15fe0c99b91a569d621b9c1204fafdecbccd/src/Installer.php#L156-L159

It appears the migrations runs anyway without getLastMigrationVersionClassName when checking the migration_versions table:

Elements\Bundle\ProcessManagerBundle\Migrations\Version20210428000000 | 2021-11-10 13:40:24 | 9
Elements\Bundle\ProcessManagerBundle\Migrations\Version20210802000000 | 2021-11-10 13:40:24 | 513

[ctippler]

Hi,

i just tried to install it on a Pimcore v10.2.4 (ProcessManger version v4.0.7) and it worked without any errors.

Are you using the latest version of the ProcessManger?

Regards, Christian

[NiklasBr]

4.0.7 This is the first time it is installed, it is not updated from a previous version. This error is very similar to this one in Data Hub by the way..

[BlackbitDevs]

On a new install first https://github.com/elements-at/ProcessManager/blob/1be2eae72815232e4282c290affde94d88102691/src/Installer.php#L66-L86 gets executed for installation and then when you execute the bundle migrations https://github.com/elements-at/ProcessManager/blob/1be2eae72815232e4282c290affde94d88102691/src/Migrations/Version20210802000000.php#L22-L30 gets executed - but as both add the PRIMARY KEY, it fails. Actually I do not even understand why the migration creates https://github.com/elements-at/ProcessManager/blob/1be2eae72815232e4282c290affde94d88102691/src/Migrations/Version20210802000000.php#L25 and https://github.com/elements-at/ProcessManager/blob/1be2eae72815232e4282c290affde94d88102691/src/Migrations/Version20210802000000.php#L27

So either have a basic installation in the Installer which never gets changed but the database may only get modified via migrations. Or introduce a Version00000001 migration and simply execute all available migrations in the Installer. I prefer the latter as you can simply add a new migration without having to have the install queries again in the Installer. The problem is the same with Pimcore itself, there we always have to create a migration and also update https://github.com/pimcore/pimcore/blob/11.x/bundles/InstallBundle/dump/install.sql - this is duplicate code and it may lead to a different database structure when someone oversees a small difference (or as in this case a useless UNIQUE index when there also in a PRIMARY KEY on the same table).

Or you could even keep the current setup but then implement the migrations idempotent so that they check if the indexes, columns or whatever already exist - and only if this is not the case, execute the corresponding SQL statement. This same problem existed in Pimcore core and got fixed in https://github.com/pimcore/pimcore/pull/9024

[ctippler]

Thx, should be fixed with https://github.com/elements-at/ProcessManager/commit/95849652dabe700e33e51bebd0d3770ced9988c8