Hi,
I have the same problem.

Environment: Debian Etch, JDK 1.6.0_07-b06, Apache Tomcat 6.0.18, MySQL Ver 14.12 Distrib 5.0.32 + My SQL connector 5.0.6

This exception ("can't create table") is thrown during:

• updating database
• browsing repositories from web UI

Example of such exception that occurs always during browsing any repository (instead of showing artifact info):

javax.servlet.ServletException: org.apache.maven.archiva.database.ArchivaDatabaseException: Error in JDO during get of Database object id [exist:exist:1.2.4] of type org.apache.maven.archiva.model.ArchivaProjectModel using no fetch-group
org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:515)
org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:419)
com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
org.apache.struts2.dispatcher.ActionContextCleanUp.doFilter(ActionContextCleanUp.java:99)

root cause

org.apache.maven.archiva.database.ArchivaDatabaseException: Error in JDO during get of Database object id [exist:exist:1.2.4] of type org.apache.maven.archiva.model.ArchivaProjectModel using no fetch-group
org.apache.maven.archiva.database.jdo.JdoAccess.getObjectById(JdoAccess.java:449)
org.apache.maven.archiva.database.jdo.JdoProjectModelDAO.getProjectModel(JdoProjectModelDAO.java:73)
org.apache.maven.archiva.database.browsing.DefaultRepositoryBrowsing.getProjectModel(DefaultRepositoryBrowsing.java:371)
org.apache.maven.archiva.database.browsing.DefaultRepositoryBrowsing.selectVersion(DefaultRepositoryBrowsing.java:154)
org.apache.maven.archiva.web.action.ShowArtifactAction.artifact(ShowArtifactAction.java:111)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:404)
com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:267)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:229)
com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:221)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)
com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)
com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)
com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:150)
org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:48)
.
.
.
root cause

javax.jdo.JDODataStoreException: Error(s) were found while auto-creating/validating the datastore for classes. The errors are printed in the log, and are attached to this exception.
NestedThrowables:
java.sql.SQLException: Can't create table './archiva/#sql-d58_482.frm' (errno: 150)
java.sql.SQLException: Can't create table './archiva/#sql-d58_482.frm' (errno: 150)
java.sql.SQLException: Can't create table './archiva/#sql-d58_482.frm' (errno: 150)
org.jpox.store.rdbms.RDBMSManager$ClassAdder.verifyErrors(RDBMSManager.java:3285)
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3335)
org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2567)
org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2419)
org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:824)
org.jpox.store.rdbms.RDBMSManager.addClass(RDBMSManager.java:838)
org.jpox.AbstractPersistenceManager.newObjectIdInstance(AbstractPersistenceManager.java:2393)
org.apache.maven.archiva.database.jdo.JdoAccess.getObjectById(JdoAccess.java:426)
org.apache.maven.archiva.database.jdo.JdoProjectModelDAO.getProjectModel(JdoProjectModelDAO.java:73)
org.apache.maven.archiva.database.browsing.DefaultRepositoryBrowsing.getProjectModel(DefaultRepositoryBrowsing.java:371)
org.apache.maven.archiva.database.browsing.DefaultRepositoryBrowsing.selectVersion(DefaultRepositoryBrowsing.java:154)
org.apache.maven.archiva.web.action.ShowArtifactAction.artifact(ShowArtifactAction.java:111)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:404)
com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:267)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:229)
com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:221)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:224)
com.opensymphony.xwork2.DefaultActionInvocation$2.doProfiling(DefaultActionInvocation.java:223)
com.opensymphony.xwork2.util.profiling.UtilTimerStack.profile(UtilTimerStack.java:455)
com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:221)
com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:150)
org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:48)
com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:86)
.
.
.

root cause

java.sql.SQLException: Can't create table './archiva/#sql-d58_482.frm' (errno: 150)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
com.mysql.jdbc.Connection.execSQL(Connection.java:3178)
com.mysql.jdbc.Statement.execute(Statement.java:711)
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
org.jpox.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:614)
org.jpox.store.rdbms.table.TableImpl.validateForeignKeys(TableImpl.java:463)
org.jpox.store.rdbms.table.TableImpl.validateConstraints(TableImpl.java:388)
org.jpox.store.rdbms.table.ClassTable.validateConstraints(ClassTable.java:2875)
org.jpox.store.rdbms.RDBMSManager$ClassAdder.performTablesValidation(RDBMSManager.java:3123)
org.jpox.store.rdbms.RDBMSManager$ClassAdder.addClassTablesAndValidate(RDBMSManager.java:3326)
org.jpox.store.rdbms.RDBMSManager$ClassAdder.run(RDBMSManager.java:2567)
org.jpox.store.rdbms.RDBMSManager$MgmtTransaction.execute(RDBMSManager.java:2419)
org.jpox.store.rdbms.RDBMSManager.addClasses(RDBMSManager.java:824)
org.jpox.store.rdbms.RDBMSManager.addClass(RDBMSManager.java:838)
org.jpox.AbstractPersistenceManager.newObjectIdInstance(AbstractPersistenceManager.java:2393)
org.apache.maven.archiva.database.jdo.JdoAccess.getObjectById(JdoAccess.java:426)
org.apache.maven.archiva.database.jdo.JdoProjectModelDAO.getProjectModel(JdoProjectModelDAO.java:73)
org.apache.maven.archiva.database.browsing.DefaultRepositoryBrowsing.getProjectModel(DefaultRepositoryBrowsing.java:371)
org.apache.maven.archiva.database.browsing.DefaultRepositoryBrowsing.selectVersion(DefaultRepositoryBrowsing.java:154)
org.apache.maven.archiva.web.action.ShowArtifactAction.artifact(ShowArtifactAction.java:111)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:404)

