Bug: Wrong database design structure

[sabersaberi2]

Steps to reproduce the issue

hi and thanks lovely Joomla
this Issue is related to #30101 but not exactly to that. it is about wrong UX and this issue about wrong Database structure.
to reproduce the issue you must generate 2000 joomla usergroups and then go to global configuration of joomla and then try to save that. (for this maybe you need increase some php variable such as max_input_var or max_execution_time or any other )
after save you get this error:
Error decoding JSON data: Control character error, possibly incorrectly encoded

Expected result

save without problem if i set correctly php.ini

Actual result

return this error after save:
Error decoding JSON data: Control character error, possibly incorrectly encoded

System information (as much as possible)

last joomla version 3.9.20
correct php.ini setting

Additional comments

the problem that limit count of joomla usergroups, is type of "params" column of _extensions table. i change TEXT type to MEDIUMTEXT to can save global configuration. Text Filter Settings tab in the global configuration menu, want save filters in the params column and TEXT type limit is 65,535 characters.

[HLeithner]

Can you explain your use case for 2000 usergroups please?

[sabersaberi2]

@HLeithner
it is only an example. and it is maybe future of my website. because i use joomdle
it is connect Joomla to Moodle and you can set some setting to create 2 usergroups in Joomla when you create one course in Moodle. in this way when you have 600 course in Moodle , you almost have 1200 usergroups in Joomla.
and now i create a component to create online classrooms in Joomla and in my scenario each classroom must have two usergroup and if Joomla has a limitation to create usergroups i must say to my users that you can not create more than for example 500 classroom in your Joomla website

[alikon]

an alter table for the field "params" of the table #__extensions from TEXT to MEDIUMTEXT/VARCHAR/BLOB/CLOB or whatever shouldn't be a big issue......what i'm mostly concerned about is the misuse of that table field
surely my bad but i still cannot understand the need of 1 Moodle course = 2 Joomla usergroup

[sabersaberi2]

@alikon
One for teacher and one for student in each course.
It is more clear that is limitation and problem creator for create huge Joomla usergroups and maybe for you it is easy to understand this problem and change type of column but most of user maybe has not any knowledge about MySQL and if they see
Error decoding JSON data: Control character error, possibly incorrectly encoded
They can not resolved that easily.
All things are difficult before they are easy.
Maybe it is better for joomla to save Text filter in other seperate table. Professional admin of joomla project better understand than me. I am not professional system designer.

[HLeithner]

if you use a json field in the database you have a good chance that it's wrong. Beside this using joomla usergroups for only one or 2 persons seams wrong too and should be handle by the component a bit smarter at least the joomla acl system is not designed for so many user groups (as you found out)... since changing the json column to something better is a bigger task I wouldn't expect it before 5.0.

Changing the params column to a bigger column doesn't makes it better, because this make joomla much slower depending how the table is used the complete content of the field have to be transferred between db server and webserver and in worst case the json gets decoded...

[Quy]

the problem that limit count of joomla usergroups, is type of "params" column of _extensions table. i change TEXT type to MEDIUMTEXT to can save global configuration. Text Filter Settings tab in the global configuration menu, want save filters in the params column and TEXT type limit is 65,535 characters.

See @wilsonge #25702 (comment) against this.

[sabersaberi2]

@Quy
Thank you but I don't talk about any other external plugin or extension. I want use Joomla with 2000 usergroups. If it is not possible, simply say Joomla have an limitation in number of usergroups. I say exactly similar Wilsonge's logic. The cell must not save many configuration because I have issue with performance in loading global configuration. Maybe filter setting parameters need separate table as I say in the previous comment, not save json in a cell. It is not result of any extension, it is individual Joomla issue.