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 2020/03/18 14:41:23 UTC
[GitHub] [incubator-shardingsphere] fantacy2001 opened a new issue #4832:
insert...ON DUPLICATE KEY UPDATE error
fantacy2001 opened a new issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832
when execute sql that likes follow
INSERT INTO stock_age (
tenant_id,
warehouse_no,
sku_no,
lot_no,
package_no,
owner_no,
product_level,
md5_value,
qty
)
VALUES
(
'T123456',
'26',
'S011',
'1',
'1',
'ownerNo',
'1',
'asdsdfdfdfd',
5
)
ON DUPLICATE KEY
UPDATE
qty = IFNULL(qty, 0) + 1
report error:
### Cause: java.sql.SQLException: No value specified for parameter 10
; bad SQL grammar []; nested exception is java.sql.SQLException: No value specified for parameter 10
Caused by: java.sql.SQLException: No value specified for parameter 10
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2281)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2261)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2191)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1165)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:64)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:60)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:79)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:61)
at org.apache.shardingsphere.core.execute.engine.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:125)
at org.apache.shardingsphere.core.execute.engine.ShardingExecuteEngine.parallelExecute(ShardingExecuteEngine.java:102)
at org.apache.shardingsphere.core.execute.engine.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:83)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:73)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:56)
at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:140)
at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:153)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:192)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
Which version of ShardingSphere did you use?
4.0.1
Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-601739138
> > @geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
>
> this sql has modified:
> ON DUPLICATE KEY
> UPDATE
> qty = values(qty) + 1
> success.
I also tested it successful. So Can close this issue?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-604353780
> @geomonlin @fantacy2001
> So What's the conclusion?
Sorry, didn't notice the message,
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] fantacy2001 commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-600983114
> @fantacy2001
> Hi how about sharding rule configuration?
only split db
<sharding:standard-strategy id="databaseStrategy" sharding-column="tenant_id" precise-algorithm-ref="commonDBShardingAlgorithm"/>
<sharding:table-rule logic-table="stock_age" actual-data-nodes="ds_ms$->{0..1}.stock_age" database-strategy-ref="databaseStrategy" />
<sharding:master-slave-rules>
<sharding:master-slave-rule id="ds_ms0" master-data-source-name="ds_0" slave-data-source-names="ds_0_slave_0, ds_0_slave_1" strategy-ref="roundStrategy" />
<sharding:master-slave-rule id="ds_ms1" master-data-source-name="ds_1" slave-data-source-names="ds_1_slave_0, ds_1_slave_1" strategy-ref="roundStrategy" />
</sharding:master-slave-rules>
the commonDBShardingAlgorithm is mapping by tenant_id, like
map.put("tenant_id","the suffix of dbname")
map.put("CP0000028","1");
map.put("CP0000030","1");
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] fantacy2001 commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-602168198
> > > @geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
> >
> >
> > this sql has modified:
> > ON DUPLICATE KEY
> > UPDATE
> > qty = values(qty) + 1
> > success.
> > I also tested it successful. So Can close this issue?
please close it ，thank you！
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-602318337
@tristaZero Hi, This can be close
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tristaZero commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-601510639
@geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tristaZero commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-602330456
@geomonlin @fantacy2001
So What's the conclusion?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tristaZero commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-601510206
@fantacy2001 Did you use spring Namespace as the client? Or Original JDBC interface?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tristaZero commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-600960581
@fantacy2001
Hi how about sharding rule configuration?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-601545430
> > @geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
>
> this sql has modified:
> ON DUPLICATE KEY
> UPDATE
> qty = values(qty) + 1
> success.
OK
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-605825176
Will close soon if no update anymore
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] fantacy2001 commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-601542617
> @geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
this sql has modified:
ON DUPLICATE KEY
UPDATE
qty = values(qty) + 1
success.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-604355890
> > > > @geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
> > >
> > >
> > > this sql has modified:
> > > ON DUPLICATE KEY
> > > UPDATE
> > > qty = values(qty) + 1
> > > success.
> > > I also tested it successful. So Can close this issue?
>
> please close it ，thank you！
What's the cause of the error? Is it convenient to tell me
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-601545448
> @geomonlin Hi, could you give this issue a simple test whether the SQL mentioned can run well in our `Sharding-example` project?
ok
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tristaZero commented on issue #4832:
insert...ON DUPLICATE KEY UPDATE error
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #4832: insert...ON DUPLICATE KEY UPDATE error
URL: https://github.com/apache/incubator-shardingsphere/issues/4832#issuecomment-604804638
@fantacy2001 Hi, could you tell us what is the solution of your Issue? We need to give this issue a final answer.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services