MySQL Forums
Forum List  »  Newbie

Re: If a proc contains text, what collation will this use?
Posted by: Rick James
Date: October 09, 2014 09:08PM

> The relevant primary keys are then obtained via matching the text. For example, a list of countries are first inserted, then a list of Cities that have CountryID as a foreign Key.

If `country` is the PRIMARY KEY, you don't need `country_id`, nor do you need an AUTO_INCREMENT.

> However the test server is using something else (I think the default latin1_swedish_ci).

The client must tell mysql what encoding (CHARACTER SET) it has. See SET NAMES. Collation is not relevant when inserting/fetching data. CHARACTER SET conversions can be made on the fly -- but you must be explicit in the client.

> I've explicitly set all tables in the schema to use utf8_unicode_ci (rather than defaulting to the server). However this is still failing their end.

The question you have not answered is whether you INSERTed the data correctly. Find a row with data other than plain English letters, do SELECT HEX(field)... -- let's see if you inserted latin1, utf8, '?', or truncated the string. Any of these could have happened if you did not do SET NAMES before INSERTing.

> ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

That does nothing other than providing defaults for _future_ CREATE TABLEs. It has no impact on the existing tables.
Ditto for ALTER TABLE -- it changes defaults for future columns; it does not change the existing columns.

Changing the _encoding_ stored in a column is not simple.

> LEFT JOIN Country c ON CONVERT(TRIM(c.Name) USING utf8) = CONVERT(TRIM(A.Country) USING utf8);

This further implies that the INSERTs were not done properly.

Also, you imply that you are using a STORED PROCEDURE? Do SHOW CREATE PROCEDURE foo -- take a look at the CHARACTER SET and COLLATIONs that were provided when you did the CREATE.

More discussion:
http://mysql.rjweb.org/doc.php/charcoll

You may have to backup, start over, reCREATE and reINSERT everything, etc.
Once _everything_ says utf8 (or utf8mb4), all of these issues should go away.

Options: ReplyQuote


Subject
Written By
Posted
Re: If a proc contains text, what collation will this use?
October 09, 2014 09:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.