Can't rebuild search index or purge deleted records

Hi,
I am having problems purging deleted records and rebuilding search indices.

In rebuilding the search index, the recurrent errors start with “Got a packet bigger than 'max_allowed_packet”
I saw a solution on the forums re: bumping up max_allowed_packet in the mysql my.cnf file but this doesn’t seem to have changed anything for me.

Below are the errors I receive in CaUtils:

[rebuild-search-index]

Memory: 278.00M 44.0% 1174/2687 ETC: 39 secs. Elapsed: 31 secs [=============> ]
PHP Fatal error: Uncaught DatabaseException: Got a packet bigger than 'max_allowed_packet' bytes in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php:316
Stack trace:

0 /Users/arkaivel/Sites/providence/app/lib/core/Db/DbStatement.php(151): Db_mysqli->execute(Object(DbStatement), Object(DbStatement), '\n\t\t\tINSERT INT...', Array, NULL)

1 /Users/arkaivel/Sites/providence/app/lib/core/Db.php(270): DbStatement->executeWithParamsAsArray(Array)

2 /Users/arkaivel/Sites/providence/app/lib/core/Plugins/SearchEngine/SqlSearch.php(1878): Db->query('\n\t\t\tINSERT INT...')

3 /Users/arkaivel/Sites/providence/app/lib/core/Plugins/SearchEngine/SqlSearch.php(1863): WLPlugSearchEngineSqlSearch->flushContentBuffer()

4 /Users/arkaivel/Sites/providence/app/lib/core/Search/SearchIndexer.php(1064): WLPlugSearchEngineSqlSearch->commitRowIndexing()

5 /Users/arkaivel/Sites/providence/app/lib/core/Search/SearchIndexer.php(261): SearchIndexer->indexRow('57', '1876', Array, true)

6 /Users/arkaivel/Sites/providence/app/lib/ca/Utils/C in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php on line 316

Fatal error: Uncaught DatabaseException: Got a packet bigger than 'max_allowed_packet' bytes in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php:316
Stack trace:

0 /Users/arkaivel/Sites/providence/app/lib/core/Db/DbStatement.php(151): Db_mysqli->execute(Object(DbStatement), Object(DbStatement), '\n\t\t\tINSERT INT...', Array, NULL)

1 /Users/arkaivel/Sites/providence/app/lib/core/Db.php(270): DbStatement->executeWithParamsAsArray(Array)

2 /Users/arkaivel/Sites/providence/app/lib/core/Plugins/SearchEngine/SqlSearch.php(1878): Db->query('\n\t\t\tINSERT INT...')

3 /Users/arkaivel/Sites/providence/app/lib/core/Plugins/SearchEngine/SqlSearch.php(1863): WLPlugSearchEngineSqlSearch->flushContentBuffer()

4 /Users/arkaivel/Sites/providence/app/lib/core/Search/SearchIndexer.php(1064): WLPlugSearchEngineSqlSearch->commitRowIndexing()

5 /Users/arkaivel/Sites/providence/app/lib/core/Search/SearchIndexer.php(261): SearchIndexer->indexRow('57', '1876', Array, true)

6 /Users/arkaivel/Sites/providence/app/lib/ca/Utils/C in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php on line 316

[purge-deleted]

Removing deleted object representations from database 8.0% 1/13 ETC: < 1 sec. Elapsed: < 1 sec [==>]
PHP Fatal error: Uncaught DatabaseException: Cannot delete or update a parent row: a foreign key constraint fails (casfom_test01.ca_download_log, CONSTRAINT fk_ca_download_log_representation_id FOREIGN KEY (representation_id) REFERENCES ca_object_representations (representation_id)) in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php:316
Stack trace:

0 /Users/arkaivel/Sites/providence/app/lib/core/Db/DbStatement.php(151): Db_mysqli->execute(Object(DbStatement), Object(DbStatement), 'DELETE FROM ca_...', Array, NULL)

1 /Users/arkaivel/Sites/providence/app/lib/core/Db.php(270): DbStatement->executeWithParamsAsArray(Array)

2 /Users/arkaivel/Sites/providence/app/lib/core/BaseModel.php(3424): Db->query('DELETE FROM ca_...')

3 /Users/arkaivel/Sites/providence/app/lib/core/BaseModelWithAttributes.php(605): BaseModel->delete(true, Array, NULL, Array)

4 /Users/arkaivel/Sites/providence/app/lib/ca/BundlableLabelableBaseModelWithAttributes.php(364): BaseModelWithAttributes->delete(true, Array, NULL, NULL)

5 in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php on line 316

Fatal error: Uncaught DatabaseException: Cannot delete or update a parent row: a foreign key constraint fails (casfom_test01.ca_download_log, CONSTRAINT fk_ca_download_log_representation_id FOREIGN KEY (representation_id) REFERENCES ca_object_representations (representation_id)) in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php:316
Stack trace:

0 /Users/arkaivel/Sites/providence/app/lib/core/Db/DbStatement.php(151): Db_mysqli->execute(Object(DbStatement), Object(DbStatement), 'DELETE FROM ca_...', Array, NULL)

1 /Users/arkaivel/Sites/providence/app/lib/core/Db.php(270): DbStatement->executeWithParamsAsArray(Array)

2 /Users/arkaivel/Sites/providence/app/lib/core/BaseModel.php(3424): Db->query('DELETE FROM ca_...')

3 /Users/arkaivel/Sites/providence/app/lib/core/BaseModelWithAttributes.php(605): BaseModel->delete(true, Array, NULL, Array)

4 /Users/arkaivel/Sites/providence/app/lib/ca/BundlableLabelableBaseModelWithAttributes.php(364): BaseModelWithAttributes->delete(true, Array, NULL, NULL)

5 in /Users/arkaivel/Sites/providence/app/lib/core/Db/mysqli.php on line 316

Thanks in advance for any and all suggestions.

Comments

  • Try setting the max-allowed-packet on your mysql instance to a larger value (256m or so)

  • I set it to 1G. ?

  • I had to create a my.cnf file as the MySql version on my Mac no longer creates one by default (vs 5.7.26 on macos 10.14). I don't think its having any effect. I logged into mysql to check variables and the current limit for max_allowed_packet is 4194304. I'd imagine that should suffice? I'm stumped.

  • Ok, nevermind. Finally succeeded with rebuilding search index after setting via command line SET GLOBAL max_allowed_packet=1073741824;.

Sign In or Register to comment.