After reading that post:
http://www.centriccrm.com/ProjectManagementIssues.do?command=Details&pid=24&iid=2803&cid=38&resetList=true

I decided to try bin/import-db.sh to take mysql data and bin/testBackup.sh to upload it to postgres
import-db.sh went successfully and I have xml backup file.

For uploading it to postgres I proceeded as recommended:
1. Grab Centric CRM v4.1 source from the SVN repository
2. Compile the source and deploy using ant deploy
3. Time to create a shell database. Run ant install.database
4. Time to initialize the object map used by the restore process. Run ant install.syncapi
5. Time to run the script bin/testBackup.bsh. Make sure you edit the db connection info. The script will take the XML backup file as input and call the restore process.

testBackup.sh starts to execute but fails during admin.base.RolePermission insert with the following error:

TransactionItem-> org.aspcfs.modules.admin.base.RolePermission insert
TransactionItem-> org.aspcfs.modules.admin.base.RolePermission insert
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.aspcfs.modules.service.base.TransactionItem.doExecute(Unknown Source)
at org.aspcfs.modules.service.base.TransactionItem.execute(Unknown Source)
at org.aspcfs.modules.service.base.Transaction.execute(Unknown Source)
at org.aspcfs.apps.transfer.writer.cfsdatabasewriter.CFSXMLDatabaseWriter.save(Unknown Source)
at org.aspcfs.apps.transfer.reader.CFSXMLReader.execute(Unknown Source)
at org.aspcfs.modules.service.utils.BackupUtils.restore(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at bsh.Reflect.invokeOnMethod(Unknown Source)
at bsh.Reflect.invokeStaticMethod(Unknown Source)
at bsh.Name.invokeMethod(Unknown Source)
at bsh.BSHMethodInvocation.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.Interpreter.eval(Unknown Source)
at bsh.Interpreter.source(Unknown Source)
at bsh.Interpreter.main(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "attempt" violates not-null constraint
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:346)
at org.aspcfs.modules.base.NotificationMessage.insert(Unknown Source)
... 23 more
Sep 14, 2007 5:21:54 PM org.aspcfs.apps.transfer.writer.cfsdatabasewriter.CFSXMLDatabaseWriter save
INFO: ERROR: Transaction failed

I found time to continue experiments with migration.
With help of sed I changed NULL to timestamps where required and now Im stack on testBackup procedure at the following:

TransactionItem-> org.aspcfs.utils.web.CustomLookupElement insert
TransactionItem-> org.aspcfs.utils.web.CustomLookupElement insert
TransactionItem-> org.aspcfs.utils.web.CustomLookupElement insert
java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedMethodAccessor16.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.aspcfs.modules.service.base.TransactionItem.doExecute(Unknown Source)
at org.aspcfs.modules.service.base.TransactionItem.execute(Unknown Source)
at org.aspcfs.modules.service.base.Transaction.execute(Unknown Source)
at org.aspcfs.apps.transfer.writer.cfsdatabasewriter.CFSXMLDatabaseWriter.save(Unknown Source)
at org.aspcfs.apps.transfer.reader.CFSXMLReader.execute(Unknown Source)
at org.aspcfs.modules.service.utils.BackupUtils.restore(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at bsh.Reflect.invokeOnMethod(Unknown Source)
at bsh.Reflect.invokeStaticMethod(Unknown Source)
at bsh.Name.invokeMethod(Unknown Source)
at bsh.BSHMethodInvocation.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.Interpreter.eval(Unknown Source)
at bsh.Interpreter.source(Unknown Source)
at bsh.Interpreter.main(Unknown Source)
Caused by: org.postgresql.util.PSQLException: No value specified for parameter 8.
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:134)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:179)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:346)
at org.aspcfs.utils.web.CustomLookupElement.insert(Unknown Source)
... 22 more
Nov 2, 2007 3:49:00 PM org.aspcfs.apps.transfer.writer.cfsdatabasewriter.CFSXMLDatabaseWriter save
INFO: ERROR: Transaction failed

I will be happy if somebody knows which parameter 8 should be for that insertion.

same problem here

dunno if this will help, but looks like the error is generated during the import of the table "lookup_lists_lookup"...

EDIT
I made also some other tests on this topic, and I've found this:

initiated MySQL DB -> imported successfully on PostgreSQL
initiated MySQL DB -> imported successfully on MySQL
production MySQL DB -> fails to be imported on PostgreSQL (as stated above)
production MySQL DB -> fails to be imported on MySQL (with the same error as above)

(for initiated DB I mean a DB that had just been initialized with ant installdb)

Ideas?

Claudio Tranchina wrote:
same problem here

dunno if this will help, but looks like the error is generated during the import of the table "lookup_lists_lookup"...

EDIT
I made also some other tests on this topic, and I've found this:

initiated MySQL DB -> imported successfully on PostgreSQL
initiated MySQL DB -> imported successfully on MySQL
production MySQL DB -> fails to be imported on PostgreSQL (as stated above)
production MySQL DB -> fails to be imported on MySQL (with the same error as above)

