datetime problems

[csillag]

Hi all,

I have a "datetime" field MySQL table (called PERIOD).
I am using SmartGWT EE's builtin SQL backend.
The DS handling the table has set autoDeriveSchema=true,
so it automatically creates a DATETIME field in the DS.
I can read out the value of this field from record using
Record.getAttributeAsDate(), and it returns a valid java.util.Date object
with the proper content.

When I assign a Date to this field (by passing it in to ListGrid.startEditingNew as a default value), the date is displayed properly.
When I try to save the record, the validation succeeds, but the SQL server throws an MysqlDataTruncation exception.

The generated SQL command is this:

INSERT INTO fundamental_data_records (DATA_ID, VALUE, PERIOD) VALUES ('167', '34', 'Wed Apr 01 00:00:00 GMT+01:00 2009')

The exception is this:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Wed Apr 01 00:00:00 GMT+01:00 2009' for column 'PERIOD' at row 1

What am I doing wrong?

* * *

If I create an SQL dump from this table, the values of the existing fields are returned in the following format: "yyyy-mm-dd hh:mm:ss" (this is not a syntactically correct date format string, just an indication of the format), which happens to be the correct textaul representation of dates for my locale.

Could this be a locale problem? (MySQL sends and expects dates in the local locale, but the SmartGWT server sends the date in some standard format, which the MySQL server refuses?)

* * *

By the way, I do not really need the time part of the field, only the date is important, so I can transfer to a time-less date type (at any level) is necessary.

Do you have any advice?
Thank you for your help!

[csillag]

The generated SQL command is this:

INSERT INTO fundamental_data_records (DATA_ID, VALUE, PERIOD) VALUES ('167', '34', 'Wed Apr 01 00:00:00 GMT+01:00 2009')

[...]

Could this be a locale problem? (MySQL sends and expects dates in the local locale, but the SmartGWT server sends the date in some standard format, which the MySQL server refuses?)

No, this is not a locales problem. (Or at least not in tha way I mentioned.)
According to the specs, MySQL allways expects DateTime in the
YYYY-MM-DD HH:MM:SS format.

But then, why is the SQL backend of SmartGWT-EE sending it like shown in the generated SQL command?

[csillag]

Any idea on how to proceed with this?

[Isomorphic]

If you want a field that expresses a date without a type, make the field type "date" not "datetime".

The problem you're having suggests that the value has been converted to a String before the SQL engine ever sees it. First check that the field in the DataSource has a type of either "date" or "datetime". If that part is correct, this could be either a client or server-side problem in your code - check the RPC tab to see whether the value for the date field is already a String in the format you're seeing before it reaches the server.

[csillag]

If you want a field that expresses a date without a type, make the field type "date" not "datetime".

OK, but I might need "datetime" in other cases, so...

The problem you're having suggests that the value has been converted to a String before the SQL engine ever sees it. First check that the field in the DataSource has a type of either "date" or "datetime".

It is "datetime".

If that part is correct, this could be either a client or server-side problem in your code

At this point, I have no server code touching the saved data.

At the client, I set the data by assigning a java.util.Date object to the default value of the field when calling startEditingNew.

[QUOTE] [...] check the RPC tab to see whether the value for the date field is already a String in the format you're seeing before it reaches the server.

This is what I captured:

{
actionURL:"http://localhost:8888/szolorg/sc/IDACall",
showPrompt:false,
transport:"xmlHttpRequest",
promptStyle:"dialog",
bypassCache:true,
data:{
values:{
DATA_ID:"168",
PERIOD:new Date(1199142000000),
VALUE:4
},
operationConfig:{
dataSource:"fundamental_data_records_auto",
repo:null,
operationType:"add"
},
componentId:"isc_OID_356",
appID:"builtinApplication",
operation:"fundamental_data_records_auto_add",
oldValues:{
DATA_ID:"168",
PERIOD:new Date(1199142000000),
VALUE:4
}
}
}

As far as I can tell, the data is not yet converted to string at this point. (And I do not think I touch it from this point on.)

[Isomorphic]

Can you show your DataSource definition and also the server-side logs for processing this request?

[csillag]

Can you show your DataSource definition and also the server-side logs for processing this request?

DS:

(Please note that besides the startard stuff, the DS definitions contains several additional attributes, which are processed by my client-side components to configure various things.)

