It seems that when table t_order created, its tableMetaData doesn't put into schemaMetaData.
I will try to find out the reason and fix it.
when configuration is as follows(actual table name use uppercase words), the issue above will absent,
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..1}.T_ORDER_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: T_ORDER_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
props:
worker.id: 123
But when TableMetaDataLoader#isTableExist return true, there is another error occur from ColumnMetaDataLoader#load.
Java code is
try (ResultSet resultSet = connection.createStatement().executeQuery(generateEmptyResultSQL(table, databaseType))) {
for (String each : columnNames) {
isCaseSensitives.add(resultSet.getMetaData().isCaseSensitive(resultSet.findColumn(each)));
Exception cause is
Error : 933, Position : 38, Sql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, OriginalSql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, Error Msg = ORA-00933: SQL 命令未正确结束
And this sql can execute successfully in PL/SQL.
when configuration is as follows(actual table name use uppercase words), the issue above will absent,
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..1}.T_ORDER_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: T_ORDER_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
props:
worker.id: 123
But when TableMetaDataLoader#isTableExist return true, there is another error occur from ColumnMetaDataLoader#load.
Java code is
try (ResultSet resultSet = connection.createStatement().executeQuery(generateEmptyResultSQL(table, databaseType))) {
for (String each : columnNames) {
isCaseSensitives.add(resultSet.getMetaData().isCaseSensitive(resultSet.findColumn(each)));
Exception cause is
Error : 933, Position : 38, Sql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, OriginalSql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, Error Msg = ORA-00933: SQL 命令未正确结束
And this sql can execute successfully in PL/SQL.
before I have found that ，SELECT * FROM "T_ORDER_0" WHERE 1 != 1; the ; in the end is not allowed int oracle grammar，I commented it in code temporarily , key still not generated, so this is only one bug in ColumnMetaDataLoader.generateEmptyResultSQL
Now the test turn into green.
changes are as follows,
1、modify ColumnMetaDataLoade#generateEmptyResultSQL
return "SELECT * FROM " + delimiterLeft + table + delimiterRight + " WHERE 1 != 1"
(remove semicolons behind WHERE 1 != 1 which not allowed in oracle)
2、return value of connection.getMetaData().getIndexInfo includes a null name record most times but not always . So IndexMetaDataLoader#load needs a check for whether indexName is null.
public static Collection<IndexMetaData> load(final Connection connection, final String table) throws SQLException {
Collection<IndexMetaData> result = new HashSet<>();
try (ResultSet resultSet = connection.getMetaData().getIndexInfo(connection.getCatalog(), connection.getSchema(), table, false, false)) {
while (resultSet.next()) {
String indexName = resultSet.getString(INDEX_NAME);
if (Strings.isNullOrEmpty(indexName)) {
continue;
result.add(new IndexMetaData(indexName));
return result;
3、connection.getMetaData().getColumns in ColumnMetaDataLoader#load need specify a schema, otherwise it will return duplicated columns, so that I use connection.getSchema() replace null.
try (ResultSet resultSet = connection.getMetaData().getColumns(connection.getCatalog(), connection.getSchema(), table, "%")) {
while (resultSet.next()) {
String columnName = resultSet.getString(COLUMN_NAME);
columnTypes.add(resultSet.getInt(DATA_TYPE));
columnTypeNames.add(resultSet.getString(TYPE_NAME));
isPrimaryKeys.add(primaryKeys.contains(columnName));
columnNames.add(columnName);
it is works to me. Can we discuss further how to modify it?
We need to make sure about oracle SQL and semicolon.
If return true for ResultSet.next() with connection.getMetaData().getIndexInfo(), what's happen for null value of resultSet.getString(INDEX_NAME)?
The 3rd modification is OK.
1、I found some infomation may explain the use of semicolon in Oracle JDBC. This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it.
Usually the semi-colon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semi-colon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.
On the other hand, the JDBC api is intended to execute a single(!) statement at a time, therefor you don't need such a separator (the statement is the whole string). This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.
Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).
Original answer link https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql
Note, however, that there is no semicolon at the end of the SQL92 CallableStatement
Original articles from Expert Oracle JDBC Programming
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null . But DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't return the statistics record.
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLExceptionGet a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
Each index column description has the following columns:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
TYPE short => index type:
tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
tableIndexClustered - this is a clustered index
tableIndexHashed - this is a hashed index
tableIndexOther - this is some other style of index
ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
CARDINALITY long => When TYPE is tableIndexStatistic, then this is the number of rows in the table; otherwise, it is the number of unique values in the index.
PAGES long => When TYPE is tableIndexStatisic then this is the number of pages used for the table, otherwise it is the number of pages used for the current index.
Original document from https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)
https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo-java.lang.String-java.lang.String-java.lang.String-boolean-boolean-
