Compass library示例连MySQL问题的解决

 昨天在学习compass,跑了下library的例子，将源码放入Eclipse的Java工程后，加入必要的jar包，发现一个错误
 SimpleDateFormat sdf = new SimpleDateFormat(Library.MetaData.Birthdate.Format);提示找不到Library类，这
估计是作者将这个类打包漏了，不过没有什么影响，改成 new SimpleDateFormat("yyyy/MM/dd")就可以了，查看compass.cfg.xml文件，这是的索引是以文件形式存放的，运行LibraryTests, 顺利通过，看了参考文档，索引还可以存放在数据库中，看了下参考文档，都是基于

<compass name="default">
 <connection>
    <jdbc>
      <dataSourceProvider>
        <driverManager url="jdbc:hsqldb:mem:test"
           username="sa" password=""
          driverClass="org.hsqldb.jdbcDriver" />
      </dataSourceProvider>
   </jdbc>
 </connection>
</compass>
这样的格式写的，可示例中是以<setting name="compass.engine.connection">target:/index</setting>去写的，当然你可以按照原来的方式去改下，这里就按照setting的方式去写，关键是找到属性名，找了参考文档在187页的A.19.Search Engine Jdbc一节中列出了相关的属性，于是想试下hsqldb，改写的compass.cfg.xml
<compass-core-configuration>
    <compass>
        <setting name="compass.engine.connection">jdbc://jdbc:hsqldb:mem:test</setting>
        <setting name="compass.engine.store.jdbc.connection.provider.class">
        org.compass.core.lucene.engine.store.jdbc.DriverManagerDataSourceProvider
        </setting>
        <setting name="compass.engine.store.jdbc.connection.driverClass">org.hsqldb.jdbcDriver</setting>
        <setting name="compass.engine.store.jdbc.connection.username">sa</setting>
        <setting name="compass.engine.store.jdbc.connection.password"></setting>
        <meta-data resource="org/compass/sample/library/library.cmd.xml" />
    </compass>
</compass-core-configuration>
测试同样顺利通过，毕竟hsqldb不是很方便，所以又想换别的数据库，当然MySQL是首选，修改compass.cfg.xml
<!DOCTYPE compass-core-configuration PUBLIC
    "-//Compass/Compass Core Configuration DTD 2.0//EN"
    "http://www.compass-project.org/dtd/compass-core-configuration-2.1.dtd">

<compass-core-configuration>
    <compass>
        <setting name="compass.engine.connection">jdbc://jdbc:mysql://localhost:3306/compass</setting>
        <setting name="compass.engine.store.jdbc.connection.provider.class">
        org.compass.core.lucene.engine.store.jdbc.DriverManagerDataSourceProvider
        </setting>
        <setting name="compass.engine.store.jdbc.connection.driverClass">com.mysql.jdbc.Driver</setting>
        <setting name="compass.engine.store.jdbc.connection.username">Roger</setting>
        <setting name="compass.engine.store.jdbc.connection.password">830708</setting>
        <meta-data resource="org/compass/sample/library/library.cmd.xml" />
    </compass>
</compass-core-configuration>，然后建立一个compass的数据库，当然Roger用户必须对数据库具有建表的权限，因为要建表来存放索引。可是运行测试的结果出乎意料，居然抛出异常：
org.compass.core.engine.SearchEngineException: Failed to create index for sub index [author]; nested exception is org.apache.lucene.store.jdbc.JdbcStoreException: Failed to execute sql [select name_, 'value_' as x, size_ from index_author where name_ = ?]; nested exception is java.lang.ArrayIndexOutOfBoundsException: null
org.apache.lucene.store.jdbc.JdbcStoreException: Failed to execute sql [select name_, 'value_' as x, size_ from index_author where name_ = ?]; nested exception is java.lang.ArrayIndexOutOfBoundsException: null
java.lang.ArrayIndexOutOfBoundsException
 at java.lang.System.arraycopy(Native Method)
 at com.mysql.jdbc.Blob.getBytes(Blob.java:123)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput.readInternal(FetchOnBufferReadJdbcIndexInput.java:132)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput.access$1400(FetchOnBufferReadJdbcIndexInput.java:35)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput$1.execute(FetchOnBufferReadJdbcIndexInput.java:91)
 at org.apache.lucene.store.jdbc.support.JdbcTemplate.executeSelect(JdbcTemplate.java:112)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput.refill(FetchOnBufferReadJdbcIndexInput.java:58)
 at org.apache.lucene.store.ConfigurableBufferedIndexInput.readByte(ConfigurableBufferedIndexInput.java:27)
 at org.apache.lucene.store.ChecksumIndexInput.readByte(ChecksumIndexInput.java:36)
 at org.apache.lucene.store.IndexInput.readInt(IndexInput.java:68)
 at org.apache.lucene.index.SegmentInfos.read(SegmentInfos.java:221)
 at org.apache.lucene.index.IndexFileDeleter.<init>(IndexFileDeleter.java:175)
 at org.apache.lucene.index.IndexWriter.init(IndexWriter.java:1093)
 at org.apache.lucene.index.IndexWriter.<init>(IndexWriter.java:698)
 at org.compass.core.lucene.engine.store.DefaultLuceneSearchEngineStore.createIndex(DefaultLuceneSearchEngineStore.java:493)
 at org.compass.core.lucene.engine.store.DefaultLuceneSearchEngineStore.createIndex(DefaultLuceneSearchEngineStore.java:487)
 at org.compass.core.lucene.engine.store.DefaultLuceneSearchEngineStore.createIndex(DefaultLuceneSearchEngineStore.java:482)
 at org.compass.core.lucene.engine.manager.DefaultLuceneSearchEngineIndexManager$1.doInTransaction(DefaultLuceneSearchEngineIndexManager.java:103)
 at org.compass.core.impl.DefaultCompass$CompassTransactionContext.execute(DefaultCompass.java:349)
 at org.compass.core.lucene.engine.manager.DefaultLuceneSearchEngineIndexManager.createIndex(DefaultLuceneSearchEngineIndexManager.java:101)
 at org.compass.sample.library.LibraryTests.setUp(LibraryTests.java:66)
 at junit.framework.TestCase.runBare(TestCase.java:132)
 at junit.framework.TestResult$1.protect(TestResult.java:110)
 at junit.framework.TestResult.runProtected(TestResult.java:128)
 at junit.framework.TestResult.run(TestResult.java:113)
 at junit.framework.TestCase.run(TestCase.java:124)
 at junit.framework.TestSuite.runTest(TestSuite.java:232)
 at junit.framework.TestSuite.run(TestSuite.java:227)
 at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:79)
 at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
 at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