<?xml version="1.0" encoding="UTF-8"?>
<DataSource ID="fundamental_data_records_auto" autoDeriveSchema="true" pluralTitle="Alapadatok" serverType="sql" sgweetEnhancements="true" tableName="fundamental_data_records" useParentFieldOrder="false" validateRelatedRecords="true">
    <fields>
        <field hidden="true" name="FUNDAMENTAL_DATA_RECORD_ID" title="Szekvencia"/>
        <field foreignKey="fundamental_data_definitions_auto.DATA_ID" method="join" name="DATA_ID" relationTarget="fundamental_data_definitions" sgweetEnhancements="true" targetFields="DATA_NAME" targetName="DATA_ID" title="Alapadt"/>
        <field canEdit="false" customSQL="true" name="DATA_ID_DATA_NAME" tableName="DATA_ID" title="Alapadt" type="text"/>
        <field name="PERIOD" title="Periódusidő"/>
        <field name="VALUE" title="Érték"/>
        <field name="COMMENT" title="Megjegyzés"/>
    </fields>
    <operationBindings>
        <operationBinding customCriteriaFields="" customValueFields="" operationType="fetch">
            <selectClause>DATA_ID.DATA_NAME as DATA_ID_DATA_NAME, $defaultSelectClause</selectClause>
            <tableClause>fundamental_data_records, fundamental_data_definitions DATA_ID</tableClause>
            <whereClause>fundamental_data_records.DATA_ID = DATA_ID.DATA_ID AND ($defaultWhereClause)</whereClause>
        </operationBinding>
    </operationBindings>
</DataSource>

Server logs:

=== 2010-01-28 15:49:28,108 [l0-5] INFO  IDACall - Performing 1 operation(s)
=== 2010-01-28 15:49:28,108 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-01-28 15:49:28,108 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add] No public zero-argument method named '_fundamental_data_records_auto_add' found, performing generic datasource operation
=== 2010-01-28 15:49:28,109 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-01-28 15:49:28,109 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2010-01-28 15:49:28,109 [l0-5] INFO  SQLDataSource - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] Performing fetch operation with
	criteria: {DATA_ID:"168"}	values: {DATA_ID:"168"}
