sandynz opened a new issue #16050:
URL: https://github.com/apache/shardingsphere/issues/16050
## Bug Report
### Which version of ShardingSphere did you use?
master branch, commit 49f1fe687fb8a05f7ae5d1db9c7361bf3b567ecb
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy, scaling enabled.
Table `bmsql_district` is sharded with count `2`.
### Expected behavior
`show create table bmsql_district;` return correct SQL.
### Actual behavior
The `REFERENCES` part table name is not logic table, which cause error when
executing this SQL.
Ran on proxy:
```
mysql> show create table bmsql_district;
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bmsql_district | CREATE TABLE `bmsql_district` (
`d_w_id` int(11) NOT NULL,
`d_id` int(11) NOT NULL,
`d_ytd` decimal(12,2) DEFAULT NULL,
`d_tax` decimal(4,4) DEFAULT NULL,
`d_next_o_id` int(11) DEFAULT NULL,
`d_name` varchar(10) DEFAULT NULL,
`d_street_1` varchar(20) DEFAULT NULL,
`d_street_2` varchar(20) DEFAULT NULL,
`d_city` varchar(20) DEFAULT NULL,
`d_state` char(2) DEFAULT NULL,
`d_zip` char(9) DEFAULT NULL,
PRIMARY KEY (`d_w_id`,`d_id`),
CONSTRAINT `d_warehouse_fkey_bmsql_district_0` FOREIGN KEY (`d_w_id`)
REFERENCES `bmsql_warehouse_0` (`w_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
```
The above SQL is got from source data source, and will be executed in target
data source in scaling.
Exception:
```
[INFO ] 2022-03-14 09:44:19.900 [elasticjob-1671513513897145-2]
o.a.s.d.p.m.p.d.MySQLDataSourcePreparer - create target table 'bmsql_warehouse'
success
[INFO ] 2022-03-14 09:44:19.904 [elasticjob-1671513513897145-2]
o.a.s.d.p.c.p.d.AbstractDataSourcePreparer - execute target table sql: CREATE
TABLE IF NOT EXISTS `bmsql_district` (
`d_w_id` int(11) NOT NULL,
`d_id` int(11) NOT NULL,
`d_ytd` decimal(12,2) DEFAULT NULL,
`d_tax` decimal(4,4) DEFAULT NULL,
`d_next_o_id` int(11) DEFAULT NULL,
`d_name` varchar(10) DEFAULT NULL,
`d_street_1` varchar(20) DEFAULT NULL,
`d_street_2` varchar(20) DEFAULT NULL,
`d_city` varchar(20) DEFAULT NULL,
`d_state` char(2) DEFAULT NULL,
`d_zip` char(9) DEFAULT NULL,
PRIMARY KEY (`d_w_id`,`d_id`),
CONSTRAINT `d_warehouse_fkey_bmsql_district_0` FOREIGN KEY (`d_w_id`)
REFERENCES `bmsql_warehouse_0` (`w_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
[ERROR] 2022-03-14 09:44:20.189 [elasticjob-1671513513897145-2]
o.a.s.d.p.s.r.RuleAlteredJob - job prepare failed, 1671513513897145-1
[INFO ] 2022-03-14 09:44:20.189 [elasticjob-1671513513897145-2]
o.a.s.d.p.s.r.RuleAlteredJobSchedulerCenter - remove and stop 1671513513897145
[ERROR] 2022-03-14 09:44:20.206 [elasticjob-1671513513897145-2]
o.a.s.e.e.h.g.LogJobErrorHandler - Job '1671513513897145' exception occur in
job processing
org.apache.shardingsphere.data.pipeline.core.exception.PipelineJobPrepareFailedException:
prepare target tables failed.
at
org.apache.shardingsphere.data.pipeline.mysql.prepare.datasource.MySQLDataSourcePreparer.prepareTargetTables(MySQLDataSourcePreparer.java:59)
at
org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.prepareTarget(RuleAlteredJobPreparer.java:90)
at
org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJobPreparer.prepare(RuleAlteredJobPreparer.java:69)
at
org.apache.shardingsphere.data.pipeline.scenario.rulealtered.RuleAlteredJob.execute(RuleAlteredJob.java:51)
at
org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
at
org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
at
org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:172)
at
org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.lambda$process$0(ElasticJobExecutor.java:153)
at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at
com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
at
com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
at
com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Cannot add foreign key constraint
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449)
at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
at
com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.lambda$execute$14(ShardingSphereStatement.java:355)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement$2.executeSQL(ShardingSphereStatement.java:405)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement$2.executeSQL(ShardingSphereStatement.java:401)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:97)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:82)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
at
org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.doExecute(DriverJDBCExecutor.java:153)
at
org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:142)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:413)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:437)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:355)
at
org.apache.shardingsphere.data.pipeline.core.prepare.datasource.AbstractDataSourcePreparer.executeTargetTableSQL(AbstractDataSourcePreparer.java:61)
at
org.apache.shardingsphere.data.pipeline.mysql.prepare.datasource.MySQLDataSourcePreparer.prepareTargetTables(MySQLDataSourcePreparer.java:55)
... 14 common frames omitted
```
### Reason analyze (If you can)
Seems `show create table bmsql_district;` doesn't handle physical table name
in `REFERENCES`.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
Table with foreign key, use sharding feature.
e.g. BenchmarkSQL.
`run/sql.mysql/tableCreates.sql`:
```
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9),
constraint pk_warehouse primary key (w_id)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9),
constraint pk_district primary key (d_w_id, d_id)
);
```
`run/sql.common/foreignKeys.sql`:
```
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
```
### Example codes for reproduce this issue (such as a github link).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
