Languages can't be deleted, "Field 'LgRemoveSpaces' doesn't have a default value"

[alk0]

Describe the bug

Once created, languages can't be deleted.

Tested with LWT Version 2.7.0-fork (March 14 2023)

To Reproduce

1. Create a new language (Languages, [+] New Language).
2. Fill in any valid settings (or use a wizard); tested with "Japanese_1" being a copy of the "normal" Japanese
3. Go back and try deleting the language ([-] button)
4. Get an error:

Fatal Error in SQL Query: UPDATE languages SET LgName = "", LgDict1URI = "", LgDict2URI = "", LgGoogleTranslateURI = "", LgExportTemplate = "", LgTextSize = DEFAULT, LgCharacterSubstitutions = "", LgRegexpSplitSentences = "", LgExceptionsSplitSentences = "", LgRegexpWordCharacters = "", LgRemoveSpaces = DEFAULT, LgSplitEachChar = DEFAULT, LgRightToLeft = DEFAULT where LgID = 2

Error Code & Message: [1364] Field 'LgRemoveSpaces' doesn't have a default value

Backtrace:

#0 /srv/http/lwt-new/inc/database_connect.php(57): do_mysqli_query('...')
#1 /srv/http/lwt-new/edit_languages.php(146): runsql('...', '...')
#2 /srv/http/lwt-new/edit_languages.php(1303): edit_languages_delete(3)
#3 /srv/http/lwt-new/edit_languages.php(1327): edit_languages_do_page()

[HugoFara]

Hi!

Do you use any specific SQL options? I cannot reproduce your issue and as far as I have checked the code, LgRemovesSpaces always come with a default value, so I don't know what's going on here.

[alk0]

Oh, interesting…

Do you use any specific SQL options?

No, not really, just a regular MariaDB setup with default settings (a default replacement of MySQL for many modern Linux distros, supposedly fully backward-compatible). OK, if you can't reproduce it… it becomes complicated. What else can I do to help investigating the bug?

Anyway, I'll keep you posted if I find anything.

[alk0]

This is the structure of the original LWT languages table:

+----------------------------+------------------+------+-----+---------+----------------+
| Field                      | Type             | Null | Key | Default | Extra          |
+----------------------------+------------------+------+-----+---------+----------------+
| LgID                       | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| LgName                     | varchar(40)      | NO   | UNI | NULL    |                |
| LgDict1URI                 | varchar(200)     | NO   |     | NULL    |                |
| LgDict2URI                 | varchar(200)     | YES  |     | NULL    |                |
| LgGoogleTranslateURI       | varchar(200)     | YES  |     | NULL    |                |
| LgExportTemplate           | varchar(1000)    | YES  |     | NULL    |                |
| LgTextSize                 | int(5) unsigned  | NO   |     | 100     |                |
| LgCharacterSubstitutions   | varchar(500)     | NO   |     | NULL    |                |
| LgRegexpSplitSentences     | varchar(500)     | NO   |     | NULL    |                |
| LgExceptionsSplitSentences | varchar(500)     | NO   |     | NULL    |                |
| LgRegexpWordCharacters     | varchar(500)     | NO   |     | NULL    |                |
| LgRemoveSpaces             | int(1) unsigned  | NO   |     | 0       |                |
| LgSplitEachChar            | int(1) unsigned  | NO   |     | 0       |                |
| LgRightToLeft              | int(1) unsigned  | NO   |     | 0       |                |
+----------------------------+------------------+------+-----+---------+----------------+

^ LgRemoveSpaces, LgSplitEachChar, LgRightToLeft are all default to 0

and this is the "new" LWT (HugoFara) one:

+----------------------------+----------------------+------+-----+---------+----------------+
| Field                      | Type                 | Null | Key | Default | Extra          |
+----------------------------+----------------------+------+-----+---------+----------------+
| LgID                       | tinyint(3) unsigned  | NO   | PRI | NULL    | auto_increment |
| LgName                     | varchar(40)          | NO   | UNI | NULL    |                |
| LgDict1URI                 | varchar(200)         | NO   |     | NULL    |                |
| LgDict2URI                 | varchar(200)         | YES  |     | NULL    |                |
| LgGoogleTranslateURI       | varchar(200)         | YES  |     | NULL    |                |
| LgExportTemplate           | varchar(1000)        | YES  |     | NULL    |                |
| LgTextSize                 | smallint(5) unsigned | NO   |     | 100     |                |
| LgCharacterSubstitutions   | varchar(500)         | NO   |     | NULL    |                |
| LgRegexpSplitSentences     | varchar(500)         | NO   |     | NULL    |                |
| LgExceptionsSplitSentences | varchar(500)         | NO   |     | NULL    |                |
| LgRegexpWordCharacters     | varchar(500)         | NO   |     | NULL    |                |
| LgRemoveSpaces             | tinyint(1) unsigned  | NO   |     | NULL    |                |
| LgSplitEachChar            | tinyint(1) unsigned  | NO   |     | NULL    |                |
| LgRightToLeft              | tinyint(1) unsigned  | NO   |     | NULL    |                |
+----------------------------+----------------------+------+-----+---------+----------------+

^ LgRemoveSpaces, LgSplitEachChar, LgRightToLeft have no default values

Forgive me if I don't understand something, my knowledge in MySQL is limited (and even more limited in PHP).

[alk0]

Hm, I changed the defaults to 0 (manually, alter table languages alter LgRemoveSpaces SET DEFAULT 0;, repeat for LgSplitEachChar and LgRightToLeft), then I could "remove" one of the languages without showing errors, but in fact it was just nullified in the languages table, not deleted, so, when I tried to delete another one, I got another error, complaining about LgName not being unique (duplicate empty string). So, I stopped trying and just removed them manually from the table (delete from languages where LgID=2;, etc) - in my case I know for sure they are not used anywhere, were created just for testing. So, for me the problem is solved.

[HugoFara]

Well, I had some time to investigate your in depth and I cannot find anything out of the ordinary... Maybe you have a permission issue or a corrupted database? I'm closing the issue for now, I can reopen it if someone has the same problem. Sorry for not being able to help you more...