=== 2010-01-28 15:49:28,110 [l0-5] WARN  SQLDataSource - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] derived query: SELECT MEASURE_UNIT_ID.MEASURE_UNIT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_NAME, MEASURE_UNIT_ID.MEASURE_UNIT_SHORT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_SHORT_NAME, DISPLAY_TYPE_ID.DISPLAY_TYPE_NAME as DISPLAY_TYPE_ID_DISPLAY_TYPE_NAME, DATA_SAVER_GROUP_ID.GROUP_NAME as DATA_SAVER_GROUP_ID_GROUP_NAME, DATA_READER_GROUP_ID.GROUP_NAME as DATA_READER_GROUP_ID_GROUP_NAME, DATA_INFORM_GROUP_ID.GROUP_NAME as DATA_INFORM_GROUP_ID_GROUP_NAME, TIME_FREQUENCY_ID.TIME_FREQUENCY_NAME as TIME_FREQUENCY_ID_TIME_FREQUENCY_NAME, TIME_FREQUENCY_ID.PERIOD_LENGTH as TIME_FREQUENCY_ID_PERIOD_LENGTH, $defaultSelectClause FROM app_groups DATA_SAVER_GROUP_ID, fundamental_data_definitions, app_groups DATA_INFORM_GROUP_ID, def_time_frequencies TIME_FREQUENCY_ID, def_display_types DISPLAY_TYPE_ID, def_measure_units MEASURE_UNIT_ID, app_groups DATA_READER_GROUP_ID WHERE fundamental_data_definitions.MEASURE_UNIT_ID = MEASURE_UNIT_ID.MEASURE_UNIT_ID AND fundamental_data_definitions.DISPLAY_TYPE_ID = DISPLAY_TYPE_ID.DISPLAY_TYPE_ID AND fundamental_data_definitions.DATA_SAVER_GROUP_ID = DATA_SAVER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_READER_GROUP_ID = DATA_READER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_INFORM_GROUP_ID = DATA_INFORM_GROUP_ID.GROUP_ID AND fundamental_data_definitions.TIME_FREQUENCY_ID = TIME_FREQUENCY_ID.TIME_FREQUENCY_ID AND ($defaultWhereClause)
=== 2010-01-28 15:49:28,119 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] Returning pooled Connection
=== 2010-01-28 15:49:28,119 [l0-5] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] Executing SQL query on 'SzolorgDB': SELECT MEASURE_UNIT_ID.MEASURE_UNIT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_NAME, MEASURE_UNIT_ID.MEASURE_UNIT_SHORT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_SHORT_NAME, DISPLAY_TYPE_ID.DISPLAY_TYPE_NAME as DISPLAY_TYPE_ID_DISPLAY_TYPE_NAME, DATA_SAVER_GROUP_ID.GROUP_NAME as DATA_SAVER_GROUP_ID_GROUP_NAME, DATA_READER_GROUP_ID.GROUP_NAME as DATA_READER_GROUP_ID_GROUP_NAME, DATA_INFORM_GROUP_ID.GROUP_NAME as DATA_INFORM_GROUP_ID_GROUP_NAME, TIME_FREQUENCY_ID.TIME_FREQUENCY_NAME as TIME_FREQUENCY_ID_TIME_FREQUENCY_NAME, TIME_FREQUENCY_ID.PERIOD_LENGTH as TIME_FREQUENCY_ID_PERIOD_LENGTH, fundamental_data_definitions.TIME_FREQUENCY_ID, fundamental_data_definitions.DATA_INFORM_GROUP_ID, fundamental_data_definitions.FLAG_SEND_MSG_AFTER_DUE_TIME, fundamental_data_definitions.DATA_NAME, fundamental_data_definitions.DESCRIPTION, fundamental_data_definitions.DUE_TIME_TIME_INTERVAL, fundamental_data_definitions.DATA_READER_GROUP_ID, fundamental_data_definitions.NEXT_SAVE_DATE, fundamental_data_definitions.DISPLAY_TYPE_ID, fundamental_data_definitions.DATA_SAVER_GROUP_ID, fundamental_data_definitions.DATA_ID, fundamental_data_definitions.MEASURE_UNIT_ID, fundamental_data_definitions.FLAG_SEND_MSG_BEFORE_DUE_TIME, fundamental_data_definitions.LAST_SAVE_DATE, fundamental_data_definitions.LOCAL_GLOBAL FROM app_groups DATA_SAVER_GROUP_ID, fundamental_data_definitions, app_groups DATA_INFORM_GROUP_ID, def_time_frequencies TIME_FREQUENCY_ID, def_display_types DISPLAY_TYPE_ID, def_measure_units MEASURE_UNIT_ID, app_groups DATA_READER_GROUP_ID WHERE fundamental_data_definitions.MEASURE_UNIT_ID = MEASURE_UNIT_ID.MEASURE_UNIT_ID AND fundamental_data_definitions.DISPLAY_TYPE_ID = DISPLAY_TYPE_ID.DISPLAY_TYPE_ID AND fundamental_data_definitions.DATA_SAVER_GROUP_ID = DATA_SAVER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_READER_GROUP_ID = DATA_READER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_INFORM_GROUP_ID = DATA_INFORM_GROUP_ID.GROUP_ID AND fundamental_data_definitions.TIME_FREQUENCY_ID = TIME_FREQUENCY_ID.TIME_FREQUENCY_ID AND ((fundamental_data_definitions.DATA_ID='168'))
=== 2010-01-28 15:49:28,120 [l0-5] INFO  DSResponse - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] DSResponse: List with 1 items
=== 2010-01-28 15:49:28,121 [l0-5] INFO  SQLDataSource - [builtinApplication.fundamental_data_records_auto_add] Performing add operation with
	criteria: {}	values: {DATA_ID:"168",PERIOD:"Tue Jan 01 00:00:00 GMT+01:00 2008",VALUE:"4"}
=== 2010-01-28 15:49:28,121 [l0-5] DEBUG SQLValuesClause - [builtinApplication.fundamental_data_records_auto_add] Sequences: {FUNDAMENTAL_DATA_RECORD_ID=__default}
=== 2010-01-28 15:49:28,125 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_add] Returning pooled Connection
=== 2010-01-28 15:49:28,126 [l0-5] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_add] Executing SQL update on 'SzolorgDB': INSERT INTO fundamental_data_records (DATA_ID, VALUE, PERIOD) VALUES ('168', '4', 'Tue Jan 01 00:00:00 GMT+01:00 2008')
=== 2010-01-28 15:49:28,126 [l0-5] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_add] Execute of update: INSERT INTO fundamental_data_records (DATA_ID, VALUE, PERIOD) VALUES ('168', '4', 'Tue Jan 01 00:00:00 GMT+01:00 2008') on db: SzolorgDB threw exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Tue Jan 01 00:00:00 GMT+01:00 2008' for column 'PERIOD' at row 1 - assuming stale connection and retrying update.
=== 2010-01-28 15:49:28,130 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_add] Returning pooled Connection
=== 2010-01-28 15:49:28,131 [l0-5] WARN  RequestContext - dsRequest.execute() failed: 
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Tue Jan 01 00:00:00 GMT+01:00 2008' for column 'PERIOD' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3556)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
	at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:439)
	at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:411)
	at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:495)
	at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:278)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1104)
	at com.isomorphic.sql.SQLDataSource.execute(SQLDataSource.java:226)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:728)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:665)
	at com.isomorphic.application.AppBase.execute(AppBase.java:498)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1219)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:155)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:106)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:54)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
