Adding Web User via Manager - IIS

Thank you all. I’ve also been stuck on this for a couple of days. The problem according to that linked article is that you get an error instead of a warning when you attempt to insert/update rows with invalid data.

When you install MySQL and run the Instance configuration wizard, you have the choice of either a Standard Configuration or a Custom Configuration. I took the Custom one, which has "Enable Strict Mode" checked by default. This is what puts the Strict rules into the SQL_MODE variable.

In principle, I think that is a good thing but as that article suggests some developers don’t. It appears anyway that ModX was built with the Strict mode turned off.

If strict mode is on then it breaks the "add web user" as well as the "add role" function on my setup. The reason being it seems to be inserting empty php variables into columns that are setup with NOT NULL.

So some possible longer term solutions/options could be:

• Check the MySQL SQL_MODE variable state in the ModX install wizard
• Set the SQL_MODE after every MySQL connect
• Fix the bits in the code that tries to insert invalid data

I like the last option of course But it may not just be the Add Web User and Add Role functions that are affected.

Thanks again.

Hi,

I have also come to grief with this error. I have MODx 0.9.5 running on Windows Server 2003 Standard, IIS 6.0, PHP: 5.1.6 and MySQL: 5.0.16-nt.

I don’t have the option of using the ’SET GLOBAL SQL_MODE=’’;’ workaround suggested above. Could anyone recommend the best way of implementing the ’SET SESSION SQL_MODE=’’;’ workaround instead?

My first preference would be to correct the PHP variable that’s submitting invalid data to MySQL. I’m still going over the code in ’mutate_user.dynamic.php’ (which I think is the right file) to see if anything looks out of place. If anyone with a better understanding of MODx internals could point me in the right direction that would be much appreciated.

Thanks for your help.

OK, I’ve had some success finding a work-around for this bug.

As indicated above, It seems MODx 0.9.5 does not play well with MySQL 5 when the SQL Mode is set to ’STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION’. In this condition the creation of new web users will fail, as will the updates to the details for the Built-in Administration account. This is because MODx inserts empty PHP variables into fields that are set to NOT NULL. See related bugs: http://modxcms.com/bugs/task/167

My suggested workaround involves modifying how these empty empty PHP variables are handled in ’save_web_user.processor.php’ (and possibly ’save_user.processor.php’ too). The variables in question are ’role’, ’gender’, ’dob’, ’blocked’, ’blockeduntil’ and ’blockedafter’. I substituted the block of code starting at about line 34 with the code below and found I could then add new web users without error. You may find you have to modify an identical block in ’save_user.processor.php’

$id = intval($_POST[’id’]);
$oldusername = $_POST[’oldusername’];
$newusername = !empty ($_POST[’newusername’]) ? $_POST[’newusername’] : "New User";
$fullname = mysql_escape_string($_POST[’fullname’]);
$genpassword = $_POST[’newpassword’];
$passwordgenmethod = $_POST[’passwordgenmethod’];
$passwordnotifymethod = $_POST[’passwordnotifymethod’];
$specifiedpassword = $_POST[’specifiedpassword’];
$email = mysql_escape_string($_POST[’email’]);
$oldemail = $_POST[’oldemail’];
$phone = mysql_escape_string($_POST[’phone’]);
$mobilephone = mysql_escape_string($_POST[’mobilephone’]);
$fax = mysql_escape_string($_POST[’fax’]);
//$dob = ConvertDate($_POST[’dob’]); /* Changed to comply with MySQL 5 strict data integrity checks */
$dob = !empty ($_POST[’dob’]) ? $_POST[’dob’] : 0;
$country = $_POST[’country’];
$state = mysql_escape_string($_POST[’state’]);
$zip = mysql_escape_string($_POST[’zip’]);
// $gender = $_POST[’gender’]; /* Changed to comply with MySQL 5 strict data integrity checks */
$gender = !empty ($_POST[’gender’]) ? $_POST[’gender’] : 0;
$photo = mysql_escape_string($_POST[’photo’]);
$comment = mysql_escape_string($_POST[’comment’]);
//$roleid = $_POST[’role’]; /* Changed to comply with MySQL 5 strict data integrity checks */
$roleid = !empty ($_POST[’role’]) ? $_POST[’role’] : 0;
$failedlogincount = $_POST[’failedlogincount’];
//$blocked = $_POST[’blocked’]; /* Changed to comply with MySQL 5 strict data integrity checks */
$blocked = !empty ($_POST[’blocked’]) ? $_POST[’blocked’] : 0;
//$blockeduntil = ConvertDate($_POST[’blockeduntil’]); /* Changed to comply with MySQL 5 strict data integrity checks */
$blockeduntil = !empty ($_POST[’blockeduntil’]) ? $_POST[’blockeduntil’] : 0;
//$blockedafter = ConvertDate($_POST[’blockedafter’]); /* Changed to comply with MySQL 5 strict data integrity checks */
$blockedafter = !empty ($_POST[’blockedafter’]) ? $_POST[’blockedafter’] : 0;
$user_groups = $_POST[’user_groups’];

Would it be better to add a default value of 0 to those fields when creating the table in the SQL so that it’s not possible to to code improperly and produce this error?

Has this been tested against the current SVN ?

Hi Zap and Ryan,

Thanks for your feedback on this issue.

Zap,
Yes, I’m definitely in favour of modifying the SQL to respecct MySQL 5 strict mode. Has this change been slated for a future MODx release?

Ryan,
I’ll check out the new snapshot from SVN. I suspect that may contain the answer to my question above.

Thanks again.

Hi,

I just tried modx-0.9.6-rc3 with MySQL in strict mode. Unfortunately, the creation of new manager and web users still fails. For example, data is getting written to the [modx_]web_users table, but not [modx_]web_user_attributes.