search

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.
Apache License 2.0
19.94k stars 6.74k forks source link

Why does the INSERT SQL have no routing #3840

Closed beijixi closed 4 years ago

beijixi commented 4 years ago

Question

sql:
INSERT INTO LDPerson(PersonID,CustomerNo,Name,Sex,Birthday,IDType,IDNo,Password,NativePlace,Nationality,RgtAddress,Marriage,MarriageDate,Health,Stature,Avoirdupois,Degree,CreditGrade,OthIDType,OthIDNo,ICNo,GrpNo,JoinCompanyDate,StartWorkDate,Position,Salary,OccupationType,OccupationCode,WorkType,PluralityType,DeathDate,SmokeFlag,BlacklistFlag,Proterty,Remark,State,VIPValue,Operator,MakeDate,MakeTime,ModifyDate,ModifyTime,GrpName,License,LicenseType,IDValidate,Socialinsuno) VALUES(101802,'12323123' ,'jason', 0 ,'1999-01-01',0, '22232223' , null , null , null , null , null , null , null , 0.0 , 0.0 , null , null , null , null , null , null , null , null , null , 0.0 , null , null , null , null , null , null , null , null , null , '1' , null , 'cbs' , '2019-12-30' , '18:26:52' , '2019-12-30' , '18:26:52' , null , null , null , null , null); sharding-config: LDPerson:   actualDataNodes: ds${0..1}.LDPerson   databaseStrategy:     inline:       shardingColumn: PersonID       algorithmExpression: ds${PersonID % 2}

description:   No matter which field is used as the route key,the data exists in ds0 and ds1

terrymanu commented 4 years ago

What is your version? Can you provide your sql.show?

beijixi commented 4 years ago

version:4.0.0-RC1

sql.show: Logic SQL: INSERT INTO LDPerson(PersonID,CustomerNo,Name,Sex,Birthday,IDType,IDNo,Password,NativePlace,Nationality,RgtAddress,Marriage,MarriageDate,Health,Stature,Avoirdupois,Degree,CreditGrade,OthIDType,OthIDNo,ICNo,GrpNo,JoinCompanyDate,StartWorkDate,Position,Salary,OccupationType,OccupationCode,WorkType,PluralityType,DeathDate,SmokeFlag,BlacklistFlag,Proterty,Remark,State,VIPValue,Operator,MakeDate,MakeTime,ModifyDate,ModifyTime,GrpName,License,LicenseType,IDValidate,Socialinsuno) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)

SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=LDPerson, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=LDPerson, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=INSERT INTO LDPerson(PersonID,CustomerNo,Name,Sex,Birthday,IDType,IDNo,Password,NativePlace,Nationality,RgtAddress,Marriage,MarriageDate,Health,Stature,Avoirdupois,Degree,CreditGrade,OthIDType,OthIDNo,ICNo,GrpNo,JoinCompanyDate,StartWorkDate,Position,Salary,OccupationType,OccupationCode,WorkType,PluralityType,DeathDate,SmokeFlag,BlacklistFlag,Proterty,Remark,State,VIPValue,Operator,MakeDate,MakeTime,ModifyDate,ModifyTime,GrpName,License,LicenseType,IDValidate,Socialinsuno) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[], values=[])

Actual SQL: ds0 ::: INSERT INTO LDPerson(PersonID,CustomerNo,Name,Sex,Birthday,IDType,IDNo,Password,NativePlace,Nationality,RgtAddress,Marriage,MarriageDate,Health,Stature,Avoirdupois,Degree,CreditGrade,OthIDType,OthIDNo,ICNo,GrpNo,JoinCompanyDate,StartWorkDate,Position,Salary,OccupationType,OccupationCode,WorkType,PluralityType,DeathDate,SmokeFlag,BlacklistFlag,Proterty,Remark,State,VIPValue,Operator,MakeDate,MakeTime,ModifyDate,ModifyTime,GrpName,License,LicenseType,IDValidate,Socialinsuno) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) ::: [101901, 0000050380, Jason, 0, 1999-01-01T00:00:00.000+0800, 0, 212365432, null, null, null, null, null, null, null, 0.0, 0.0, null, null, null, null, null, null, null, null, null, 0.0, null, null, null, null, null, null, null, null, null, 1, null, cbs, 2019-12-31T00:00:00.000+0800, 12:14:10, 2019-12-31T00:00:00.000+0800, 12:14:10, null, null, null, null, null]

Actual SQL: ds1 ::: INSERT INTO LDPerson(PersonID,CustomerNo,Name,Sex,Birthday,IDType,IDNo,Password,NativePlace,Nationality,RgtAddress,Marriage,MarriageDate,Health,Stature,Avoirdupois,Degree,CreditGrade,OthIDType,OthIDNo,ICNo,GrpNo,JoinCompanyDate,StartWorkDate,Position,Salary,OccupationType,OccupationCode,WorkType,PluralityType,DeathDate,SmokeFlag,BlacklistFlag,Proterty,Remark,State,VIPValue,Operator,MakeDate,MakeTime,ModifyDate,ModifyTime,GrpName,License,LicenseType,IDValidate,Socialinsuno) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) ::: [101901, 0000050380, Jason, 0, 1999-01-01T00:00:00.000+0800, 0, 212365432, null, null, null, null, null, null, null, 0.0, 0.0, null, null, null, null, null, null, null, null, null, 0.0, null, null, null, null, null, null, null, null, null, 1, null, cbs, 2019-12-31T00:00:00.000+0800, 12:14:10, 2019-12-31T00:00:00.000+0800, 12:14:10, null, null, null, null, null]

terrymanu commented 4 years ago

Any exception or warning occur?

terrymanu commented 4 years ago

I guess in your SQL have some keyword of MySQL, such as Password, Position. Because of 4.0.0-RC1 is not stable, so can you try 4.0.0-RC3?

beijixi commented 4 years ago

Thanks I add the reference symbol to all the columns，It has been running normally.