Pentaho Server CE启动触发MySQLSyntaxErrorException问题求助

这个错误是Pentaho依赖的Jackrabbit内容仓库在初始化数据库文件系统时，生成的建表语句未指定varchar类型长度，而MySQL（无论5.x还是8.x版本）都要求varchar必须明确长度，导致抛出MySQLSyntaxErrorException。

解决方案1：修改Jackrabbit配置指定varchar默认长度 #

• 打开Pentaho Server安装目录下的 pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml
• 找到<FileSystem class="org.apache.jackrabbit.core.fs.db.DatabaseFileSystem">配置块
• 在该块的参数列表中添加以下配置：

  <param name="varcharLength" value="255"/>
  

• 保存文件后重启Pentaho Server

解决方案2：手动创建数据库表 #

如果配置修改无效，可手动创建缺失的表（修正varchar长度问题）：

1. 连接到Pentaho使用的MySQL数据库
2. 执行以下SQL语句：

   CREATE TABLE FSENTRY (
       FSENTRY_PATH varchar(255) NOT NULL,
       FSENTRY_NAME varchar(255) NOT NULL,
       FSENTRY_DATA VARBINARY NULL,
       FSENTRY_LAST_MODIFIED BIGINT NOT NULL,
       FSENTRY_LENGTH BIGINT NOT NULL,
       FSENTRY_IS_DIRECTORY BOOLEAN NOT NULL,
       PRIMARY KEY (FSENTRY_PATH, FSENTRY_NAME)
   ) ENGINE=InnoDB;
   

3. 重启Pentaho Server

报错日志详情 #

2022-07-21 15:49:54,826 ERROR [org.apache.jackrabbit.core.fs.db.DatabaseFileSystem] failed to initialize file system
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not null, FSENTRY_NAME varchar not null, FSENTRY_DATA varbinary null, FSENTRY_LA' at line 1
    at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:?]
    at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]
    at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500) ~[?:?]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:481) ~[?:?]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:824) ~[mysql-connector-java-5.1.17.jar:?]
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:667) ~[mysql-connector-java-5.1.17.jar:?]
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[commons-dbcp-1.4.jar:1.4]
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[commons-dbcp-1.4.jar:1.4]
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[commons-dbcp-1.4.jar:1.4]
    at org.apache.jackrabbit.core.util.db.ConnectionHelper.reallyExec(ConnectionHelper.java:308) ~[jackrabbit-data-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.util.db.ConnectionHelper$1.call(ConnectionHelper.java:293) ~[jackrabbit-data-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.util.db.ConnectionHelper$1.call(ConnectionHelper.java:289) ~[jackrabbit-data-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.util.db.ConnectionHelper$RetryManager.doTry(ConnectionHelper.java:544) ~[jackrabbit-data-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.util.db.ConnectionHelper.exec(ConnectionHelper.java:297) ~[jackrabbit-data-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.util.db.CheckSchemaOperation.run(CheckSchemaOperation.java:89) ~[jackrabbit-data-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.fs.db.DatabaseFileSystem.init(DatabaseFileSystem.java:193) ~[jackrabbit-core-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.config.RepositoryConfigurationParser$6.getFileSystem(RepositoryConfigurationParser.java:1164) ~[jackrabbit-core-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.config.RepositoryConfig.getFileSystem(RepositoryConfig.java:926) ~[jackrabbit-core-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.RepositoryImpl.<init>(RepositoryImpl.java:265) ~[jackrabbit-core-2.16.5.jar:2.16.5]
    at org.apache.jackrabbit.core.RepositoryImpl.create(RepositoryImpl.java:616) ~[jackrabbit-core-2.16.5.jar:2.16.5]
    at org.springframework.extensions.jcr.jackrabbit.RepositoryFactoryBean.createRepository(RepositoryFactoryBean.java:68) ~[se-jcr-0.9.jar:?]
    at org.springframework.extensions.jcr.RepositoryFactoryBean.afterPropertiesSet(RepositoryFactoryBean.java:61) ~[se-jcr-0.9.jar:?]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1847) ~[spring-beans-5.3.3.jar:5.3.3]

内容的提问来源于stack exchange，提问作者m_and_m