• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Fixed MariaDB 10.1.x - Incorrect integer value: '' for column 'content_id' at row 1

maxicep

Active member
#1
That bug is occurred on moderation query in xenforo system.

We are getting this errors after switch to mariadb 10.1.x and Moderators can't delete the posts or threads (some posts or threads still deleting without problem but most of threads /posts can't delete now. )

Code:
Server Error Log
Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Incorrect integer value: '' for column 'content_id' at row 1 - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Admin, 49 minutes ago
This is the output of post/thread delete page.
Code:
Server Error

Mysqli statement execute error : Incorrect integer value: '' for column 'content_id' at row 1

Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
Zend_Db_Adapter_Abstract->query() in XenES/Model/Elasticsearch.php at line 301
XenES_Model_Elasticsearch->logFailedIndex() in XenES/Search/SourceHandler/ElasticSearch.php at line 1012
XenES_Search_SourceHandler_ElasticSearch->_logFailedIndex() in XenES/Search/SourceHandler/ElasticSearch.php at line 113
XenES_Search_SourceHandler_ElasticSearch->deleteFromIndex() in XenForo/Search/Indexer.php at line 105
XenForo_Search_Indexer->deleteFromIndex() in XenForo/Search/DataHandler/Thread.php at line 85
XenForo_Search_DataHandler_Thread->_deleteFromIndex() in XenForo/Search/DataHandler/Abstract.php at line 315
XenForo_Search_DataHandler_Abstract->deleteFromIndex() in XenForo/DataWriter/Discussion.php at line 725
XenForo_DataWriter_Discussion->_deleteFromSearchIndex() in XenForo/DataWriter/Discussion.php at line 790
XenForo_DataWriter_Discussion->_postDelete() in XenForo/DataWriter.php at line 1793
XenForo_DataWriter->delete() in XenForo/Model/InlineMod/Thread.php at line 219
XenForo_Model_InlineMod_Thread->deleteThreads() in XenForo/ControllerPublic/InlineMod/Thread.php at line 44
XenForo_ControllerPublic_InlineMod_Thread->actionDelete() in XenForo/FrontController.php at line 351
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
XenForo_FrontController->run() in /home/nginx/domains/domain.com/public/forum/index.php at line 13

In my researches, If a PHP script tries to set a string value, such as an empty string, into a MySQL integer data type or column, you will likely see this error message:
Incorrect integer value ” for column ‘name’ at row 1
Solution is looking that edit mysql server settings (my.cnf) and apply these commands but it doesn't fix the problem now. (sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES)

Edit:
I have tried to Disable plugins one by one and it occur when enabled your "Xenforo Enhanced Search" plugin. So, problem should be related with it.
 
Last edited:

maxicep

Active member
#2
I think, this problem related with this query on your xenforo enhanced search feature

library/XenES/Model/Elasticsearch.php

