Purge translation strings in core_sys_lang_words?

[Sonya*]

It seems that I have a lot of duplicate translation strings in my database. The following queries on the database with two languages returns 815 entries.

SELECT word_key, COUNT(word_key) FROM `core_sys_lang_words` GROUP BY word_key HAVING (COUNT(word_key) != 2)

Some strings exist only in one language (lang_id = 2), another are doubled, tripled and even more like "by":

4

I assume that this has happened because the translation has been started some versions ago. Unused strings have been never deleted and have been imported again and again while uploading a language file. This causes issues with translation, as strings with word_keys that are used multiple times cannot be translated.

My question is: how can I purge the table safely? Look on the screenshot. How do I know what word key is used and what not? I would like to release clean language pack. And I do not want to pass on the garbage from my database to the users.  

Any idea?

Edited June 6, 2020 by Sonya*

[Sonya*]

And not all strings are garbage from language pack. 62 duplicate strings can be found in default fresh install of the latest version. Try this right after install:

SELECT word_key, COUNT(word_key) FROM `core_sys_lang_words` GROUP BY word_key HAVING (COUNT(word_key) > 1)

All these strings cannot be translated proper. And I do not know what application is the "right" one for the string to remove the other. It seems that it does not make sense to purge my database for lang_id = 1. There should be a patch that purges the table on all user installations.  

Any suggestion of how to deliver full language pack without duplicate strings and fully translatable?

Edited June 7, 2020 by Sonya*

[Maxxius]

Interesting..

[Sonya*]

And another "strange" thing. This query submitted after fresh install returns 42 entries:

SELECT * FROM `core_sys_lang_words` WHERE `word_default` = ''

What is the purpose of those strings that do not even have English? I am not able to translate them as there is ... ehm... nothing to translate 😉

[Adriano Faria]

1 hour ago, Sonya* said:

What is the purpose of those strings that do not even have English?

One example is the follow button (Notify me of replies) when you’re creating content. The main lang bit for it empty and only the _suffix has the text. That’s how it is otherwise it will show a text in the field, right here:

[bfarber]

Looks like if word_default and word_default_version are both NULL you can probably remove the row, looking at your screenshot. I'd tread cautiously though.

[Sonya*]

1 hour ago, bfarber said:

I'd tread cautiously though.

I have removed all strings that:

• exists only in language pack
• do have empty word_default in the language pack

But there are still 62 strings in English that are duplicate. These strings are delivered with the latest version of IPS (4.4.10) and cannot be translated. Examples are:

It will not help if I purge my database from those strings. They exist in every database out there. These strings should be purged by IPS. 🙄

[Daniel F]

I can confirm any_time(which I have fixed for 4.5), but nothing else from your list was duplicate for me.

 

[Martin A.]

I can guarantee that most of those other "duplicate" language strings are for jslang.

@Sonya* Add "word_js=0" (or "word_js=1") to your query, and then see if you have any duplicates. Some strings do indeed have the same key, but that's only because one of them is for javascript. Maybe there should have been something in the translation table to highlight that, similar to the app/plugin column.

[Ilya Hoilik]

Afair, js language strings follow the lowerCamelCase naming convention, not the under_score.

[Martin A.]

2 minutes ago, Ilya Hoilik said:

Afair, js language strings follow the lowerCamelCase naming convention, not the under_score.

Except when they don't.

[Sonya*]

Though it is still not solved. The garbage in languages from previous version persists in database after upgrade. Actual we have upgraded to 4.5. There a lot of language strings that have been abandoned in English. They are just no more needed. They have been deleted(?) while upgrade. The same strings in other languages are NOT deleted. They are kept as ghosts entries not having corresponding string in English, not having word_default. They exist ONLY in the language file. 

We still need a routine that deletes those ghost strings from the language packs.

Edited June 19, 2020 by Sonya*

[Ilya Hoilik]

Sonya, try a lifehack. Enable Translated filter and sort by English ascending. As a result you will see a lot of translated strings with no English text. Just remove the translation from all of them and they also be removed from exported language pack.

[Sonya*]

I know 😉 I delete them with SQL though. But this is not what customers should do.

I tell customers they should delete the language pack before they update. Then import the language pack after update. This removes those ghost strings as well.

However I wish there were a purge method for the customers who are not aware. As they get duplicate strings some days (see initial post) and do not understand what is wrong. They ask why they cannot translate those duplicate strings properly.🤔

[Sonya*]

6 hours ago, Ilya Hoilik said:

Sonya, try a lifehack.

I have tried. This does not remove value on duplicate strings. They can only deleted in the database. I do not know any other way to get rid of them 😞

[Ilya Hoilik]

1 minute ago, Sonya* said:

They can only deleted in the database

That's unfortunate 😞

[Sonya*]

This is another example for referrals:

Some keys have been moved from nexus to core.

Quote

we decided that this should now be available as a core feature. Source: https://invisioncommunity.com/news/product-updates/45-invites-and-referrals-r1173/

 

Now, after update we have these strings in core AND nexus in other languages. Changing the German phrase is not possible from now:

The only way to get rid of them after update is to work with SQL.

I have added it to bug tracker as this is actual and reproducible issue in 4.5 

 

Edited June 21, 2020 by Sonya*

[bfarber]

On 6/21/2020 at 4:17 AM, Sonya* said:

I have added it to bug tracker as this is actual and reproducible issue in 4.5 

 

Yup, that's fine - that's the best option in a case like this.