📋

Description

This error indicates that a non-integer value was supplied in a context where MariaDB strictly requires an integer. This often occurs when a numeric function, a column definition, or a system variable expects a whole number, but receives a floating-point number, a string, or another data type. It prevents the database from performing the intended operation due to a type mismatch.

💬

Error Message

Only integers allowed as number here

🔍

Known Causes

3 known causes

⚠️

Invalid Data Type in SQL Query

Attempting to insert, update, or use a non-integer value (e.g., a decimal or string) in a column or function argument that is defined to accept only integers.

⚠️

Non-Integer Argument for Function

Supplying a floating-point number or a string where a MariaDB built-in function or a stored procedure expects an integer as an argument.

⚠️

System Variable Type Mismatch

Setting a MariaDB system variable (e.g., `wait_timeout`, `max_connections`) to a non-integer value, such as a decimal or a string representation, during configuration or runtime.

🛠️

Solutions

3 solutions available

1. Correct Data Type in Column Definition easy

Ensure the column where you're inserting data is defined as an integer type.

1

Identify the table and column that is causing the error.

2

Check the current data type of the column using `DESCRIBE` or `SHOW CREATE TABLE`.

DESCRIBE your_table_name;
-- OR
SHOW CREATE TABLE your_table_name;

3

If the column is not an integer type (e.g., it's `VARCHAR`, `TEXT`, `FLOAT`, `DECIMAL`), alter the table to change it to an appropriate integer type like `INT`, `BIGINT`, `SMALLINT`, or `TINYINT`.

ALTER TABLE your_table_name MODIFY your_column_name INT;
-- Use BIGINT for larger numbers, SMALLINT for smaller, etc.

4

Re-attempt the `INSERT` or `UPDATE` operation with valid integer values.

2. Validate Input Before Insertion medium

Cleanse or validate data from your application before sending it to the database.

1

In your application code (e.g., Python, PHP, Java), before executing the SQL query, check if the value intended for an integer column is indeed an integer.

if isinstance(value_to_insert, int):
    # Proceed with SQL insertion
else:
    # Handle the error: log, prompt user, or cast if appropriate and safe

2

If the input is a string that *should* be an integer (e.g., '123'), attempt to cast it to an integer type within your application.

try:
    integer_value = int(string_representation)
    # Proceed with SQL insertion using integer_value
except ValueError:
    # Handle cases where the string cannot be converted to an integer

3

Alternatively, ensure that any numeric strings (like '0123') are correctly parsed as integers by your application's casting mechanisms.

3. Review and Correct SQL Statement easy

Inspect the SQL query for non-integer values being passed to integer columns.

1

Examine the `INSERT` or `UPDATE` statement that is generating the error.

2

Look for values that are not enclosed in single quotes (which typically denotes strings) but are also not valid integer literals. Examples include floating-point numbers (e.g., `123.45`), strings that look like numbers but are not quoted (e.g., `abc`), or expressions that evaluate to non-integers.

INSERT INTO your_table (int_column) VALUES ('abc'); -- Incorrect
INSERT INTO your_table (int_column) VALUES (123.45); -- Incorrect
INSERT INTO your_table (int_column) VALUES ('123'); -- Correct if the column is INT and '123' is acceptable
INSERT INTO your_table (int_column) VALUES (123); -- Correct

3

Correct the values to be valid integers or, if the column is intended to store non-integers, adjust the column's data type as per Solution 1.

🔗

Related Errors

5 related errors

MariaDB Error 1578: Expected Integer Value