Could it be either a SQL_ASCII or UTF-8 matrix database?

(James) #1

Hi,

 

I noticed our International Character Set in the System Configuration (within Matrix) is set to UTF-8, however our live matrix database encoding within Postgresql is set to SQL_ASCII. Should I be worried or does this not really matter?  So can UTF8 data (as set in International settings) live inside SQL_ASCII database OK?

 

If I wanted to restore a copy of the live matrix db to our postgres test box where new databases are now UTF8 by default (including template1, template0, postgres) can I do that without issues?  Will a new UTF8 postgres encoded matrix database hold the existing data from the live postgres matrix (SQL_ASCII db) without messing it up?  Presumably it's OK as the data within it has always been  UTF8 according to the International system config settings in matrix?  Or am I getting this all mixed up?

 

Thanks in advance.

(Greg Sherwood) #2

UTF8 data will be stored correctly in an SQL_ASCII because it basically ignores all encoding. This lets you use different encoding on the different sites you might have in your Matrix install, and also lets us recommend a single DB encoding no matter what content is going to be heading into the database. I would not recommend changing the DB encoding of your live system.

 

Migrating from SQL_ASCII to UTF8 can be problematic in general because you may have invalid UTF8 characters in your database already.

 

For copying a Matrix system to a test box, you should try and create the test DB with the same encoding, or you will find that either your data does not migrate or your test system works differently to your live system. If that is not an option, you can obviously try to use the UTF8 database (it works in almost all cases) but I've found it best to have the test environment match the live environment as much as possible.

(James) #3

Thanks Greg, sounds sensible for the test and live to be the same.  Presumably our live db is only SQL_ASCII because that was how the old Squiz downloadable VM was set up. 

 

This seemed to work for anyone else who needs to know: postgres=# CREATE DATABASE matrix ENCODING 'SQL_ASCII' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C' OWNER matrix;

 

Without specifying the template I got an error about incompatiblility with the encoding of the template database which is UTF8.

 

Thanks again.