Also another exception is recorded in archiva.log file during repository scanning:

2009-03-30 13:44:52,698 [http-8080-5] INFO org.apache.maven.archiva.web.action.admin.SchedulerAction - [ActionMessage] Your request to have repository [kedros-releases] be indexed has been queued.
2009-03-30 13:44:52,712 [pool-2-thread-1] INFO org.apache.maven.archiva.scheduled.executors.ArchivaRepositoryScanningTaskExecutor - Executing task from queue with job name: repository-job:kedros-releases
2009-03-30 13:44:52,757 [pool-2-thread-1] INFO org.apache.maven.archiva.repository.scanner.RepositoryScannerInstance - Walk Started: [kedros-releases] /opt/repositories/kedros-releases
2009-03-30 13:44:52,765 [pool-2-thread-1] ERROR org.apache.maven.archiva.repository.scanner.functors.ConsumerProcessFileClosure - Consumer [repository-purge] had an error when processing file [/opt/repositories/kedros-releases/.index/nexus-maven-repository-index.zip]: Not enough parts to the path [.index/nexus-maven-repository-index.zip] to construct an ArchivaArtifact from. (Requires at least 4 parts)
org.apache.maven.archiva.consumers.ConsumerException: Not enough parts to the path [.index/nexus-maven-repository-index.zip] to construct an ArchivaArtifact from. (Requires at least 4 parts)
at org.apache.maven.archiva.consumers.core.repository.RepositoryPurgeConsumer.processFile(RepositoryPurgeConsumer.java:174)
at org.apache.maven.archiva.repository.scanner.functors.ConsumerProcessFileClosure.execute(ConsumerProcessFileClosure.java:50)
at org.apache.commons.collections.functors.IfClosure.execute(IfClosure.java:117)
at org.apache.commons.collections.CollectionUtils.forAllDo(CollectionUtils.java:388)
at org.apache.maven.archiva.repository.scanner.RepositoryScannerInstance.directoryWalkStep(RepositoryScannerInstance.java:137)
at org.codehaus.plexus.util.DirectoryWalker.fireStep(DirectoryWalker.java:174)
at org.codehaus.plexus.util.DirectoryWalker.scanDir(DirectoryWalker.java:392)
at org.codehaus.plexus.util.DirectoryWalker.scanDir(DirectoryWalker.java:386)
at org.codehaus.plexus.util.DirectoryWalker.scan(DirectoryWalker.java:345)
at org.apache.maven.archiva.repository.scanner.DefaultRepositoryScanner.scan(DefaultRepositoryScanner.java:117)
at org.apache.maven.archiva.repository.scanner.DefaultRepositoryScanner.scan(DefaultRepositoryScanner.java:61)
at org.apache.maven.archiva.scheduled.executors.ArchivaRepositoryScanningTaskExecutor.executeTask(ArchivaRepositoryScanningTaskExecutor.java:139)
at org.codehaus.plexus.taskqueue.execution.ThreadedTaskQueueExecutor$ExecutorRunnable$1.run(ThreadedTaskQueueExecutor.java:116)
at edu.emory.mathcs.backport.java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:442)
at edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java:176)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:987)
at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:528)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.apache.maven.archiva.consumers.core.repository.RepositoryPurgeException: Not enough parts to the path [.index/nexus-maven-repository-index.zip] to construct an ArchivaArtifact from. (Requires at least 4 parts)
at org.apache.maven.archiva.consumers.core.repository.RetentionCountRepositoryPurge.process(RetentionCountRepositoryPurge.java:99)
at org.apache.maven.archiva.consumers.core.repository.RepositoryPurgeConsumer.processFile(RepositoryPurgeConsumer.java:170)
... 17 more
Caused by: org.apache.maven.archiva.repository.layout.LayoutException: Not enough parts to the path [.index/nexus-maven-repository-index.zip] to construct an ArchivaArtifact from. (Requires at least 4 parts)
at org.apache.maven.archiva.repository.content.DefaultPathParser.toArtifactReference(DefaultPathParser.java:65)
at org.apache.maven.archiva.repository.content.AbstractDefaultRepositoryContent.toArtifactReference(AbstractDefaultRepositoryContent.java:54)
at org.apache.maven.archiva.repository.content.ManagedDefaultRepositoryContent.toArtifactReference(ManagedDefaultRepositoryContent.java:340)
at org.apache.maven.archiva.consumers.core.repository.RetentionCountRepositoryPurge.process(RetentionCountRepositoryPurge.java:65)
... 18 more
2009-03-30 13:44:53,385 [pool-2-thread-1] INFO org.apache.maven.archiva.repository.scanner.RepositoryScannerInstance - Walk Finished: [kedros-releases] /opt/repositories/kedros-releases
2009-03-30 13:44:53,388 [pool-2-thread-1] INFO org.apache.maven.archiva.scheduled.executors.ArchivaRepositoryScanningTaskExecutor - Finished repository task:
.\ Scan of kedros-releases \.__________________________________________
Repository Dir : /opt/repositories/kedros-releases
Repository Name : Kedros Internal Releases Repository
Repository Layout : default
Known Consumers : (7 configured)
auto-remove
auto-rename
metadata-updater
repository-purge
update-db-artifact
create-missing-checksums
index-content
Invalid Consumers : <none>
Duration : 629 Milliseconds
When Gathered : 3/30/09 1:44 PM
Total File Count : 910
Avg Time Per File :
_____________________________________________________________

