<p>MySQL raises error 1691 when a LIMIT clause uses a stored procedure variable that is not an integer type.</p>
<p>MySQL Error 1691: ER_WRONG_SPVAR_TYPE_IN_LIMIT occurs when the LIMIT clause receives a variable that is not integer-based. Cast the variable to INT or redefine it as INT to resolve the issue.</p>
A variable of a non-integer based type in LIMIT clause
MySQL throws error 1691 when the LIMIT clause receives a variable whose data type is not integer-based. The parser expects INT, BIGINT, or another numeric integer type. Using VARCHAR, DECIMAL, or DATE triggers the failure.
This error appears most often inside stored procedures where LIMIT parameters are passed as variables. The server validates types at execution time and stops the query to prevent ambiguous row counts.
The primary cause is supplying a non-integer variable to LIMIT or OFFSET. MySQL only allows integer literals or integer expressions in LIMIT.
Another trigger is assigning dynamic SQL results into a user variable declared as VARCHAR and then reusing that variable in LIMIT.
First confirm the variable type with SHOW CREATE PROCEDURE or a DESCRIBE statement. If it is not INT, change the declaration to INT.
When the variable must remain non-integer elsewhere, cast it to INT in the LIMIT clause: LIMIT CAST(v_limit AS UNSIGNED).
Stored procedure loops often pass a VARCHAR cursor index to LIMIT. Redefine that index as INT to solve the problem.
Dynamic paging that stores a user-supplied page size in a TEXT variable fails; switch to UNSIGNED INT or cast on use.
Always declare pagination and offset variables as UNSIGNED INT. Validate user input before assignment.
Use strict SQL mode and parameterized queries in an IDE like Galaxy, which highlights type mismatches during editing.
Error 1064 appears for general syntax mistakes; unlike 1691, it does not pinpoint type issues in LIMIT.
Error 1264 occurs when inserting out-of-range integers; ensure casting does not overflow when fixing 1691.
The LIMIT clause receives a VARCHAR or DECIMAL variable declared in the procedure header.
Dynamic paging size is stored in a TEXT user variable and reused directly in LIMIT.
STRICT mode prevents automatic conversion from string to integer, exposing the type mismatch.
A developer mislabels the second LIMIT parameter, leading to error 1691 at runtime.
General SQL syntax mistake; differs from 1691 by lacking explicit type guidance.
Occurs when an integer conversion overflows; may appear after casting in 1691 fixes.
Raised when an expression returns an unsupported type for a function parameter.
No. LIMIT accepts only integer values. Cast DECIMAL to INT or redefine the variable.
Yes. STRICT modes disable implicit casts, making type mistakes surface as error 1691.
Using prepared statements with integer bind types prevents non-integer values from reaching LIMIT.
Galaxy highlights type mismatches in real time and suggests INT casting, reducing runtime errors.
.avif)
.avif){kind=logo}
