High Number of Groups leads to Problem with Rules (assets table)

[michaelulm]

Steps to reproduce the issue

we are using groups object to represent "customers". now we reached a "high" number of groups, more than 150.
The main problem happens when several groups has some special permissions which are not inherits from parents, and when we disallow a special rule. in this way the json string increase and cannot be inserted / updated into the assets table

Expected result

endless groups with individual rules should work

Actual result

if we reach too many individual rule settings some groups, all settings get lost. we used a workaround to config rules column with VARCHAR(16000), but we think, that will not be enough. is it possible to use TEXT as default, or maybe MEDIUMTEXT or LONGTEXT, to support high number of groups and individual rules settings.

System information (as much as possible)

joomla 3.6.5

Additional comments

is there any known reason, that TEXT type wouldn't work in assets table at rules column?

[photodude]

offhand, (quick google search), TEXT, BLOB and VARCHAR for INNODB are stored inline with the table. So likely no technical difference other than performance.

Since MySQL 5.7.8 a native JSON data type is supported, but Joomla supports MySQL 5.1 as a minimum so that's not an option yet. PostgreSQL support's JSON columns at v9.2 and MSSQL support's JSON columns at SQLSRV 2016

I would agree that MEDIUMTEXT might be a better choice than VARCHAR since it's good for large json bodies, at least until JSON columns are an available option in the Joomla minimum requirements. (note: The space required to store a JSON document is roughly the same as for LONGBLOB or LONGTEXT but limited to the value of the max_allowed_packet system variable) but note due to the sql versions that support JSON it's a long ways off to change to a pure JSON column format.

but with that said there are some trade-offs
One performance difference is that INNODB TEXT fields force temporary tables to disk during SELECT statements. This can be a performance hit for smaller data, but large VARCHARS will fill up the memory buffers and may still go to disk causing a different performance issue.
in MyISAM Varchar is stored in the table, but text is linked to the table, so performance gain when using VARCHAR. but note: we usually only care about INNODB as that's what's typically used currently.

In the long run to get the type of expected result you want "unlimited groups with unlimited individual rules" additional consideration will likely need to be taken for the ALC storage and overall asset table design.

[photodude]

Joomla 4 has minimum MSSQL of 5.6 and PostgreSQL11.0 so native JSON data type columns are still not an option to help solve this issue.

[csthomas]

If you have a patience:

• http://mysqlserverteam.com/externally-stored-fields-in-innodb/
• https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html

IMO the best would be to use TEXT / VARCHAR(someLimit) on table with ROW_FORMAT=COMPRESSED.

[Llewellynvdm]

This problem does not only come up with many groups but all so with many permissions like this component

I would say MEDIUMTEXT is working well to insure high volume structures are not broken.

Sure we can wait for the DB to improve, but for now if using a 3rd party component with huge permission structures

--
-- Always insure this column rules is large enough for all the access control values.
--
ALTER TABLE `#__assets` CHANGE `rules` `rules` MEDIUMTEXT NOT NULL COMMENT 'JSON encoded access control.';

This may be a temp fix, but if not used the whole permissions structure breaks once the field size is reacted, and this causes unexpected behavior.

[jwaisner]

@michaelulm are you still experiencing this issue with the latest J3?

[alikon]

even i think this is a valid point, i also think this deserve a broader "discussion"