FrontAccounting forum — Search function in 2.4.2

Re: Search function in 2.4.2

null@example.com (spott) — Mon, 21 Aug 2017 07:51:38 +0000

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

Re: Search function in 2.4.2

null@example.com (apmuthu) — Mon, 21 Aug 2017 03:06:48 +0000

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.

Re: Search function in 2.4.2

null@example.com (spott) — Sun, 20 Aug 2017 13:47:05 +0000

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]

Re: Search function in 2.4.2

null@example.com (apmuthu) — Sun, 20 Aug 2017 12:11:00 +0000

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.

Re: Search function in 2.4.2

null@example.com (spott) — Sun, 20 Aug 2017 12:00:48 +0000

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

Re: Search function in 2.4.2

null@example.com (apmuthu) — Sun, 20 Aug 2017 11:11:19 +0000

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.

Search function in 2.4.2

null@example.com (spott) — Sun, 20 Aug 2017 07:54:56 +0000

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.