Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Repeated column when use encrypt with JPA + Springboot #2884

Closed
KomachiSion opened this issue Aug 16, 2019 · 11 comments · Fixed by #3663 or #3809
Closed

Repeated column when use encrypt with JPA + Springboot #2884

KomachiSion opened this issue Aug 16, 2019 · 11 comments · Fixed by #3663 or #3809
Assignees
@kimmking
Labels
type: duplicate

Comments

@KomachiSion
Copy link
Member

Bug Report

Which version of ShardingSphere did you use?

4.0.0-RC2-SNAPSHOT

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

Insert encrypt successfully.

Actual behavior

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column 'user_name_plain' specified twice
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.executeUpdate(EncryptPreparedStatement.java:120)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
	... 23 more

Reason analyze (If you can)

Logic SQL: insert into t_user (assisted_query_pwd, pwd, user_name, user_name_plain, user_id) values (?, ?, ?, ?, ?)

actual SQL: insert into t_user (assisted_query_pwd, pwd, user_name, user_name_plain, user_id, assisted_query_pwd, user_name_plain) values (?, ?, ?, ?, ?, ?, ?)

SQL generated by JPA has included user_name_plain , but encrypt add user_name_plain again.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

clone example code.
run sharding-jdbc-example/other-feature-example/encrypt-example/encrypt-spring-boot-example/src/main/java/org/apache/shardingsphere/example/encrypt/table/spring/boot/SpringBootExample.java

Example codes for reproduce this issue (such as a github link).

https://github.com/apache/incubator-shardingsphere-example
@apache apache deleted a comment from yhsmy Aug 31, 2019
@RaigorJiang
Copy link
Contributor

Is this issue still there? I will study it.

@RaigorJiang
Copy link
Contributor

Update, it's now processing.

@KomachiSion
Copy link
Member Author

@RaigorJiang , Thanks for your contributions.

@RaigorJiang RaigorJiang mentioned this issue Dec 4, 2019
Merged
@RaigorJiang
Copy link
Contributor

RaigorJiang commented Dec 4, 2019 •
edited

By the way, the property <shardingsphere.version> need to upgrade to '4.0.0-RC4-SNAPSHOT' in
shardingsphere-example project.

@KomachiSion
Copy link
Member Author

@RaigorJiang , I saw the version has been upgrade in 6 days ago.

@RaigorJiang
Copy link
Contributor

@KomachiSion I see, thanks.

