Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 升级v7.1.0后sql执行异常
To improve efficiency, please provide the following information. Clear problem descriptions can lead to quicker resolutions:
[Background] Upgraded from version v6.1.1 to v7.1.0
[Issue] See screenshot, it was okay before the upgrade, but after the upgrade, it reports an error: Incorrect arguments to LIMIT
[Business Impact] Testing on the development station, no major impact for now
[TiDB Version] v7.1.0
[Attachments] Relevant logs and monitoring
Why do you need to use BigDecimal for limit? Print out the assembled SQL and take a look.
This is already the complete SQL: select REC_ID from ORIG.TB_A0013 limit ?
As for why BigDecimal is used, it is probably a historical issue, involving many aspects, and it is not easy to change for now.
It doesn’t seem like there should be any issues. select REC_ID from ORIG.TB_A0013 limit 10 - Can you set a breakpoint and debug to see if there’s anything unusual with the variable sql?
Directly using numbers without binding parameters is not a problem.
The parameters used when executing the query are not quite right. Try executing it manually to see.
There is no problem executing statements without parameters directly.
For example: select REC_ID from ORIG.TB_A0013 limit 10000
Enable database logging to see what this database has received.
Could you please advise on the specific methods to open and view logs? Thank you.
This is the IDE side, can you check TiDB’s own logs?
You can find it, please provide the query method.
set @@tidb_general_log = true;
Then check the tidb.log, the default location is /tidb-deploy/tidb-4000/log.
Note: Run this in a test environment, don’t try it casually in a production environment.
Thanks, I am in a development environment here. I will try it tomorrow and provide feedback once I have results.
It is best to either connect to fixed TiDB nodes or leave just one TiDB node to facilitate log viewing.
Would using BigDecimal in the database result in this?
SELECT * FROM bilibili_user LIMIT 10.0;
The result is out, and it matches the error reported to the frontend. The parameters passed in also look normal.
The parameter after limit is incorrect.
No, it seems that it was transmitted later. It is estimated that the new version has stricter requirements for this type. Try changing BigDecimal to the corresponding JAVA class for int.
I checked the parameters and they are fine. If the parameters are changed to Integer, it also works fine. Is there any official documentation on the new version’s strict type requirements? Otherwise, we wouldn’t know what else to watch out for. Additionally, with official documentation, we can plan targeted modifications for development.
I tested both decimals and strings separately, and it is indeed as I suspected; the type requirements have become stricter.
In v6.4.0, everything is still fine.
root[test]>select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.4.0 |
+--------------------+
1 row in set (0.01 sec)
root[test]>
root[test]>PREPARE st FROM 'SELECT * FROM t limit ?';
Query OK, 0 rows affected (0.01 sec)
root[test]>SET @a='1';
Query OK, 0 rows affected (0.01 sec)
root[test]>EXECUTE st USING @a;
+---+------+
| a | b |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.01 sec)
root[test]>PREPARE st FROM 'SELECT * FROM t limit ?';
Query OK, 0 rows affected (0.01 sec)
root[test]>SET @a=1.0;
Query OK, 0 rows affected (0.01 sec)
root[test]>EXECUTE st USING @a;
+---+------+
| a | b |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.01 sec)
In v7.1.0, there are issues, and it directly reports an error:
-- int passed as string
[root@127.0.0.1][test][02:10:39]> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v7.1.0 |
+--------------------+
1 row in set (0.00 sec)
[root@127.0.0.1][test][02:14:42]> PREPARE st FROM 'SELECT * FROM t1 limit ?';
Query OK, 0 rows affected (0.00 sec)
[root@127.0.0.1][test][02:14:52]> SET @a='1';
Query OK, 0 rows affected (0.00 sec)
[root@127.0.0.1][test][02:14:52]> EXECUTE st USING @a;
ERROR 1210 (HY000): Incorrect arguments to LIMIT
-- Same issue with decimal.
[root@127.0.0.1][test][02:14:52]> PREPARE st FROM 'SELECT * FROM t1 limit ?';
Query OK, 0 rows affected (0.01 sec)
[root@127.0.0.1][test][02:18:32]> SET @a=1.0;
Query OK, 0 rows affected (0.00 sec)
[root@127.0.0.1][test][02:18:32]> EXECUTE st USING @a;
ERROR 1210 (HY000): Incorrect arguments to LIMIT
There is indeed no official documentation on this.
It reminds me that there are quite a few scenarios where int type variables are passed as strings.
I did another test, and fortunately, it only gives a warning.
[root@127.0.0.1][test][02:18:33]> desc t1;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | varchar(10) | YES | | NULL | |
| d | varchar(20) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
4 rows in set (0.00 sec)
[root@127.0.0.1][test][02:22:12]>
[root@127.0.0.1][test][02:22:14]> PREPARE st FROM 'SELECT * FROM t1 where a=?';
Query OK, 0 rows affected (0.00 sec)
[root@127.0.0.1][test][02:22:49]> SET @a='1';
Query OK, 0 rows affected (0.00 sec)
[root@127.0.0.1][test][02:22:49]> EXECUTE st USING @a;
+------+---------+-------+------------+
| a | b | c | d |
+------+---------+-------+------------+
| 1 | 3668850 | 095dc | 5dea14941a |
+------+---------+-------+------------+
1 row in set, 1 warning (0.04 sec)
[root@127.0.0.1][test][02:22:49]> show warnings;
+---------+------+-------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------+
| Warning | 1105 | skip prepared plan-cache: '1' may be converted to INT |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)
|
username: porpoiselxj
|
username: 
|
username: 
|
username: 
|
username: 
|
username: