XF 1.5 Support for mysqlnd_ms within XenForo? (Master Slave Databases)

[TML]

Hello,


I've recently deployed the mysqlnd_ms PHP module which provides replication and load balancing support to all PHP MySQL extensions that use mysqlnd. This means that we are able to load balance all database traffic according to a specified central configuration. The plugin itself routes SELECT style queries to the slave database pool, and INSERT style queries to the master database pool (as defined in our configuration). The plugin is working brilliantly for the vast majority of the features that XenForo offers on forums that we host, however there are some issues within the XenForo add-reply functionality that seem to surface on all of our XenForo applications when a user attempts to submit a reply to an existing thread.


I see the following error output within the XenForo error log:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : The MySQL server is running with the --read-only option so it cannot execute this statement - library/Zend/Db/Statement/Mysqli.php:214

Generated By: Username, A moment ago

Stack Trace

#0 /mnt/efs-domain/vhosts/domain.com/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)

#1 /mnt/efs-domain/vhosts/domain.com/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)

#2 /mnt/efs-domain/vhosts/domain.com/library/Zend/Db/Adapter/Abstract.php(753): Zend_Db_Adapter_Abstract->query('\r\n\t\t\tSELECT *\r\n...', 1690277)

#3 /mnt/efs-domain/vhosts/domain.com/library/XenForo/Discussion/Definition/Thread.php(93): Zend_Db_Adapter_Abstract->fetchRow('\r\n\t\t\tSELECT *\r\n...', 1690277)

#4 /mnt/efs-domain/vhosts/domain.com/library/XenForo/DataWriter/Discussion.php(1224): XenForo_Discussion_Definition_Thread->getDiscussionForUpdate(Object(Zend_Db_Adapter_Mysqli), 1690277)

#5 /mnt/efs-domain/vhosts/domain.com/library/XenForo/DataWriter/DiscussionMessage.php(452): XenForo_DataWriter_Discussion->getDiscussionForUpdate()

#6 /mnt/efs-domain/vhosts/domain.com/library/XenForo/DataWriter/DiscussionMessage.php(354): XenForo_DataWriter_DiscussionMessage->_setPosition()

#7 /mnt/efs-domain/vhosts/domain.com/library/XenForo/DataWriter.php(1460): XenForo_DataWriter_DiscussionMessage->_preSave()

#8 /mnt/efs-domain/vhosts/domain.com/library/XenForo/ControllerPublic/Thread.php(571): XenForo_DataWriter->preSave()

#9 /mnt/efs-domain/vhosts/domain.com/library/ForumCube/RestrictedForums/Extend/ControllerPublic/Thread.php(31): XenForo_ControllerPublic_Thread->actionAddReply()

#10 /mnt/efs-domain/vhosts/domain.com/library/SV/AlertImprovements/XenForo/ControllerPublic/Thread.php(45): ForumCube_RestrictedForums_Extend_ControllerPublic_Thread->actionAddReply()

#11 /mnt/efs-domain/vhosts/domain.com/library/XenForo/FrontController.php(369): SV_AlertImprovements_XenForo_ControllerPublic_Thread->actionAddReply()

#12 /mnt/efs-domain/vhosts/domain.com/library/XenForo/FrontController.php(152): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))

#13 /mnt/efs-domain/vhosts/domain.com/index.php(13): XenForo_FrontController->run()

#14 {main}

Request State

array(3) {

  ["url"] => string(73) "https://www.domain.com/threads/thread-title.1690277/add-reply"

  ["_GET"] => array(0) {

  }

  ["_POST"] => array(9) {

    ["message_html"] => string(16) "<p>post reply text.<br></p>"

    ["_xfRelativeResolver"] => string(64) "https://www.domain.com/threads/thread-title.1690277/"

    ["attachment_hash"] => string(32) "b2398bd346daxXxXxeafd04f93ce3a16"

    ["last_date"] => string(10) "1516816478"

    ["last_known_date"] => string(10) "1516816478"

    ["_xfToken"] => string(8) "********"

    ["_xfRequestUri"] => string(39) "/threads/thread-title.1690277/"

    ["_xfNoRedirect"] => string(1) "1"

    ["_xfResponseType"] => string(4) "json"

  }

}

So as you can tell by the first line of the error, "The MySQL server is running with the --read-only option so it cannot execute this statement" indicates that some form of write/delete is being executed on the slave instance. This is because while the PHP plugin does attempt to process SELECT queries to the MySQL Slave, and INSERT queries into the MySQL Master, there are more complicated procedures that developers may use such as "SELECT INTO TABLE", which actually require the writable database be used.

I have been trying to reverse engineer the XenForo code to hopefully find where the query in the error log above is located, but have not been successful in this.

I am wondering if it would be possible to write a replacement actionAddReply() function, or if there is a path where I can review the MySQL statements that XenForo uses, and add the necessary MYSQLND_MS_MASTER_SWITCH constant to direct mysqlnd_ms to forward that query to the master, rather than passively treating it as a basic select.

I do know that there are XenForo plugins that exist which offer the ability to split master and slave connections, however to deploy and provide ongoing management of these types of scripts would be a considerable challenge, and they do not seem to offer much in the way of global configuration.

Perhaps someone has gone through this before and can offer some insight?