"I know all" tries to import same word multiple times, resulting in unique key error.

[jzohrab]

Describe the bug

If a text contains the same word multiple times with different cases, "I know all" tries to import them all, resulting in a unique key error.

Backtrace:

Backtrace:

#0  do_mysqli_query(INSERT INTO words (
            WoLgID, WoText, WoTextLC, WoStatus, WoStatusChanged, WoTodayScore, WoTomorrowScore, WoRandom ) 
        VALUES( 9, 'Hola', 'hola', 99 , NOW(),  
       ... SNIP
        END), RAND() )) called at [/Applications/MAMP/htdocs/lwt/inc/database_connect.php:60]
... SNIP ...
#2  all_words_wellknown_process_word(99, Hola, hola, 9) called at [/Applications/MAMP/htdocs/lwt/all_words_wellknown.php:116]
#3  all_words_wellknown_main_loop(11, 99) called at [/Applications/MAMP/htdocs/lwt/all_words_wellknown.php:175]
#4  all_words_wellknown_content(11, 99) called at [/Applications/MAMP/htdocs/lwt/all_words_wellknown.php:195]
#5  all_words_wellknown_full(11, 99) called at [/Applications/MAMP/htdocs/lwt/all_words_wellknown.php:202]

Error:

Error Code & Message: [1062] Duplicate entry 'hola-9' for key 'WoTextLCLgID'

To Reproduce

1. Create a new text, with the text "hola hola. Hola. HOLA. Hola hola.". The text is imported correctly.
2. Click on "read text", then click "I know all".
3. error message.

Expected behavior
A clear and concise description of what you expected to happen.

Desktop (please complete the following information):

• OS: mac
• Browser: chrome
• Version This is LWT Version 2.5.2-fork (September 27 2022)

Server Software | Apache/2.4.46
-- | --
PHP Version | 7.4.21
MySQL Version | 5.7.34

Server Software	[Apache/2.4.46](https://en.wikipedia.org/wiki/Apache_HTTP_Server)
[PHP](https://en.wikipedia.org/wiki/PHP) Version	7.4.21
[MySQL](https://en.wikipedia.org/wiki/MySQL) Version	5.7.34

[jzohrab]

This is a rather fundamental problem ... is there a setting or something that my database should have, so that this doesn't throw, or doesn't even try to import?

The data model as it is right now won't work, unless there is something I'm missing.

[HugoFara]

Hi! I've looked into the source code related to your issue, and this kind of problem was clearly bound to happen. The core of the problem seems to be

SELECT DISTINCT Ti2Text, LOWER(Ti2Text) AS Ti2TextLC

in all_words_wellknown_get_words (all_words_wellknown.php). It select distinct pairs of (mixed case, lower case), were we would prefer pairs of (mixed case, distinct lower case). So nothing wrong on your part, it's on LWT side.

As discussed on the Discord, I've found a solution, which is simply to check if word already exists in the database on insertion, so I modified all_words_wellknown_process_word to do so. Until now, things are working just fine, so I hope it will be enough!