@terrymanu terrymanu added this to the 4.0.0-Release milestone Dec 5, 2019
avalon566 pushed a commit that referenced this issue Dec 18, 2019
@KomachiSion @avalon566
Sharding scaling synchronize main branch (#3754)
a41f437 
* Correct mismatched comments (#3594)

* #3587, cache DataSourceMetaData (#3588)

* #3587, cache DataSourceMetaData

* #3587, add test case

* fixes apollo debug & warn log, control log out by slf4j xml file. (#3583)

* fixes apollo debug & warn log.

* Move jul-to-slf4j -> root pom.

* fixes jul-to-slf4j scope -> compile.

* remove maven default scope.

* Update snowflake algorithm to support modulus of 2^n  (#3595)

* update vibration offset

* add vibration offset test case

* update vibration offset

* update vibration offset

* modify vibration offset test case

* modify vibration offset test case

* Refactor metadata (#3604)

* decouple DatabaseType and DataSourceInfo

* fix use == for string equals

* decouple DataSourceMetaData and DataSourceInfo

* fix javadoc

* refactor LogicSchema.getDataSourceInfoMap()

* reuse DatabaseAccessConfiguration and remove DataSourceInfo

* refactor SQLExecuteCallback

* change TableMetaDataInitializer's getTables parameter error. (#3589)

* Modify SnowflakeShardingKeyGeneratorTest Unit-test case (#3610)

* make all pom files consist (#3614)

* make consist with pom

* make consist with pom

* adjust dependency sequence of ui-backend

* for code style

* ignore db test (#3615)

* simplify test case assertLastDigitalOfGenerateKeySameMillisecond (#3616)

* support SELECT DATABASE() (#3622)

* Fixes #3627 (#3629)

* move StatementExecuteUnit to sql package

* move ShardingExecuteEngine to engine package

* for code style

* rename QueryResultBasedQueryResultFixture and ResultSetBasedQueryResultFixture

* refactor DistinctQueryResult.getQueryRows()

* skip distinct merge for group by and aggregation only

* add DQLMergeEngine.isNeedProcessOrderBy

* use group by to merge distinct row

* add DQLMergeEngine.setGroupByForDistinctRow

* remove useless SelectItemsSegment.findSelectItemSegments

* code style for AggregationDistinctQueryResult

* code style for AggregationDistinctQueryResult

* remove DistinctQueryResult.divide()

* remove useless DistinctQueryResult

* use group by to process aggregation distinct

* remove useless AggregationDistinctQueryResult

* remove useless AggregationDistinctQueryMetaData

* remove useless QueryResult.getQueryResultMetaData()

* remove useless QueryRow.distinctColumnIndexes

* remove useless QueryRow.distinctColumnIndexes

* remove useless QueryRow.@EqualsAndHashCode

* remove useless QueryRow

* adjust constructor seq of DQLMergeEngine

* refactor DQLMergeEngine's constructor

* move ProjectionsContext.getColumnLabels() to ProjectionsContextEngine

* remove DQLMergeEngine.tableMetas

* refactor DQLMergeEngine

* code style for ProjectionsContextEngine

* #3550, update SERVER_VERSION of Sharding-Proxy (#3632)

* Add maxVibrationOffset to LeafSnowflakeKeyGenerator (#3636)

* #3640, fix missing milliseconds when reading (#3643)

* #3640, fix missing milliseconds when reading

* #3640, for test case

* Uniform naming style for properties (#3649)

* Decouple sharding-core-execute module and encrypt (#3654)

* remove getter of StreamQueryResult.queryResultMetaData

* refactor MemoryQueryResult

* refactor StreamQueryResult

* refactor StreamQueryResult

* refactor MemoryQueryResult

* refactor QueryResultMetaData

* move decrypt from MemoryQueryResult & StreamQueryResult to EncryptResultSet, ShardingResultSet & JDBCDatabaseCommunicationEngine

* remove useless SuppressWarnings

* adjust method sequence of QueryResult

* use MergedResult.getValue(columnIndex) instead of MergedResult.getValue(columnLabel)

* remove useless MergedResult.getValue(columnLabel)

* remove useless QueryResult.getValue(columnLabel)

* decouple QueryResultMetaData and queryWithCipherColumn

* remove QueryResultMetaData.queryWithCipherColumn

* refactor QueryResultMetaData.findShardingEncryptorWithTable

* remove QueryResultMetaData.getShardingEncryptor

* remove QueryResultMetaData.columnLabelAndIndexes

* remove QueryResultMetaData

* remove QueryResultUtil

* Update docker java version (#3652)

* Update docker java version

* Revert "Update docker java version"

This reverts commit 16c913b.

* Change postgresql driver version

* Remove QueryResult‘s fixture from merge test case (#3661)

* remove useless throw exception for MergeEngineFactory

* remove useless throw exception for MergeEngineFactoryTest

* decouple MergeEngineFactoryTest and ResultSetBasedQueryResultFixture

* decouple DALMergeEngineTest and ResultSetBasedQueryResultFixture

* decouple ShowCreateTableMergedResultTest and ResultSetBasedQueryResultFixture

* decouple ShowDatabasesMergedResultTest and ResultSetBasedQueryResultFixture

* decouple ShowOtherMergedResultTest and ResultSetBasedQueryResultFixture

* decouple ShowTablesMergedResultTest and ResultSetBasedQueryResultFixture

* refactor LogicTablesMergedResult's constructor

* decouple DQLMergeEngineTest and ResultSetBasedQueryResultFixture

* decouple StreamMergedResultTest and ResultSetBasedQueryResultFixture

* decouple MemoryQueryResultRowTest and ResultSetBasedQueryResultFixture

* decouple MemoryMergedResultTest and ResultSetBasedQueryResultFixture

* decouple GroupByMemoryMergedResultTest and ResultSetBasedQueryResultFixture

* decouple GroupByRowComparatorTest and ResultSetBasedQueryResultFixture

* decouple GroupByStreamMergedResultTest and ResultSetBasedQueryResultFixture

* decouple GroupByValueTest and ResultSetBasedQueryResultFixture

* decouple IteratorStreamMergedResultTest and ResultSetBasedQueryResultFixture

* decouple OrderByStreamMergedResultTest and ResultSetBasedQueryResultFixture

* decouple OrderByValueTest and ResultSetBasedQueryResultFixture

* decouple LimitDecoratorMergedResultTest and ResultSetBasedQueryResultFixture

* decouple RowNumberDecoratorMergedResultTest and ResultSetBasedQueryResultFixture

* decouple TopAndRowNumberDecoratorMergedResultTest and ResultSetBasedQueryResultFixture

* remove ResultSetBasedQueryResultFixture

* decouple DescribeTableMergedResultTest and QueryResultBasedQueryResultFixture

* remove QueryResultBasedQueryResultFixture

* Decouple hook and route dependency for execute module (#3665)

* decouple RouteUnit and SQLExecutionHook

* decouple RouteUnit and StatementExecuteUnit

* refactor SQLExecuteCallback.executeSQL

* decouple RouteUnit and SQLExecutePrepareCallback

* refactor SQLExecutePrepareCallback.createStatementExecuteUnit()

* parse sqlserver keyword (#3667)

* ci test (#3669)

* Fixes #3671 (#3672)

* remove useless constructor for ShowDatabasesMergedResult

* refactor ShowDatabasesMergedResult

* hide plain column for desc table

* fix test case

* Simplify memory merged result  (#3682)

* for code style

* pull up currentResultSetRow to MemoryMergedResult

* remove useless labelAndIndexMap for MemoryMergedResult

* refactor MemoryMergedResult.init()

* pull up setCurrentResultSetRow to MemoryMergedResult

* remove ShowIndexMergedResult, ShowTablesMergedResult & ShowTableStatusMergedResult

* Fixes #2884 (#3663)

* Fixes #2884
* Fixes #2884, add test cases;
* Format adjustment.
* End file with a blank line.

* load-table-metadata (#3684)

* optimize orchestration datasource (#3685)

* Decouple encrypt and sharding with ResultSet (#3688)

* remove ShardingRule.isRoutingByHint()

* refactor AbstractDataSourceAdapter.close()

* remove useless getter for AbstractConnectionAdapter

* decouple encrypt and sharding from ResultSet

* clear code,delete no use empty judgment code. (#3690)

* change TableMetaDataInitializer's getTables parameter error.

* clear code,delete no use empty judgment code.

* for code style (#3689)

* fixes #3701, refactor ColumnMetaData class, remove notNull & autoIncrement fields. (#3705)

* Standardize of merge engnie (#3709)

* add LocalDataMergedResult

* remove ShowDatabasesMergedResult's constructor of queryResults

* refactor LocalDataMergedResult

* add LocalDataMergedResult & SingleLocalDataMergedResult

* remove LocalMergedResultAdapter

* rename MergedResultMetaData to EncryptorMetaData

* rename ShowOtherMergedResult to TransparentMergedResult

* refactor test case for TransparentMergedResultTest

* refactor SingleLocalDataMergedResultTest

* add MultipleLocalDataMergedResultTest

* add package name of encrypt.dal and encrypt.dql

* rename EncryptMergeEngine to DQLEncryptMergeEngine

* add generic for MemoryMergedResult

* refactor EncryptResultSet's field as final

* add desc table for encrypt only

* refactor desc table for sharding + encrypt

* remove DescribeTableMergedResult from DALMergeEngine

* statement function strtodate (#3718)

* Refactor: update a tiny issue (#3714)

* Statement function base64 aesencrypt (#3726)

* statement function base64 aesencrypt

* statement function base64 aesencrypt

* statement function base64 aesencrypt

* statement function base64 aesencrypt

* #3725, fix MemoryQueryResult wrong data type (#3728)

* Encrypt statement column (#3715)

* encrypt-statement-column

* encrypt statement column

* encrypt statement column

* encrypte statement column

* Check tableRule and throw error information friendly (#3734)

* Encrypt select statement in operator (#3733)

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* encrypt statement in operator

* Decouple Rewrite module with sharding and encrypt (#3741)

* move getLogicAndActualTables from BaseShardingEngine to ShardingSQLRewriteEngine

* refactor ShardingSQLRewriteEngine

* add DefaultSQLBuilder and ShardingSQLBuilder

* refactor DefaultSQLBuilder & ShardingSQLBuilder's input params

* add AbstractSQLBuilder

* split Alterable to LogicAndActualTablesAware and RoutingUnitAware

* remove useless toString

* split InsertValuesToken to ShardingInsertValuesToken & EncryptInsertValuesToken

* Add Multilingual Settings (#3708)

* feature[sharding-ui-frontend]:Support multilingual presentation #3266

* refactor[sharding-ui-frontend]:Improve translation content

* [sharding-ui-frontend]:Add Multilingual Settings

* feature[sharding-ui-frontend]:Keep the name unified

* Add new module shardingsphere-sql-rewriter (#3746)

* add new module shardingsphere-sql-rewriter

* rename package from org.apache.shardingsphere.core.rewrite ot org.apache.shardingsphere.sql.rewriter

* rename package from org.apache.shardingsphere.core.rewrite ot org.apache.shardingsphere.sql.rewriter

* add module shardingsphere-sql-rewriter-test

* remove useless jaxb entity

* rename EncryptRewriteAssertionsRootEntityLoader to RewriteAssertionsRootEntityLoader

* refactor EncryptSQLRewriteEngineParameterizedTest

* refactor EncryptSQLRewriteEngineParameterizedTest

* add SQLRewriteEngineTestParameters

* add AbstractSQLRewriteEngineParameterizedTest

* refactor AbstractSQLRewriteEngineParameterizedTest.testParameters

* add SQLRewriteEngineTestParameters.type

* remove useless getter for AbstractSQLRewriteEngineParameterizedTest

* rename SQLRewriteEngineParameterizedTest to SQLRewriterParameterizedTest

* remove useless param from AbstractSQLRewriterParameterizedTest

* rename AbstractSQLRewriterParameterizedTest.createSQLRewriteResults()

* refactor EncryptSQLRewriterParameterizedTest

* refactor EncryptSQLRewriterParameterizedTest

* refactor pom of shardingsphere-sql-rewriter

* use RelationMetas instead of TableMetas in rewriter module

* add InsertValue

* add ShardingInsertValue

* decouple sharding-core-common & shardingsphere-sql-rewriter

* rename package from org.apache.shardingsphere.sql.rewriter.feature to org.apache.shardingsphere.sql.rewriter

* replace removed datasourceInfo
@KomachiSion KomachiSion mentioned this issue Dec 26, 2019
Merged
@KomachiSion KomachiSion reopened this Dec 26, 2019
@KomachiSion
Copy link
Member Author

PR#3663 will cause new issue #3770 , so revert this PR and reopen this issue.

I think we should fix this issue with implementing the DDL of encrypt.

@KomachiSion KomachiSion reopened this Dec 27, 2019
@KomachiSion KomachiSion modified the milestones: 4.0.0-Release, 5.0.0 Dec 27, 2019
@terrymanu
Copy link
Member

Fixed at #3340

@terrymanu terrymanu removed this from the 5.0.0 milestone Mar 18, 2020
@kimmking kimmking reopened this May 9, 2020
@kimmking
Copy link
Member

kimmking commented May 9, 2020

It still here.

@kimmking kimmking self-assigned this Jun 17, 2020
@kimmking
Copy link
Member

repeated column is okay now.
but example can't be running now.
I will fix it.

@IceOutMan
Copy link

repeated column is okay now.
but example can't be running now.
I will fix it.

4.1.0 still has it

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@kimmking kimmking

Labels
type: duplicate
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fixes #2884
Revert PR#3663 KomachiSion/incubator-shardingsphere
6 participants
@kimmking @terrymanu @RaigorJiang @IceOutMan @tristaZero @KomachiSion