UTF8 error when setting up database

Discussions About MOSS (Myst Online Server Software)

Moderators: a'moaca', rarified

User avatar
Mac_Fife
Member
Posts: 1239
Joined: Fri Dec 19, 2008 12:38 am
Location: Scotland
Contact:

Re: UTF8 error when setting up database

Post by Mac_Fife »

Marten wrote:Problem #2: After adapting for problem #1, the instructions also don't say you should run these commands as root. If I run this from any account other than root, I get prompted for the postgres user password, which I don't know.
I've never tried any of this but are the instructions here under "Basic Server Setup" any help?: https://help.ubuntu.com/community/PostgreSQL
Mac_Fife
OpenUru.org wiki wrangler
User avatar
Marten
Member
Posts: 180
Joined: Fri Dec 26, 2008 1:19 am

Re: UTF8 error when setting up database

Post by Marten »

Hi Mac,

I appreciate the attempt, but what I really want is for someone else to fix up the instructions so that they are correct and complete, and so they will work not only for me, but for others who come along in the future. I don't believe I have the expertise to improve the instructions myself. I'm sharing my novice stumbling with you all so that you can see "Oh yeah, that step IS a bit vague, isn't it?" and fix it.

Once we get past this bit, I'll load the SDLs and see if I get the same error I got before (that led to me starting this thread). And if so, at that point I'll try and pull out the debugger as Christian suggests (it's a great idea).
The music is reversible, but time is not.
User avatar
Mac_Fife
Member
Posts: 1239
Joined: Fri Dec 19, 2008 12:38 am
Location: Scotland
Contact:

Re: UTF8 error when setting up database

Post by Mac_Fife »

Ok Marten. My take was that maybe if PostgresSQL has been set up according some "standard" procedure then maybe running that step as 'root' isn't necessary (and it seemed like it answered the unknown password question).
Mac_Fife
OpenUru.org wiki wrangler
User avatar
rarified
Member
Posts: 1061
Joined: Tue Dec 16, 2008 10:48 pm
Location: Colorado, US

Re: UTF8 error when setting up database

Post by rarified »

I've just had a chance to look at your dumpall output. A couple of things differ from my dump.

Your output has "standard_conforming_strings = on", mine is "off". Your setting appears to be global, mine is displayed as part of the "postgres" database (after the \connect postgres line, whose properties I assume are inherited by "moss".)

You do not have any access right manipulation on the "moss" database. Also, your "moss" database is created from the "template0" template, mine is from the "template1" template. (May be a postgres 9.x versus 8.x difference... do you have a "template1" database?) My "moss" database was created with "ENCODING = 'UTF8'", yours has no ENCODING attribute. I have these access statements:

Code: Select all

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;
I'll look again at the MOSS setup instructions to see if I did something different (if I can remember :roll:). A'moaca may have assumed the default postgres installation setting was for UTF8.

Did you find your pg_hba.conf file? What does it contain?

_R
One of the OpenUru toolsmiths... a bookbinder.
User avatar
Marten
Member
Posts: 180
Joined: Fri Dec 26, 2008 1:19 am

Re: UTF8 error when setting up database

Post by Marten »

Thanks rarified! I hope this answers all of the questions you've raised. Please let me know if I've missed any.

According to the Postgres 9.3 documentation, with emphasis mine:
standard_conforming_strings (boolean):
This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off)."
'psql -l' indicates that there are indeed both template0 and template1 databases. I don't know why the pg_dumpall output is not indicating the moss database's encoding, but psql -l seems to indicate that all 4 databases including moss have UTF8 encoding.

Code: Select all

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 moss      | postgres | 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)
According to the Postgres 9.3 documentation, by default all databases are created from template1. But that contradicts the observed outcome of following the MOSS/Setup instructions to create the moss database. I don't know why.

I did find pg_hba.conf. The only active lines in the this configuration file are as follows:

Code: Select all

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
Except for installing packages that Ubuntu did not install by default, I have not performed any additional steps other than what was in the MOSS/Setup instructions, so I think discrepancies can only be explained by one or more of the following:
  • We interpreted the instructions differently
  • You did extra things not in the instructions
  • There are differences between your version and my version of Postgres (which may be attributable to either version number or maintainer changes).
The music is reversible, but time is not.
User avatar
Marten
Member
Posts: 180
Joined: Fri Dec 26, 2008 1:19 am

Re: UTF8 error when setting up database

Post by Marten »

The rock is hard, the rock-biter is patient. 8-)
The music is reversible, but time is not.
User avatar
JWPlatt
Member
Posts: 1137
Joined: Sun Dec 07, 2008 7:32 pm
Location: Everywhere, all at once

Re: UTF8 error when setting up database

Post by JWPlatt »

Should MOSS shards be entered into the Journal of Irreproducible Results?

I'll bet if this were a Windows environment, using GUI tools, you could have one up in two minutes. :P
Perfect speed is being there.
cjkelly1
Member
Posts: 67
Joined: Mon Dec 29, 2008 6:08 am

Re: UTF8 error when setting up database

Post by cjkelly1 »

Just swinging by and I have a small bit of time for comment and some hopefully
useful information.

Windows and a GUI would not make any difference to this problem. The issue is that it is an unmaintained
project. Windows has lots of similar problems with unmaintained software - for example: old stuff that simply
will not not run on Win7/8 anymore. Not much keeps working indefinitely without any maintenance. I am actually
surprised that MOSS has run so long and so well with not much maintenance.

In any case, every once in a while I fire up the MOSS and play the Uru for nostalgia, since it is easy and speedy and
I do not have to worry about downloading stuff, accounts, nor worry about client changes. I am running a Debian box
and PgSQL 9.1, so can give data on this setup. It has been a while since I set this up, so hopefully I am remembering
the pain points I hit.

First difference that you have already noticed is that pg now defaults to "peer" auth for local sockets in
pg_hba.conf. We can use "trust" as rarified has recommended, but that allows any user at all on the machine
to access the socket, which bothers me personally. I used md5, because at least a password is required for that.
It is merely a matter of preference, since MOSS is not often run on a true multi-tenant box.

I see you already have a "moss" user created for the db, so to create the DB easily, log into your box as root and do
a "su postgres" or "su postgresql" (does not matter which one your system uses - we just want to be db admin), fire up
psql and do this command:

Code: Select all

CREATE DATABASE moss WITH ENCODING='UTF8' LC_CTYPE='en_US.UTF-8' LC_COLLATE='en_US.UTF-8' OWNER=moss TEMPLATE=template0 CONNECTION LIMIT=-1;
That will set up your DB with the right owner with no hassle or encoding warnings. I do not have UTF as a locale, so the
above was the only way I could set it up without psql complaining about my system locale.

You also need to load the extension differently in 9.1, but I see that there is another thread where someone
already put the details on how to do that, so you are good there.

rarified has the correct answer for the UTF error on the SDL manager import. You need to set
"standard_conforming_strings = off". The problem is that with it on, the "\" is treated literally,
and the old formatted escapes that MOSS uses break. I set the "standard_conforming_strings = off" in the
postgresql.conf file, and have not noticed any problems with other apps.

I would recommend changing the above setting before running in the "moss.sql" file. I do not recall if there are
any old-style escapes in that file that would be a problem, but it is best to have it all set properly before importing data.

Well, that is all I have. Hope it might help a little bit.
Post Reply

Return to “MOSS”