I asked the devs to take a look at this thread.
Unknown column 'A5.phone' in 'where clause'
- Edited
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.
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;
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.
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.
- Best Answerset by KevinTheJedi
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.
- Edited
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.
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.
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.