What you describe sounds right to me and seems consistent with the error stacktrace.. I would increase the MySQL wait_timeout to 3600 and, depending on your server, you might want to also increase max_connections.
cheers, Travis On Tue, Jan 8, 2013 at 4:10 AM, vijeshnair <vijeshkn...@gmail.com> wrote: > Solr version : 4.0 (running with 9GB of RAM) > MySQL : 5.5 > JDBC : mysql-connector-java-5.1.22-bin.jar > > I am trying to run the full import for my catalog data which is roughly > 13million of products. The DIH ran smoothly for 18 hours, and processed > roughly 10million of records. But all of a sudden it broke due to the jdbc > exception i.e. Communication failure with the server. I did an extensive > googling on this topic, and there are multiple recommendation to use > "readonly=true", "autocommit=true" etc. If I understand it correctly, the > possible reason is when DIH stops indexing due to the segment merging, and > when it tries to reconnect with the server. When index is slightly large > and > multiple merging happening at the same time, DIH stops indexing for some > time, and by the time it re-starts MySQL would have already discontinued > the > connection. So I am going to increase the wait time out at MySQL side from > the default 120 to some thing slightly large, to see if that solve the > issue > or not. I would know the result of that approach only after completing one > full run, which I will update you tomorrow. Mean time I thought of > validating my approach, and checking with you for any other fix which > exist. > > Here is the error stack > > Jan 8, 2013 12:44:00 PM org.apache.solr.handler.dataimport.JdbcDataSource > closeConnection > SEVERE: Ignoring Error when closing connection > java.sql.SQLException: Streaming result set > com.mysql.jdbc.RowDataDynamic@32d051c1 is still active. No statements may > be > issued when any streaming result sets are open and in use on a given > connection. Ensure that you have called .close() on any active streaming > result sets before attempting more queries. > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:923) > at > com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:3234) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2399) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728) > at > com.mysql.jdbc.ConnectionImpl.rollbackNoChecks(ConnectionImpl.java:4908) > at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4794) > at > com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4403) > at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1594) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:400) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:391) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:291) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:280) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Jan 8, 2013 12:44:00 PM org.apache.solr.handler.dataimport.JdbcDataSource > closeConnection > SEVERE: Ignoring Error when closing connection > com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: > Communications link failure during rollback(). Transaction resolution > unknown. > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) > at java.lang.reflect.Constructor.newInstance(Constructor.java:513) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at com.mysql.jdbc.Util.getInstance(Util.java:386) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) > at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4808) > at > com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4403) > at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1594) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:400) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:391) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:291) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:293) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:280) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Jan 8, 2013 12:44:00 PM org.apache.solr.handler.dataimport.JdbcDataSource > closeConnection > SEVERE: Ignoring Error when closing connection > com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: > Communications link failure during rollback(). Transaction resolution > unknown. > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) > at java.lang.reflect.Constructor.newInstance(Constructor.java:513) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at com.mysql.jdbc.Util.getInstance(Util.java:386) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) > at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4808) > at > com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4403) > at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1594) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:400) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:391) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:291) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:293) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:280) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Jan 8, 2013 12:44:00 PM org.apache.solr.handler.dataimport.JdbcDataSource > closeConnection > SEVERE: Ignoring Error when closing connection > com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: > Communications link failure during rollback(). Transaction resolution > unknown. > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) > at java.lang.reflect.Constructor.newInstance(Constructor.java:513) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at com.mysql.jdbc.Util.getInstance(Util.java:386) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) > at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4808) > at > com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4403) > at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1594) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:400) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:391) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:291) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:293) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:280) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Jan 8, 2013 12:44:00 PM org.apache.solr.handler.dataimport.JdbcDataSource > closeConnection > SEVERE: Ignoring Error when closing connection > com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: > Communications link failure during rollback(). Transaction resolution > unknown. > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) > at java.lang.reflect.Constructor.newInstance(Constructor.java:513) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at com.mysql.jdbc.Util.getInstance(Util.java:386) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) > at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4808) > at > com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4403) > at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1594) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:400) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:391) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:291) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:293) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:280) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Jan 8, 2013 12:44:00 PM org.apache.solr.handler.dataimport.JdbcDataSource > closeConnection > SEVERE: Ignoring Error when closing connection > com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: > Communications link failure during rollback(). Transaction resolution > unknown. > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) > at java.lang.reflect.Constructor.newInstance(Constructor.java:513) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at com.mysql.jdbc.Util.getInstance(Util.java:386) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) > at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) > at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4808) > at > com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4403) > at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1594) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:400) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:391) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:291) > at > > org.apache.solr.handler.dataimport.DocBuilder.closeEntityProcessorWrappers(DocBuilder.java:293) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:280) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Jan 8, 2013 12:44:00 PM org.apache.solr.common.SolrException log > SEVERE: Full Import failed:java.lang.RuntimeException: > java.lang.RuntimeException: > org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to > execute query: SELECT DISTINCT mpn FROM product_retailers WHERE product_id > = > '9393557198' Processing Document # 10194031 > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:273) > at > > org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:382) > at > > org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:448) > at > > org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:429) > Caused by: java.lang.RuntimeException: > org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to > execute query: SELECT DISTINCT mpn FROM product_retailers WHERE product_id > = > '9393557198' Processing Document # 10194031 > at > > org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:413) > at > > org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:326) > at > org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:234) > ... 3 more > Caused by: org.apache.solr.handler.dataimport.DataImportHandlerException: > Unable to execute query: SELECT DISTINCT mpn FROM product_retailers WHERE > product_id = '9393557198' Processing Document # 10194031 > at > > org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71) > at > > org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:252) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:209) > at > > org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:38) > at > > org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59) > at > > org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:73) > at > > org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:243) > at > > org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:472) > at > > org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:498) > at > > org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:411) > ... 5 more > Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: > Communications link failure > > The last packet successfully received from the server was 0 milliseconds > ago. The last packet sent successfully to the server was 0 milliseconds > ago. > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > at > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) > at > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) > at java.lang.reflect.Constructor.newInstance(Constructor.java:513) > at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) > at > com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117) > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3589) > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3478) > at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4019) > at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490) > at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728) > at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2678) > at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894) > at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732) > at > > org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:245) > ... 13 more > Caused by: java.io.EOFException: Can not read response from server. > Expected > to read 4 bytes, read 0 bytes before connection was unexpectedly lost. > at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3039) > at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3489) > ... 22 more > > > > > -- > View this message in context: > http://lucene.472066.n3.nabble.com/DIH-fails-after-processing-roughly-10million-records-tp4031508.html > Sent from the Solr - User mailing list archive at Nabble.com. > -- ** *Travis Low, Director of Development* ** <t...@4centurion.com>* * *Centurion Research Solutions, LLC* *14048 ParkEast Circle *•* Suite 100 *•* Chantilly, VA 20151* *703-956-6276 *•* 703-378-4474 (fax)* *http://www.centurionresearch.com* <http://www.centurionresearch.com> **The information contained in this email message is confidential and protected from disclosure. If you are not the intended recipient, any use or dissemination of this communication, including attachments, is strictly prohibited. If you received this email message in error, please delete it and immediately notify the sender. This email message and any attachments have been scanned and are believed to be free of malicious software and defects that might affect any computer system in which they are received and opened. No responsibility is accepted by Centurion Research Solutions, LLC for any loss or damage arising from the content of this email.
