search

HugoFara / lwt

Learn languages by reading! A language learning app stemmed from Learning with Texts (LWT).
https://hugofara.github.io/lwt/
The Unlicense
169 stars 19 forks source link

"Duplicate entry '0'" when archiving texts #118

Closed HenryWales closed 1 year ago

HenryWales commented 1 year ago

I don't want to bother you with all the reports. I appreciate your work on this fork. This issue is the most serious of all by far.

For a reason I can't explain, when trying to archive a text it uses 0 as the primary key instead of the proper number (and the text archived with this ID of 0 shows at the very bottom of the archived texts list). This leads to a duplicate error as soon as there is already one successfully archived text taking ID 0.

The error message:

Fatal Error in SQL Query: INSERT INTO archivedtexts ( AtLgID, AtTitle, AtText, AtAnnotatedText, AtAudioURI, AtSourceURI ) SELECT TxLgID, TxTitle, TxText, TxAnnotatedText, TxAudioURI, TxSourceURI FROM texts WHERE TxID = 133

Error Code & Message: [1062] Duplicate entry '0' for key 'PRIMARY'

Backtrace:

#0 C:\xampp\htdocs\lwt\inc\database_connect.php(57): do_mysqli_query('INSERT INTO arc...')
#1 C:\xampp\htdocs\lwt\edit_texts.php(391): runsql('INSERT INTO arc...', 'Archived Texts ...')
#2 C:\xampp\htdocs\lwt\edit_texts.php(1314): edit_texts_archive(133)
#3 C:\xampp\htdocs\lwt\edit_texts.php(1336): edit_texts_do_page()

I have done several restore and backup operations on my DB today, so I thought it might be related, but the issue persists upon emptying the DB and trying with the demo DB (import of the demo DB is partly broken btw).

I had done some cosmetics tweaks in some files, I triple-checked that the issue persists after restoring all lwt files to the 2.8.1 release version (my tweaks didn't even touch edit_texts.php).

Server info:

Server Software | Apache/2.4.56 PHP Version | 8.2.4 MySQL Version | 10.4.28-MariaDB

If you can't reproduce the issue, I can run on my installation any info debugging code you provide me and report the results to help you find the source.

HugoFara commented 1 year ago

Hi!

That's a nice spotted issue! I don't know why but the field "AtID" of the archivedtexts table was no auto incremented (AUTO_INCREMENT).

I bundled a fix to the database, that will come with the next release. In the meantime you can fix the issue in your own database by running

ALTER TABLE archivedtexts MODIFY COLUMN AtID smallint(5) unsigned NOT NULL AUTO_INCREMENT

Tell me if you need anything else!

Hugo

HenryWales commented 1 year ago

Running once the code from your commit containing the SQL command has fixd the issue, archival now works properly. Thank you.