translations:find -> Integrity constraint violation: 1062 Duplicate entry

[vesper8]

running translations:find works great the first time I run it, but if I add new keys in my code and run it again I get this error

if I truncate the table and start fresh then it works fine again.. this seems related to the unique indexing on the table but.. how come it fails to see there are already items with the key "category" in the case below and tries to insert them anyway?

pa translations:find

   Illuminate\Database\QueryException  : SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'json-JSON-category' for key 'ixk_ltm_translations_locale_group_key' (SQL: insert into `ltm_translations` (`locale`, `group`, `key`, `value`, `saved_value`, `status`, `updated_at`, `created_at`) values (json, JSON, category, category, category, 1, 2018-10-21 13:24:58, 2018-10-21 13:24:58))

  at api.mydomain.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   Doctrine\DBAL\Driver\PDOException::("SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'json-JSON-category' for key 'ixk_ltm_translations_locale_group_key'")
      api.mydomain.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:144

  2   PDOException::("SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'json-JSON-category' for key 'ixk_ltm_translations_locale_group_key'")
      api.mydomain.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:142

  Please use the argument -v to see more details.

[vsch]

@vesper8, this could be related to #123 if the keys have different case. The queries do case sensitive queries but the index is built as not case sensitive.

I will need to see how this can be added to a migration in database independent way.

[vesper8]

in my case however all keys are always all lowercase.. so not sure if related to that.. hrmm.. will try to drill down further

[vesper8]

ok so I've been debugging the issue and think I found out what's going on.. at least this should help you come up with a fix

so I'm tracing the process by adding a bunch of debugging inside the firstOrNewTranslation method

I run translations:find and it finds a key called "JSON.category" (which already exists and has already been translated)

it runs through firstOrNewTranslation

and it looks for an existing database entry with these attributes:

array (3) [
    'locale' => string (2) "en"
    'group' => string (4) "JSON"
    'key' => string (8) "category"
]

it correctly finds one.. and moves on

then it repeats this, this time it looks for these attributes:

array (6) [
    'locale' => string (4) "json"
    'group' => string (4) "JSON"
    'key' => string (8) "category"
    'value' => string (8) "category"
    'saved_value' => string (8) "category"
    'status' => integer 1
]

Here it will fail to find the existing entry since the "saved_value" in the database happens to be "Category" with a capital.. however this is the translated string so it could have been anything.. it could have been "A Category" for example.

It fails to find the existing entry, it then tries to create a new entry and runs into the integrity issue above

I feel that the firstOrNewTranslation should probably not be checking for the "saved_value" attribute ever.. right ? I think that's the problem here.. the firstOrNewTranslation method should only ever be checking for the locale, group and key to determine if a key already exists.. not also the value, saved_value and status

I added these unsets here

        if ($checkDB) {
            $query = $this->translation->on($this->getConnectionName());

            unset($attributes['value']);
            unset($attributes['saved_value']);
            unset($attributes['status']);

            foreach ($attributes as $attribute => $value) {
                $query = $query->where($attribute, $value);
            }

            $translation = $query->first();
        }

and it makes the problem go away for me

[vesper8]

on a separate but not entirely unrelated note, I'm still confused why this 'json' locale even exists and why the database rows for the 'json' locale contain the same 'value' and 'saved_value' as the 'en' locale rows

[vsch]

@vesper8, you found a bug. Only locale, group and key should be used for the check. The fix is only using these columns. Reseting other values will cause issues for the following code which uses the attributes to set a new translation if one is not found.

        if ($checkDB) {
            $query = $this->translation->on($this->getConnectionName());

            foreach ($attributes as $attribute => $value) {
                if ($attribute === 'locale' || $attribute === 'group' || $attribute === 'key') {
                    $query = $query->where($attribute, $value);
                }
            }

            $translation = $query->first();
        }

saved_value refers to the value which is in the translation files. When the translation is first read from files the saved value is the same as value. Later if the value is modified then LTM will display this translation as unpublished.

[vsch]

@vesper8, the json locale stores keys used in the JSON translation files. Since these are not compatible with keys used by LTM (and the rest of translation files) LTM generates unique keys but in order to map them back to original values needs to save them in the DB. json locale is used to identify the saved mapping.

[vesper8]

thanks for the quick fix!

I just ran a composer update and it didn't get the new version even though you pushed it 2 hours ago

  - Updating vsch/laravel-translation-manager (2.6.38 => 2.6.44): Loading from cache

Maybe just a cache issue.. I know github is having problems today

[vsch]

@vesper8, updated packagist.org manually. There seems to be some glitches on that site and I don't know if it is github's fault or packagist but managed to get myself locked out of my account trying to update github hooks. Bugs are a part of developer's life. ;)