Piwigo.org

dadoc · 2020-06-24 13:04:10

[I've already posted my question in the German Piwigo forum, but without response so far]
As editor-in chief of a German photo magazine, I'm doing some research on topics for articles on image administration (online & offline) in our fortcoming issues. For this reason, I re-visited Piwigo and was impressed about the big advances since my last test. I'm definitely thinking about using it for my private image collections, too.

When installing, I noticed that Piwigo apparently still needs MyISAM in order to have all the features work. At least, I get errors when using InnoDB instead due to the missing FULLTEXT index (same problem as described in this thread):

Code:

Warning:  [mysql error 1191] Can't find FULLTEXT index matching the column list

SELECT
    *
  FROM piwigo_categories
    INNER JOIN piwigo_user_cache_categories ON id = cat_id and user_id = 1
  WHERE (MATCH(name, comment) AGAINST( 'martin' IN BOOLEAN MODE)) in /homepages/17/d829450095/htdocs/piwigo/include/dblayer/functions_mysqli.inc.php on line 856


Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in /homepages/###/htdocs/piwigo/include/dblayer/functions_mysqli.inc.php:220 Stack trace: #0 /homepages/###/htdocs/piwigo/include/functions_search.inc.php(1143): pwg_db_fetch_assoc(false) #1 /homepages/###/htdocs/piwigo/include/functions_search.inc.php(1384): qsearch_get_categories(Object(QExpression), Object(QResults)) #2 /homepages/###/htdocs/piwigo/include/functions_search.inc.php(1301): get_quick_search_results_no_cache('martin', Array) #3 /homepages/###/htdocs/piwigo/include/functions_search.inc.php(1474): get_quick_search_results('martin', Array) #4 /homepages/###/htdocs/piwigo/include/section_init.inc.php(350): get_search_results('11', NULL) #5 /homepages/###/htdocs/piwigo/index.php(12): include('/homepages/###') #6 {main} thrown in /homepages/###/htdocs/piwigo/include/dblayer/functions_mysqli.inc.php on line 220

The error is thrown wirh any search term > three characters. Search terms with < 4 characters don't produce the error, but neither any results...

After altering the db to MyIsam, the error disappears.

The problem is that the big hosting companies in Germany (e.g. Strato, Ionos) don't support MyIsam any longer.

Is there any solution to use InnoDB with Piwigo in a way that the quick search works?

Thanks & best regards
Martin

Piwigo 2.10.2
Linux
PHP: 7.3.19 [2020-06-24 13:00:39]
MySQL: 5.7.30-log [2020-06-24 13:00:39]
External ImageMagick 6.8.9-9

dadoc · 2020-06-29 10:20:14

Good Morning,
I‘ve just received the official reply from my hoster (Ionos):
„It‘s a known fact that MyISAM will be removed from future MySQL versions because in almost any aspect its technically worse than InnoDB.
Since MySQL 5.7 Fulltext indices, geotagging indices etc. are fully supported by InnoDB, while MyISAM still doesn‘ offer ACID compatibility.
Hence we have decided to discontinue MyISAM support.“

They state that Piwigo should work fine with InnoDB, pointing at [Github] Piwigo issue #539
But of course it doesn‘t work, at least the search function won‘t.
Does it make sense to manually add fulltext indices to the db and if so, how should this be done?
Thanks &best regards
Maetin

kleiner_Hobbit · 2021-07-15 10:33:22

In this thread you will find the answer for your problem:
https://piwigo.org/forum/viewtopic.php?id=30893

tomasz wrote:
I logged on to MySQL administration are where I have my website hosted and issued the following commands:

create fulltext index piwigo_tags_idx on piwigo_tags(name);
create fulltext index piwigo_categories_idx on piwigo_categories(name,comment);
create fulltext index piwigo_images_idx on piwigo_images(name,comment);

This worked for me.

Last edited by kleiner_Hobbit (2021-07-15 10:33:38)

Piwigo.org

Announcement

#1 2020-06-24 13:04:10

Introduction and first question regarding sql error myisam vs. innodb

Code:

#2 2020-06-29 10:20:14

Re: Introduction and first question regarding sql error myisam vs. innodb

#3 2021-07-15 10:33:22

Re: Introduction and first question regarding sql error myisam vs. innodb

tomasz wrote:

Board footer