Orkun Posted October 26, 2016 Share Posted October 26, 2016 Hi guys I have a problem with a specific filter/selector I try to achieve. The following filter causes a SQL-Error. 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 More sharing options...
BitPoet Posted October 26, 2016 Share Posted October 26, 2016 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. 2 Link to comment Share on other sites More sharing options...
Orkun Posted October 27, 2016 Author Share Posted October 27, 2016 (edited) 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 October 27, 2016 by Nukro It Works! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now