WebFaction
Community site: login faq

I created some tables in my database today and they all have character encoding LATIN1. However, I need them to be UTF-8. The other tables in my database are all UTF-8. I have even created some of the new tables using the same scripts as before so I don't know what is causing this difference. Some tables were made from create table scripts; some via Django models.

I used the following statement to verify the character set:

SELECT table_name, CCSA.character_set_name FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = T.table_collation;

I realize that this looks up the character set based on table_collate so it may just be table_collate that is different. But I also need that to be utf8_general_ci, the same as before. I do not assign the table_collate in any of my scripts, nor did I do so before.

asked 14 Aug '12, 08:37

tpip
4111421
accept rate: 0%

edited 15 Aug '12, 05:50


The default character encoding on your server is latin1, but the encoding for the DB itself should override that when creating new tables. For example, I just created a new utf8 MySQL database on a test account on your server, logged into it at the command line, ran "create table foo (fld1 varchar(100));", and the database, "foo" table, and "fld1" field were all created as utf8.

I'm pretty sure Django is hard-wired to create everything as utf8, so I'm not sure how you're ending up with latin1 tables and fields.

You should be able to fix them by following this example: Character encoding hell: Converting MySQL latin1 to utf8 for use in Django

permanent link

answered 14 Aug '12, 13:54

seanf
12.2k41936
accept rate: 37%

The default character set of the database was latin1:

SELECT * FROM information_schema.SCHEMATA S WHERE schema_name = "name";

I altered it back to utf8:

ALTER DATABASE name default CHARACTER SET utf8;
ALTER DATABASE name default COLLATE utf8_general_ci;

The new table is now created as utf8. I will delete the other tables and create them again.

But I'd sure hate if this happened again - the db used to be utf8 and the old tables are, in fact, utf8. I haven't touched the char set so something is going on with webfaction!

(15 Aug '12, 02:16) tpip

Hi,

It is possible that some collations/charsets for your databases were changed when we migrated your account to a new server last month (our migration script did have such a bug). But for any new databases you create, this shouldn't be a problem.

(15 Aug '12, 03:34) todork

Alright, thanks.

(15 Aug '12, 05:50) tpip
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×168
×3

question asked: 14 Aug '12, 08:37

question was seen: 4,934 times

last updated: 15 Aug '12, 05:50

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2019 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM