MySQL Compatibility Fix

felix

Yes, I'm fully updated. I've tested with and without turning off NO_ZERO_DATE in mysql. (I'll do a separate message on how to do that in case anyone finds it useful.)

NO_ZERO_DATE enabled (mysql default) on newest update:

Clients page doesn't load clients even when selecting dates.
Tickets page loads tickets only after selecting a date range.

NO_ZERO_DATE disabled on newest update:

Clients page doesn't load clients even when selecting dates.
Tickets page loads tickets as expected.

NO_ZERO_DATE enabled (mysql default) on updates rolled back:

Clients page loads clients only when selecting dates.
Tickets page loads tickets only when selecting dates.

NOZERODATE: disabled - on updates rolled back:

Clients page displays clients (not alpha sorted - I think it's unrelated)
Tickets page displays tickets (not sure what the expected sorting should be)

felix

For any mysql users interested in turning off "NO*ZERO*DATE" for compatibility.

sudo mysql

SELECT @@sql_mode;

This will return the current sql mode. You'll want to turn off NO*ZERODATE, NO*ZERO*IN*DATE, and STRICTTRANSTABLES. These are enabled by default. To disable them, only include the other modes in the next step.

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

The default mysql modes also includes "NO*AUTO*CREATE_USER", but it wasn't enabled on my install so I didn't include it.

quit

sudo service mysql restart

You'll now have compatibility with mariadb while using mysql (alpha sorting didn't work for me until I clicked on the NAME heading on the clients page, but not a big deal). These changes can be reversed by going in reverse order.

Sources:

wrongecho

felix Thanks for sharing! I wonder if is a better solution than adjusting the code then? We specifically call out MariaDB in the install guide, but this could be a workaround for anyone that insists/has (for whatever reason) to use MySQL?

felix

I was hoping I could help you reach full compatibility, but I've reached the limits of my coding skills. At least I was able to contribute the mysql work around. I'll leave my NO-ZERO-DATE off and use the older version until you can roll back the clients.php changes you made for me. I appreciate it, by the way.

johnny

Hi @felix Goahead and update to the latest version I think we have fixed the issue, but we need you to test to make sure it did fix the issue.

Also we moved all the search by dates in the file pagination_head.php to dedup the same same code being used across all search pages.

https://github.com/itflow-org/itflow/commit/2acb5e0b7bd4b095cf5d49adfdb7be8cfd780768

felix

On the latest update with default mysql settings the tickets page loads without issue. It previously had the same problem as the clients page. The clients page doesn't load any clients.

felix

The last working version with NO-ZERO-DATE date off is commit 704c8e409f

johnny

One update that we did was add tag searching support which should not cause the issue your seeing. Can you post your php log errors when accessing clients liating

felix

johnny

[error] 2510331#2510331: *12679 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.omitted.net/clients.php on line 103" while reading upstream, client: xx.xx.xx.xx, server: helpdesk.omitted.net, request: "GET /clients.php HTTP/2.0", upstream: "fastcgi://unix:/run/php/php7.4-fpm-helpdesk-jhuh1.sock:", host: "helpdesk.ommited.net", referrer: "https://helpdesk.omitted.net/tickets.php"

johnny

also I think you might be missing a DB update. Post Your DB version here as well.

Also check the table client_tags

let me know the names of the fields in there

johnny

I think this has to do with tags. Check you table client_tags make sure both fields match the following name "client_tag_client_id" and "client_tag_tag_id"

felix

johnny Both columns are there.

I also did a fresh install with a fresh DB. The clients page does not display "No Results" like all the other pages show. Even after adding a client I get the same issue. I can create a ticket and it will display.

johnny

Hang tight i'm going to setup a MySQL Instance and play around with this see if I can replicate and come up with a solution for you

johnny

Hi @felix This has been fixed go ahead and update and let us know.

The issue was in the GROUP BY clause in the query. MySQL is very strict and required all non aggregate columns to be specified in the clause not just client_id.

felix

johnny That fixed it. Thank you!