1 Reply Latest reply on Dec 4, 2007 8:53 AM by shevelin

    Problems with data migration from Forums 5.5.8 to Clearspace 1.8.0

      Hi, guys,


      I've found a problem with data migration from current forums (version 5.5.8) to latest clearspacex standalone unix distribution using Postgres 8 as a data storage.

      It seems, in my particular case something goes wrong with creation of SQL insert statements. The value of 'name' field (that I believe should be a string literal) isn't quoted, so whole SQL statement isn't valid.


      Could you please give me any suggestions how to handle this problem?



      WARNING: Could not import user

      java.sql.BatchUpdateException: Batch entry 14 INSERT INTO jiveUser (userID, username, name, nameVisible, email, emailVisible, userEnabled, creationDate, modificationDate, passwordHash) VALUES (24, someemail@some.server, Some Really Long Name, 1, someemail@some.server, 0, 1, 114838733053, 1331468202437, 0fa76954b0626a8edf1daa) was aborted.  Call getNextException to see the cause.

           at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2534)

           at org.postgresql.core.v3.QueryExecutorImpl$ErrorTrackingResultHandler.handleError(QueryExecutorImpl.java:284)

           at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)

           at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:333)

           at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2596)

           at com.jivesoftware.community.importer.tasks.UserImportTask.importUsersViaJdbc(UserImportTask.java:175)

           at com.jivesoftware.community.importer.tasks.UserImportTask.importUsers(UserImportTask.java:91)

           at com.jivesoftware.community.importer.tasks.UserImportTask.run(UserImportTask.java:77)

           at com.jivesoftware.community.importer.BaseImporter.run(BaseImporter.java:127)

           at com.jivesoftware.util.task.TaskEngine$TaskEngineWorker.run(TaskEngine.java:392)


        • Re: Problems with data migration from Forums 5.5.8 to Clearspace 1.8.0

          Ok, I've found a solution.

          The main reason why INSERT statement fails is difference in 'jiveUser' table definition, the 'username' and 'passwordhash' fields length is different in Jive Forums and in ClearspaceX databases.


          I've patched jiveUser table definition in /database/jive_clearspacex_postgres.sql to have this fixed:


          CREATE TABLE jiveUser (

              userID            BIGINT NOT NULL,

              username          VARCHAR(150) UNIQUE NOT NULL,

              passwordHash      VARCHAR(35) NOT NULL,

              name              VARCHAR(100),

              nameVisible       INTEGER NOT NULL,

              email             VARCHAR(100) NOT NULL,

              emailVisible      INTEGER NOT NULL,

              userEnabled       INTEGER NOT NULL,

              creationDate      BIGINT NOT NULL,

              modificationDate  BIGINT NOT NULL,

              lastLoggedIn      BIGINT NOT NULL DEFAULT 0,

              CONSTRAINT jiveUser_pk PRIMARY KEY (userID)




          P.S. It's a bit confusing to see unquoted string values in logged INSERT statements, especially when you're suggested to call getNextException instead of having exact error description