SQL UPDATE breaking replication

[rebelde]

I think I found a solution, but I want to report this to you anyway. After upgrading this add-on and a few others, MySQL replication is broken. The problem is that the xf_session_activitity table gets and UPDATE on an line that does not exist. Causing replication to break on me. (I tell the slave servers to skip the line and it is good for a while).

This broke when I updated from 3.8.10 to 3.9.0.

Please only update lines that you are certain are in the table.

Background:
I get these mysql errors, so the table probably fails to record some sessions: The table 'xf_session_activity' is full
- I may have just fixed that by changing it to InnoDB
Mysql binlog_format=MIXED, so replication often sends changes to rows.

Here are two example changes that has stopped replication:

### UPDATE `xf`.`xf_session_activity`
### WHERE
###   @1=0
###   @2='B∙B\x09'
###   @3='B∙B\x09'
###   @4='XF\x5cPub\x5cController\x5cThread'
###   @5='Index'
###   @6=1
###   @7='thread_id=3549969'
###   @8=1645106372
###   @9='google'
### SET
###   @1=0
###   @2='B∙B\x09'
###   @3='B∙B\x09'
###   @4='XF\x5cPub\x5cController\x5cSearch'
###   @5='Results'
###   @6=1
###   @7='search_id=11461581'
###   @8=1645106382
###   @9='google'
# at 97685754



### UPDATE `xf`.`xf_session_activity`
### WHERE
###   @1=0
###   @2='B∙B\x07'
###   @3='B∙B\x07'
###   @4='XF\x5cPub\x5cController\x5cThread'
###   @5='Index'
###   @6=1
###   @7='thread_id=2098007'
###   @8=1645170539
###   @9='google-adsense'
### SET
###   @1=0
###   @2='B∙B\x07'
###   @3='B∙B\x07'
###   @4='XF\x5cPub\x5cController\x5cSearch'
###   @5='Results'
###   @6=1
###   @7='search_id=11474726'
###   @8=1645170552
###   @9='google'
# at 77131503

This is from the binlog, so the actual statements will be different.

Again, I think you just need to make an adjustment to how the session_activity table gets updated. I've never had this problem before.

For the record, I updated a few different plugins the day that this started. It could be any of these, though I suspect it is ES Essentials.

addon-Xon-ElasticSearchEssentials-3.9.0​

addon-Xon-SignupAbuseBlocking-1.10.8​

addon-Xon-MultipleAccountToThread-1.2.0​

SV-StandardLib-1.10.2​

Andy-ForumModerators-1.8​

Thank for everything!

 

[Xon]

This is nothing todo with my add-ons, and is a result of a core XenForo update. This is the result of XenForo 2.2.8 implementing a less deadlock prone way of updating the xf_session_activity table.

You either need to rebuild the slave servers or truncate the xf_session_activity table. MEMORY tables just aren't replication safe with statement/mixed replication when that sort of "table is full" error occurs.

 

[rebelde]

I'm not entirely sure. That is why I'm taking the time to report this to you.

This is not your fault, obviously:
The table 'xf_session_activity' is full

But, and again, I might be wrong, replication worked well despite this until I upgraded those add-ons.

If nobody else is complaining, then whatever.

Just trying to help!

 

[Xon]

Statement based replication (which mixed replication uses) is sensitive to errors which break transitionally applying the query on slaves from the master. MEMORY tables are just MyIASM tables which don't write to disk, and since they don't support transactions really aren't safe.

I recommend opening a ticket with XenForo or posting on the community forums about this issue.

 

[rebelde]

OK, I think I understand how this could be a XF bug. Do you know what line of code in this add-on triggers the UPDATE to the xf_session_activity table? This would help me create a good bug report.

Also, I now see that the The table 'xf_session_activity' is full error is not recent. It has nothing to do with this problem, though strangely converting the table to InnoDB seems to have solved this issue for me.

Thanks again!