使用flyway和shardingsphere报错了

使用flyway和shardingsphere报错了

line 1:96 no viable alternative at input ‘UNION’
line 1:96 mismatched input ‘UNION’ expecting {TRUNCATE, POSITION, VIEW, AS, ANY, OFFSET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, BOOLEAN, DATE, TIME, TIMESTAMP, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, MAX, MIN, SUM, COUNT, AVG, CURRENT, ENABLE, DISABLE, INSTANCE, DO, DEFINER, CASCADED, LOCAL, CLOSE, OPEN, NEXT, NAME, TYPE, TABLES, TABLESPACE, COLUMNS, FIELDS, INDEXES, STATUS, MODIFY, VALUE, DUPLICATE, FIRST, LAST, AFTER, OJ, ACCOUNT, USER, ROLE, START, TRANSACTION, WITHOUT, ESCAPE, SUBPARTITION, STORAGE, SUPER, TEMPORARY, THAN, UNBOUNDED, SIGNED, UPGRADE, VALIDATION, ROLLUP, SOUNDS, UNKNOWN, OFF, ALWAYS, COMMITTED, LEVEL, NO, PASSWORD, PRIVILEGES, ACTION, ALGORITHM, AUTOCOMMIT, BTREE, CHAIN, CHARSET, CHECKSUM, CIPHER, CLIENT, COALESCE, COMMENT, COMPACT, COMPRESSED, COMPRESSION, CONNECTION, CONSISTENT, DATA, DISCARD, DISK, ENCRYPTION, END, ENGINE, EVENT, EXCHANGE, EXECUTE, FILE, FIXED, FOLLOWING, GLOBAL, HASH, IMPORT_, LESS, MEMORY, NONE, PARSER, PARTIAL, PARTITIONING, PERSIST, PRECEDING, PROCESS, PROXY, QUICK, REBUILD, REDUNDANT, RELOAD, REMOVE, REORGANIZE, REPAIR, REVERSE, SESSION, SHUTDOWN, SIMPLE, SLAVE, VISIBLE, INVISIBLE, ENFORCED, AGAINST, LANGUAGE, MODE, QUERY, EXTENDED, EXPANSION, VARIANCE, MAX_ROWS, MIN_ROWS, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CACHE, SQL_NO_CACHE, STATS_AUTO_RECALC, STATS_PERSISTENT, STATS_SAMPLE_PAGES, ROW_FORMAT, WEIGHT_STRING, COLUMN_FORMAT, INSERT_METHOD, KEY_BLOCK_SIZE, PACK_KEYS, PERSIST_ONLY, BIT_AND, BIT_OR, BIT_XOR, GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, AUTO_INCREMENT, AVG_ROW_LENGTH, DELAY_KEY_WRITE, ROTATE, MASTER, BINLOG, ERROR, SCHEDULE, COMPLETION, EVERY, HOST, SOCKET, PORT, SERVER, WRAPPER, OPTIONS, OWNER, RETURNS, CONTAINS, SECURITY, INVOKER, TEMPTABLE, MERGE, UNDEFINED, DATAFILE, FILE_BLOCK_SIZE, EXTENT_SIZE, INITIAL_SIZE, AUTOEXTEND_SIZE, MAX_SIZE, NODEGROUP, WAIT, LOGFILE, UNDOFILE, UNDO_BUFFER_SIZE, REDO_BUFFER_SIZE, HANDLER, PREV, ORGANIZATION, DEFINITION, DESCRIPTION, REFERENCE, FOLLOWS, PRECEDES, IMPORT, CONCURRENT, XML, DUMPFILE, SHARE, CODE, CONTEXT, SOURCE, CHANNEL, CLONE, AGGREGATE, INSTALL, COMPONENT, UNINSTALL, RESOURCE, EXPIRE, NEVER, HISTORY, OPTIONAL, REUSE, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS, RETAIN, RANDOM, OLD, ISSUER, SUBJECT, CACHE, GENERAL, SLOW, USER_RESOURCES, EXPORT, RELAY, HOSTS, FLUSH, RESET, RESTART, IO_THREAD, SQL_THREAD, SQL_BEFORE_GTIDS, SQL_AFTER_GTIDS, MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE, RELAY_LOG_POS, SQL_AFTER_MTS_GAPS, UNTIL, DEFAULT_AUTH, PLUGIN_DIR, STOP, IDENTIFIER_, STRING_}
line 1:106 extraneous input ‘(’ expecting SELECT

我使用的是flyway 7.1.1版本
目前定位到是这一句报错了E:\mvnrepository\org\flywaydb\flyway-core\7.1.1\flyway-core-7.1.1.jar!\org\flywaydb\core\internal\info\MigrationInfoServiceImpl.class 的第363行报错了，在检测schema是否为空时报了以上错误，看起来貌似不影响程序正常使用

在对比schema是否为空的时候flyway使用到了这个类MySQLSchema
在doEmpty方法中使用了如下sql进行判断

protected boolean doEmpty() throws SQLException {
    List<String> params = new ArrayList<>(Arrays.asList(name, name, name, name, name));
    if (database.eventSchedulerQueryable) {
        params.add(name);
    }

    return jdbcTemplate.queryForInt("SELECT SUM(found) FROM ("
                    + "(SELECT 1 as found FROM information_schema.tables WHERE table_schema=?) UNION ALL "
                    + "(SELECT 1 as found FROM information_schema.views WHERE table_schema=? LIMIT 1) UNION ALL "
                    + "(SELECT 1 as found FROM information_schema.table_constraints WHERE table_schema=? LIMIT 1) UNION ALL "
                    + "(SELECT 1 as found FROM information_schema.triggers WHERE event_object_schema=?  LIMIT 1) UNION ALL "
                    + "(SELECT 1 as found FROM information_schema.routines WHERE routine_schema=? LIMIT 1)"
                    // #2410 Unlike MySQL, MariaDB 10.0 and newer don't allow the events table to be queried
                    // when the event scheduled is DISABLED or in some rare cases OFF
                    + (database.eventSchedulerQueryable ? " UNION ALL (SELECT 1 as found FROM information_schema.events WHERE event_schema=? LIMIT 1)" : "")
                    + ") as all_found",
            params.toArray(new String[0])
    ) == 0;
}

可以看出，flyway使用了UNION ALL语句进行对shema的判空操作，这也就造成了这个报错，因为shardingjdbc目前的版本4.1.1并不运行使用UNION ALL等关联其他表的查询。

目前在shardingjdbc的新版本文档中并没有找到关于union all的支持说明，准备实验一下，看新版本的5.0.0是否支持union all
shardingjdbc5.0.0支持版本https://shardingsphere.apache.org/document/5.0.0/cn/features/sharding/use-norms/sql/

找到了问题就知道如何解决了，理论上只要修改一下这个sql不让他使用shardingjdbc不支持的关键词就可以避免这个报错了。