Code:
        $this->_getDb()->query("
            INSERT INTO xf_es_search_failed
                (content_type, content_id, action, data, fail_count, reindex_date)
            VALUES
                (?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                fail_count = VALUES(fail_count),
                reindex_date = VALUES(reindex_date)
        ", array($contentType, $contentId, $action, serialize($record), $failCount, $reindexDate));

        return true;
    }
It cause the error on mysql.
 

Mike

XenForo developer
Staff member
#3
I'll move this to the ES bug forum, but I think it's fundamentally related to issues with your Elasticsearch setup, as it's erroring trying to log a failed index.
 

maxicep

Active member
#4
I'll move this to the ES bug forum, but I think it's fundamentally related to issues with your Elasticsearch setup, as it's erroring trying to log a failed index.
Hello Mike

What's wrong with above SQL Query you think?

When i look the stack trace, line #4 trying to insert null and zero values. It may occurred this problem.

Stack Trace
Code:
#0 /home/nginx/domains/domain.com/public/forum/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/nginx/domains/domain.com/public/forum/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/nginx/domains/domain.com/public/forum/library/XenES/Model/Elasticsearch.php(301): Zend_Db_Adapter_Abstract->query('\r\n\t\t\tINSERT INT...', Array)
#3 /home/nginx/domains/domain.com/public/forum/library/XenES/Search/SourceHandler/ElasticSearch.php(1012): XenES_Model_Elasticsearch->logFailedIndex('delete', '', '', NULL, 0)
#4 /home/nginx/domains/domain.com/public/forum/library/XenES/Search/SourceHandler/ElasticSearch.php(113): XenES_Search_SourceHandler_ElasticSearch->_logFailedIndex('delete', '', '')
#5 /home/nginx/domains/domain.com/public/forum/library/XenForo/Search/Indexer.php(105): XenES_Search_SourceHandler_ElasticSearch->deleteFromIndex('post', Array)
#6 /home/nginx/domains/domain.com/public/forum/library/XenForo/Search/DataHandler/Post.php(99): XenForo_Search_Indexer->deleteFromIndex('post', Array)
#7 /home/nginx/domains/domain.com/public/forum/library/XenForo/Search/DataHandler/Abstract.php(315): XenForo_Search_DataHandler_Post->_deleteFromIndex(Object(XenForo_Search_Indexer), Array)
#8 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter/DiscussionMessage.php(986): XenForo_Search_DataHandler_Abstract->deleteFromIndex(Object(XenForo_Search_Indexer), Array)
#9 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter/DiscussionMessage.php(744): XenForo_DataWriter_DiscussionMessage->_deleteFromSearchIndex()
#10 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter/DiscussionMessage.php(585): XenForo_DataWriter_DiscussionMessage->_indexForSearch()
#11 /home/nginx/domains/domain.com/public/forum/library/XenForo/DataWriter.php(1423): XenForo_DataWriter_DiscussionMessage->_postSave()
#12 /home/nginx/domains/domain.com/public/forum/library/XenForo/Model/InlineMod/Post.php(149): XenForo_DataWriter->save()
#13 /home/nginx/domains/domain.com/public/forum/library/XenForo/ControllerPublic/InlineMod/Abstract.php(125): XenForo_Model_InlineMod_Post->deletePosts(Array, Array, NULL)
#14 /home/nginx/domains/domain.com/public/forum/library/XenForo/ControllerPublic/InlineMod/Post.php(56): XenForo_ControllerPublic_InlineMod_Abstract->executeInlineModAction('deletePosts', Array, Array)
#15 /home/nginx/domains/domain.com/public/forum/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_InlineMod_Post->actionDelete()
#16 /home/nginx/domains/domain.com/public/forum/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#17 /home/nginx/domains/domain.com/public/forum/index.php(13): XenForo_FrontController->run()
#18 {main}
 

Mike

XenForo developer
Staff member
#5
I will need to attempt to reproduce it. I'm mostly just pointing out that in normal situations, this code shouldn't be running at all; it's only running because the indexing action is failing within Elasticsearch.
 

maxicep

Active member
#6
Yes but if elasticsearch service occuring due any problem, your elasticsearc add-on shouldn't do this anyway. If it must write the error to database, it must without error @Mike
 

Mike

XenForo developer
Staff member
#7
I've found some issues with logging failed indexing actions when deleting from an index. In many cases, it wasn't actually logging any action, so I've adjusted that to log more correctly. I've also put some changes in to ensure this won't error.
 

maxicep

Active member
#8
I've found some issues with logging failed indexing actions when deleting from an index. In many cases, it wasn't actually logging any action, so I've adjusted that to log more correctly. I've also put some changes in to ensure this won't error.
Thank you for your fixing. So we need to wait newer version of ES add-on right ?
 

Mike

XenForo developer
Staff member
#9
Yes. This is mostly just covering up a different error, which is one that should be investigated as the root action (why index-related actions are failing in ElasticSearch). This just prevents the second error.
 

maxicep

Active member
#10
Yes i understand.

Btw, i fixed the problem with rebuild all search index in a night. But if any error occured on the elasticsearch service at server, xenforo will not record the errors until update your plugin. So we are waiting it.
 

maxicep

Active member
#11
Yes. This is mostly just covering up a different error, which is one that should be investigated as the root action (why index-related actions are failing in ElasticSearch). This just prevents the second error.
Mike, i have still errors about same problem and we can't delete some posts or threads at this time. So, can you please share the changes how it will be fixed ? Because i don't know when new version is ready.
 

Mike

XenForo developer
Staff member
#17
At this point, we'll probably just do a release along with the next XF release.

Just to reiterate though, this does not cause any errors itself. It only comes up if you are already having underlying errors. You may be able to find details of that error in the Elasticsearch log.