Revolution: SQL error in log when removing a user

[muzzwood]

Summary

The following error is logged when removing/deleting a user.
[2017-12-19 12:29:12] (ERROR @ /home/vagrant/Code/modx/public_html/core/xpdo/xpdo.class.php : 1320) Could not get table class for class: modAccess [2017-12-19 12:29:12] (ERROR @ /home/vagrant/Code/modx/public_html/core/xpdo/xpdo.class.php : 1289) Could not get table name for class: modAccess [2017-12-19 12:29:12] (ERROR @ /home/vagrant/Code/modx/public_html/core/xpdo/om/xpdoobject.class.php : 240) Error 42000 executing statement: Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `modAccess` WHERE `modAccess`.`principal` = 3' at line 1 )

Step to reproduce

Create a test user then remove it via the manager then check the error log.

Observed behavior

This was originally thought to be a problem with the Login Extra. The same error was appearing when adding a user and assigning a usergroup. See here: https://github.com/modxcms/Login/issues/117
However I noticed it also happens when deleting a user account so the cause may be in the core.
It's worth noting that everything works as expected - the user is removed- it's just that the error is logged.

Expected behavior

If removing the user is successful there should not be an error logged.

Environment

MODX 2.6.1, PHP7.1, Tried on both Apache and Nginx, Multiple versions of MySQL.

[shamarin]

Confirm. If i remove a user i get an error in log file.

[sottwell]

Likewise confirmed on MODX Cloud.

Revo 2.6.1
PHP 7.1.1

[sottwell]

Also on 3.0 on Localhost, PHP 7.1.8

[OptimusCrime]

Can you investigate what processor is invoked that causes this problem?

I would also appreciate some more specific steps to reproduce this. "The same error was appearing when adding a user and assigning a usergroup." is too unspecific for me.

[jonleverrier]

Also confirmed.

Steps to reproduce:

1. Create a new user with a username, full name and email address (leave all the other user settings)
2. Click save
3. Once the user has been saved, delete the user from the grid view here /manager/a=security/user
4. View error log

"The same error was appearing when adding a user and assigning a usergroup." is too unspecific for me.

When the register snippet (which comes with the Login extra) has &usergroups set and a user registers, it generates an error in the error log, as reported here. https://github.com/modxcms/Login/issues/117

[OptimusCrime]

Thank you :) If I get the time, I'll see if I can prepare a pull request for this. I think it should be relatively easy to fix.

[muzzwood]

So the processor that's called when deleting a user is: https://github.com/modxcms/revolution/blob/2.x/core/model/modx/processors/security/user/delete.class.php
but I can't see anything explicitly in it that would cause the error.
(In all fairness I don't understand how modAccess works.) Is there an event being triggered?

[wax100]

Hi! As I can find the problem is in composite because Xpdo make such SQL
SELECT modAccess.id AS modAccess_id,
modAccess.target AS modAccess_target,
modAccess.principal_class AS modAccess_principal_class, modAccess.principal AS modAccess_principal, modAccess.authority AS modAccess_authority, modAccess.policy AS modAccess_policy
FROM AS modAccess WHERE modAccess.principal = 2

I try to change lines in files
\core\model\schema\modx.mysql.schema.xml line 849

     <composite alias="Acls" class="modAccess" local="id" foreign="principal" cardinality="many" owner="local" />

to

     <composite alias="Acls" class="modAccessAction" local="id" foreign="principal" cardinality="many" owner="local" />

And file core\model\modx\sqlsrv\modprincipal.map.inc.php line 20
'class' => 'modAccess',
to
'class' => 'modAccessAction',

_Am I right?_

[Mark-H]

You may be on to something @wax100 - because it's a composite, xPDO will try to remove the related records. I don't think your fix is correct though, as there are various access types which all extend modAccess, and we're not just trying to delete modAccessAction's.

I'm not immediately sure what a better fix would be, it might just be removing that relation (=potential breaking change) or at the least changing it to an aggregate instead of composite so xPDO doesn't attempt to remove stuff that doesn't exist in that specific way.

[jolichter]

Hi, same issue and confirmed:

MODX Revolution 2.7.1-pl
PHP 7.2.17
MySQL 5.7.26