Search results do not appear

saccade · 2019-11-01 10:06:10

Hello,
I have problems with the article search in the article list tab:
I already read and tried everything here Search results do not appear

but without any improvement.

I can describe my problem in more detail:
If I check e.g. only “categories” in the search options it will search and find correctly within categories.
As well in ID, section etc.
But when searching in all of them checked it will find nothing … even the individually searched/found categories will now have no results.
Search in “title, Body, Excerpt” doesn’t work at all.

Any hint or idea?

Last edited by saccade (2019-11-01 10:07:12)

colak · 2019-11-01 18:39:03

Di you check that your db is OK? It might need some repairs.

saccade · 2019-11-01 18:51:45

Hi Yiannis,
I did check that. Everything is fine in the database.
Even the indices are as they should be.
also I checked the searchable-parameter.
Anyway I “repaired” the tables twice.

Edit save one thing: the “Cardinality” of the “searching”-index is the same as Posted or PRIMARY, it is 7980, it’s not 1 as in your example in the other trhread – should it be?.

Last edited by saccade (2019-11-01 18:55:28)

colak · 2019-11-01 19:14:34

I remember I had the problem (different cause) with the front end search and etc advised me to re-install wet_haystack. I’m by no means certain but it might work for you.

jakob · 2019-11-01 21:05:04

I too would have though it has to do with the indexes and that they may need wiping/recreating.

Does putting the site in debug mode and looking at the tag trace (should work for the admin side too) provide any clues?

saccade · 2019-11-01 21:38:04

I had wet_haystack installed long ago, and now re-installed, configured and de-installed again without any effect (but isn’t it for the front-end search?).

I had debug mode already in effect, but there were a lot of non-related warnings, I couldn’t find any clue in between.

Is there a direct way to wipe and recreate in MySQL?

I think, I’ll try wet_haystack once again.

saccade · 2020-02-27 09:22:13

OK, still not solved.

Meanwhile I upgraded to txp 4.8.0 – everything except admin side search is fine.
Dear developers: Thank you very much for all your good thoughts, skills, effort, work to let txp flourish!

I checked every possible combination of wet_haystack on/off/configuration,
I directly deleted and recreated an index in the database …

nothing changed the odd behaviour:

As long as I search from admin side article tab for ID, categories … whatever without title/body/excerpt the search results are fine.
But if I search only title/body/excerpt or title/body/excerpt together with any other – the result will be: “no results”. nothing, not a single article.
Even trying different kinds of search phrase doesn’t change anything.

I’v checked it with “debug”-Mode: Even not a single notice! Very clean tab!

At least the only thought I have now in mind: I have over 10400 articles now.
But it ceased searching articles even before they were 9900.

I’m really perplexed.

Bloke · 2020-02-27 10:10:31

This is baffling indeed. Just to recap:

Your searchable_article_fields pref is set to “Title,Body,Excerpt”.
Your textpattern table is fully repaired (check in rss_admin_db_manager or your MySQL client interface of choice).

Correct?

If so, create a new hidden article and populate it with the following Body content, replacing your-db-name-here where appropriate:

==<txp:php>
$type = safe_rows(
    'TABLE_NAME, ENGINE',
    'information_schema.TABLES',
    'TABLE_SCHEMA = "your-db-name-here" AND TABLE_NAME = "textpattern"'
);
$indexes = safe_rows(
    'TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLLATION, CARDINALITY, SEQ_IN_INDEX',
    'information_schema.STATISTICS',
    'TABLE_SCHEMA = "your-db-name-here" AND TABLE_NAME = "textpattern"'
);
dmp($type, $indexes);
</txp:php>==

View the article and check the results it splurges out. You should first see the database engine type (which is likely to be InnoDB) and then a whole raft of indexes. Check each one looks kinda okay.

The cardinality is just an estimate of the number of unique values in the index, so you’d expect the values in the title, body and excerpt indices to be higher. The collation will likely be ‘A’ (ascending) or ‘D’ (descending) or NULL for those tables where an order makes no sense.

The main indexes you’re looking for are the ones with COLUMN_NAME = Title, Body and/or Excerpt. There should be at least two, both with INDEX_NAME = searching and INDEX_TYPE = FULLTEXT. You may also have ones with INDEX_NAME = wet_haystack, depending if the plugin is installed and/or has left them behind when uninstalled. If you’ve uninstalled the plugin you can remove these indexes.

If any of the ‘searching’ indices are missing or don’t look quite right, you should drop the odd-looking ones and recreate them like this:

CREATE FULLTEXT searching ON textpattern (Title, Body);

See if any of that helps.

saccade · 2020-02-27 10:14:23

Hi Bloke! Nice to see you here :)

Yes, correct.

I’ll now do as recommended …

Bloke · 2020-02-27 10:16:52

