g2.2: Fatal database error

web-app

Gemini 2.2:
How can this be fixed? This happens on new issue, or new component adds. Possibly elsewhere. It was not happening before. We have not used the software in several weeks.
The only think I can think of is that the database schema was corrupted during a move or restore.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

        <b> Exception Details: </b>System.Data.SqlClient.SqlException:

Cannot insert the value NULL into column 'compid', table 'cpg1.dbo.components'; column does not allow nulls. INSERT fails.
The statement has been terminated.

[SqlException (0x80131904): Cannot insert the value NULL into column 'compid', table 'cpg1.dbo.components'; column does not allow nulls. INSERT fails.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +9
   NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd) +32


   NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlCommandInfo sql, Object obj, ISessionImplementor session) +387

PaulS · 1
Thursday, March 27, 2008, 5:57:32 PM

Replies (8)

Can you please check the components table and see if the compid column is marked as identity?

It seems like you have moved the tables but did not move the exact structure etc...

Saar Cohen · 5000
Thursday, March 27, 2008, 8:40:03 PM

It's not an identity. This has burned me before in sql server. I remember being able to transfer more of the schema in an import operation using an earlier version, but now I don't see this option in the import/export and I forget that it doesn't copy everything.

How can I retain the rows in each table but repair the schema?

Can I setup an empty database with the correct schema then do an import from the damaged database?

PaulS · 1
Thursday, March 27, 2008, 10:00:59 PM

Yes, you can. But you will have to enable identity insert option.

How did you transfer the tables?

Saar Cohen · 5000
Thursday, March 27, 2008, 10:08:31 PM

I did the move to a new shared hosting a couple of months ago. I created a new database, then did either an import or export using Sql Server Management Studio. Import/export are about the same, the destination and source are just reversed. I don't know if I have the original undamaged DB I am still looking.

Is there a schema repair script? What do you suggest?

I have several hundred issue rows at risk I don't want to lose. I'm all backed up now, and can try some tests.

PaulS · 1
Thursday, March 27, 2008, 10:28:41 PM

I suggest creating a new database from the scripts provided with Gemini, truncate all the new tables and import the data from your current database making sure you enable identity insert.

Otherwise,

Saar Cohen · 5000
Thursday, March 27, 2008, 11:23:15 PM

I suggest creating a new database from the scripts provided with Gemini, truncate all the new tables and import the data from your current database making sure you enable identity insert.

Otherwise, you can try setting all id columns to identity.

Saar Cohen · 5000
Thursday, March 27, 2008, 11:23:18 PM

I tried starting with a fresh 2.2 database from the install. I've tried it with and without deleting all rows.
I have been able to do a complete import wizard import with no errors. But the resulting database is messed up, user access is missing, the issues don't show up. It's seriously broken. I don't think this procedure works.

The broken database works properly enough to view all the issues, this is why the error didn't show up for weeks.

I'm not sure about editing the id columns. There are a lot of relationships aren't there? I didn't see any primary keys in the install clean version, just indexes and indentity columns. Anyway, it's a lot of tables to edit.

PaulS · 1
Friday, March 28, 2008, 1:04:20 AM

I had to revert to an older database that had the schema. There was no way I could go through every table, index, default value etc and set it. I lost a few of the last issue rows, but those were already fixed anyway.

PaulS · 1
Friday, March 28, 2008, 2:26:57 PM

Gemini Community Support Site

g2.2: Fatal database error