=== 2010-01-28 15:49:28,131 [l0-5] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2010-01-28 15:49:28,132 [l0-5] DEBUG RPCManager - non-DMI response, dropExtraFields: false

[Isomorphic]

You didn't post the whole log - please start with the very first message logged as part of the HTTP request where this problem occurs.

[csillag]

You didn't post the whole log - please start with the very first message logged as part of the HTTP request where this problem occurs.

Sorry, I did not notice.

Here it is, from the start:

=== 2010-01-28 15:49:28,104 [l0-5] INFO  RequestContext - URL: '/szolorg/sc/IDACall', User-Agent: 'Mozilla/5.0 (X11; U; Linux i686 (x86_64); hu; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7': Moz (Gecko) with Accept-Encoding header
=== 2010-01-28 15:49:28,106 [l0-5] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2010-01-28 15:49:28,107 [l0-5] DEBUG RPCManager - Processing 1 requests.
=== 2010-01-28 15:49:28,108 [l0-5] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    values:{
        DATA_ID:"168",
        PERIOD:new Date(1199142000000),
        VALUE:4
    },
    operationConfig:{dataSource:"fundamental_data_records_auto", operationType:"add"},
    componentId:"isc_OID_356",
    appID:"builtinApplication",
    operation:"fundamental_data_records_auto_add",
    oldValues:{
        DATA_ID:"168",
        PERIOD:new Date(1199142000000),
        VALUE:4
    },
    criteria:{}
}
=== 2010-01-28 15:49:28,108 [l0-5] INFO  IDACall - Performing 1 operation(s)
=== 2010-01-28 15:49:28,108 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-01-28 15:49:28,108 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add] No public zero-argument method named '_fundamental_data_records_auto_add' found, performing generic datasource operation
=== 2010-01-28 15:49:28,109 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-01-28 15:49:28,109 [l0-5] DEBUG AppBase - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
=== 2010-01-28 15:49:28,109 [l0-5] INFO  SQLDataSource - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] Performing fetch operation with
	criteria: {DATA_ID:"168"}	values: {DATA_ID:"168"}
