Data conversion error?
HK
The following error occurred.
org.h2.jdbc.JdbcSQLException: データ変換中にエラーが発生しました "1970-01-01"
Data conversion error converting "1970-01-01"; SQL statement:
select
SMS_SID
from
SML_SMEIS
where
SMS_JKBN = 9 [90021-128]
at org.h2.message.Message.getSQLException(Message.java:110)
at org.h2.value.Value.convertTo(Value.java:784)
at org.h2.expression.Comparison.getValue(Comparison.java:248)
at org.h2.expression.Expression.getBooleanValue(Expression.java:
188)
at org.h2.command.dml.Select.queryFlat(Select.java:493)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:558)
at org.h2.command.dml.Query.query(Query.java:243)
at org.h2.command.CommandContainer.query(CommandContainer.java:81)
at org.h2.command.Command.executeQuery(Command.java:132)
at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:
98)
at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:
92)
SMS_SID, SMS_JKBN is Integer.
How to convert date type error why?
Schema is created in the following statement.
create table SML_SMEIS
(
USR_SID integer not null,
SMS_SID integer not null,
SMS_SDATE timestamp,
SMS_TITLE varchar(50),
SMS_MARK integer,
SMS_BODY varchar(1000),
SMS_JKBN integer,
SMS_AUID integer not null,
SMS_ADATE timestamp not null,
SMS_EUID integer not null,
SMS_EDATE timestamp not null,
primary key (SMS_SID)
) ;
Version 1.1 * DB is created.
Currently being converted. (Extension ". h2.db")
JAVA: 1.6.0_18
Thanks
--
HK
Thomas Mueller
I'm not sure what it could be, but it doesn't look like SMS_JKBN is a
integer column. Could you run the following statements in the H2
Console and post the results?
show columns from SML_SMEIS;
select top 10 * from SML_SMEIS;
Regards,
Thomas
HK
1) show columns from SML_SMEIS;
USR_SID INTEGER(10) NO NULL
SMS_SID INTEGER(10) NO PRI NULL
SMS_SDATE TIMESTAMP(23) YES NULL
SMS_TITLE VARCHAR(50) YES NULL
SMS_MARK INTEGER(10) YES NULL
SMS_BODY VARCHAR(1000) YES NULL
SMS_JKBN INTEGER(10) YES NULL
SMS_AUID INTEGER(10) NO NULL
SMS_ADATE TIMESTAMP(23) NO NULL
SMS_EUID INTEGER(10) NO NULL
SMS_EDATE TIMESTAMP(23) NO NULL
2) select top 10 * from SML_SMEIS;
Contents of the data is not published
Displayed without any problems.
3) select SMS_SID,SMS_JKBN from SML_SMEIS where SMS_JKBN is not null
Be described in part
SMS_SID SMS_JKBN
27150 0
27151 0
28057 0
28058 0
28059 1970-01-01
Data does, "1970-01-01" that contains.
Only one line
SMS_TITLE, SMS_BODY including non-ASCII.
Version1.1 from Version1.2 Do not convert to the cause?
"SML_SMEIS" table will have 20,594 lines.
Connect Options:
LOCK_MODE=3
LOCK_TIMEOUT=5000
DEFAULT_LOCK_TIMEOUT=5000
MULTI_THREADED=0
IFEXISTS=TRUE
AUTOCOMMIT=OFF
DB_CLOSE_ON_EXIT=FALSE
CACHE_SIZE=32768
CACHE_TYPE=SOFT_LRU
MVCC=TRUE
PAGE_STORE=TRUE
Thanks
--
HK
Thomas Mueller
That's very strange. It looks like the database is corrupted in a way
I have (so far) never seen. I have some questions:
- Did you use multiple connections?
- A workarounds is: use the tool org.h2.tools.Recover to create
the SQL script file, and then re-create the database using this script.
Does it work when you do this?
- With which version of H2 was this database created?
You can find it out using:
select * from information_schema.settings where name='CREATE_BUILD'
or have a look in the SQL script created by the recover tool.
- Did the application run out of memory (once, or multiple times)?
- Do you use any H2-specific system properties?
- Is the application multi-threaded?
- What operating system, file system, and virtual machine
(java -version) do you use?
- How did you start the Java process (java -Xmx... and so on)?
- To you use temporary tables?
- Is it (or was it at some point) a networked file system?
- How big is the database (file sizes)?
- How much heap memory does the Java process have?
- Is the database usually closed normally, or is process terminated
forcefully or the computer switched off?
- Is it possible to reproduce this problem using a fresh database
(sometimes, or always)?
- Are there any other exceptions (maybe in the .trace.db file)?
Could you send them please?
- Do you still have any .trace.db files, and if yes could you send them?
- Could you send the .h2.db file where this exception occurs?
Regards,
Thomas
HK
yes.
> A workarounds is: use the tool org.h2.tools.Recover to create
> the SQL script file,
That worked.
> and then re-create the database using this script.
> Does it work when you do this?
org.h2.tools.RunScript occurs same error.
Exception in thread "main" org.h2.jdbc.JdbcSQLException: データ変換中にエラーが
発生しました "'1970-01-01' (SMS_JKBN INTEGER)"
Data conversion error converting "'1970-01-01' (SMS_JKBN INTEGER)";
SQL statement:
> - With which version of H2 was this database created?
> You can find it out using:
> select * from information_schema.settings where name='CREATE_BUILD'
> or have a look in the SQL script created by the recover tool.
Current value is 128.
Value before conversion is 66.
> - Did the application run out of memory (once, or multiple times)?
Occurred several times.
> - Do you use any H2-specific system properties?
select * from INFORMATION_SCHEMA.SETTINGS
NAME VALUE
CREATE_BUILD 128
CLUSTER ''
LOCK_MODE 3
DEFAULT_TABLE_TYPE 0
WRITE_DELAY 500
DEFAULT_LOCK_TIMEOUT 5000
CACHE_SIZE 235929
MAX_MEMORY_UNDO 5000
info.BUILD_ID 129
info.VERSION_MAJOR 1
info.VERSION_MINOR 2
info.VERSION 1.2.129 (2010-02-19)
property.java.runtime.version 1.6.0_18-b07
property.java.vm.name Java HotSpot(TM) Client VM
property.java.vendor Sun Microsystems Inc.
property.os.name Linux
property.os.arch i386
property.os.version 2.6.26-2-686
property.sun.os.patch.level unknown
property.file.separator /
property.path.separator :
property.line.separator
property.user.country JP
property.user.language ja
property.user.variant
property.file.encoding UTF-8
CLUSTER ''
EXCLUSIVE FALSE
MODE REGULAR
MULTI_THREADED 0
MVCC TRUE
QUERY_TIMEOUT 0
h2.allowBigDecimalExtensions false
h2.baseDir null
h2.check true
h2.check2 false
h2.clientTraceDirectory trace.db/
h2.collatorCacheSize 32000
h2.defaultMaxMemoryUndo 50000
h2.lobFilesPerDirectory 256
h2.logAllErrors false
h2.logAllErrorsFile h2errors.txt
h2.maxFileRetry 16
h2.maxQueryTimeout 0
h2.lobCloseBetweenReads false
h2.objectCache true
h2.objectCacheSize 1024
h2.objectCacheMaxPerElementSize 4096
h2.optimizeInList true
h2.optimizeSubqueryCache true
h2.recompileAlways false
h2.redoBufferSize 262144
h2.runFinalize true
h2.scriptDirectory
h2.serverCachedObjects 64
h2.serverResultSetFetchSize 100
h2.sortNullsHigh false
info.FILE_WRITE_TOTAL 1028713
info.FILE_WRITE 13756
info.FILE_READ 33139
info.CACHE_MAX_SIZE 60397824
info.CACHE_SIZE 59140096
> - Is the application multi-threaded?
Yes.
> - What operating system, file system, and virtual machine
(java -version) do you use?
Current Version:
java version "1.6.0_18"
Java(TM) SE Runtime Environment (build 1.6.0_18-b07)
Java HotSpot(TM) Client VM (build 16.0-b13, mixed mode, sharing)
Before Version:
java version "1.6.0_0"
OpenJDK Runtime Environment (build 1.6.0_0-b11)
OpenJDK Client VM (build 1.6.0_0-b11, mixed mode, sharing)
> - How did you start the Java process (java -Xmx... and so on)?
> - How much heap memory does the Java process have?
-Xmx768m -Xms256M
> - To you use temporary tables?
No.
> - Is it (or was it at some point) a networked file system?
No.
> - How big is the database (file sizes)?
110MB.
> - Is the database usually closed normally, or is process terminated
> forcefully or the computer switched off?
JVM that was three times dead.
Are usually successful.
> - Are there any other exceptions (maybe in the .trace.db file)?
> Could you send them please?
> - Do you still have any .trace.db files, and if yes could you send them?
> - Could you send the .h2.db file where this exception occurs?
I do not want the public in this group.
It's also because it contains information that is not public.
I processed the data with minimal data is sent to the person that you
can do.
Regards,
HK