Nizzo Many thanks to Nizzo. That hack worked. Dropping the user__cdata table did not work. The table was not regenerated and locks on-screen. Nizzo's approach stopped the errors being dumped and allow us to use the search criteria.

Adding detail here for devs. We force all tables/db to be InnoDB using utf8mb4/utf8mb4__unicode_520_ci format (international). Noticed that the newly generated table is MyISAM using utf8_general_ci. But the rows of user data are regenerated correctly in that table with the correct collations.

    4 months later

    USE information_schema;

    SELECT concat("ALTER DATABASE ",table_schema,
    "     CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
    FROM TABLES
    WHERE table_schema like "osticket"
    GROUP BY table_schema;

    SELECT concat("ALTER TABLE ",table_schema,".",table_name,
    "     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
    FROM TABLES
    WHERE table_schema like "osticket"
    GROUP BY table_schema, table_name;

    12 days later

    Same problem here. Newly installed in the beginning of December. Fixed the problem with Nizzo's hack. So, it really seems to be a bug in this version and needs to be adressed.

    Thanks for this fix, can confirm this has occurred for me also on a new install. I completed @Nizzo fix and I was able to create tickets using the search function for a user.

    Install Details: v1.15.4 (6bd7884)

    Thank you.

    I took a different approach - I have simply went Agent Panel->Manage->Forms-Contact Information and selected checkboxes next to Phone and Notes fields (Delete) and saved the form which made the 2 fields which are optional to be deleted. Now error is gone.

    BTW the form page is distorted as form is wider than the page area it should use.

    Since this seems to be the popular thread for this topic I'll quote the resolution below:

    Please add a new dummy field to the Contact Information Form (Admin Panel > Manage > Forms > Contact Information), save changes, delete the newly created dummy field, save changes again, and search for a User in the User Directory (Agent Panel > Users > User Directory). This should drop and recreate the User CDATA table with the appropriate information. The search for the User should show results, if not, wait a few minutes, refresh the page, and try again.

    Note:
    This is a known issue that is on our bugsheet. The issue is an unintended result of us changing how the CDATA tables work in the backend.

    a month later

    It works!

    I've been trying to request it for days since I installed it, but the trick of Nizzo

    Thanks!

      10 months later

      I have similar problem, but in my case search works at some places but does not at others.
      Search box in user list - works.
      Search dialog which opens on new ticket creation, change user ticket or add collaborator - does not work.
      Search box in new ticket form which appears on drop-down under select user - works!

      Please, where the SQL logs which some users have posted here come from, so I can look at mine?
      Our version is v1.17, on Windows. I recently upgraded from v1.14. Before the upgrade, search worked everywhere.

        george63

        You will need to consult your webserver and MySQL documentation to find where the logs are stored. The path is always specific to the setup so there is no one place to check.

        Cheers.

            3 months later

            KevinTheJedi I'm sure there is a default place to check for MySQL on windows. I've installed MySQL because of oSTicket following video instruction on osTicket's web site. I never used MySQL before and I don't need it for anything else. I just want user search in osTicket to work as expected, nothing more. Unfortunately it does not work and none of suggested workarounds in this thread does not work either.

                Write a Reply...