V8-archive: Duplicate entry 'directus_settings-project_foreground'

[hufon]

Bug Report

Steps to Reproduce

1. Deploy 8.0.0-RC1 tag version
2. Go to http://localhost/_
3. See error
   {"error":{"code":null,"message":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'directus_settings-project_foreground' for key 'idx_collection_field'","class":"PDOException","file":"/opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php","line":134}}

Expected Behavior

Should work

Actual Behavior

Error

Other Context & Screenshots

Technical Details

• Device: All
• OS: Centos 7
• Web Server: Apache 2.4
• PHP Version: 7.1.30
• Database: Mysql 5.7
• Install Method: Docker

[hufon]

For information, if i drop the line project_foreground and project_background from the directus_field table it works...

[benhaynes]

@bjgajjar — seems like the migrations are trying to add that record twice(?)

[bjgajjar]

@benhaynes - Maybe [Though it should not happen]. Still, I'll check and update you soon on this :)

[bjgajjar]

@hufon - It'll be great help if you can provide the below details.

• From your description, it seems like you are upgrading it. But still for the confirmation; may I know that did you upgrade the Directus version or this issue occurs in fresh installation?
• If you implement the upgradation, then please let us know your older version from which you upgrade it into the 8.0.0-RC1

[hufon]

@hufon - It'll be great help if you can provide the below details.

* From your description, it seems like you are upgrading it. But still for the confirmation; may I know that did you upgrade the Directus version or this issue occurs in fresh installation?

* If you implement the upgradation, then please let us know your older version from which you upgrade it into the `8.0.0-RC1`

No, i'm not upgrading it.
I use it into Kubernetes on docker with DIRECTUS_USE_ENV=1 (no php config files)
The installation is done with

• ./bin/directus install:database
• ./bin/directus install:install -e -p

Theses two steps went ok, but it fails when querying the API. If i delete the existing lines in directus_field for project_foreground and project_background, it works!

[bjgajjar]

It seems like you upgrade the version of Docker as the last release of Docker was 21 days ago.
This same issue is not replicated in directus build.
Thoughts @WoLfulus?

[WoLfulus]

I'm not sure about this one to be honest.

8.x images are not released yet since it's a major release and contains several breaking changes. We'll need to rework the build scripts and probably image contents in that case.

I'm not sure how the database ended in that corrupted state, did you run those manually?

Do you mind sharing your k8s manifests? I can try reproducing it, but as I said, we don't have images for 8.x yet, so I'll probably test it once we get to implement those.

[hufon]

In fact, i don't use K8s exactly because we are on Redhat OpenShift.
Here's the template we use.
We build directus with S2I (Source 2 Image) and we don't use your images.
directus-template.yaml.txt

May be i can try to reproduce the problem with docker-compose...

[hufon]

I can send you a dump of my database when it's just installed...

[hufon]

I've a reproductible test case:

• I've build a v8.0.0-rc1 docker image with directus docker script, and i push it to my repo.
• Download https://gist.github.com/hufon/b601c4f3bbe0bc5f8ba27db85ec5435b this docker-compose file
  ```$ docker-compose up -d

$ docker-compose -f ./docker-compose-test.yaml exec api /var/www/bin/directus install:database

$ docker-compose -f ./docker-compose-test.yaml exec api /var/www/bin/directus install:install

$ curl http://localhost:8080/_/
{"error":{"code":null,"message":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'directus_settings-project_foreground' for key 'idx_collection_field'"}}`

[hufon]

MySQL [directus]> select * from directus_fields where field='project_foreground'G;
******** 1. row ********
id: 111
collection: directus_settings
field: project_foreground
type: file
interface: file
options: NULL
locked: 1
validation: NULL
required: 0
readonly: 0
hidden_detail: 0
hidden_browse: 0
sort: 5
width: half
group: NULL
note: Centered image (eg: logo) for the login page
translation: NULL

[bjgajjar]

@WoLfulus - Can you please verify this once in Docker?

[hufon]

StackTrace :
`

--
  | Stack trace:
  | #0 /opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php(134): PDO->exec
  | #1 /opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/AdapterWrapper.php(171): PhinxDbAdapterPdoAdapter->execute
  | #2 /opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Migration/AbstractMigration.php(221): PhinxDbAdapterAdapterWrapper->execute
  | #3 /opt/app-root/src/migrations/upgrades/schemas/20191105063515_update_general_setting_variabe_name.php(59): PhinxMigrationAbstractMigration->execute
  | #4 /opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php(124): UpdateGeneralSettingVariabeName->change
  | #5 /opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(375): PhinxMigrationManagerEnvironment->executeMigration
  | #6 /opt/app-root/src/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php(351): PhinxMigrationManager->executeMigration
  | #7 /opt/app-root/src/src/core/Directus/Util/Installation/InstallerUtils.php(229): PhinxMigrationManager->migrate
  | #8 /opt/app-root/src/src/core/Directus/Util/Installation/InstallerUtils.php(216): DirectusUtilInstallationInstallerUtils::runMigrationAndSeeder
  | #9 /opt/app-root/src/src/core/Directus/Application/Http/Middleware/DatabaseMigrationMiddleware.php(13): DirectusUtilInstallationInstallerUtils::updateTables
  | #10 [internal function]: DirectusApplicationHttpMiddlewareDatabaseMigrationMiddleware->__invoke
  | #11 /opt/app-root/src/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
  | #12 [internal function]: SlimDeferredCallable->__invoke
  | #13 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
  | #14 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): SlimRoute->Slim{closure}
  | #15 /opt/app-root/src/vendor/slim/slim/Slim/Route.php(268): SlimRoute->callMiddlewareStack
  | #16 /opt/app-root/src/vendor/slim/slim/Slim/App.php(503): SlimRoute->run
  | #17 /opt/app-root/src/src/core/Directus/Application/Http/Middleware/AbstractRateLimitMiddleware.php(34): SlimApp->__invoke
  | #18 [internal function]: DirectusApplicationHttpMiddlewareAbstractRateLimitMiddleware->__invoke
  | #19 /opt/app-root/src/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
  | #20 [internal function]: SlimDeferredCallable->__invoke
  | #21 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
  | #22 /opt/app-root/src/vendor/directus/proxy-detection/src/ProxyDetectionMiddleware.php(30): SlimApp->Slim{closure}
  | #23 /opt/app-root/src/src/core/Directus/Application/Http/Middleware/ProxyMiddleware.php(18): RKAMiddlewareProxyDetectionMiddleware->__invoke
  | #24 [internal function]: DirectusApplicationHttpMiddlewareProxyMiddleware->__invoke
  | #25 /opt/app-root/src/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
  | #26 [internal function]: SlimDeferredCallable->__invoke
  | #27 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
  | #28 /opt/app-root/src/vendor/akrabat/ip-address-middleware/src/IpAddress.php(113): SlimApp->Slim{closure}
  | #29 [internal function]: RKAMiddlewareIpAddress->__invoke
  | #30 /opt/app-root/src/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
  | #31 [internal function]: SlimDeferredCallable->__invoke
  | #32 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
  | #33 /opt/app-root/src/src/core/Directus/Application/Http/Middleware/CorsMiddleware.php(71): SlimApp->Slim{closure}
  | #34 [internal function]: DirectusApplicationHttpMiddlewareCorsMiddleware->__invoke
  | #35 /opt/app-root/src/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
  | #36 [internal function]: SlimDeferredCallable->__invoke
  | #37 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
  | #38 /opt/app-root/src/src/core/Directus/Application/Http/Middleware/ResponseCacheMiddleware.php(62): SlimApp->Slim{closure}
  | #39 [internal function]: DirectusApplicationHttpMiddlewareResponseCacheMiddleware->__invoke
  | #40 /opt/app-root/src/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
  | #41 [internal function]: SlimDeferredCallable->__invoke
  | #42 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
  | #43 /opt/app-root/src/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): SlimApp->Slim{closure}
  | #44 /opt/app-root/src/vendor/slim/slim/Slim/App.php(392): SlimApp->callMiddlewareStack
  | #45 /opt/app-root/src/vendor/slim/slim/Slim/App.php(297): SlimApp->process
  | #46 /opt/app-root/src/src/core/Directus/Application/Application.php(161): SlimApp->run
  | #47 /opt/app-root/src/public/index.php(5): DirectusApplicationApplication->run [] []

`

[hufon]

The problem comes from : 20191105063515_update_general_setting_variabe_name.php

[hufon]

The problematic query is :
UPDATE directus_fields SET field = 'project_foreground',type= 'file',interface= 'file' WHEREcollection= 'directus_settings' ANDfield= 'project_icon'

[hufon]

The existing line is :

mysql> SELECT * from directus_fields WHERE collection = 'directus_settings' AND field = 'project_icon'G;
******** 1. row ********
id: 169
collection: directus_settings
field: project_icon
type: string
interface: icon
options: NULL
locked: 0
validation: NULL
required: 0
readonly: 0
hidden_detail: 0
hidden_browse: 0
sort: NULL
width: half
group: NULL
note: NULL
translation: NULL
1 row in set (0.00 sec)

[hufon]

The problem is that there's already a project_icon and a project_foreground settings in the database

[hufon]

Conflict with 20191001092213_add_project_settings.php

[hufon]

1. install:database : migrations/db/seeds/FieldsSeeder.php -> Insert project_foreground
2. when running the first time, migrations are automatically played
3. migration 20191001092213_add_project_settings.php -> Insert project_icon
4. migration 20191105063515_update_general_setting_variabe_name -> Update project_icon -> project_foreground -> break unique index...

The best way to fix the problem may be to try / catch the update into 20191105063515_update_general_setting_variabe_name

[hufon]

Fixed for me...