MySQL Bugs: #73671: COLLATE clause causes error on I

Bug #73671	COLLATE clause causes error on I_S.rotuines
Submitted:	21 Aug 2014 13:26	Modified:	19 Apr 2018 13:01
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.6.20	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The statement 

SELECT `SPECIFIC_NAME` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'databsename' COLLATE utf8_bin AND ROUTINE_TYPE = 'FUNCTION';

.. returns bogus error, that table queried is uft8mb4.  But the table is I_S.routines and it is utf8 (verified also on the system where the error occurs). 

How to repeat:
I will upload an image. The error appears in a GUI tool (SQLyog). The query is generated by the tool.

I have not been able to reproduce the error (neither with SQLyog nor any other client) on my own systems. I have asked for more details from our user reporting this (asking him to try the statement with more clients and different substitutions of 'databasename' in the query).

I suspect that is a data specific bug occuring with specific utf8mb4 data. The affected user is Chinese.

Suggested fix:
No clue.

SQLyog error dialog with query and error essage.

Attachment: F640AED7-FFAA7528-AEDFF553.jpg (image/jpeg, text), 56.59 KiB.

If somebody can think of other questions it would make sense to ask this user about his environment, please specify!

fixed typo in synopsis.

Hi Peter,

Thank you for your bug report.

Needless to say, we do not use nor do we support SQLyog. Hence, what we need is a totally reproducible test case, with Chinese ideograms and everything, so that we can reproduce it with latest 5.6 and 5.7 releases of our servers and with our tools that can present ideograms.

Thank you very much, in advance ....

This has long been sorted out. User had executed "SET NAMES utf8mb4;" in the client. 

Closing.

closing.

Peter,

Thank you for the feedback.