You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/09/07 02:00:45 UTC
[GitHub] [shardingsphere] yanghaoyu opened a new issue #12249: Duplicate foreign keys when creating sub-table data
yanghaoyu opened a new issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249
### Which version of ShardingSphere did you use?
master branch(5.0.0-RC1)
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
create CREDENTIAL table successfully, in actual table,this is four table on mysql server
### Actual behavior
o.a.s.p.f.c.CommandExecutorTask - Exception occur:com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can't write; duplicate key in table 'credential_1'
### Reason analyze (If you can)
The foreign key name was not changed when the table was split
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
schemaName: test
dataSources:
ds_0:
url: jdbc:mysql://db:3306/test?useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
CREDENTIAL:
actualDataNodes: ds_0.CREDENTIAL_${0..3}
tableStrategy:
standard:
shardingColumn: ID
shardingAlgorithmName: credential_inline
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
bindingTables:
- CREDENTIAL
shardingAlgorithms:
credential_inline:
type: INLINE
props:
algorithm-expression: CREDENTIAL_${Math.abs(ID.hashCode() % 4)}
keyGenerators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
```
Create database statement is
CREATE TABLE `CREDENTIAL` (
`ID` varchar(36) NOT NULL,
`SALT` tinyblob,
`TYPE` varchar(255) DEFAULT NULL,
`USER_ID` varchar(36) DEFAULT NULL,
`CREATED_DATE` bigint(20) DEFAULT NULL,
`USER_LABEL` varchar(255) DEFAULT NULL,
`SECRET_DATA` longtext,
`CREDENTIAL_DATA` longtext,
`PRIORITY` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_USER_CREDENTIAL` (`USER_ID`),
CONSTRAINT `FK_PFYR0GLASQYL0DEI3KL69R6V0` FOREIGN KEY (`USER_ID`) REFERENCES `USER_ENTITY` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-1046266756
I just close this issue because of so long time without feedback.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-914905884
@yanghaoyu Thanks, I am curious what you expected for `foreign Key` is just for `binding Table` or any sharding tables?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-915043174
Using alter-statements to add FK with different name to three physical tables.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yanghaoyu commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
yanghaoyu commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-914960109
Are there any plans to support foreign keys?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yanghaoyu commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
yanghaoyu commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-918190940
Hi @tristaZero When the table is in different instances, do I configure the corresponding broadcast table?
I’m just a beginner, that’s a suggestion。
English level is average, please forgive me for Google Translate
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu closed issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-914888429
Hi @yanghaoyu
Thank you for your report, foreign keys may not be supported yet.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yanghaoyu commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
yanghaoyu commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-914944196
@tristaZero Thanks，
I am using open source software. The database tables are prepared in advance and contain a lot of foreign keys, but I want to store a lot of data, so I want to try the table splitting function
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-917599403
Hi your case, i.e, `bindTables` exist in the same instance, so I think `foreign key` works.
But once tables locates in different instances, I guess we need a huge work. What do you think? @yanghaoyu
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #12249: Duplicate foreign keys when creating sub-table data
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #12249:
URL: https://github.com/apache/shardingsphere/issues/12249#issuecomment-919671444
Hi, @yanghaoyu No worries about your English, I can get your point. ;-)
Actually `broadcast` is another way to support `foreign key`, but you'd better give a look at [its explanation](https://shardingsphere.apache.org/document/5.0.0-beta/cn/features/sharding/concept/sql/) to check whether it fits your case.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org