Jump to content

Selector causes SQL-Error in Lister/Listerpro when using "Contains Words" Option (Possibly sql_mode related)


Orkun
 Share

Recommended Posts

Hi guys

I have a problem with a specific filter/selector I try to achieve. The following filter causes a SQL-Error.

interface.png

This error also appears when I want to sort subfields(options and page fields - multi and single select) in the lister or with the API.

Some Infos

Processwire Version: 2.7.3

ListerPro Version: 1.0.8

Media Manager: 0.0.6

PHP-Version:  7.0.8-0 UBUNTU 0.16.04.3

MySQL-Version: 5.7.13-0ubuntu0.16.04.2-log - (Ubuntu)

 

I tried changing the sql_mode many times:

DEFAULT IS: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • NO_ENGINE_SUBSTITUTION

I tried all above but with minimal effect. When I remove "ONLY_FULL_GROUP_BY" it doesn't cause a SQL-Error but it still can't find the page. Also sorting subfields start working. But everything works fine locally on a fresh blank install with the media manager and listerpro. My local MySQL Version is 5.6.25 and PHP version is 5.6.

-----------------------------------------------------------

The Intention why I am asking (last post):

-----------------------------------------------------------

Some other Processwire Topics related to sql_modes:

I wanted to ask in the forum before I try it on github: https://github.com/processwire/processwire-issues

-----------------------------------------------------------

Some related links to sql_modes:

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

 

Greetings 

Orkun

 

Link to comment
Share on other sites

The two sql_mode settings incompatible with ProcessWire should be ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES. The dev version (>= 3.0.37) removes those by default for MySQL versions >= 5.7.0. It might make sense to file a request to run that logic for all MySQL versions that support these settings.

Also, for *= and ~= to work, the search string needs to be 4 characters or longer. See the selector operator documentation (especially the two paragraphs before the "Negative Operators" heading) for details.

  • Like 2
Link to comment
Share on other sites

17 hours ago, BitPoet said:

The two sql_mode settings incompatible with ProcessWire should be ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES. The dev version (>= 3.0.37) removes those by default for MySQL versions >= 5.7.0. It might make sense to file a request to run that logic for all MySQL versions that support these settings.

Also, for *= and ~= to work, the search string needs to be 4 characters or longer. See the selector operator documentation (especially the two paragraphs before the "Negative Operators" heading) for details.

Thanks! @BitPoet

I removed now ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES from the sql_mode and it works know with 4 characters :). Now I am trying to change the full text index minimum word length. I found a site which describes this easy and clean: https://www.electrictoolbox.com/mysql-full-text-index-word-length/. Now I tried this on localhost where I changed the ft_min_word_len to 3. It worked in the lister pro at first, but it doesn't affected the media manager search. So I made "REPAIR TABLE field_media_manager_image QUICK; and it also started working. Do I have to do this repair only for the media manager fields(tables)? Are all other Fields/Tables(created by the GUI) automatically updated since the lister pro worked straight after I changed the ft_min_word_len?

Greetings

Orkun

 

EDIT: I have let my hoster to change the ft_min_word_len to 3. Now filtering for tags works great in the media manager/listerpro. I also had to reindex the tables of the media manager to get it finally working. Made it with REPAIR TABLE tablename QUICK; :)

Edited by Nukro
It Works!
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...