Hi. I found a quick fix for the MySQL bug. MySQL doesn't like zeros for dates. This behavior can be changed, but a better solution is to use non-zero minimums and maximums. Instead of 0000-00-00 to 9999-00-00 you can use 1000-01-01 to 9999-01-01.

I submitted a pull request.

MySQL is too high class to date zeros!

Upon more testing I noticed it affects the search function and alphabetical sorting….

Changing the "to" date to 2999-12-31 fixes searching, but not alpha sort.

I see that, I may have to take a different approach to this one. Thank you for the PR but i'm going to work on a long term solution

Clients aren't displaying at all (even if I select a date range) after an update. Table headings (Name, Primary Address,…) do show. If I select a future date I still get heading and no "No Results" message.

    felix

    Possibly related to the recent change we did for allow you to search the client page via tag. Have you updated the database to v0.4.5?

      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)

      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:

        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?

          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.

          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.

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

          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

            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

            [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"

              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"

                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.

                  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

                  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.