BUG: Audit fails with sequence

[Macomi]

(My version: v9.1p_2014-07-09/Enterprise Deployment (built 2014-07-31))

There is an error when using an audit data source for my data source. I followed the showcase example.

Here is my datasource:

<DataSource  
    ID="s3ninputresourcepools"  
    serverType="sql"  
    tableName="Resourcepool" tableForChangeColumns="Resourcepool"
    audit="true"  
    autoCreateAuditTable="true">
    
    <fields>
    	<field name="ScenariosPool_ScenarioID" type="integer" hidden="true" />
    	<field name="ResourcePoolID" type="sequence" primaryKey="true" hidden="true" autoGenerated ="true" />
        
    	<field name="ResourceID" type="integer" displayField="ResourceName" optionDataSource="s3ninput_resources" title="Resource" />
		<field name="FacilityID" type="integer" displayField="FacilityName" optionDataSource="s3ninput_facility" title="Facility" />
		
    	<field name="Quantity" type="double" />
        <field name="QuantityUnitID" type="integer" valueField="UnitID" displayField="UnitName" optionDataSource="s3ninput_units" title="Unit" />
		
		<field name="DayType" type="text" title="Day type">
    		<valueMap>
    			<value id="Workdays">Work days</value>
    			<value id="Weekdays">Week days</value>
    			<value id="Holidays">Holidays</value>
    			<value id="Weekends">Weekends</value>
    			<value id="All">All</value>
    		</valueMap>
    	</field>
    	<field name="StartTime" type="time" title="Start time" />
		<field name="EndTime" type="time" title="End time" />
    </fields>
</DataSource>

Here is the generated SQL:

CREATE TABLE audit_s3ninputresourcepools 
(ScenariosPool_ScenarioID integer default null, 
ResourcePoolID integer auto_increment, 
ResourceID integer default null, FacilityID integer default null, Quantity double default 0.0, QuantityUnitID integer default null, DayType varchar(255), StartTime time, EndTime time, 
audit_revision integer auto_increment not null, 
audit_operationType varchar(255), audit_changeTime datetime, audit_modifier varchar(255), CONSTRAINT audit_s3ninputresourcepools_UI 
PRIMARY KEY (audit_revision))ENGINE=InnoDB on

Which, of course, throws the error:

Incorrect table definition; there can be only one auto column and it must be defined as a key

I am aware that, as a work around, I can:
1) create the table manually, or
2) set auditRevisionFieldName=""

But... I would be best to have the revision field and to not have to create the table manually.

Anyway, this is a bug originated from the 'sequence'/auto generated field on the datasource, right?
ResourcePoolID is of type sequence and auto generated in my datasource, so your generated SQL for the audit table states: ResourcePoolID integer auto_increment
The audit_revision field on the new audit table needs to auto increments, so your generated SQL for the audit table states: audit_revision integer auto_increment not null

On the audit table, ResourcePoolID does not need to be auto_increment... your generated SQL should drop the 'auto_increment' from the CREATE statement, right?

Thank you.

[Isomorphic]

Thanks for the notification. We'll take a look and follow up with you.

Regards
Isomorphic Software

[Macomi]

Thank you, and good luck with it ;)

[Macomi]

Any news on this?

[Isomorphic]

Hello,
We've fixed this bug - please pick up the next nightly, 9.1 or 10.0 build (dated Sep 20 or later) to get this fix.

Regards
Isomorphic Software