SearchableDataObject DB table has multiple keys/indexes

[HARVS1789UK]

SearchableDataObject DB table has multiple keys/indexes

[g4b0]

That's a problem I was speaking about with @wernerkrauss on the IRC channel. The problem is that it isn't possible to hook the dev/build since SS v. 3.1.17, and I'm actually building my keys using a workaround.

I can maybe create a new branch that drop support for older installations, and go ahead with the new version...

What do you think about that?

[HARVS1789UK]

Hi @g4b0

I am using SS v3.1.15 which I believe is the most up to date stable release at the current time. Do you mean that your module has already been updated to work with a newer version of SS which is still in beta and because of that you have started to use this work around?

I don't really fully understand what the issue is, or what causes it to occur (I understand the error message itself, but not why the code attempts to create multiple indexes which seem to be identical) it seems to happen to me after some random period of time when doing a /dev/build (or perhaps after a fixed number of /dev/builds based on my DB contents?).

I presume simply removing the augmentDatabase() method all together would mean that the /dev/tasks/PopulateSearch task would need running any time I wanted newly added data objects to be added to the SearchableDataObjects table in the DB? Not really too sure when the augmentDatabase() method is called?

Kind regards,

HARVS1789UK

[wernerkrauss]

@g4b0 wasn't there another solution discussed by hooking in init() on dev/build contoller?
@HARVS1789UK version 3.1.17 is surely a typo, but he was referencing to a quite new method that won't work in older installs yet.

[g4b0]

@wernerkrauss yep, I have to further investigate about it...
@HARVS1789UK yes, it was a typo, I don't have access to future SS release :)

[dnpg]

I've came up with this same issue and I found a way to solve it. Change the line 62 on searchable-dataobjects/code/SearchableDataObject.php:

DB::query("ALTER TABLE SearchableDataObjects ADD FULLTEXT (Title ,Content)");

to:

$count = DB::query("SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='SearchableDataObjects' AND index_name='Title'")->value();
if($count == 0){
    DB::query("ALTER TABLE SearchableDataObjects ADD FULLTEXT Title (`Title` ,`Content`)");
} 

The problem was that the Index were created over an over everytime we executed dev/build until it ran out of limit.

[g4b0]

Nice workaround @diegopego86, it could be useful for maintaining compatibility with old installations.

[g4b0]

included in version 2.1.3
Thanks a lot @diegopego86

In a future 3.* version I will hook into dev/build loosing compatibility with older SS version