Page 1 of 3
UTF8 error when setting up database
Posted: Sat Sep 06, 2014 10:40 pm
by Marten
I am trying to set up MOSS from scratch on a Linux (Ubuntu) system. I've had some stumbles along the way, made some mistakes, but I think I have recovered from each of those sufficiently. I've had a little help from JWPlatt to work around a file referenced in the instructions that is missing from the server. But now I'm stuck on a problem with loading defaults into the database, and am unsure how to proceed. It's possible that this is a downstream consequence of something I did earlier, but ... I'm not sure, and would like some advice.
I am pretty sure that I created the database in UTF8 format. Under "Setup the database", the instructions are very clear that this is important.
I'm now at the "After initial startup" point in the MOSS/Setup instructions. I run this command:
global_sdl_manager game/SDL < ~/MOSS/support/set_to_moul.txt
I mostly get alternating "No node found for age <name> (use 'c')" and "Created new default '<name> SDL" messages. Hopefully that is OK? The instructions don't say what to expect.
Toward the bottom though, I get this, which is clearly an error:
No node found for age city (use 'c')
Created new default 'city' SDL
SQL error: ERROR: invalid byte sequence for encoding "UTF8": 0xe0 0x30 0x31
Writing the node failed for reason 1
There are unsaved changes, change anyway? Variable philDRCVest not found
SQL error: ERROR: invalid byte sequence for encoding "UTF8": 0xe0 0x30 0x31
Writing the node failed for reason 1
So... it appears to me that the file I am loading into the database isn't quite right. This is the unmodified file, pulled via Mercurial from the repository.
Any ideas?
Re: UTF8 error when setting up database
Posted: Sat Sep 06, 2014 11:27 pm
by rarified
Minkata took the SQL unaltered.
When you bring up the postgress client do you get this:
Code: Select all
moss=> show server_encoding;
server_encoding
-----------------
UTF8
(1 row)
moss=>
_R
Re: UTF8 error when setting up database
Posted: Sat Sep 06, 2014 11:46 pm
by Marten
Yes, I do.
Code: Select all
moss@moulbox:~$ psql
psql (9.3.5)
Type "help" for help.
moss=> show server_encoding;
server_encoding
-----------------
UTF8
(1 row)
moss=>
And my file isn't modified, to my knowledge.
Code: Select all
moss@moulbox:~/MOSS/support$ md5sum set_to_moul.txt
0445e6ab412faf5c7980f2241c5b985d set_to_moul.txt
Re: UTF8 error when setting up database
Posted: Sat Sep 06, 2014 11:56 pm
by rarified
Hmmm, II'm still using Postgres 8.3.
The only place I see a reference to UTF8 in a dabase dump (via "pg_dump -U moss")
is in the client_encoding variable as well...
Code: Select all
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'standard public schema';
--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner:
--
CREATE PROCEDURAL LANGUAGE plpgsql;
SET search_path = public, pg_catalog;
--
-- Name: uuid(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE FUNCTION uuid() RETURNS text
AS '/usr/postgres/8.3/lib/moss_uuidgen', 'moss_uuidgen_text'
LANGUAGE c STRICT;
ALTER FUNCTION public.uuid() OWNER TO postgres;
SET default_tablespace = '';
SET default_with_oids = false;
Is this what you see?
Re: UTF8 error when setting up database
Posted: Sun Sep 07, 2014 12:02 am
by Marten
I see the client encoding of UTF8, but my dump is much longer than that. Too long to paste here.
Just a second. Will add dropbox link in a moment.
Edit: Here is what I see.
https://www.dropbox.com/s/y22tuq9on9499 ... p.txt?dl=0
Re: UTF8 error when setting up database
Posted: Sun Sep 07, 2014 12:21 am
by rarified
Sorry, I truncated it to the first part of the dump that shows global settings.
There may be a global server option that needed configuration as well; I'll look in a bit (after dinner)
_R
Re: UTF8 error when setting up database
Posted: Sun Sep 07, 2014 1:00 am
by rarified
Starting to look at your dump vs. mine.
I didn't see a character-specific setting in the server postgresql.conf file.
I do note your dump has set standard_conforming_strings = on; which I don't.
And be aware I'm using postgres 8.3 not 9.x.
_R
Re: UTF8 error when setting up database
Posted: Sun Sep 07, 2014 1:06 am
by rarified
One other attribute to check:
Code: Select all
-bash-3.2# psql -U moss -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
moss | moss | UTF8
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)
-bash-3.2#
Edit: That could be it. Using '
pg_dumpall -U moss' I see the '
CREATE DATABASE' statement for the
'moss' database:
Code: Select all
--
-- Database creation
--
CREATE DATABASE moss WITH TEMPLATE = template0 OWNER = moss ENCODING = 'UTF8';
REVOKE ALL ON DATABASE moss FROM PUBLIC;
REVOKE ALL ON DATABASE moss FROM moss;
GRANT ALL ON DATABASE moss TO moss;
GRANT ALL ON DATABASE moss TO PUBLIC;
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT ALL ON DATABASE template1 TO postgres;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
...
See if your dumped database create statement includes ENCODING.
_R
Re: UTF8 error when setting up database
Posted: Sun Sep 07, 2014 1:16 am
by Marten
OK, there's one difference - the owner doesn't match because I originally created the DB under another owner.
Code: Select all
moss@moulbox:~$ psql -U moss -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
moss | alderem | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
I can change the owner, if you'll tell me how...
It appears that might be an issue for running the next command.
Code: Select all
moss@moulbox:~$ pg_dumpall -U moss > all.txt
pg_dumpall: query failed: ERROR: permission denied for relation pg_authid
pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2
moss@moulbox:~$
Thanks for your time in helping with this...
Re: UTF8 error when setting up database
Posted: Sun Sep 07, 2014 1:24 am
by rarified