I'm getting similar errors.

The InnoDB engine status shows:

090331  0:42:19 Error in foreign key constraint of table archiva_archiva/#sql-7a54_b851:                                                       
 FOREIGN KEY (`CI_MANAGEMENT_ARCHIVA_CIMANAGEMENT_ID_OID`) REFERENCES `ARCHIVA_CIMANAGEMENT` (`ARCHIVA_CIMANAGEMENT_ID`):                      
Cannot find an index in the referenced table where the                                                                                         
referenced columns appear as the first columns, or column types                                                                                
in the table and the referenced table do not match for constraint.                                                                             
Note that the internal storage type of ENUM and SET changed in                                                                                 
tables created with >= InnoDB-4.1.12, and such columns in old tables                                                                           
cannot be referenced by such columns in new tables.                                                                                            
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html                                                                 
for correct foreign key definition.

This was caused by the changes in MRM-836..

You would need to re-create the database because of the changes in MRM-836..

The primary keys for the following tables were changed to a different auto generated id field, which caused the errors above. The old primary keys that were replaced by the new id field in the patch for MRM-836 actually contains auto-generated ids (unique sequential numbers but in String format) that's why in the case of MRM-836, the value displayed for the organization is a "number" instead of the actual organization name set in the pom.

• ARCHIVA_CIMANAGEMENT (old primary key was the 'url' field)
• ARCHIVA_ORGANIZATION (old primary key was the 'name' field)
• ARCHIVA_ISSUE_MANAGEMENT (old primary key was the 'url' field)
• ARCHIVA_INDIVIDUALS (old primary key was the 'email' field)

The old data now had 'null' primary keys and also the FK constraints were broken because of the change.

If it is really not an option to re-create the database, the other solution I could think of is either to create a tool for migrating the data OR revert the above tables to their old primary keys then add another column to contain the unsaved values (e.g. the ciManagement & issueManagement urls, the organization name and the individual email). The second option is easier to do and the user doesn't have to do anything, the only drawback is that we have to retain the column names of the old primary keys (url, name and email) which is a bit misleading because their values are just sequential numbers in String format.

Thoughts or comments anyone?

It would be good to capture this information either

• in the user guide ( known issues upgrading 1.1.3 to 1.2.x )
• or in the log file alongside the exception
• or in the web interface for the admin user

For me dropping the database was an easy fix, although not an ideal one.

Agreed. I'll put this in the known issues in the upgrade docs for 1.2.1 to get this documented somewhere..
Thanks for the suggestion Robert!

Fixed in trunk -r778118 with the second option I posted above..

We had been using archiva 1.2 without much problems(except for NPE by apache lucene when an artifact is uploaded manually) in our organization. Today I upgraded to version 1.2.1 and tried uploading an artifact. The NPE was gone and we were glad but then we started getting errors related to ARCHIVA_CIMANAGEMENT, ARCHIVA_ORGANIZATION, and ARCHIVA_ISSUE_MANAGEMENT. I will atach the complete exception trace here.

We had been using archiva 1.2 without much problems(except for NPE by apache lucene when an artifact is uploaded manually) in our organization. Today I upgraded to version 1.2.1 and tried uploading an artifact. The NPE was gone and we were glad but then we started getting errors related to ARCHIVA_CIMANAGEMENT, ARCHIVA_ORGANIZATION, and ARCHIVA_ISSUE_MANAGEMENT. I have attached a file archiva_error.txt with complete stack trace.

Our update was successful with 1.2.1.