Uploaded image for project: '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

        Expenses

          Activity

          Hide
          badlop Badlop added a comment -

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

          Show
          badlop Badlop added a comment - Thanks, Mike. I've added your tweak with a function that detects at runtime the database type used.
          Hide
          mk.fg 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
          mk.fg 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
          jondoveston 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
          jondoveston 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 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 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
          jondoveston 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
          jondoveston 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 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 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:

                Development