> 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.