ejabberd development
  1. ejabberd development
  2. EJAB-1337

Use the syntax PRIMARY KEY (field(int)) only for MySQL

    Details

      Description

      I'm sorry if this is the wrong forum for this report. I am a developer but not very experienced with erlang or ejabberd at the moment. I have successfully compiled and run the ejabberd 3.0.0 using mnesia but when I tried to switch to a postgres backend (using the postgresql erlang drivers from here http://frihjul.net/pgsql) I get these errors from the postgresql logs

      2010-11-03 13:50:17 GMT STATEMENT: CREATE TABLE passwd ("user" text, "host" text, "password" text, PRIMARY KEY ("user"(105), "host"(105)))
      2010-11-03 13:50:17 GMT ERROR: relation "passwd" does not exist at character 40
      2010-11-03 13:50:17 GMT STATEMENT: SELECT "user", "host", "password" FROM passwd WHERE passwd."host" = 'localhost'

      Thank you

      Jon

        Activity

        Hide
        Badlop
        added a comment -

        Thanks, Mike. I've added your tweak with a function that detects at runtime the database type used.

        Show
        Badlop
        added a comment - Thanks, Mike. I've added your tweak with a function that detects at runtime the database type used.
        Hide
        Mike Kazantsev
        added a comment -

        Bumped into the same issue with latest git version.

        "PRIMARY KEY (some_fileld(int))" syntax is mysql-specific and postgresql does not support this.
        Also, postgresql has no limit on key size (and TEXT field size, for that matter), so I suppose these "(105)" things can be added just for mysql.

        Following patch fixes the problem for postgresql users, while possibly (see http://bugs.mysql.com/bug.php?id=4541) breaking things for mysql users. I'm a total stranger to erlang and can't come up with a better patch (with check for db type) within a reasonable time.

        diff --git a/src/gen_storage_odbc.erl b/src/gen_storage_odbc.erl
        index 9fd56b4..dcb0e8d 100644
        — a/gen_storage_odbc.erl
        +++ b/gen_storage_odbc.erl
        @@ -167,7 +167,7 @@ create_table(#tabdef{name = Tab,
        %% last_activity key (text, text) with this error:
        %% #42000Specified key was too long; max key length is 1000bytes"
        %% Similarly for rosteritem and other tables, maybe also PgSQL.

        • set -> [", PRIMARY KEY (", string:join(K, "(105), "), "(105))"];
          + set -> [", PRIMARY KEY (", string:join(K, ", "), ")"];
          bag -> []
          end,
          case odbc_command(Host,

          1.7.4.1
        Show
        Mike Kazantsev
        added a comment - Bumped into the same issue with latest git version. "PRIMARY KEY (some_fileld(int))" syntax is mysql-specific and postgresql does not support this. Also, postgresql has no limit on key size (and TEXT field size, for that matter), so I suppose these "(105)" things can be added just for mysql. Following patch fixes the problem for postgresql users, while possibly (see http://bugs.mysql.com/bug.php?id=4541 ) breaking things for mysql users. I'm a total stranger to erlang and can't come up with a better patch (with check for db type) within a reasonable time. diff --git a/src/gen_storage_odbc.erl b/src/gen_storage_odbc.erl index 9fd56b4..dcb0e8d 100644 — a/gen_storage_odbc.erl +++ b/gen_storage_odbc.erl @@ -167,7 +167,7 @@ create_table(#tabdef{name = Tab, %% last_activity key (text, text) with this error: %% #42000Specified key was too long; max key length is 1000bytes" %% Similarly for rosteritem and other tables, maybe also PgSQL. set -> [", PRIMARY KEY (", string:join(K, "(105), "), "(105))"] ; + set -> [", PRIMARY KEY (", string:join(K, ", "), ")"] ; bag -> [] end, case odbc_command(Host, – 1.7.4.1
        Hide
        Jon Doveston
        added a comment -

        Ok I've dropped all the tables from the schema and added all the privileges I can find for the ejabberd user. During this I saw another error in the postgresql log, the full error looks like this:

        2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection
        2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection
        2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection
        2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection
        2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection
        2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection
        2010-11-08 13:42:16 GMT ERROR: syntax error at or near "(" at character 84
        2010-11-08 13:42:16 GMT STATEMENT: CREATE TABLE passwd ("user" text, "host" text, "password" text, PRIMARY KEY ("user"(105), "host"(105)))
        2010-11-08 13:42:16 GMT ERROR: relation "passwd" does not exist at character 40
        2010-11-08 13:42:16 GMT STATEMENT: SELECT "user", "host", "password" FROM passwd WHERE passwd."host" = 'localhost'

        not sure about the eof error, but the "(" error could be causing a problem. I'll investigate if there is a more detailed log level in postgres.

        Jon

        Show
        Jon Doveston
        added a comment - Ok I've dropped all the tables from the schema and added all the privileges I can find for the ejabberd user. During this I saw another error in the postgresql log, the full error looks like this: 2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection 2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection 2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection 2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection 2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection 2010-11-08 13:38:18 GMT LOG: unexpected EOF on client connection 2010-11-08 13:42:16 GMT ERROR: syntax error at or near "(" at character 84 2010-11-08 13:42:16 GMT STATEMENT: CREATE TABLE passwd ("user" text, "host" text, "password" text, PRIMARY KEY ("user"(105), "host"(105))) 2010-11-08 13:42:16 GMT ERROR: relation "passwd" does not exist at character 40 2010-11-08 13:42:16 GMT STATEMENT: SELECT "user", "host", "password" FROM passwd WHERE passwd."host" = 'localhost' not sure about the eof error, but the "(" error could be causing a problem. I'll investigate if there is a more detailed log level in postgres. Jon
        Hide
        Badlop
        added a comment -

        ejabberd master takes care to create the tables, the administrator only needs to care about creating the database and granting privileges.

        The file pg.sql is no longer required, but it's still provided as a reference/documentation.

        I see that the ejabberd Guide mentions:

        Also the file pg.sql in the directory src/odbc might be interesting for you. This file contains the ejabberd schema for PostgreSQL. At the end of the file you can find information to update your database schema.

        That sentences should be rewritten to indicate clearly that the purpose of the file is only informative, and that the tables are created automatically by ejabberd when required.

        Show
        Badlop
        added a comment - ejabberd master takes care to create the tables, the administrator only needs to care about creating the database and granting privileges. The file pg.sql is no longer required, but it's still provided as a reference/documentation. I see that the ejabberd Guide mentions: Also the file pg.sql in the directory src/odbc might be interesting for you. This file contains the ejabberd schema for PostgreSQL. At the end of the file you can find information to update your database schema. That sentences should be rewritten to indicate clearly that the purpose of the file is only informative, and that the tables are created automatically by ejabberd when required.
        Hide
        Jon Doveston
        added a comment -

        I got the pgsql process-one fork and used it. Same problem. I wanted to make the minimum changes to ejabberd.cfg to get a working server so my only changes are switching the auth to

        {auth_method, storage}

        .

        {auth_storage, odbc}

        .

        and adding the

        {odbc_server, {pgsql, "localhost", "ejabberd", "ejabberd", "password"}}

        I assume the odbc settings are ok because a connection is being established. I should have mentioned before that the pg.sql schema file has the remnants of a diff/merge in it. At least the one a worked with before. I had to tidy it up before running the schema script. The error mentioned previously is also reported in the ejabberd.log as

        =INFO REPORT==== 2010-11-08 10:46:12 ===
        application: ejabberd
        exited: {bad_return,
        {{ejabberd_app,start,[normal,[]]},
        {'EXIT',
        [

        {severity,'ERROR'}

        ,

        {code,"42P01"}

        ,

        {message,"relation \"passwd\" does not exist"}

        ,

        {position,40}

        ,

        {file,"parse_relation.c"}

        ,

        {line,885}

        ,

        {routine,"parserOpenTable"}

        ]}}}
        type: temporary

        Jon Doveston

        Show
        Jon Doveston
        added a comment - I got the pgsql process-one fork and used it. Same problem. I wanted to make the minimum changes to ejabberd.cfg to get a working server so my only changes are switching the auth to {auth_method, storage} . {auth_storage, odbc} . and adding the {odbc_server, {pgsql, "localhost", "ejabberd", "ejabberd", "password"}} I assume the odbc settings are ok because a connection is being established. I should have mentioned before that the pg.sql schema file has the remnants of a diff/merge in it. At least the one a worked with before. I had to tidy it up before running the schema script. The error mentioned previously is also reported in the ejabberd.log as =INFO REPORT==== 2010-11-08 10:46:12 === application: ejabberd exited: {bad_return, {{ejabberd_app,start,[normal,[]]}, {'EXIT', [ {severity,'ERROR'} , {code,"42P01"} , {message,"relation \"passwd\" does not exist"} , {position,40} , {file,"parse_relation.c"} , {line,885} , {routine,"parserOpenTable"} ]}}} type: temporary Jon Doveston
        Hide
        Badlop
        added a comment -

        using the postgresql erlang drivers from here http://frihjul.net/pgsql)

        For ejabberd, use this driver:
        https://forge.process-one.net/browse/ejabberd-modules/pgsql/trunk/src

        when I tried to switch to a postgres backend

        If it still fails with that same error, show here the changes you make in ejabberd.cfg for using postgres.

        Show
        Badlop
        added a comment - using the postgresql erlang drivers from here http://frihjul.net/pgsql ) For ejabberd, use this driver: https://forge.process-one.net/browse/ejabberd-modules/pgsql/trunk/src when I tried to switch to a postgres backend If it still fails with that same error, show here the changes you make in ejabberd.cfg for using postgres.

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:
              Days since last comment:
              2 years, 33 weeks, 3 days ago

              Issue deployment