Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

saccade
Plugin Author
From: Neubeuern, Germany
Registered: 2004-11-05
Posts: 489

Re: Search results do not appear

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

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,131
Website

Re: Search results do not appear

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.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#13 2020-02-27 11:47:58

saccade
Plugin Author
From: Neubeuern, Germany
Registered: 2004-11-05
Posts: 489

Re: Search results do not appear

now … that’s mysterious …

though the query is dumped when simply opening the article tab,

after initiating the URL-search, there is NO dump. simply a clean tab with a clean “no results” message.

does that mean it gets kicked out even before touching the database?

edit:
also if I open the article tab, the query will be dumped,

if I now enter something in the search input, notabene Title/Body/Excerpt and Category checked,
the an empty results will appear, but the dump doesn’t change!, so there has been no query.

and checked again: if I check only Title/Body/Excerpt and conduct a search it is entirely the same:
no change in the query dump, it is the original one as of opening the article tab.

Last edited by saccade (2020-02-27 11:59:47)

Offline

#14 2020-02-27 11:53:42

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,131
Website

Re: Search results do not appear

Maybe. Intriguing. What happens if you change the URL to omit the title, body, excerpt:

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

The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#15 2020-02-27 12:02:17

saccade
Plugin Author
From: Neubeuern, Germany
Registered: 2004-11-05
Posts: 489

Re: Search results do not appear

this is going to work, also with expected results in the articles list:

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 v07_txp_discuss WHERE parentid = textpattern.ID) AS total_comments
            FROM v07_textpattern textpattern
        LEFT JOIN v07_txp_category category1 ON category1.name = textpattern.Category1 AND category1.type = 'article'
        LEFT JOIN v07_txp_category category2 ON category2.name = textpattern.Category2 AND category2.type = 'article'
        LEFT JOIN v07_txp_section section ON section.name = textpattern.Section
        LEFT JOIN v07_txp_users user ON user.name = textpattern.AuthorID WHERE ( textpattern.Category1 like '%gottesdienste%' or textpattern.Category2 like '%gottesdienste%' or category1.title like '%gottesdienste%' or category2.title like '%gottesdienste%' )  ORDER BY textpattern.ID desc LIMIT 0, 96

Last edited by saccade (2020-02-27 12:03:29)

Offline

#16 2020-02-27 12:10:31

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,131
Website

Re: Search results do not appear

So it seems the reason the search isn’t working is maybe not to do with the database at all, but because the query isn’t even reaching the part of the page to perform the search. Or something. Hmmm.

*scratches head and stubble*

I wonder if the vendors/Textpattern/Search files will reveal anything. I think to narrow this down we’re going to need some low-level debugging.

Do you mind providing me with some file-access credentials so I can poke around your Textpattern installation and upload a few tweaked Txp files to try and figure out what’s going on? Or shall we carry on back-and-forthing here trying to find where things are going sideways?


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#17 2020-02-27 12:13:09

saccade
Plugin Author
From: Neubeuern, Germany
Registered: 2004-11-05
Posts: 489

Re: Search results do not appear

I’ll send you access :)

Offline

#18 2020-02-27 16:49:01

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,131
Website

Re: Search results do not appear

Thanks to Michael’s generosity at letting me use his server, I’ve narrowed it down. This is actually an issue with the volume of data in the textpattern table and a host-imposed setting to protect the shared hosting environment. Our admin-side search query is choking thus:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

We need to find a way to fix this in core so the queries are less hungry, or in some way manageable. For anyone hitting this issue, we apologise, and will try to find a fix.

Last edited by Bloke (2020-02-27 16:51:10)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

Board footer

Powered by FluxBB