J3 Issue No Code Attached Yet
avatar michaelulm
michaelulm
25 Mar 2017

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?

Votes

# of Users Experiencing Issue
1/1
Average Importance Score
5.00

avatar michaelulm michaelulm - open - 25 Mar 2017
avatar joomla-cms-bot joomla-cms-bot - labeled - 25 Mar 2017
avatar photodude
photodude - comment - 25 Mar 2017

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.

avatar csthomas
csthomas - comment - 25 Mar 2017

If you have a patience:

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

avatar franz-wohlkoenig franz-wohlkoenig - change - 2 Apr 2017
Status New Discussion
avatar Llewellynvdm
Llewellynvdm - comment - 11 Apr 2017

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.

avatar brianteeman brianteeman - change - 25 Mar 2018
Labels Added: J3 Issue
avatar brianteeman brianteeman - labeled - 25 Mar 2018
avatar jwaisner
jwaisner - comment - 12 Mar 2020

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

avatar jwaisner jwaisner - change - 12 Mar 2020
Status Discussion Information Required

Add a Comment

Login with GitHub to post a comment