UTF8 error when setting up database

Discussions About MOSS (Myst Online Server Software)

Moderators: a'moaca', rarified

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. Will take me a bit to unravel this, and having dinner myself.
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 »

Alllright. I was able to jump through the necessary hoops to fix the database ownership...

Code: Select all

moss@moulbox:~$ psql -l moss
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 moss      | moss     | 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)

... HOWEVER, i still hit a wall when I run pg_dumpall.

Code: Select all

moss@moulbox:~$ pg_dumpall -U moss
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

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
Problem is, I don't see how to get to the CREATE DATABASE statement you're wanting me to check. pg_dump doesn't produce it, and pg_dumpall seems to be blocked as it is running into the permission denied problem. A websearch suggests that if I give superuser permission to the moss role, then I can get the info you're seeking. But nothing in MOSS/Setup suggested I should ever have to do that.

What should I try next?
The music is reversible, but time is not.
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 »

Let's see what your moss role allows. Do this and check the output:

Code: Select all

-bash-3.2# psql -U postgres -c 'select * from pg_roles;'
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid
----------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           |           -1 | ********    |               |           |    10
 moss     | f        | t          | f             | t           | f            | t           |           -1 | ********    | infinity      |           | 16384
(2 rows)

-bash-3.2#
_R

Edit: this also works and shows the inheritance:

Code: Select all

-bash-3.2# psql -U moss -c '\du'
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+------------
 moss      | no        | no          | yes       | no limit    | {postgres}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

-bash-3.2#
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 »

Code: Select all

moss@moulbox:~$ psql -U postgres -c 'select * from pg_roles;'
psql: FATAL:  Peer authentication failed for user "postgres"

moss@moulbox:~$ psql -U moss -c '\du'
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 alderem   | Create role, Create DB                         | {moss}
 moss      |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

I could... just wipe and restart if that would be easiest. :)
The music is reversible, but time is not.
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 »

Marten wrote: I could... just wipe and restart if that would be easiest. :)
We may almost be there :(

Can you start a PG shell with "psql -U postgres" and get to the "postgres=# " prompt?
If so we may salvage some of it, but have to change all the ownerships (definitely the "moss" database, perhaps the tables and functions as well).

Or you can just do:

Code: Select all

shell$ dropdb -U postgres moss
shell$ dropuser -U postgres moss
shell$ dropuser -U postgres alderem
to wipe, then start again.

Going to head to bed soon, need to be up at 5am :shock:
_R
One of the OpenUru toolsmiths... a bookbinder.
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 »

Ahhh, one more place permissions are granted. Find your 'pg_hba.conf' file (it would be in the database directory tree for the postgres server. You could run pg_config --configure and look for the --datadir element for the database directory).

I added permisssions for moss that gave some privileges for local connections. The tail end of my pg_hba.conf file:

Code: Select all

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

host    all             moss    x.x.x.x/x         trust
The x.x.x.x/x is the network address mask for my local network(s).

_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 »

Alright. As it sounds like you have a busy day tomorrow, and I don't want to eat up much more of your time, I think I'll take the lessons I've had from this round, and start over from scratch. And I'll see if I run into the UTF8 problem again, when I get there. It doesn't seem to me like something that should be related to the ownership issues, but I know those ownership issues are going to bite me until I have them sorted out so best to start fresh.

Thanks again. Will let you know how far I get on attempt #2.
The music is reversible, but time is not.
Christian Walther
Member
Posts: 317
Joined: Sat Dec 13, 2008 10:54 am

Re: UTF8 error when setting up database

Post by Christian Walther »

Marten wrote:
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
I don’t know much about PostgreSQL, but 0xe0 0x30 0x31 is in fact invalid UTF-8, and it doesn’t occur in set_to_moul.txt, so it seems to me that your global_sdl_manager is doing something weird. Maybe running it in a debugger and breaking on throwing of a pqxx::sql_error helps you figure out what.
User avatar
Marten
Member
Posts: 180
Joined: Fri Dec 26, 2008 1:19 am

Re: UTF8 error when setting up database

Post by Marten »

I dumped the databases and went back to *Set Up the Database*, using the user account "moss" instead of the user account 'alderem.' This time I took more notes about the instruction problems I encountered, and how I adapted around them.

I have not yet loaded the SDL file. I'm at a "sanity checkpoint" and I'd like input and thoughts.

For setting up the DB, the wiki says:

Code: Select all

su pgsql -c "psql postgres"
create database moss with encoding='UTF8';
\c moss
create role moss with login;
\q
su pgsql -c "psql moss -f moss.sql"
Problem #1: Instructions assume the PostgreSQL user account is 'pgsql'. It isn't on Ubuntu. It's 'postgres'.
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 adapted the instructions to this. Does this seem right?

Code: Select all

moss@moulbox:~$ sudo su - postgres -c "psql postgres"
create database moss with encoding='UTF8';
\c moss
create role moss with login;
\q
sudo su - postgres -c "psql moss -f ~moss/MOSS/postgresql/moss.sql"
That brings me to the sanity checks I wanted to run. First one:

Code: Select all

moss@moulbox:~/MOSS/postgresql$ psql -U moss -l
                                  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)
Note that the moss database is owned by 'postgres'. This is different than what rarified showed. When I look at the instructions, it seems clear that 'postgres' should be the expected owner, because the database is created before the moss role is instantiated. But given the problems I already noted above, I'm suspicious that while I've gotten the result I expected, it might not be the result I actually need.

Second one:
I've figured out that I still get the same error I previously mentioned from pg_dumpall *if I try to run the command from the 'moss' account*, but if I use the 'postgres' account then it works fine! I found the CREATE DATABASE line that rarified was asking about. Here's the line as it appeared in my output, without the "ENCODING=" that rarified suggested might need to be there:

Code: Select all

CREATE DATABASE moss WITH TEMPLATE = template0 OWNER = postgres;
Here's the whole dump, in case anyone is curious:
https://www.dropbox.com/s/ztdx3fcmlh932 ... l.txt?dl=0

...

So there are my 2 sanity checks and concerns. The database belongs to postgres, and the CREATE DATABASE line does not mention ENCODING. These are the results from following the MOSS/Setup instructions to my best ability, and to the extent that they can be followed (adapting to the assumptions and omissions I've noted).

So - before I proceed to load the SDL - is my setup sane, or is it broken? And if it's broken, can someone give me working instructions that lead to the right outcome?

Thanks :)
The music is reversible, but time is not.
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 »

Will reply longer after lunch, but your first observation about postgres vs. pgsql is correct.
Also, yes it's correct that pg_dumpall would only be allowed under postgres.

_R
One of the OpenUru toolsmiths... a bookbinder.
Post Reply

Return to “MOSS”