Clients page stopped displaying again

felix

It happened on an update after commit 5640a22c18.

2023/03/07 09:29:17 [error] 556250#556250: *315106 FastCGI sent in stderr: "PHP message: PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in /home/helpdesk-jhuh1/helpdesk.---.net/clients.php on line 103" while reading upstream, client: 69.137.195.222, server: helpdesk.---.net, request: "GET /clients.php HTTP/2.0", upstream: "fastcgi://unix:/run/php/php7.4-fpm-helpdesk-jhuh1.sock:", host: "helpdesk.---.net", referrer: "https://helpdesk.---.net/dashboard_financial.php"

johnny

Hi @felix

This is a known bug that is affecting MySQL only. It is caused by the GROUP BY clients.client_id on line 24 of the clients.php

https://github.com/itflow-org/itflow/blob/4344a4fcca67581ce29e577361bc84dd6a1fae78/clients.php#L24

We have no fix in sight for this for MySQL users and are trying to reach out for additional help to anyone that may have a fix.

The problem is if we remove the GROUP BY clause it creates duplicate clients and sometime more depending on how many tags are assigned to the client.

We highly recommend against using MySQL and instead use MariaDB as we test thoroughly with MariaDB.

To get it working for your instance you will have to remove line 24 all together, and on each update until a fix comes out.

If you have any SQL skills you can try playing around with the the query on lines 12 through 26 of clients.php file and see what you come up with.

In the meantime we are focusing on some other higher backend priorities that need major attention within the code.

felix

When I run the query in terminal I get

ERROR 1055 (42000): Expression #59 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.client_tags.client_tag_tag_id.' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It seemed like such an arbitrary decision when I chose mysql when I first set up my server.

johnny

I know you would think MySQL and MariaDB would be one in the same. Thats what I thought. Initially we moved to MariaDB because Linux Distro started preferring it over MySQL due to the Oracle Acquisition, but in that time MariaDB became way more performant than MySQL. Its Blazing Fast and just all around more robust.

As far as the error. If you add the other columns in the Group By clause it still produces dupes, which is strange.

felix

johnny I have clients displaying now. From what I can tell mysql doesn't like that client_tag_tag_id belongs to a different table from the column being used to GROUP BY. Having both columns in GROUP BY satisfied the requirement. Line 24 becomes:

GROUP BY clients.client_id, client_tags.client_tag_tag_id

It seems mysql's development track has focused on strict error prevention. I don't know enough on the subject to determine if they were successful in that goal The following link contains some examples of the reasoning for using such strict rules, specifically in regards to GROUP BY. It also includes an alternate solution of using ANY_RESULT(client_tags) after SELECT, you wouldn't be able to use the wildcard and it defeats the purpose of mysql's error correction (if it has a benefit).

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

johnny

Hi felix

We attempted adding clienttags.clienttag_tag_id but it still duplicates clients that have more than 1 tag attached to it. For example goto settings > Tags > Create like 3 tags

Now go to clients and edit a client click on tag tab select all 3 tags then hit save. You will notice that the client has been duplicated 3 times.

felix

johnny I added the following line just before the query on clients.php. All it does is tell mysql to act like a traditional DB for that session only. I'm assuming it has virtually no effect on mariadb since it acts traditionally by default. Now instead of coding for mysql compliance to achieve compatibility, you can force mysql to comply to mariadb at run time.

$mysqli->query("SET SESSION sql_mode = 'TRADITIONAL'");

Source: https://stacktuts.com/how-to-change-sql-mode-at-runtime-in-php

johnny

Nice Find @felix I will add this to the existing code in the next update

johnny

Hi @felix Instead of adding:

$mysqli->query("SET SESSION sql_mode = 'TRADITIONAL'");

on the top of clients.php

add it to the bottom of your config.php

This was it doesn't interfere with updates.

This will be the preferred method

Let us know if it works as intended and we will document this in our docs section.

felix

That works, such a simple solution too.