+ Start a Discussion
jhartjhart 

Critical bug in Apex code: MIXED_DML_OPERATION prevents Users from having related objects

The "MIXED_DML_OPERATION" issue that plagues unit tests (see this forum discussion) is now effecting production code as well.

Our application adds an extra custom field to Users called "Other Emails".  This is a string field where a User can enter a bunch of email aliases; whenever it's updated we normalize the information into a separate "EmailAddress" table and the Address then points back at the User.

As of yesterday, that worked great.  I updated my User record to add an address to the "Other Emails" field.  The trigger code executed perfectly, and the EmailAddress object was created & linked to my User record.

As of today, it is failing:



This is a critical part of an Apex-based application that we have been developing for over 8 months, and this section of code has always worked until today.

We've had to comment out unit tests to deal with this issue - which was lame, but acceptable - but actually changing our object relationships and code at this late date would be difficult.
gjsgjs
The new mixed DML behavior should be only from 154 API Version onwards. For some reason our regression test case passes, but I tried your code and see the problem. I have created a bug, will update once a dev looks at it.
jhartjhart
GJS - thanks for your quick reply.
 
Can you elaborate on what you mean by "new mixed DML behavior should be only from 154 API Version onwards"?
 
I hope you don't mean that, in the future, triggers that update other objects in response to User changes will not be allowed.  This would be a big problem for us, and I assume for other developers as well.
gjsgjs
Can you please post your trigger?  If I understand you correctly, you should be fine once we fix the problem (ie you should be able to use a trigger to update another entity when a user field is updated by a non-trigger operation). The change in behaviour in 154 is that if a trigger itself updates a user, it cannot update any other entity.
 
 

In previous releases, in a single transaction that involved triggers, you could perform DML operations on

more than one type of sObject, for example, you could insert an account, then insert a user. As of Summer

'08, you can only perform DML operations on a single type of sObject from the following list of sObjects.

For example, you cannot insert an account, then insert a user, or update a group, then insert a group

member.

Group

GroupMember

QueueSObject

User

UserRole

UserTerritory

Territory

In addition, User and Territory now support the insert and update DML operations, and UserRole

now supports the insert, update delete and upsert DML operations.

Apex DML operations are not supported on the following sObjects:

AccountTerritoryAssignmentRule

AccountTerritoryAssignmentRuleItem

UserAccountTeamMember

For more information, see sObjects That Cannot Be Used Together in DML Operations.

jhartjhart
The trigger is moderately complex and doesn't do any direct DML (it calls out to other classes), so I don't think posting it would be helpful.  However, I can confirm that it doesn't update any of the restricted object types - it's an "after update, after delete, after insert" trigger that compares the old & new User objects and updates the normalized "Email Address" and "Email Address : User" junction object.  That's an update to two object types, but none of them are on the restricted list.
 
I just read through the "sObjects That Cannot Be Used Together in DML Operations" page and I see this final statement:
 
If you are using a Visualforce page with a custom controller, you can only perform DML operations on a single type of sObject within a single request or action. However, you can perform DML operations on different types of sObjects in subsequent requests, for example, you could create an account with a save button, then create a user with a submit button.
 
The docs don't explicitly state this, but I assume this restriction only applies to objects on the restricted list.
 
We have a page + custom controller that creates both Leads & Contacts with a single button press.  That page still works fine, so I assume that this is (and will be) allowed except when performing DML on a restricted object type.
 
thanks!
 
Chirag MehtaChirag Mehta
Hey did anyone got a solution to have two DML(insert on setup object like custom object and update on user(non setup) object)
 
Pls share if there's any workaround! .
Message Edited by Chirag Mehta on 05-29-2009 09:53 AM
sfdcdev.wordpress.comsfdcdev.wordpress.com

Hello,

 

I found a work around for the above issue is test methods. I have no idea why salesforce is behaving like this. The trick is to use future and runas together .. and you would be able to get around this issue.

 

Suppose you have to insert a User record and also a Account record into Salesforce for testing, if you have done it in normal way it woudl result in MIXED_DML exception. 

As a work around: I created a static Variable User in the class. In the test method all I did was to create the user record, insert it into salesforce and populate the class variable. In the setter method of the user record, I called thea @future method that inserts an account record by using System.runsAs().

By doing this the whole thing worked with no errors. Hope this helps. 

Here is the code:: - This code is pretty similar to what I was trying to explain in the sentense above, there is one more step of insertign groupMember record in the below code, but this code solved my issues.  Please let me know if this resolved the issue. 

