从数据库中读取数据(MySQL)

I've installed Solr, as well as the DataImport handler/plugin...and after some trial and error I was able to get Solr to connect to a DB.

我已经安装了Solr，以及DataImport处理器/插件…经过一些尝试和错误之后，我能让Solr连接到DB。

Unfortunately, I don't know Java, and this error response below is stumping me...

不幸的是，我不知道Java，下面的错误响应将我难住了……

 {
   "responseHeader": {
   "status": 0,
   "QTime": 2682
 },
   "initArgs": [
   "defaults",
  [
  "config",
  "data-config.xml"
  ]
],
"command": "full-import",
"mode": "debug",
"documents": [],
"verbose-output": [
  "entity:product",
  [
  "document#1",
   [
    "query",
     "SELECT A.product_attribute_id, A.product_id, A.model, A.sku_code, A.status, A.image_filename, A.local_filename, A.width, A.width_final, A.length, A.length_final, A.msrp, A.map_price, A.sku_upc_code, A.url, A.discontinued, A.sq_foot, A.weight, A.rolled_length, A.rolled_width, A.rolled_height, A.lifestyle, A.detailed_color, A.bullet_point1, A.bullet_point2, A.bullet_point3, A.on_hand, A.eta_date, A.pile_height, A.eta_quantity, DATE_FORMAT(FROM_UNIXTIME(A.last_updated), '%e %b %Y') AS last_updated, P.sku_description, P.url as product_url, P.design, C.collection_id, C.collection_name, C.url as collection_url, M.manufacturer_name, M.url as manufacturer_url, O.origin_name, T.type_name, CO.content_name, S.style_name, COL.color_name, COL.color_id, PS.parentstyle_name, PC.parentcolor_name, SHA.shape_name, SA.sale_amount, DATE_FORMAT(FROM_UNIXTIME(SA.sale_start), '%e %b %Y') AS sale_start, DATE_FORMAT(FROM_UNIXTIME(SA.sale_end), '%e %b %Y') AS sale_end FROM tr_product_attributes as A INNER JOIN tr_products as P ON A.product_id = P.product_id INNER JOIN tr_collections as C ON P.collection = C.collection_id INNER JOIN tr_manufacturers as M ON P.manufacturer = M.manufacturer_id INNER JOIN tr_origins as O ON P.origin = O.origin_id INNER JOIN tr_types as T ON P.type = T.type_id INNER JOIN tr_contents as CO ON P.content = CO.content_id INNER JOIN tr_styles as S ON A.style = S.style_id INNER JOIN tr_parentstyles as PS ON A.parentstyle = PS.parentstyle_id INNER JOIN tr_colors as COL ON A.color = COL.color_id INNER JOIN tr_parentcolors as PC ON A.parentcolor = PC.parentcolor_id INNER JOIN tr_shapes as SHA ON A.shape = SHA.shape_id LEFT JOIN tr_sales as SA ON P.collection = SA.sale_collection_id WHERE A.status=1 AND A.url NOT LIKE '%http://www.XXXXXXX.com/rugs/rug/-///%' AND PC.parentcolor_name!='NA' AND A.active='Y' AND A.discontinued=0 AND A.local_filename LIKE '%jpg%' AND P.status=1 AND P.discontinued=0 AND C.status=1 AND C.discontinued=0 AND M.status=1",
    "time-taken",
    "0:0:2.652",
    "EXCEPTION",
    "org.apache.solr.handler.dataimport.DataImportHandlerException: Error reading data from database Processing Document # 1\n\tat org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:70)\n\tat org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:398)\n\tat org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.access$600(JdbcDataSource.java:296)\n\tat org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:336)\n\tat org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:328)\n\tat org.apache.solr.handler.dataimport.EntityProcessorBase.getNext(EntityProcessorBase.java:133)\n\tat org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:74)\n\tat org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243)\n\tat org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:475)\n\tat org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:414)\n\tat org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:329)\n\tat org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:232)\n\tat org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:416)\n\tat org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:480)\n\tat org.apache.solr.handler.dataimport.DataImportHandler.handleRequestBody(DataImportHandler.java:185)\n\tat org.apache.solr.handler.RequestHandlerBase.handleRequest(RequestHandlerBase.java:143)\n\tat org.apache.solr.core.SolrCore.execute(SolrCore.java:2064)\n\tat org.apache.solr.servlet.HttpSolrCall.execute(HttpSolrCall.java:654)\n\tat org.apache.solr.servlet.HttpSolrCall.call(HttpSolrCall.java:450)\n\tat org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:227)\n\tat org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:196)\n\tat org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)\n\tat org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)\n\tat org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:577)\n\tat org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:223)\n\tat org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127)\n\tat org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515)\n\tat org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)\n\tat org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061)\n\tat org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)\n\tat org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215)\n\tat org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:110)\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)\n\tat org.eclipse.jetty.server.Server.handle(Server.java:497)\n\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)\n\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)\n\tat org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)\n\tat java.lang.Thread.run(Thread.java:745)\nCaused by: java.sql.SQLException: Value '\u000535298\u000541486\u0010SE30 Gingerlilly\rSE30R024X036S\u00011E/img/RugImages/Vendors/Colonial Mills/Generation/SE30-gingerlilyl.jpg\u0014SE30-gingerlilyl.jpg\u00052.000\u00052' 0\"\u00053.000\u00053' 0\"\b119.0000\u000789.0000\f082262203658Zhttp://www.XXXXXXX.com/rugs/rug/colonial-mills-generation-seascape/gingerlilly/41486/35298\u00010\u00010\u00013\u00010\u00014\u00014\u0000\u0000\u0000\u0000\u0000\u00010\n0000-00-00\u00010\u00010\n1 Jan 1970\u0004NULLMhttp://www.XXXXXXX.com/rugs/rug/colonial-mills-generation-seascape-se30/41486\bSeascape\u0003347\u0013Generation-SeascapeOhttp://www.XXXXXXX.com/rugs/brands/colonial-mills/collection/generationseascape\u000eColonial Mills1http://www.XXXXXXX.com/rugs/brands/colonial-mills\u0003USA\u0007Braided\rPolypropylene\u0007Braided\u000bGingerlilly\u0003626\fTransitional\fGold, Yellow\tRectangle���\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000' can not be represented as java.sql.Date\n\tat com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)\n\tat com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)\n\tat com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)\n\tat com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)\n\tat com.mysql.jdbc.ResultSetRow.getDateFast(ResultSetRow.java:145)\n\tat com.mysql.jdbc.BufferRow.getDateFast(BufferRow.java:689)\n\tat com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:2012)\n\tat com.mysql.jdbc.ResultSetImpl.getDate(ResultSetImpl.java:1975)\n\tat com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4587)\n\tat com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4710)\n\tat org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:358)\n\t... 39 more\n"
  ]
]
],
"status": "idle",
"importResponse": "",
"statusMessages": {
"Time Elapsed": "0:0:2.667",
"Total Requests made to DataSource": "1",
"Total Rows Fetched": "0",
"Total Documents Processed": "0",
"Total Documents Skipped": "0",
"Full Dump Started": "2015-10-21 14:05:52",
"Full Import failed": "2015-10-21 14:05:55"
}
}

