1 Topic by spott (edited by spott 08/20/2017 08:00:56 am)

  • spott
  • Senior Member
  • Offline
  • From: Estonia
  • Registered: 05/30/2009
  • Posts: 70

Topic: Search function in 2.4.2

Hi
I today upgraded from 2.3.26 to 2.4.2. And one thing, what I discovered - customer search is broken - ok - its not broken, but it has lost one very important thing - Most of my customers and suppliers names beginning with uppercase letter - some of them are all uppercase in name. In 2.3 version - in search - there was no problem with search - I can write in lowercase beginning of the name and it finds it. Example - Netikink - for searching I need to write only - neti and thats fine. Now in 2.4 search doesn't search any more uppercase/lowercase letters together. So when I am searching now neti - it gives me nothing. I need to write Neti - and then it finds it.

Is it possible to fix it?

EDIT: Item search and supplier search working as needed - there is no problem with uppercase/lowecase. So the problem is only in customer part - when searching customers.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Search function in 2.4.2

If we want the "LIKE" parameter to be case-insensitive then the database collation should be utf8_general_ci. Otherwise if the db and connection collations are different, we will need to set the comparables in the LIKE statement to be "LOWER(field1) LIKE CONCAT("%",@cmpstr,"%")" in places like function get_customers_search() in sales/includes/db/customers_db.inc or cast / convert the collations on a per sql basis.

The real workaround:

After the last db_query() in the function set_global_connection() in includes/db/connect_db_mysqli.inc, add the following:

    db_query("SET @@collation_connection = @@collation_database");

The above will help avoid "Illegal mix of collations" error by synching the connection collation to that of the database and make for case-insensitive searching. It will also benefit those in a single db environment on a shared hosting platform with possibly different collations for each application - YMMV.

It is possible that the collation of the customer name may have been of a different collation as well - check your CoA and db schema!

@joe: can we commit this - after all the db collation should dictate such usage.

apmuthu's Website

  • spott
  • Senior Member
  • Offline
  • From: Estonia
  • Registered: 05/30/2009
  • Posts: 70

Re: Search function in 2.4.2

Hi
I added this line to includes/db/connect_db_mysqli.inc - it looks now so:
$db = mysqli_connect($connection["host"], $connection["dbuser"], $connection["dbpassword"], "",
        !empty($connection["port"]) ? $connection["port"] : 3306); // default port in mysql is 3306
       
    mysqli_select_db($db, $connection["dbname"]);
    ///// From mysqli release 5.6.6 the sql_mode is no longer empty as it was prior to
    ///// this release. Just for safety we make it empty for all 5.6 release and higher.
    ///// This non empty sql_mode values can interphere with FA, so all is set empty during
    ///// our sessions.
    ///// We are, however, investigating the existing code to be compatible in the future.
    db_query("SET sql_mode = '".SQL_MODE."'");
    /////added fix
    db_query("SET @@collation_connection = @@collation_database");
    /////
    $SysPrefs->refresh();
    return $db;

But it doesn't help. Or I need something to change in customers_db.inc file also?
Collation - in config_db.php and also in database - all tables are utf8_unicode_ci

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Search function in 2.4.2

Check the output of:

SHOW VARIABLES LIKE "%collation%";

Clear the company/#/js_cache/*/*.js files, logout, clear browser cache and login again after closing all browser instances.

apmuthu's Website

  • spott
  • Senior Member
  • Offline
  • From: Estonia
  • Registered: 05/30/2009
  • Posts: 70

Re: Search function in 2.4.2

Nothing - I cleared cache, even tried incognito with browser.
Variables - I don't have ssh access to server, but phpMyAdmin shows that:
[img]https://www.upload.ee/thumb/7368594/database.png[/img]

Post's attachments

database.png 44 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Search function in 2.4.2

It appears that your session vale for the collation is specifically utf8mb4_unicode_ci which is different from the others.

You can test it in a local XAMPP first and see if the issue persists.

I have no issue with the standard en_US-demo.sql on XAMPP. You too can try the standard en_US demo CoA as a plain vanilla install (not an upgrade) and see if the issue persists. If it goes away, then compare your db schemas to see what gives.

If all else fails, PM me a link to your DB backup sans pwds/txns and the specific build of FA you are using and I will test it out in a sandbox. That way we can check if it is a php script issue or a MySQL db issue.

State the versions of PHP, MySQL, Apache, OS platform.

apmuthu's Website

  • spott
  • Senior Member
  • Offline
  • From: Estonia
  • Registered: 05/30/2009
  • Posts: 70

Re: Search function in 2.4.2

Hi - I will test it at the end of the week.