用hsqldb好好的，可是换成mysql就出问题，心想程序肯定不会有问题，唯一的问题就可能出现在mysql上，首先想的是驱动的问题，因为以前用SQL Server 2000也因为驱动出现了莫名其妙的问题，本来我用的是mysql-connector-java-3.12-bin.jar，一直也没出现问题，所以就没换，我找了5.0.0-beta-bin.jar换上，还是报同样的错误，难道还不够新，又下了个5.1.7换上，还是报错，不过这次不一样：
org.compass.core.engine.SearchEngineException: Failed to create index for sub index [author]; nested exception is org.apache.lucene.store.jdbc.JdbcStoreException: Failed to execute sql [select name_, 'value_' as x, size_ from index_author where name_ = ?]; nested exception is java.sql.SQLException: "pos" + "length" arguments can not be larger than the BLOB's length.
org.apache.lucene.store.jdbc.JdbcStoreException: Failed to execute sql [select name_, 'value_' as x, size_ from index_author where name_ = ?]; nested exception is java.sql.SQLException: "pos" + "length" arguments can not be larger than the BLOB's length.
java.sql.SQLException: "pos" + "length" arguments can not be larger than the BLOB's length.
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
 at com.mysql.jdbc.Blob.getBytes(Blob.java:142)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput.readInternal(FetchOnBufferReadJdbcIndexInput.java:132)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput.access$1400(FetchOnBufferReadJdbcIndexInput.java:35)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput$1.execute(FetchOnBufferReadJdbcIndexInput.java:91)
 at org.apache.lucene.store.jdbc.support.JdbcTemplate.executeSelect(JdbcTemplate.java:112)
 at org.apache.lucene.store.jdbc.index.FetchOnBufferReadJdbcIndexInput.refill(FetchOnBufferReadJdbcIndexInput.java:58)
 at org.apache.lucene.store.ConfigurableBufferedIndexInput.readByte(ConfigurableBufferedIndexInput.java:27)
 at org.apache.lucene.store.ChecksumIndexInput.readByte(ChecksumIndexInput.java:36)
 at org.apache.lucene.store.IndexInput.readInt(IndexInput.java:68)
 at org.apache.lucene.index.SegmentInfos.read(SegmentInfos.java:221)
 at org.apache.lucene.index.IndexFileDeleter.<init>(IndexFileDeleter.java:175)
 at org.apache.lucene.index.IndexWriter.init(IndexWriter.java:1093)
 at org.apache.lucene.index.IndexWriter.<init>(IndexWriter.java:698)
 at org.compass.core.lucene.engine.store.DefaultLuceneSearchEngineStore.createIndex(DefaultLuceneSearchEngineStore.java:493)
 at org.compass.core.lucene.engine.store.DefaultLuceneSearchEngineStore.createIndex(DefaultLuceneSearchEngineStore.java:487)
 at org.compass.core.lucene.engine.store.DefaultLuceneSearchEngineStore.createIndex(DefaultLuceneSearchEngineStore.java:482)
 at org.compass.core.lucene.engine.manager.DefaultLuceneSearchEngineIndexManager$1.doInTransaction(DefaultLuceneSearchEngineIndexManager.java:103)
 at org.compass.core.impl.DefaultCompass$CompassTransactionContext.execute(DefaultCompass.java:349)
 at org.compass.core.lucene.engine.manager.DefaultLuceneSearchEngineIndexManager.createIndex(DefaultLuceneSearchEngineIndexManager.java:101)
 at org.compass.sample.library.LibraryTests.setUp(LibraryTests.java:66)
 at junit.framework.TestCase.runBare(TestCase.java:132)
 at junit.framework.TestResult$1.protect(TestResult.java:110)
 at junit.framework.TestResult.runProtected(TestResult.java:128)
 at junit.framework.TestResult.run(TestResult.java:113)
 at junit.framework.TestCase.run(TestCase.java:124)
 at junit.framework.TestSuite.runTest(TestSuite.java:232)
 at junit.framework.TestSuite.run(TestSuite.java:227)
 at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:79)
 at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45)
 at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
 at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
初步看出来是BLOB的字段大小的问题，看了下数据库，这是其实已经生成了index_author和index_book两个表，而且都有两行数据，可是为什么还报错呢，自己也百思不得其解，只好Google了一下，几经周折才找了解决方案http://forum.compass-project.org/thread.jspa?messageID=293782&#293782，照着做了下，果然测试成功，注意是"`"不是"'",可是为什么呢？查看了下源码，找到compass-2.1.0\src\main\src\org\apache\lucene\store\jdbc\dialect\MySQLDialect文件中的openBlobSelectQuote()和closeBlobSelectQuote()可以看到返回是"'",而像openQuote()和closeQuote()返回的而是"`",二者竟然不一样，难道是作者大意了？至此，问题全部解决。