Error when attempting to change username

[Apt Irrelevance]

Receiving this error message when trying to edit a username in the admin control panel. This has been tested on multiple user accounts by multiple admins.

The following error occurred:
 
Mysqli statement execute error : The total number of locks exceeds the lock table size
Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 317
Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 632
Zend_Db_Adapter_Abstract->update() in XenForo/DataWriter/User.php at line 916
XenForo_DataWriter_User->_postSave() in XenForo/DataWriter.php at line 1385
XenForo_DataWriter->save() in XenForo/ControllerAdmin/User.php at line 481
XenForo_ControllerAdmin_User->actionSave() in UserEss/ControllerAdmin/User.php at line 30
UserEss_ControllerAdmin_User->actionSave() in XenForo/FrontController.php at line 310
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
XenForo_FrontController->run() in /var/www/html/admin.php at line 13

I'm not sure if this is an issue with our database, with Xenforo, or somehow a casualty of our data migration.

 

[CTXMedia]

What is the value of innodb_buffer_pool_size in your my.cnf?

http://rackerhacker.com/2010/02/16/mysql-the-total-number-of-locks-exceeds-the-lock-table-size-2/

MySQL is trying to tell you that it doesn't have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.

I would imagine the size of your user table (250,000 members) might require more buffer space to complete the username change query.

I could be wrong but it's worth checking.

 

[Apt Irrelevance]

Thanks. We increased it to 1GB and increased the innodb timeout to 100 seconds and that seems to be working for the most part. It's not logging any errors in the error log, however, it's still giving a timeout error on submit after about 30 seconds.

 

[CTXMedia]

PHP limits script execution to 30 seconds by default to avoid runaway processes trashing your server.

You can adjust it in your php.ini file to run longer (which may help your username changes complete without error):

max_execution_time = 30;

Adjust to suit.

 

[Jake Bunce]

Is this enabled? Try disabling it:

Admin CP -> Home -> Options -> Performance -> Update denormalized usernames on username change

 

[Apt Irrelevance]

Heh, well when I tried disabling that, I got this error:

Server Error
 
mkdir(): Permission denied
 
    XenForo_Application::handlePhpError()
    mkdir() in XenForo/Helper/File.php at line 66
    XenForo_Helper_File::createDirectory() in XenForo/Template/FileHandler.php at line 74
    XenForo_Template_FileHandler->_createTemplateDirectory() in XenForo/Template/FileHandler.php at line 106
    XenForo_Template_FileHandler->_deleteTemplate() in XenForo/Template/FileHandler.php at line 67
    XenForo_Template_FileHandler::delete() in XenForo/Model/Template.php at line 1440
    XenForo_Model_Template->deleteTemplateFiles() in XenForo/Option/TemplateFiles.php at line 27
    XenForo_Option_TemplateFiles::verifyOption()
    call_user_func_array() in XenForo/DataWriter/Option.php at line 345
    XenForo_DataWriter_Option->_validateOptionValuePreSave() in XenForo/DataWriter/Option.php at line 211
    XenForo_DataWriter_Option->_preSave() in XenForo/DataWriter.php at line 1422
    XenForo_DataWriter->preSave() in XenForo/DataWriter.php at line 1361
    XenForo_DataWriter->save() in XenForo/Model/Option.php at line 568
    XenForo_Model_Option->updateOptions() in XenForo/ControllerAdmin/Option.php at line 174
    XenForo_ControllerAdmin_Option->actionSave() in XenForo/FrontController.php at line 310
    XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
    XenForo_FrontController->run() in /var/www/html/admin.php at line 13

Possible CHMOD settings issue on the server?

 

[Jake Bunce]

That would be from this setting on the same page which is normally disabled:

Admin CP -> Home -> Options -> Performance -> Fetch public templates as files

 

[Apt Irrelevance]

You're recommending I enable it, correct? I'll have to double check and make sure we have an opcode cache installed first.

 

[Jake Bunce]

I recommend disabling it unless you want to use that feature in which case you need to make sure data and internal_data are writable.

edit - well, those directories should be writable anyways.

 

[Apt Irrelevance]

Oh, I see. Well it was already disabled when the above error was thrown. I thought it might be a CHMOD issue because we moved the Xenforo installation recently.