I think i've narrowed it down to this particular part of the error message...

我想我已经把它缩小到这个错误信息的特定部分了…

 Caused by: java.sql.SQLException: Value '\u000535298\u000541486\u0010SE30 Gingerlilly\rSE30R024X036S\u00011E/img/RugImages/Vendors/Colonial Mills/Generation/SE30-gingerlilyl.jpg\u0014SE30-gingerlilyl.jpg\u00052.000\u00052' 0\"\u00053.000\u00053' 0\"\b119.0000\u000789.0000\f082262203658Zhttp://www.XXXXXXX.com/rugs/rug/colonial-mills-generation-seascape/gingerlilly/41486/35298\u00010\u00010\u00013\u00010\u00014\u00014\u0000\u0000\u0000\u0000\u0000\u00010\n0000-00-00\u00010\u00010\n1 Jan 1970\u0004NULLMhttp://www.XXXXXXX.com/rugs/rug/colonial-mills-generation-seascape-se30/41486\bSeascape\u0003347\u0013Generation-SeascapeOhttp://www.XXXXXXX.com/rugs/brands/colonial-mills/collection/generationseascape\u000eColonial Mills1http://www.XXXXXXX.com/rugs/brands/colonial-mills\u0003USA\u0007Braided\rPolypropylene\u0007Braided\u000bGingerlilly\u0003626\fTransitional\fGold, Yellow\tRectangle���\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000' can not be represented as java.sql.Date

The only problem is that there is no "Date" field in this set of data. I have a few "Timestamp" fields, but when I wrote the config file for Solr I have these represented as Numeric or Intergers...or something like that. Not date fields. In addition, I temporarily adjusted the "Timestamp" fields to VARCHAR 255 just to see if that would make a difference, and it did not.

唯一的问题是在这组数据中没有“日期”字段。我有一些“时间戳”字段，但是当我为Solr编写配置文件时，我将它们表示为数字或Intergers…之类的。没有日期字段。另外，我临时调整了“时间戳”字段到VARCHAR 255，只是为了看看是否会有所不同，而它没有。

Any help is greatly appreciated!

非常感谢您的帮助!

1 个解决方案

#1

You probably have some error in solr mapping. The error says it expected to convert the "blah blah Gingerlilly blah" value read from the database to a Date and failed.

在solr映射中可能有一些错误。该错误表示，它期望将从数据库中读取的“blah blah blah blah”的值转换为日期并失败。

If you're able to find what column does contain the "Gingerlilly" stuff in the select's output, you'll have a good pointer at what configuration to check.

如果您能够找到在select的输出中包含“Gingerlilly”内容的列，那么您将有一个很好的指针来检查配置。