Soundex Search [Support Thread]

[neekfenwick]

This is the official support thread for the Soundex Search addon, found at https://www.zen-cart.com/downloads.php?do=file&id=2380 and a GitHub repository is linked from there.

The addon adds SOUNDEX (https://mariadb.com/kb/en/soundex/) matching to the normal customer facing Search function, normally available from the header of the site. This means that if you have a product called "Wonderfudge bracelets" and a customer types "wanderfudge" into the search bar, they will still find your product.

One big catch of this addon is that it relies on Stored Procedures and Triggers to maintain its lookup data, so if your database user does not have privileges to create these entities, for example you're on a restrictive shared hosting environment, then the install will fail. It tries to test these privileges before taking any actions and should warn you with sensible error messages.

v1.0.1 released today to fix minor issues with procedure names. Developed against Zen Cart 1.5.8 and later, though the readme describes how it can be made to work with earlier Zen Cart versions. Note: I haven't actually tested it much on earlier versions so your mileage may vary! Written using PHP 8.x and MariaDB 10.5 but should be compatible with earlier versions. If anyone can raise a useful bug report here against earlier versions, I can look at fixing compatibility.

[neekfenwick]

tl;dr: with the new stored procedures and triggers introduced to your database by this addon, you will need to ensure backups are made with the mysqldump "-R" option (or equivalent in your backup system to include procedures, functions and triggers), and you may need to massage the dump if you need to import it into a database using a different username. If you are backing up and restoring to the same database (e.g. a backup of your live shop) you should still ensure you include stored procedures and functions in your backup, an option that may not be enabled by default in your system.

When importing a backup created via something like mysqldump you may see this error message:

ERROR 1227 (42000) at line 96: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

The problem is probably "DEFINER" lines in the dumped SQL related to the triggers from this addon file which cause errors during import. Exactly how you can get around this depend on exactly how you are creating your export file (what backup tool or command you're using), and the environment you are importing it in (what command line tools you have available).

You should ensure you use the "-R" switch (or equivalent) when backing up your database:

$ # On one machine (probably live)
$ mysqldump -R my_zen_database products products_description > backup.sql

$ # On another machine with different username
$ mysql my_other_zen_database < backup.sql

I found the backup will contain the username of the original `mysqldump` command, e.g.:

/*!50003 CREATE*/ /*!50017 DEFINER=`live_username`@`localhost`*/ /*!50003 TRIGGER soundex_prod_desc_update

When this SQL is executed against your target database it will probably fail because the user `live_username` does not exist in that database, and you get the above error message. mysqldump does not appear to have any options that can make the dumped SQL file friendly. From searching online, there is a mysqlpump command shipped with MySQL that has a `--skip-definer` option but I'm using MariaDB which does not appear to have this tool (see https://jira.mariadb.org/browse/MDEV-12808)

The fix is to either change the DEFINER line to use your local username, or remove the DEFINER clause entirely.

To change the clause: s/DEFINER=`live_username`/DEFINER=`local_username`/
To remove the clause: s/\/\*!\d* DEFINER=[^*]*\*\///

For stored procedures, the dump will contain the DEFINER username for stored procedures:

CREATE DEFINER=`live_username`@`localhost` PROCEDURE `soundex_search_init_for_value`(

Here we again need to either change the username, or remove the DEFINER line entirely.

To change the clause (same as above): s/DEFINER=`live_username`/DEFINER=`local_username`/
To remove the clause: s/CREATE DEFINER=[^ ]*/CREATE /

[carlwhat]

good info.

thanks for posting.

[neekfenwick]

v1.0.2 released at https://github.com/neekfenwick/sound...ses/tag/v1.0.2
Notes: Fix query building so that only specific table and column combinations are matched. Previously we were matching broadly on the word value without specifying which table and column it was supposed to match against. Only a single file has changed, you can copy includes/classes/observers/auto.soundex_search_observer.php to pick up the fix.

For some reason the Zen Cart plugin page isn't showing any version beyond v1.0.0, but I have submitted them there too. Maybe they are pending moderation.

[neekfenwick]

I hope you are all doing well :)

v1.0.4 released at https://github.com/neekfenwick/sound...ses/tag/v1.0.4 and updated on the addons page.

This is a bugfix release to fix an inefficiency that caused unnecessary slowdown, which might be invisible to people with relatively few numbers of products.

My unfamiliarity with triggers led me to forget that the trigger would fire no matter which column was updated, and would cause recalculation of all the soundex values. On a large table, this could take several seconds if you have tens of thousands of products, even when updating a completely unrelated column such as products.products_sort_order. These updates should now be ignored by the trigger, and complete quickly as expected.

To update from a previous version it's probably best to run the uninstall procedure, which removes the old triggers and tables from the database, then install the addon again, which puts the new ones in place. This is covered in the readme, but essentially it's a case of hiding two files, running the uninstall script, then putting those two files back in place:

** BACKUP YOUR DATABASE! **
$ mv htdocs/YOUR_ADMIN/includes/init_includes/init_soundex_search.php /tmp
$ mv htdocs/YOUR_ADMIN/includes/auto_loaders/config.soundex_search.php /tmp
... go to https://your_site.com/YOUR_ADMIN/index.php?cmd=soundex_search_uninstall and confirm the uninstall messages appear
... put those two files back in place
... visit your admin and confirm the installation messages appear