Index and indexing in Elasticsearch — 3 min
Overview
In Elasticsearch, an index (plural: indices) contains a schema and can have one or more shards and replicas. An Elasticsearch index is divided into shards and each shard is an instance of a Lucene index.
Indices are used to store the documents in dedicated data structures corresponding to the data type of fields. For example, text fields are stored inside an inverted index whereas numeric and geo fields are stored inside BKD trees.
Examples
Create index
The following example is based on Elasticsearch version 5.x onwards. An index with two shards, each having one replica will be created with the name test_index1
PUT /test_index1?pretty
{
"settings" : {
"number_of_shards" : 2,
"number_of_replicas" : 1
},
"mappings" : {
"properties" : {
"tags" : { "type" : "keyword" },
"updated_at" : { "type" : "date" }
}
}
}
List indices
All the index names and their basic information can be retrieved using the following command:
GET _cat/indices?v
Index a document
Let’s add a document in the index with the command below:
PUT test_index1/_doc/1
{
"tags": [
"opster",
"elasticsearch"
],
"date": "01-01-2020"
}
Query an index
GET test_index1/_search
{
"query": {
"match_all": {}
}
}
Query multiple indices
It is possible to search multiple indices with a single request. If it is a raw HTTP request, index names should be sent in comma-separated format, as shown in the example below, and in the case of a query via a programming language client such as python or Java, index names are to be sent in a list format.
GET test_index1,test_index2/_search
Delete indices
DELETE test_index1
Common problems
- It is good practice to define the settings and mapping of an Index wherever possible because if this is not done, Elasticsearch tries to automatically guess the data type of fields at the time of indexing. This automatic process may have disadvantages, such as mapping conflicts, duplicate data and incorrect data types being set in the index. If the fields are not known in advance, it’s better to use dynamic index templates.
- Elasticsearch supports wildcard patterns in Index names, which sometimes aids with querying multiple indices, but can also be very destructive too. For example, It is possible to delete all the indices in a single command using the following commands:
DELETE /*
To disable this, you can add the following lines in the elasticsearch.yml:
action.destructive_requires_name: true
And before it’s lost to the sands of time, the KB article:
Article ID: 151693 — Last Review: October 3, 2003 — Revision: 3.0
FIX: Err Msg «Unknown Token Received from SQL Server»
Symptoms
When you set the statement options to use a server-side cursor and prepare a select statement on a SQL Server system table, the first execution of the select creates the cursor successfully. After you close this cursor, if you execute the prepared statement again, the following error message appears:
unknown token received from SQL Server.
Workaround
Use a forward-only cursor instead of a static, keyset, or dynamic cursor on the system tables. Note that the problem does not occur if a server-side cursor is created on a user-defined table or view.
If a forward-only cursor is unacceptable, prepare the select statement again on the system table and execute it to create a server-side cursor. Note that the first execution works fine and applications typically do not need to create a cursor on a system table repeatedly.
Status
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.50.0201. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.
OK there is an update, after trying to convert hibernate hql query to sql query using JDBC directly, the following exception occurs which is pretty similar to the previous one specially in unexpected token concern
17:45:02,384 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) org.hibernate.exception.DataException: could not execute statement
17:45:02,385 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:135)
17:45:02,385 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
17:45:02,386 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
17:45:02,387 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
17:45:02,388 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136)
17:45:02,389 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58)
17:45:02,390 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3067)
17:45:02,391 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3509)
17:45:02,391 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
17:45:02,392 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:364)
17:45:02,393 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:356)
17:45:02,394 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:277)
17:45:02,395 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:328)
17:45:02,396 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
17:45:02,397 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1234)
17:45:02,397 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,398 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,399 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,400 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,401 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,402 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,402 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
17:45:02,403 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
17:45:02,404 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
17:45:02,405 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
17:45:02,406 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
17:45:02,406 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
17:45:02,407 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329)
17:45:02,408 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,409 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,410 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,411 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,412 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,412 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,413 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,414 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,415 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,416 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,417 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:101)
17:45:02,417 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,418 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,419 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)
17:45:02,420 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
17:45:02,421 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153)
17:45:02,422 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155)
17:45:02,423 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
17:45:02,424 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
17:45:02,424 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368)
17:45:02,425 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)
17:45:02,426 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671)
17:45:02,426 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930)
17:45:02,427 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at java.lang.Thread.run(Unknown Source)
17:45:02,428 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while reading the value from the stream object. Error: "java.io.IOException: The stream is closed."
17:45:02,429 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSWriter.error(IOBuffer.java:3960)
17:45:02,430 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSWriter.writeStream(IOBuffer.java:3755)
17:45:02,430 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSWriter.writeRPCInputStream(IOBuffer.java:5994)
17:45:02,431 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.DTV$SendByRPCOp.execute(dtv.java:1421)
17:45:02,432 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.DTV.executeOp(dtv.java:1847)
17:45:02,432 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.DTV.sendByRPC(dtv.java:1896)
17:45:02,433 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.Parameter.sendByRPC(Parameter.java:1157)
17:45:02,433 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.sendParamsByRPC(SQLServerPreparedStatement.java:527)
17:45:02,434 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(SQLServerPreparedStatement.java:843)
17:45:02,435 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:463)
17:45:02,436 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
17:45:02,437 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
17:45:02,437 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
17:45:02,438 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
17:45:02,439 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
17:45:02,439 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356)
17:45:02,440 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
17:45:02,441 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
17:45:02,441 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) ... 47 more
17:45:02,442 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) org.hibernate.exception.DataException: could not execute statement
17:45:02,442 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:135)
17:45:02,443 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
17:45:02,444 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
17:45:02,445 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
17:45:02,446 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:136)
17:45:02,446 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:58)
17:45:02,447 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3067)
17:45:02,448 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3509)
17:45:02,448 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88)
17:45:02,449 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:364)
17:45:02,450 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:356)
17:45:02,450 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:277)
17:45:02,451 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:328)
17:45:02,452 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52)
17:45:02,452 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1234)
17:45:02,453 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,453 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,454 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,455 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,455 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,456 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,457 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
17:45:02,458 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
17:45:02,458 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
17:45:02,459 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
17:45:02,460 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
17:45:02,461 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
17:45:02,462 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329)
17:45:02,463 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,464 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,465 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,466 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,467 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,468 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,469 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,470 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at .
17:45:02,471 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,473 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,474 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:101)
17:45:02,474 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
17:45:02,475 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
17:45:02,476 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)
17:45:02,477 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
17:45:02,478 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153)
17:45:02,479 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155)
17:45:02,480 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
17:45:02,481 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
17:45:02,482 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368)
17:45:02,483 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)
17:45:02,485 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671)
17:45:02,486 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930)
17:45:02,487 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at java.lang.Thread.run(Unknown Source)
17:45:02,487 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while reading the value from the stream object. Error: "java.io.IOException: The stream is closed."
17:45:02,489 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSWriter.error(IOBuffer.java:3960)
17:45:02,490 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSWriter.writeStream(IOBuffer.java:3755)
17:45:02,491 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSWriter.writeRPCInputStream(IOBuffer.java:5994)
17:45:02,492 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.DTV$SendByRPCOp.execute(dtv.java:1421)
17:45:02,492 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.DTV.executeOp(dtv.java:1847)
17:45:02,493 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.DTV.sendByRPC(dtv.java:1896)
17:45:02,494 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.Parameter.sendByRPC(Parameter.java:1157)
17:45:02,495 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.sendParamsByRPC(SQLServerPreparedStatement.java:527)
17:45:02,497 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doPrepExec(SQLServerPreparedStatement.java:843)
17:45:02,498 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:463)
17:45:02,499 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
17:45:02,500 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
17:45:02,501 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
17:45:02,501 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
17:45:02,502 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
17:45:02,502 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356)
17:45:02,503 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:147)
17:45:02,504 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:133)
17:45:02,505 ERROR [stderr] (http-localhost-127.0.0.1-8080-2) ... 47 more
17:45:02,507 SEVERE [com.microsoft.sqlserver.jdbc.internals.TDS.TOKEN] (http-localhost-127.0.0.1-8080-2) ConnectionID:73 ClientConnectionId: ebfce55d-b7b9-4427-a6db-4ecfed90bf65: getNextResult: Encountered unexpected unknown token (0x4)
17:45:02,508 SEVERE [com.microsoft.sqlserver.jdbc.internals.TDS.Reader] (http-localhost-127.0.0.1-8080-2) ConnectionID:73 ClientConnectionId: ebfce55d-b7b9-4427-a6db-4ecfed90bf65 got unexpected value in TDS response at offset:1342
- Remove From My Forums
-
Question
-
Hi,
I received the following error all of a sudden for a job last night:
Executed as :domainusername. Unknown token received from SQL server[SQLSTATE HY000](error 0) String data, right truncation [SQLSTATE 01004](Message 0). The step failed
I ran the job again this morning. It started running fine and sent data to another database through ODBC driver, but failed after some time with the same error. This job was running fine since the last 2 years and facing this error for the first time. Can
anyone explain the reason for this error?Thanks,
Preetha
-
Moved by
Wednesday, January 15, 2014 7:04 AM
Not db design
-
Moved by
Answers
-
Hi Preetha, the following article lists several suggested workarounds for this error. Search for the error message on this page and you’ll see the suggestions below.
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html
Thanks,
Sam Lester (MSFT)
http://blogs.msdn.com/b/samlester
This posting is provided «AS IS» with no warranties, and confers no rights. Please remember to click
«Mark as Answer» and
«Vote as Helpful» on posts that help you. This can be beneficial to other community members reading the thread.-
Proposed as answer by
Fanny Liu
Thursday, January 23, 2014 12:05 PM -
Marked as answer by
Kalman Toth
Friday, January 24, 2014 2:43 PM
-
Proposed as answer by
-
Don’t know about the unknown token, but this seems to be the root cause:
String data, right truncation. This error mainly occurs when you try to shove too many characters/bytes into a varchar/varbinary column. I have also seen it with some encryption functions.This error can be a nightmare to troubleshoot, since the message gives no clue of the overlong value or which column it occurred with. If you run the batch text for the step manually, you should at least be able to find which line it occurs on.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
-
Marked as answer by
Kalman Toth
Friday, January 24, 2014 2:43 PM
-
Marked as answer by
Hi,
I am trying to load an XML file from an Oracle table. The XSD has been provided (TEST_PERSON.xsd) and the target XML should have that specified structure.
But the interface fails saying:
ODI-1228: Task SrcSet0 (Loading) fails on the target XML connection XML_DS_PERSON.
Caused By: java.sql.SQLException: unknown token
Could you please help me?
I have created the topology as follows:
JDBC Url:
jdbc:snps:xml?d=C:oracleproduct11.1.1Oracle_ODI_1oracledidemodemoxmlTEST_PERSON.xsd&re=personnel&ro=true
I have tested the connection of the XML Data server which has shown Successful Connection.
The physical schema has been created as follows.
Physical Schema:
DATA Schema: TEST_PERSON
WORK Schema: TEST_PERSON
The model has been reversed successfully and the data stores are available in the Designer.
the knowledge modules are chosen as follows:
LKM: LKM SQL to SQL
IKM: IKM XML Control Append
CKM: CKM SQL
the Target Properties selected in the IKM are as follows:
CREATE_XML_FILE: true
XML_PATH: <default>:D:Test_Files<%=odiRef.getSession(«SESS_NO»)>.xml
While executing the interface, I have found the following err:
ODI Error:
ODI-1228: Task SrcSet0 (Loading) fails on the target XML connection PERSONAL_XML.
Caused By: java.sql.SQLException: unknown token
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at com.sunopsis.jdbc.driver.xml.SnpsXmlConnection.prepareStatement(SnpsXmlConnection.java:1192)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:200)
at $Proxy2.prepareStatement(Unknown Source)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:83)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:117)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:111)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:158)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)
Caused by: org.hsqldb.HsqlException: unknown token
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserBase.read(Unknown Source)
at org.hsqldb.ParserDQL.readNewSchemaObjectName(Unknown Source)
at org.hsqldb.ParserDDL.compileCreateTable(Unknown Source)
at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
… 28 more
Related Code:
create table TEST_PERSON.C$_0PERSONNEL
(
C1_PERSONNELPK NUMERIC(10) NULL,
C2_SNPSFILENAME VARCHAR(255) NULL,
C3_SNPSFILEPATH VARCHAR(255) NULL
)
The same error comes whether I select the «Staging Area Different from Target» checkbox or not.
Another thing to note is that, there is no Oracle schema named TEST_PERSON, the code which has been generated here tries to create a table in the schema name as the name mentioned in the Work schema of the physical schema of XML data server. Even if I change the name of the work schema to an existing Oracle schema, I am getting the same error saying unknown token.
Please let me know if you have any ideas. I will appreciate any help regarding this at all very much.
Thanks and Regards,
Anindita Mitra