(for initiated DB I mean a DB that had just been initialized with ant installdb)

...
TransactionItem-> org.aspcfs.utils.web.CustomLookupElement insert
TransactionItem-> org.aspcfs.utils.web.CustomLookupElement insert
TransactionItem-> org.aspcfs.utils.web.CustomLookupElement insert
java.lang.reflect.InvocationTargetException
at sun.reflect.GeneratedMethodAccessor16.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.aspcfs.modules.service.base.TransactionItem.doExecute(Unknown Source)
at org.aspcfs.modules.service.base.TransactionItem.execute(Unknown Source)
at org.aspcfs.modules.service.base.Transaction.execute(Unknown Source)
at org.aspcfs.apps.transfer.writer.cfsdatabasewriter.CFSXMLDatabaseWriter.save(Unknown Source)
at org.aspcfs.apps.transfer.reader.CFSXMLReader.execute(Unknown Source)
at org.aspcfs.modules.service.utils.BackupUtils.restore(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at bsh.Reflect.invokeOnMethod(Unknown Source)
at bsh.Reflect.invokeStaticMethod(Unknown Source)
at bsh.Name.invokeMethod(Unknown Source)
at bsh.BSHMethodInvocation.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.BSHPrimaryExpression.eval(Unknown Source)
at bsh.Interpreter.eval(Unknown Source)
at bsh.Interpreter.source(Unknown Source)
at bsh.Interpreter.main(Unknown Source)
Caused by: java.sql.SQLException: No value specified for parameter 8
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:1699)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:1647)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:791)
at org.aspcfs.utils.web.CustomLookupElement.insert(Unknown Source)
... 22 more
11-nov-2007 19.32.48 org.aspcfs.apps.transfer.writer.cfsdatabasewriter.CFSXMLDatabaseWriter save
INFO: ERROR: Transaction failed

Ideas?

I removed from database-backup.xml entries like AssignmentNote,
with presence of them even import PostgreSQL - restore PostgreSQL
was not working, and completely imported DB to new centric instance,
but Im not sure in data consistency, because usernames have another
contact informations after restore. E.g. all administrative accounts
are now Account Managers and they using wrong contact data, like
First Name, Last Name, etc.

Im not sure that testBackup.sh can be easily used for such type of
migrations.

Also I tried bin/restore-db.sh, but it looks like this tool is not for
recovery, it completes successfully with [java] INFO: Records: 9034
but there is no changes in the DB.

Completely stacked with DB migration...

Maybe it is easier to find direct mysql - postgresql convertor, or
anything else, dont know yet...

IMO, the tests we've built until now demonstrates that the backup-and-restore procedures is working with a clean install, but the restore procedure will become unusable by the data contained in the environment when has been used: the data is the culprit of this failure, not the DB.
Looks like the restore part needs an in-dept analysis...
Who will ever need a restore procedure that won't restore anything but a clean environment?
Another good point will be a tool for performing some data consistency and sanity check so in the case of a incomplete restore (like Alexander 's case) or in the case of a suspect DB corruption, the operator can have a clue about the status of the whole system.

I got a lot of similar error just by getting the SQL structure for all primary index, without an 'autoincrement' option just migrating from MySQL -> MySQL.

I guess the object-data binding layer has some problem with insert/update and those 'non null' field that get inserted/updated with NULL value.

Perhaps you could try to isolate one of the table that get such error (you'll find one pretty immediatly after the logon as the access_log table get updated after the login-check) and if you get such 'non default' or 'non valid' field from the database, you could try to change the structure of the table to see if its pass. If so its just a DDL problem to solve.

Hope this will help.

Finally, few weeks ago I converted DB from MySQL to PostgreSQL by doing the following steps (Maybe somebody will find it helpful):

1. Compiling new centric instance for postgres db with ant deploy
2. ant install.database (database schema)
3, Taking MySQL dump without schema
mysqldump --compatible=postgresql -t > mysql.dump
4. fixing mysql.dump in accordance with postgresql tables and trying to insert
5. after successful converting of dump, delete postgres DB, create new and execute 2nd step and insert dump cleanly.
6. fix sequences, something like:

1. !/bin/bash

BASE_FILE=table_full.sql
DB=centric_crm

grep ^INSERT $BASE_FILE |awk -F "\"" '{print $2}'|while read TABLE;
do
SEQ=`psql -c "\d $TABLE" $DB|grep "nextval"|awk -F \' '{print $2}'`
KEY=`psql -c "\d $TABLE" $DB|grep "nextval"|awk -F "|" '{print $1}'`
if [ -n $SEQ ]; then
psql -c "SELECT setval('$SEQ', max($KEY)) FROM $TABLE;" $DB

1. echo $SEQ $KEY

7. start centric and try to login and perform operations.

The time consuming part is step 4 but there is a clear logic and it is good chance to get known to centric DB structure.