saccade wrote #321958:

Hi Bloke! Nice to see you here :)

Part of the furniture… :)

saccade · 2020-02-27 10:46:48

The code seems to be not working, I only get an empty “array (…)” db-name is entered exactly.

but I have direct access to the database.
the textpattern-table is InnoDB

and here there is a screenshot of the indices
but what I wonder: in other installs I can see an information_schema-db besides my txp-database, but not here
and: there is no collation entry in the searching index.
btw this searching index I just built from within phpmyadmin

Schlüsselname	Typ	Unique	Gepackt	Spalte	Kardinalität	Kollation	Null	Kommentar
PRIMARY	BTREE	Ja	Nein	ID	9223	A	Nein
categories_idx	BTREE	Nein	Nein	Category1 (10)	16	A	Nein
				Category2 (10)	28	A	Nein
Posted	BTREE	Nein	Nein	Posted	9223	A	Nein
Expires_idx	BTREE	Nein	Nein	Expires	9223	A	Ja
author_idx	BTREE	Nein	Nein	AuthorID	78	A	Nein
section_status_idx	BTREE	Nein	Nein	Section (249)	18	A	Nein
				Status	44	A	Nein
url_title_idx	BTREE	Nein	Nein	url_title (250)	9223	A	Nein
searching	FULLTEXT	Nein	Nein	Title	9223		Nein
				Body	9223		Nein
				Excerpt	9223		Nein

Bloke · 2020-02-27 11:27:00

Thanks. Not sure why that code returned nothing (nor why you have no information_schema – I thought that was vital for the operation of MySQL 5.x+!)

Regardless, your results look pretty much the same as mine, though I have wet_haystack entries too. The collation is expected to be empty for the ‘searching’ indices.

*scratches head* This really is a conundrum. I can’t figure out why merely including those columns in the search will prevent previously successful searches on other columns from returning anything.

If it wasn’t for that, I might think it could be some utf encoding thing in the URL param that’s killing the search – an umlaut or something – but it can’t be that.

We need to somehow intercept the search query, capture it and see what Txp is searching for – and where – and see if that is what we expect. Bonus points: we could try running that query in your SQL client directly and tinker with it until we figure out what’s going on.

Try this:

Edit your /textpattern/include/txp_list.php file and find the main query, then add ,1 just before the closing bracket so the query gets dumped out:

$rs = safe_query(
            "SELECT
                textpattern.ID, textpattern.Title, textpattern.url_title, textpattern.Section,
                textpattern.Category1, textpattern.Category2,
                textpattern.Status, textpattern.Annotate, textpattern.AuthorID,
                UNIX_TIMESTAMP(textpattern.Posted) AS posted,
                UNIX_TIMESTAMP(textpattern.LastMod) AS lastmod,
                UNIX_TIMESTAMP(textpattern.Expires) AS expires,
                category1.title AS category1_title,
                category2.title AS category2_title,
                section.title AS section_title,
                user.RealName AS RealName,
                (SELECT COUNT(*) FROM ".safe_pfx('txp_discuss')." WHERE parentid = textpattern.ID) AS total_comments
            FROM $sql_from WHERE $criteria ORDER BY $sort_sql LIMIT $offset, $limit"
        ,1);

Then, fashion a URL query with a search in it, like this:

https://example.org/textpattern/index.php?crit=penguins&event=list&step=list&select_all=all&search_method%5B%5D=title_body_excerpt&search_method%5B%5D=categories

Replace ‘penguins’ in the above with your search criteria of a category name that exists. Put that URL in your browser and run it. You should see the SQL query dumped out at the top of the page with the search criteria in it (as the last row). Copy that entire query block and paste it here (blot out any sensitive info if there is any).

You can then remove the ,1 so your site returns to normal.

We should then be able to analyze this query and run it directly in your SQL client so we can try and find out why it’s returning nothing.

Textpattern CMS

Textpattern CMS support forum

#1 2019-11-01 10:06:10

Search results do not appear

#2 2019-11-01 18:39:03

Re: Search results do not appear

#3 2019-11-01 18:51:45

Re: Search results do not appear

#4 2019-11-01 19:14:34

Re: Search results do not appear

#5 2019-11-01 21:05:04

Re: Search results do not appear

#6 2019-11-01 21:38:04

Re: Search results do not appear

#7 2020-02-27 09:22:13

Re: Search results do not appear

#8 2020-02-27 10:10:31

Re: Search results do not appear

#9 2020-02-27 10:14:23

Re: Search results do not appear

#10 2020-02-27 10:16:52

Re: Search results do not appear

saccade wrote #321958:

#11 2020-02-27 10:46:48

Re: Search results do not appear

#12 2020-02-27 11:27:00

Re: Search results do not appear

Board footer