Reindexing ca_storage_locations blows up because of bad sql?

When I try to reindex ca_storage_locations, it blows up because of bad sql.

It appears that field_row_id is being set (from an array?) so that it sometimes has two semicolon separated values...and that blows up the sql.

INSERT INTO  ca_sql_search_word_index (  table_num,  row_id,  field_table_num,  field_num,  field_container_id,  field_row_id,  word_id,  boost,  access,  rel_type_id  )

VALUES  (89, 646, 89, 'A22', NULL, 646, 31502, 1, 0, 0),... ,(89,649,88,'I4',NULL,665;666,26875,1,0,0)

Any ideas where to start looking?



  • As usual, start by tell me exactly what version you're running.

  • oh...sorry. Pulled from git/develop.

    Running on shared Debian server, php 7.4.10, mysql 5.6.32

  • Any ideas? At this point, I can not search for any storage locations. A search for "*" returns zero storage locations (but 100+ of everything else)

    If I catch the sql error and do a stack dump I get this: But I'm getting lost trying to find where the field_row_id is getting set so I ca figure out why it is being filled with a list and not a single value.



      [0] => Array


          [file] => /hermes/bosnaweb28a/b1269/ipw.pelhamhs/public_html/ca/app/lib/Db/DbStatement.php

          [line] => 150

          [function] => execute

          [class] => Db_mysqli

          [type] => ->


      [1] => Array


          [file] => /hermes/bosnaweb28a/b1269/ipw.pelhamhs/public_html/ca/app/lib/Db.php

          [line] => 260

          [function] => executeWithParamsAsArray

          [class] => DbStatement

          [type] => ->


      [2] => Array


          [file] => /hermes/bosnaweb28a/b1269/ipw.pelhamhs/public_html/ca/app/lib/Plugins/SearchEngine/SqlSearch2.php

          [line] => 1050

          [function] => query

          [class] => Db

          [type] => ->


      [3] => Array


          [file] => /hermes/bosnaweb28a/b1269/ipw.pelhamhs/public_html/ca/app/lib/Plugins/SearchEngine/SqlSearch2.php

          [line] => 1034

          [function] => flushContentBuffer

          [class] => WLPlugSearchEngineSqlSearch2

          [type] => ->


      [4] => Array


          [file] => /hermes/bosnaweb28a/b1269/ipw.pelhamhs/public_html/ca/app/lib/Search/SearchIndexer.php

          [line] => 1162

          [function] => commitRowIndexing

          [class] => WLPlugSearchEngineSqlSearch2

          [type] => ->


      [5] => Array


          [file] => /hermes/bosnaweb28a/b1269/ipw.pelhamhs/public_html/ca/app/lib/Search/SearchIndexer.php

          [line] => 275

          [function] => indexRow

          [class] => SearchIndexer

          [type] => ->


  • You somehow have two preferred labels with the same locale for a storage location. How you got that to happen I cannot say, but this is the first time I've seen it.

    You can delete the second label with this SQL:

    DELETE FROM ca_storage_location_labels WHERE label_id = 666 and location_id = 649

    Then reindex and it should work... unless there are other multiple labels lurking in your data.

    Don't forget to backup your database before executing SQL on it.


  • That seemed to do it. Thank you! Understanding the indexing /re-indexing is complex. This was a rather obscure failure result from what seems like a not so unlikely error (multiple preferred labels). I think better error trapping would be good.

    So indexing completes.

    I have another strange issue:

    If I search for a storage location [Find]->[Storage Locations]->[Basic Search] and search for wildcard "*" I get all storage locations. But, if I search from the multi-search box, it returns everything thing (objects, etc) but storage_locations. This is still in Prov.

    Any pointers? Thanks

  • What happens if you search for text present in a location name?

  • Same thing. Correct results from: ..../ca/index.php/find/SearchStorageLocations/Index/reset/save

    No results from .../ca/index.php/find/QuickSearch/Index.

    (search for 'gould'):

    Your search found 22 storage locations

    EditLocation identifierName

    1 ppl_gould_02 GOULD FAMILY ARCHIVES COLLECTION: Gould Family Papers, Box 2

    2 ppl_gould_04 GOULD FAMILY ARCHIVES COLLECTION: Gould Family Papers, Box 4

    3 ppl_gould_13 GOULD FAMILY ARCHIVES COLLECTION: Gould Family Cards, Box 3

    4 ppl_gould_14 GOULD FAMILY ARCHIVES COLLECTION: Gould Family Photographs, Box 14

    5 ppl_gould_01 GOULD FAMILY ARCHIVES COLLECTION: Gould Family Papers, Box 1

    ...etc etc

Sign In or Register to comment.