global class TestExampleClass {


static User user = new User();
static GroupMember grpMember = new GroupMember();

private static void setUser(User auser){
user = auser;
TestCopyUserGrpNameToProspectFA.createGrpAndGrpMember();
}

private static void setGrpMember(GroupMember aGroupMember){

grpMember = aGroupMember;

TestCopyUserGrpNameToProspectFA.testSinglegrpNametoProsFA();

}




//Only Test Method that would start the whole process.
@istest
private static void starter(){
createUser();
}



static void createUser(){
//User user = new User();
user.Username = 'test@us.xyz.com';
user.LastName = 'LastTestName';
user.Email = 'test@us.xyz.com';
user.alias = 'testAl';
user.TimeZoneSidKey = 'America/New_York';
user.LocaleSidKey = 'en_US';
user.EmailEncodingKey = 'ISO-8859-1';
user.ProfileId = [select id from Profile where Name='System Administrator'].Id;
user.LanguageLocaleKey = 'en_US';
insert user;
setUser(user);
}



static void createGrpAndGrpMember(){
Group grp = new Group();
grp.Name = 'test10';
insert grp;

grpMember.UserOrGroupId = user.Id;
grpMember.GroupId = grp.id;
insert grpMember;
setGrpMember(grpMember);
}








@future
private static void testSinglegrpNametoProsFA(){


Map<String,Schema.RecordTypeInfo> tskRtMapByName = Schema.SObjectType.Account.getRecordTypeInfosByName();

User user2 = new User();
user2.Username = 'test2@us.xyz.com';
user2.LastName = 'Last2TestName';
user2.Email = 'test2@us.xyz.com';
user2.alias = 'testA2';
user2.TimeZoneSidKey = 'America/New_York';
user2.LocaleSidKey = 'en_US';
user2.EmailEncodingKey = 'ISO-8859-1';
user2.ProfileId = [select id from Profile where Name='System Administrator'].Id;
user2.LanguageLocaleKey = 'en_US';

System.runAs(user2){
String individualProspectRid = RecordTypeHelper.getRecordTypeId('Account', 'Individual Prospect');
Account account = new Account();
account.LastName = 'test1';
account.OwnerId = user.Id;
account.RecordTypeId = individualProspectRid;
insert account;
GroupMember grpMem = [Select g.UserOrGroupId, g.Id, g.Group.Name, g.GroupId From GroupMember g where UserOrGroupId =:user.Id];
System.assertEquals(grpMem.Group.Name,[select FANumber__pc from Account where Id=:account.id].FANumber__pc);
}

}
}

 

 

 

 

jhartjhart

Girish,

 

That's a creative way to deal with it - nice!  There are a couple caveats:

 

1.  The @future operation is in a separate transaction (of course).

 

2.  There's a salesforce bug where @future methods run in the wrong user context:

 

cheers,

j

 

Message Edited by jhart on 05-29-2009 08:52 AM
Chirag MehtaChirag Mehta

Is there a need to use System.runsAs()?

 

Can you please clarify more on why to use System.runsAs() for @future method call ?

sfdcdev.wordpress.comsfdcdev.wordpress.com

Yes, we have to use the System.runAs(). As JHart mentioned in his message -  because of the salesforce 'bug' -  @future method runs in user context. So once you use runAs and run the future method in a different user context its as if you are starting with a clean slate. You would not have the Mixed DML exceptions as the the insertion of User and the insertion of the Account are performed in two different contexts.

 

Girish Suravajhula

 

Message Edited by Girish989 on 05-29-2009 11:24 AM
jhartjhart

Girish,

 

Your approach (splitting "restricted type" ops into an @future) call is interesting, but the implementation path you show above doesn't work (or, at least, it wouldn't work outside of a testMethod & I'm surprised it works even there - if so it's b/c the testMethod doesn't actually spawn a new thread for the @future call, which may or may not be the case).

 

Here's why:  static variables in Apex Code don't work like static variables in Java.  In Java, the lifecycle of a static var is the lifecycle of the entire virtual machine, so if you set a static var & then reference it later in another thread it would still be set.  In Apex, static variables exist only for the lifetime of a single request, so in your @future method the variable will be null regardless of what you did with it outside the @future method.

 

Even if that weren't the case (ie, if you were in Java), using a static var like that is a bad idea b/c another thread may alter the value of the static var before the @future method gets a chance to operate on it.

 

Both of these are easily fixed by simply passing the value into the @future method, so instead of:

 

static User user = null

static void doSomething() {
user = new User(...);
doSomethingLater();
}

@future
static void doSomethingLater() {
insert user; // FAILS IN APEX B/C USER WILL BE NULL
// BAD IDEA IN JAVA, AS VAR MAY HAVE CHANGED SINCE CALLER SET IT
}

 


 You should do this:

 

 

static void doSomething() {
User user = new User(...);
doSomethingLater(user);
}

@future
static void doSomethingLater(User user) {
insert user; // works
}

 

 

 

 

