Variable Collation doesn't match Extractvalue Variable
Posted by:
Jonny Test
Date: December 14, 2009 10:00PM
Hello everyone.
I come with what I hope is an easy issue to resolve.
I have the following lines of code:
SET v_DetailPath = CONCAT('/Recipe/RecipeDetails[',v_DetailLoop,']/');
SELECT v_RecipeID
, ExtractValue (v_XML, CONCAT(v_DetailPath,'@StepTypeID')) AS StepType
, ExtractValue (v_XML, CONCAT(v_DetailPath,'@SortOrder')) AS SortOrder
, ExtractValue (v_XML, CONCAT(v_DetailPath,'Header/text()')) AS Header
, ExtractValue (v_XML, CONCAT(v_DetailPath,'StepDetails/text()')) AS StepDetails;
When trying to run these two statements I get the following error:
Error 12/14/2009 10:54:40 PM 0:00:00.053 MySQL Database Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'extractvalue' 15 0
Now, I went and figured out that the v_DetailPath is the one using utf8_general_ci, and the code pulling from the xml is the utf8_unicode_ci.
I have all my tables and database settings set to utf8_unicode_ci.
What I am trying to figure out is two this.
1) how can I convert v_DetailPath to utf8_unicode_ci? I have tried the convert function, but I apparently am not using it correctly.
2) why if my setting in database are what they say below, why is my variable getting set to general and not unicode?
Variable_name Value
collation_connection utf8_unicode_ci
collation_database utf8_unicode_ci
collation_server utf8_unicode_ci
Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets
Note that this will eventially get dropped into a table and as such, I have verified that the table also has the same unicode collation.
Any help will be greatly appreciated.
Sincerely,
Johny Test