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

maxicep

maxicep

Active member
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:
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.
 
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.
 
Mike said:
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.
Click to expand...
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}
 
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.
 
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
 
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.
 
Mike said:
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.
Click to expand...
Thank you for your fixing. So we need to wait newer version of ES add-on right ?
 
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.
 
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.
 
Mike said:
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.
Click to expand...
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.
 
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.
 

Similar threads

Nirjonadda
Fixed Incorrect integer value: 'use_field_mapping8' for column 'user_id' at row
Replies
1
Views
54
XF Bug Bot
XF Bug Bot
Fajfie
Fixed XF\Db\Exception: MySQL query error [1366]: Incorrect integer value: '' for column `XXX`.`xf_deletion_log`.`delete_user_id` at row 1
Replies
2
Views
986
Jeremy P
Jeremy P
alsoGAMER
  • Question Question
XF 2.1 Incorrect integer value: 'ThemeHouse\Nodes:LayoutSeparator' for column `xf`.`xf_node_type`.`entity_identifier` at row 1
Replies
3
Views
638
alsoGAMER
alsoGAMER
Wesker
  • Question Question
XF 1.5 URGENT - Out of range value for column 'conversations_unread' at row 1
Replies
4
Views
2K
Wesker
Wesker
R
  • Question Question
XF 1.5 Mysqli statement execute error : Incorrect integer value: 'privacy-policy-old' for column 'page_id' at row 1 in Zend/Db/Statement/Mysqli.php on line 2
Replies
3
Views
1K
rdn
R
Back
Top Bottom