Message Edited by jhart on 05-29-2009 02:12 PM
trigon-grouptrigon-group

This thread implies that the workaround only is useful in unit tests, not in code deployed to production.  Has anyone come up with a solution that allows changes to a setup object (i..e 'User' object) and non-setup objects (i.e. a custom object) in the same action.  My need is to update the 'User' object and create new objects of a custom type in the same action that is being invoked by the press of a button on a Visualforce page.  Is this doable...?

 

Thanks!

jhartjhart

Update the user in the vforce thread & then pass the user (or userid) to your @future method.  Or vice-versa.

 

The point is that you should use the @future method arguments to hold your context, rather than static vars.

Chirag MehtaChirag Mehta

To add more on below thought ....

 

As this page of the Apex docs indicates, you can't just make a User trigger that updates a Contact or an Account, because it's forbidden to modify those "non-setup" objects from a "setup" object like User.  

 

Fortunately there is a simple solution: the @future annotation.  The @future annotation allows you to create Apex that runs asynchronously at some point in the future (in my tests I've found that it runs immediately, or at least very soon after the trigger executes).  Methods that use @future are subject to different limits than normal trigger operations because @future methods don't hold up the trigger (and therefore the entire user experience) while they're working.  Therefore @future methods are often used to perform long-running web service callouts from triggers asynchronously.  However, they can also be handy for a case like ours, where we want to update an object that we're not normally eligible to update.

 

Read More @ http://blogs.salesforce.com/support/2009/01/allowing-custom.html

 

 

 

Scott.MScott.M

Why would this behavior apply to tests? Surely salesforce can see the need to do more than one type of dml operation during setup in a test?

 

 

Scott.MScott.M

I ran into something similar before and there is actually a very simple solution :) I just completely forgot about it:

 

http://community.salesforce.com/sforce/board/message?board.id=apex&message.id=9742

 

If you wrap the dml statements in a run as{} block then it will solve everything.

 

Cheers,

Scott

jhartjhart

Scott -

 

Nice - I'm marking this as the preferred solution.  runAs() blocks didn't exist when I first logged this bug (nor did @future, for that matter) but it's great to see they can be used to workaround mixed DML issues.

 

Given that the workaround is so easy, and they're all in the same transaction even with runas ... what is the point of the MIXED_DML restriction in the first place?  none that I can see.

jhartjhart

Note that "runAs" can only be used in test methods, so I un-marked the runAs post as the solution...

Larry LeonidasLarry Leonidas

Works.  If you guys haven't had any closure..... a simple System RunAs() on the "Setup Objects" does it. For example:

 

@isTest
private class TestAutoFollowTrigger {

     static void createUsersandGroups(){
			
	/////Profile to assign (could also create one if you want)
	Profile sysadmin = [SELECT id FROM Profile WHERE Name = 'System Administrator' LIMIT 1];
    	
	/////New User (shows all required fields)
    	User requser = new User(username = 'XTestrequser@platinumgroup.org', email = 'XTestrequser@platinumgroup.org', LastName = 	  
'XTestrequser', Alias = 'XTestOps', TimeZoneSidKey = 'America/Los_Angeles', LocaleSidKey = 'en_US', LanguageLocaleKey = 'en_US', EmailEncodingKey = 'ISO-8859-1', ProfileId = sysadmin.id); insert requser; /////New Group Group claims2g = new Group(Name = 'XClaims2'); insert claims2g; /////New Group Member GroupMember claimsgmG = new GroupMember(GroupId = claims2g.id, UserorGroupId = requser.id); insert claimsgmG; } static testMethod void AutoFollowTest() { User admin = [SELECT id FROM User WHERE LastName = 'Admin' LIMIT 1]; System.runAS(admin) { createUsersandGroups(); } test.startTest(); ///YOUR TEST CODE HERE........................ test.stopTest(); }

 

 

jhartjhart

"runAs" can only be used in test methods, so this doesn't help with actual running code.

Larry LeonidasLarry Leonidas

@trigon-group


I'd say your best bet is to break apart into seperate classes and methods and use Apex:actionFunction.

Larry LeonidasLarry Leonidas

@jhart

Oh yes, sorry. If you're using a MailController then maybe a seperate the method out into Trigger.

john_hezhengjohn_hezheng

Yes, I face the similary issue.  what I am trying to do is:

1. we have a custom object employee__c

2. we want to migrate the employee into SFDC from our HR application

3. when the employee is upload, the corresponding user is created automatically by the trigger on employee__c object

 

but we face the Mixed DML issue as well, could anyone have the solution?  thanks!

 

JavedGouryJavedGoury

well,

 

you can update the custom object in class and can write trigger on User to update.

 

I have done this once.

 

Thanks,

Javed

nivyajnivyaj

JavedGoury- can you please post your code?