XF 1.5 Need help with mysqli error

[Rho Delta]

An exception occurred: Mysqli statement execute error : Duplicate entry '2573752' for key 'PRIMARY' in /home/mike/public_html/library/Zend/Db/Statement/Mysqli.php on line 214

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 574
Zend_Db_Adapter_Abstract->insert() in XenForo/Model/Search.php at line 77
XenForo_Model_Search->insertSearch() in XenForo/ControllerPublic/FindNew.php at line 206
XenForo_ControllerPublic_FindNew->findNewPosts() in XenForo/ControllerPublic/FindNew.php at line 24
XenForo_ControllerPublic_FindNew->actionPosts() in Waindigo/Thumbnails/Extend/XenForo/ControllerPublic/FindNew.php at line 16
Waindigo_Thumbnails_Extend_XenForo_ControllerPublic_FindNew->actionPosts() in Hex/Thumbnails/Extend/XenForo/ControllerPublic/FindNew.php at line 16
Hex_Thumbnails_Extend_XenForo_ControllerPublic_FindNew->actionPosts() in XenForo/FrontController.php at line 351
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
XenForo_FrontController->run() in /home/mike/public_html/index.php at line 13

This happens when you click the new posts button. Can anyone help me please?

 

[TPerry]

Yeah, try disabling the Waindigo add-on that results in

XenForo_ControllerPublic_FindNew->actionPosts() in Waindigo/Thumbnails/Extend/XenForo/ControllerPublic/FindNew.php at line 16
Waindigo_Thumbnails_Extend_XenForo_ControllerPublic_FindNew->actionPosts() in Hex/Thumbnails/Extend/XenForo/ControllerPublic/FindNew.php at line 16

the stack trace you provided.. then see if that resolves it.

Or, make sure you are using the current version - which I think is now here:https://xenforo.com/community/resources/th-thumbnails.4834/

 

[Rho Delta]

I uninstalled the thumbnails add on and rebuilt the search index and the error still occurs but now says this:

An exception occurred: Mysqli statement execute error : Duplicate entry '2573752' for key 'PRIMARY' in /home/mike/public_html/library/Zend/Db/Statement/Mysqli.php on line 214

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 574
Zend_Db_Adapter_Abstract->insert() in XenForo/Model/Search.php at line 77
XenForo_Model_Search->insertSearch() in XenForo/ControllerPublic/FindNew.php at line 206
XenForo_ControllerPublic_FindNew->findNewPosts() in XenForo/ControllerPublic/FindNew.php at line 24
XenForo_ControllerPublic_FindNew->actionPosts() in XenForo/FrontController.php at line 351
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
XenForo_FrontController->run() in /home/mike/public_html/index.php at line 13

 

[Mike]

Can you run this query and show us the output?

SHOW CREATE TABLE xf_search;

 

[MikeMpls]

I am getting the same issue now every time I try to do a search in this particular forum (XF 1.4.0):

Mysqli statement execute error : Duplicate entry '28595107' for key 'PRIMARY'

1. Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
2. Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
3. Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 574
4. Zend_Db_Adapter_Abstract->insert() in XenForo/Model/Search.php at line 77
5. XenForo_Model_Search->insertSearch() in Tinhte/XenTag/XenForo/Model/Search.php at line 39
6. Tinhte_XenTag_XenForo_Model_Search->insertSearch() in XenForo/ControllerPublic/Search.php at line 257
7. XenForo_ControllerPublic_Search->actionSearch() in Tinhte/XenTag/XenForo/ControllerPublic/Search.php at line 33
8. Tinhte_XenTag_XenForo_ControllerPublic_Search->actionSearch() in XenForo/FrontController.php at line 347
9. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
10. XenForo_FrontController->run() in /home/ihmt/public_html/index.php at line 13

An upgrade is pending -- what are the odds that upgrading will make this go away?

 

[Mike]

Unlikely. My question from above would apply here too.

 

[MikeMpls]

Done from console in phpMyAdmin:



The problem is still happening.

I also rebuilt the search cache this morning, with no effect.

 

[MikeMpls]

The table only shows one search ID of 28595107, and it is the highest/last entry in the table.

Is there a counter somewhere that should be bumped so the next search index is 28595108?

Or should I delete the entry with search ID 28595107?

 

[Mike]

I need you to expand that so I can see the full values of the response.

This isn't an error that should be possible. The automatically increasing counter is handled within MySQL itself and we have minimal direct control over it. We don't actually request a particular value here, so if MySQL isn't giving a usable value, it could indicate some amount of DB corruption, though I'm not positive.

 

[MikeMpls]

This?

Your SQL query has been executed successfully.

SHOW CREATE TABLE xf_search

xf_search CREATE TABLE `xf_search` (
`search_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`search_results` mediumblob NOT NULL,
`result_count` smallint(5) unsigned NOT NULL,
`search_type` varchar(25) NOT NULL,
`search_query` varchar(200) NOT NULL,
`search_constraints` mediumblob NOT NULL,
`search_order` varchar(50) NOT NULL,
`search_grouping` tinyint(4) NOT NULL DEFAULT '0',
`user_results` mediumblob NOT NULL,
`warnings` mediumblob NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`search_date` int(10) unsigned NOT NULL,
`query_hash` varchar(32) NOT NULL DEFAULT '',
`tinhte_xentag_tags` mediumblob,
PRIMARY KEY (`search_id`),
KEY `search_date` (`search_date`),
KEY `query_hash` (`query_hash`)
) ENGINE=MyISAM AUTO_INCREMENT=28595107 DEFAULT CHARSET=utf8

 

[Mike]

Well something broke in MySQL then, as the auto_increment value is wrong, given the last value in the table. This isn't something we have any control over, so I really don't know what would've caused it.

You can try running this query:

ALTER TABLE xf_search AUTO_INCREMENT=28595108;

I do note that the table is also MyISAM, as I assume you don't (or didn't) have InnoDB available. This isn't really ideal either and could possibly be somewhat related.

 

[MikeMpls]

Thank you! That fixed it, just saw the first real search results in several days.

I have a generic, off-the-shelf, VPS package from KnownHost. I will look into InnoDB.

 

[MikeMpls]

InnoDB is available to me today. I will work the conversion into the upcoming upgrades.

When I created the site in 2011, my hosting was with FatCow (one of those wonderful Endurance International companies), which has never allowed InnoDB I left FatCow when one of my threads went viral & overloaded their server. The MyISAM table types would have moved with the dumps & been reloaded the same way on KH.

Looks like InnoDB might have lightened the load a bit on FatCow but they were non-responsive when I asked about upgrades..

 

[TPerry]

If maintaining the 1.x line (and even if not if done before the 2.0 upgrade) you may find this helpful. I had a similar issue occur when I moved to another VPS and forgot to enable INNODB in the my.conf file.
https://xenforo.com/community/resources/guide-to-convert-from-myisam-to-innodb.4005/