=== 2010-01-28 15:49:28,110 [l0-5] WARN  SQLDataSource - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] derived query: SELECT MEASURE_UNIT_ID.MEASURE_UNIT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_NAME, MEASURE_UNIT_ID.MEASURE_UNIT_SHORT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_SHORT_NAME, DISPLAY_TYPE_ID.DISPLAY_TYPE_NAME as DISPLAY_TYPE_ID_DISPLAY_TYPE_NAME, DATA_SAVER_GROUP_ID.GROUP_NAME as DATA_SAVER_GROUP_ID_GROUP_NAME, DATA_READER_GROUP_ID.GROUP_NAME as DATA_READER_GROUP_ID_GROUP_NAME, DATA_INFORM_GROUP_ID.GROUP_NAME as DATA_INFORM_GROUP_ID_GROUP_NAME, TIME_FREQUENCY_ID.TIME_FREQUENCY_NAME as TIME_FREQUENCY_ID_TIME_FREQUENCY_NAME, TIME_FREQUENCY_ID.PERIOD_LENGTH as TIME_FREQUENCY_ID_PERIOD_LENGTH, $defaultSelectClause FROM app_groups DATA_SAVER_GROUP_ID, fundamental_data_definitions, app_groups DATA_INFORM_GROUP_ID, def_time_frequencies TIME_FREQUENCY_ID, def_display_types DISPLAY_TYPE_ID, def_measure_units MEASURE_UNIT_ID, app_groups DATA_READER_GROUP_ID WHERE fundamental_data_definitions.MEASURE_UNIT_ID = MEASURE_UNIT_ID.MEASURE_UNIT_ID AND fundamental_data_definitions.DISPLAY_TYPE_ID = DISPLAY_TYPE_ID.DISPLAY_TYPE_ID AND fundamental_data_definitions.DATA_SAVER_GROUP_ID = DATA_SAVER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_READER_GROUP_ID = DATA_READER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_INFORM_GROUP_ID = DATA_INFORM_GROUP_ID.GROUP_ID AND fundamental_data_definitions.TIME_FREQUENCY_ID = TIME_FREQUENCY_ID.TIME_FREQUENCY_ID AND ($defaultWhereClause)
=== 2010-01-28 15:49:28,119 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] Returning pooled Connection
=== 2010-01-28 15:49:28,119 [l0-5] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] Executing SQL query on 'SzolorgDB': SELECT MEASURE_UNIT_ID.MEASURE_UNIT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_NAME, MEASURE_UNIT_ID.MEASURE_UNIT_SHORT_NAME as MEASURE_UNIT_ID_MEASURE_UNIT_SHORT_NAME, DISPLAY_TYPE_ID.DISPLAY_TYPE_NAME as DISPLAY_TYPE_ID_DISPLAY_TYPE_NAME, DATA_SAVER_GROUP_ID.GROUP_NAME as DATA_SAVER_GROUP_ID_GROUP_NAME, DATA_READER_GROUP_ID.GROUP_NAME as DATA_READER_GROUP_ID_GROUP_NAME, DATA_INFORM_GROUP_ID.GROUP_NAME as DATA_INFORM_GROUP_ID_GROUP_NAME, TIME_FREQUENCY_ID.TIME_FREQUENCY_NAME as TIME_FREQUENCY_ID_TIME_FREQUENCY_NAME, TIME_FREQUENCY_ID.PERIOD_LENGTH as TIME_FREQUENCY_ID_PERIOD_LENGTH, fundamental_data_definitions.TIME_FREQUENCY_ID, fundamental_data_definitions.DATA_INFORM_GROUP_ID, fundamental_data_definitions.FLAG_SEND_MSG_AFTER_DUE_TIME, fundamental_data_definitions.DATA_NAME, fundamental_data_definitions.DESCRIPTION, fundamental_data_definitions.DUE_TIME_TIME_INTERVAL, fundamental_data_definitions.DATA_READER_GROUP_ID, fundamental_data_definitions.NEXT_SAVE_DATE, fundamental_data_definitions.DISPLAY_TYPE_ID, fundamental_data_definitions.DATA_SAVER_GROUP_ID, fundamental_data_definitions.DATA_ID, fundamental_data_definitions.MEASURE_UNIT_ID, fundamental_data_definitions.FLAG_SEND_MSG_BEFORE_DUE_TIME, fundamental_data_definitions.LAST_SAVE_DATE, fundamental_data_definitions.LOCAL_GLOBAL FROM app_groups DATA_SAVER_GROUP_ID, fundamental_data_definitions, app_groups DATA_INFORM_GROUP_ID, def_time_frequencies TIME_FREQUENCY_ID, def_display_types DISPLAY_TYPE_ID, def_measure_units MEASURE_UNIT_ID, app_groups DATA_READER_GROUP_ID WHERE fundamental_data_definitions.MEASURE_UNIT_ID = MEASURE_UNIT_ID.MEASURE_UNIT_ID AND fundamental_data_definitions.DISPLAY_TYPE_ID = DISPLAY_TYPE_ID.DISPLAY_TYPE_ID AND fundamental_data_definitions.DATA_SAVER_GROUP_ID = DATA_SAVER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_READER_GROUP_ID = DATA_READER_GROUP_ID.GROUP_ID AND fundamental_data_definitions.DATA_INFORM_GROUP_ID = DATA_INFORM_GROUP_ID.GROUP_ID AND fundamental_data_definitions.TIME_FREQUENCY_ID = TIME_FREQUENCY_ID.TIME_FREQUENCY_ID AND ((fundamental_data_definitions.DATA_ID='168'))
=== 2010-01-28 15:49:28,120 [l0-5] INFO  DSResponse - [builtinApplication.fundamental_data_records_auto_add, builtinApplication.null] DSResponse: List with 1 items
=== 2010-01-28 15:49:28,121 [l0-5] INFO  SQLDataSource - [builtinApplication.fundamental_data_records_auto_add] Performing add operation with
	criteria: {}	values: {DATA_ID:"168",PERIOD:"Tue Jan 01 00:00:00 GMT+01:00 2008",VALUE:"4"}
