Advanced search bug?

[Paulo--M--]

Hello, I installed OpenDB 1.6.0.0 dev4 and I've been exploring it for a few days now. First of all, thanks for this application... I've come across a small issue and was wondering whether it might be a general problem or something specific to my installation. Here's the description.

In the Advanced Search, if I select Attribute Type: ALT_TITLE - alternate title (Attribute Type Lookup: only shows 'Use the value field --->' in the dropdown menu, so it's used) Attribute Value: Guard (default 'partial match' remains set) and I hit Search, I draw a blank.

And yet there IS a film in my test collection that, in addition to the standard title (in the 'title' field in the 'item table'), has a corresponding alternate title of 'The Guard' (it is in the 'item_attribute' table and correctly shows up in the item's display page). Apparently there's either a bug in the MySQL query, or I'm simply not doing things properly... If the latter case, could someone please tell me how to search the database for an item, based on a part of the title, either in the TITLE itself OR in the ALT_TITLE fields?... Many thanks in anticipation.

[pellcorp]

I can't reproduce this problem. In the lib/item.php, search for:

//echo "\nListing Query: $query";

Remove the comments and try execute the search. Copy the query into a phpmyadmin session against the opendb database and see if mysql reports any issues, or perhaps with a little playing around you can identify an issue with the query.

[Paulo--M--]

There are two instances of 'Listing Query' and I had actually already tried to uncomment them... :-) The first instance (count) returns count 0 in phpMyAdmin, while the second returns an empty result set... The script is displaying "correctly" what it retrieves from the database; it seems to be the query that's not doing its job properly. I did try to go through both queries, but I drew a blank... :-( I'm not sure whether it's proper to include the queries themselves here, so I'm not doing that for now (unless instructed otherwise by you). Any further advice is much appreciated -- especially since I do understand the problems of limited time due to younger ones... :-) Thanks.

[pellcorp]

Not sure whether you will consider it or not, but if you want to send me a copy of your db and the queries to jason at pellcorp dot com

I will deploy your db locally and test the query myself. I will destroy the db afterwards.

Otherwise I am not sure what to suggest

[Paulo--M--]

Not a problem at all :-) It's just a test DB with a few items. It's on my home network, though, so I'll only be able to send you the material later tonight. Many thanks for the support.

[Paulo--M--]

Hello, I sent you all the stuff yesterday by email. This is by no means harrassment :-) I just wanted to make sure that the email reached its destination. No hurry. Thanks.

[pellcorp]

Got it plan to look at it today

[pellcorp]

I just noticed that you are using an old version. Can you please upgrade to latest master version.

I can't reproduce your problem once I upgraded to latest master.

What version of mysql are you using?

[Paulo--M--]

Many thanks for the continued feedback. I will install the latest version later tonight (as you saw, I only have a few test entries in the database, so it's not a problem installing things from scratch). I'll also check and send you the mysql version in use. Thanks again.

[pellcorp]

You can also just install new version point it at old database and opendb will upgrade your db for you.

[Paulo--M--]

I'm afraid that this turned out to be a horribly long day and I only found the time now to install the latest version (OpenDb 1.6.0beta4). Downloaded the latest master, unzipped, installed, pointed at the existing DB, all updates were processed successfully. Unfortunately, the advanced search described in the detail in my opening post yields the exact same blank result... :-(

Meanwhile, however, I may have found the "root of all evils"... :-) My NAS is actually running MariaDB -- apparently everyone is going the way of MariaDB these days... Anyway, phpMyAdmin reports: Database server Server: Localhost via UNIX socket Server type: MariaDB Server version: 5.5.37-MariaDB - Source distribution Protocol version: 10 Server charset: UTF-8 Unicode (utf8)

It may just be that this is the reason for the query failing. In my limited knowledge of the matter, I was under the impression that MariaDB should be 100% compatible with MySQL commands, but perhaps in some finer details/queries this is not so.

Any further comments/suggestions/pointers are greatly appreciated. I fully understand if the reply is that MariaDB is simply not supported :-) Thank you for everything so far.

[pellcorp]

Should be supported fine. My only concern is if like queries are case insensitive. Can you modify %guard% to be exact case match. Just in phpmyadmin.

[Paulo--M--]

That's it!... :-) In the database, the alternative title contains the word 'Guard'; your query uses LIKE '%GUARD%', irrespective of the lower-/uppercase combinations entered in the search form -- that brings up a blank result. In phpMyAdmin, however, whereas LIKE '%GUARD%' and LIKE '%guard%' bring up blanks also, using LIKE '%Guard%' correctly fishes out the record from the database... Well done for thinking of that.

I have no idea whether it is possible to change the query so that it becomes compatible with MariaDB... Many thanks for the continued feedback.

[pellcorp]

Like queries are supposed to be case insensitive. I will see if I can reproduce with mariadb. Probably can just stick an upper around column

[pellcorp]

It's the collation that the columns were configured with. try this and then rerun query with %GUARD%

ALTER TABLE item_attribute CHANGE attribute_val attribute_val TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ;

[Paulo--M--]

Congratulations!... :-) Very good; this seems to solve the problem, yes. Thank you very much for the great support on this issue.

[pellcorp]

I am adding to upgrade script and new install script

[Paulo--M--]

Great attentive support. Congratulations.