Favorites Plugin crashes site with first save (1.6.1a and earlier on Dreamhost)

david1pro

I've had this problem since 1.59 (my first install). On a fresh install of 1.6.1a, I can enable the favorites plugin and it will then show the favorites link and add the favorites buttons. However, as soon as any user selects their first favorite, the site crashes - instantly - on the front end and that user is no longer able to access the site. If you are not that user (such as opening the site in incognito mode in the browser so you're not logged in), the site still works. In reviewing the database, it does seem that the favorite is saved before the crash, as I can find the listing for it via phpMyAdmin under the column "zp_plugin_storage." By clicking "copy" to better view that entry, I can see:

The database entry type is: favorites
The database entry "aux" is: David"
The database entry "data" is" a:2:{s:4:"type";s:6:"images";s:2:"id";s:55:"Parks/Germantown/Germantown Metropark 6-25-2018 (1).jpg";} (which is the picture I added as a favorite)

In viewing the logs in Zenphoto, there seem to be two error entries that are created, both at the same time (same timestamp to the second). They are:

{2716924:Fri, 23 Dec 2022 17:30:45 GMT}

WARNING: mysqli::query(): (HY000/3685): Illegal argument to a regular expression. in /home/(mysite)/zp-core/classes/class-dbmysqli.php on line 69

mysqli->query called from dbMySQLi->query (class-dbmysqli.php [69])
from dbMySQLi->queryFullArray (class-dbmysqli.php [114])
from favorites->__construct (class-favorites.php [29])
from require_once (favoritesHandler.php [312])
from include (index.php [26])
from index.php [79]

{2716924:Fri, 23 Dec 2022 17:30:45 GMT}

USER ERROR: mysqli Error: ( SELECT aux FROM [prefix]plugin_storage WHERE type="favorites" AND aux REGEXP '[[::]]' ) failed. mysqli returned the error Illegal argument to a regular expression. in /home/(mysite)/zp-core/classes/class-dbmysqli.php on line 80

trigger_error called from dbMySQLi->query (class-dbmysqli.php [80])
from dbMySQLi->queryFullArray (class-dbmysqli.php [114])
from favorites->__construct (class-favorites.php [29])
from require_once (favoritesHandler.php [312])
from include (index.php [26])
from index.php [79]

If you see anything that might be a hint, please let me know and I'll test anything you wish. Thanks!

acrylian

That's different than your other issue. I have seen that error before and we had a similar report for 1.6 with favorite. I thought we had fixed that. here the issue is that the regex is empty for some reason making it invalid. Thanks, the error messages help a lot.

kuzzzma

Have the same problem on 1.6.1a
Upon first save of favourite - frontend will display error 500, but backend will still be accessible.
The only way to return site to life - is to disable plugin.

My error log shows:
[Mon Jul 03 04:57:57.598262 2023] [fcgid:warn] [pid 105956:tid 139932816070400] [client 178.237.248.52:7763] mod_fcgid: stderr: PHP Fatal error: mysqli Error: ( SELECT aux FROM [prefix]plugin_storage WHERE type="favorites" AND aux REGEXP '[[:<:]]kuzzzma[[:>:]]' ) failed. mysqli returned the error Illegal argument to a regular expression. in /home/kuzzzma/demo.private-universe.net/zp-core/classes/class-dbmysqli.php on line 83, https://demo.private-universe.net/hinterfolk/

acrylian

What mysql version are you using? All utf8mb4 in case we have some encoding issue?

kuzzzma

I've the same issue on several sites:
all 1.6.1a
2 of them have PHP version: 8.1.17
another uses PHP version: 8.0.28

encoding is the same:
mysqli version: 8.0.28-0ubuntu0.20.04.3

character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8mb4
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: utf8mb3
character_set_system: utf8mb3
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: utf8mb4_0900_ai_ci
collation_database: utf8mb4_general_ci
collation_server: utf8_unicode_ci

acrylian

I will try locally. Perhaps it is some MySQL 8 thing. Sadly I have no access to that. Locally I sadly still have 5.7 and our live site actually uses MariaDB 10.5.x.

acrylian

I cannot reproduce this or probably I don't know how. Do you have the "Multiple sets" options checked? As far as I see actually that regex is only needed in that case as then there is a serialized array in the aux column. Otherwise for single sets it is just the user name so a simple like actually would work.

kuzzzma

No multiple sites or multilingual support
1 locale only enabled - Eng_US.

Settings for Favorites had no multiple sets checked, when I first encountered this:

data in database table is similar to OP:
aux contains my plain username
data has "a:2:{s:4:"type";s:6:"albums";s:2:"id";s:6:"review";}"

I deleted the row from table and enabled multiple sets.

Same error, but now aux contains "a:2:{i:0;s:7:"kuzzzma";i:1;s:4:"test";}" (test was the instance name)

error.log is identical.

If you want to test - I can give you access to my demo site & database for it, it has no sensitive info and I use it for testing themes only.

acrylian

Same error, but now aux contains "a:2:{i:0;s:7:"kuzzzma";i:1;s:4:"test";}" (test was the instance name)

Yes, that is the storage difference with multiple sets enabled I menationed (username and set name instead of user name only).
It looks exactly like on my install. I can only assume it might indeed be the mysql version. No idea right now.

david1pro

At least I now know I'm not the only one with this exact issue. Hopefully acrylian takes kuzzzma's generous offer to use his demo site & database. I'd love for my family members to be able to save favorites.

acrylian

We will review this again. Test sites doe not help if we cannot savely reproduce things with the same conditions on our own test sites.

acrylian

Since I cannot reproduce it you need to try something for me. Please find line 28 in class-favorites.php:

$list = $_zp_db->queryFullArray('SELECT `aux` FROM ' . $_zp_db->prefix('plugin_storage') . ' WHERE `type`="favorites" AND `aux` REGEXP ' . $_zp_db->quote('[[:<:]]' . $user . '[[:>:]]'));

Please change this to:

$list = $_zp_db->queryFullArray('SELECT `aux` FROM ' . $_zp_db->prefix('plugin_storage') . ' WHERE `type`="favorites" AND `aux` REGEXP "[[:<:]]' . $user . '[[:>:]]"');

Basically that is just removing the $_zp->quote() call on the REGEX value as it adds single quotes for values while the first on "type" in the query uses double quotes. Perhaps your MySQL version is just more picky than mine…just like PHP 8+ is compare to older versions.

acrylian

Late follow up but do you perhaps use MySQL 8 on that install? Then this is – as we found out on https://forum.zenphoto.org/discussion/1411202/search-not-working#latest – probably due to syntax changes in MySQL 8. The 1.6.3a support build has an update for this. Untested because we don't have MySQL 8.

kuzzzma

@acrylian thanks for the info!

I was able to test latest version (from Mar 23, 2024) with:
PHP version: 8.1.27
Database: MySQL 8.0.28

and adding and removing favourites works now without causing error 500

acrylian

Great, thanks for the confirmation!