=== 2010-01-28 15:49:28,121 [l0-5] DEBUG SQLValuesClause - [builtinApplication.fundamental_data_records_auto_add] Sequences: {FUNDAMENTAL_DATA_RECORD_ID=__default}
=== 2010-01-28 15:49:28,125 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_add] Returning pooled Connection
=== 2010-01-28 15:49:28,126 [l0-5] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_add] Executing SQL update on 'SzolorgDB': INSERT INTO fundamental_data_records (DATA_ID, VALUE, PERIOD) VALUES ('168', '4', 'Tue Jan 01 00:00:00 GMT+01:00 2008')
=== 2010-01-28 15:49:28,126 [l0-5] INFO  SQLDriver - [builtinApplication.fundamental_data_records_auto_add] Execute of update: INSERT INTO fundamental_data_records (DATA_ID, VALUE, PERIOD) VALUES ('168', '4', 'Tue Jan 01 00:00:00 GMT+01:00 2008') on db: SzolorgDB threw exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Tue Jan 01 00:00:00 GMT+01:00 2008' for column 'PERIOD' at row 1 - assuming stale connection and retrying update.
=== 2010-01-28 15:49:28,130 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.fundamental_data_records_auto_add] Returning pooled Connection
=== 2010-01-28 15:49:28,131 [l0-5] WARN  RequestContext - dsRequest.execute() failed: 
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Tue Jan 01 00:00:00 GMT+01:00 2008' for column 'PERIOD' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3556)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
	at com.isomorphic.sql.SQLDriver.doUpdate(SQLDriver.java:439)
	at com.isomorphic.sql.SQLDriver.update(SQLDriver.java:411)
	at com.isomorphic.sql.SQLDriver.executeUpdate(SQLDriver.java:495)
	at com.isomorphic.sql.SQLDataSource.executeNativeUpdate(SQLDataSource.java:278)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1104)
	at com.isomorphic.sql.SQLDataSource.execute(SQLDataSource.java:226)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:728)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:665)
	at com.isomorphic.application.AppBase.execute(AppBase.java:498)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1219)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:155)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:106)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:54)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
=== 2010-01-28 15:49:28,131 [l0-5] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2010-01-28 15:49:28,132 [l0-5] DEBUG RPCManager - non-DMI response, dropExtraFields: false

[Isomorphic]

Ahem, never mind about the logs, your DataSource does not declare a type for the field at all:

        <field name="PERIOD" title="Periódusidő"/>

[csillag]

Ahem, never mind about the logs, your DataSource does not declare a type for the field at all:

        <field name="PERIOD" title="Periódusidő"/>

The DS declaration was this:

<DataSource ID="fundamental_data_records_auto" autoDeriveSchema="true" pluralTitle="Alapadatok" serverType="sql" sgweetEnhancements="true" tableName="fundamental_data_records" useParentFieldOrder="false" validateRelatedRecords="true">

Does not

autoDeriveSchema="true"

take care of this?

Here is the SQL script used to create the table:

CREATE TABLE IF NOT EXISTS `fundamental_data_records` (
  `FUNDAMENTAL_DATA_RECORD_ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Szekvencia',
  `DATA_ID` int(10) unsigned NOT NULL COMMENT 'Alapadat azonosító',
  `PERIOD` datetime NOT NULL COMMENT 'Periódusidő',
  `VALUE` double NOT NULL COMMENT 'Érték',
  `COMMENT` varchar(256) DEFAULT NULL COMMENT 'Megjegyzés',
  PRIMARY KEY (`FUNDAMENTAL_DATA_RECORD_ID`),
  KEY `FKfundamental_data_records_data` (`DATA_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

[Isomorphic]

If you override the auto-derived schema, your definition of the field is expected to be complete (you can't omit type).

[csillag]

If you override the auto-derived schema, your definition of the field is expected to be complete (you can't omit type).

This is interesting, because
- On the client side, if I read out the type of the field, it's "datetime", so it is not completely lost.
- The documentation of DataSource.getAutoDeriveScheme says this:

Local fields (fields defined in this DataSource) are added to inherited fields to form the full set of fields. Fields with the same name are merged in the same way that databound component fields are merged with DataSource fields.

I thought this means that the field definitions are merged together. You have also confirmed this interpretation answering my question in http://forums.smartclient.com/showthread.php?t=9066

* * *

So, when overriding auto-derived field definitions (or inhereted field definitions, in general), exactly what can be omitted, and what has to be repeated?

[Isomorphic]

We'll record this as a possible bug, in the meantime, provide complete definitions.

[csillag]

We'll record this as a possible bug, in the meantime, provide complete definitions.

OK, repeating type in the definition solves the problem!
Thank you for your help.