📋

Description

This error occurs when defining `FLOAT(M,D)`, `DOUBLE(M,D)`, or `DECIMAL(M,D)` column types in MariaDB. It indicates that the specified total number of digits (M, precision) is less than the number of digits allowed after the decimal point (D, scale). MariaDB requires that M must always be greater than or equal to D for these data types.

💬

Error Message

For float(M,D, double(M,D or decimal(M,D, M must be >= D (column '%s').

🔍

Known Causes

3 known causes

⚠️

Precision Less Than Scale

The column definition explicitly sets the precision (M) to be a value smaller than the scale (D), which violates MariaDB's numeric type constraints.

⚠️

Misinterpretation of M, D Parameters

Users sometimes misunderstand 'M' as the number of digits before the decimal point, rather than the total number of digits for the number.

⚠️

Invalid Schema Copy-Paste

The column definition might have been copied from a different database system or an erroneous source without validating MariaDB's specific requirements for numeric types.

🛠️

Solutions

3 solutions available

1. Correct Invalid Precision/Scale in `CREATE TABLE` Statement easy

Modify the `CREATE TABLE` statement to ensure `M` is always greater than or equal to `D` for numeric types.

1

Locate the `CREATE TABLE` statement that is causing the error.

2

Identify the column definition for `FLOAT`, `DOUBLE`, or `DECIMAL` that has an invalid precision (`M`) and scale (`D`). Remember that for `FLOAT` and `DOUBLE`, precision `M` is optional and refers to the number of digits, while scale is not applicable. For `DECIMAL(M,D)`, `M` is the total number of digits, and `D` is the number of digits after the decimal point.

3

Adjust the precision (`M`) and scale (`D`) values so that `M >= D`. If you intended a certain number of decimal places, ensure the total number of digits is at least that much.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name DECIMAL(10, 2); -- Example: 10 total digits, 2 after decimal

4

Re-execute the corrected `CREATE TABLE` statement.

2. Modify Existing Column with Invalid Precision/Scale medium

Use `ALTER TABLE` to correct the precision and scale of an existing problematic column.

1

Identify the table and column that has the invalid numeric precision/scale. You can often find this by examining the error message which usually points to the specific column name.

2

Connect to your MariaDB server using a client like `mysql` or `mariadb`.

3

Use the `ALTER TABLE ... MODIFY COLUMN` statement to redefine the column with valid precision and scale. Ensure the new `M` is greater than or equal to `D`.

ALTER TABLE your_table_name MODIFY COLUMN problematic_column_name DECIMAL(12, 4); -- Example: Adjusting to 12 total digits, 4 after decimal

4

Verify the change by describing the table structure.

DESCRIBE your_table_name;

3. Use Default Precision/Scale for FLOAT and DOUBLE easy

Remove explicit precision/scale for FLOAT and DOUBLE columns to let MariaDB handle it.

1

Locate the `CREATE TABLE` or `ALTER TABLE` statement where `FLOAT` or `DOUBLE` is defined with an explicit precision (e.g., `FLOAT(8,2)`).

2

Remove the precision and scale specifiers. For `FLOAT` and `DOUBLE`, precision is optional and refers to the number of digits, but scale is not applicable in the same way as `DECIMAL`. The error message implies an attempt to define scale for these types, which is incorrect.

CREATE TABLE example_table (id INT, value FLOAT); -- Correct way for FLOAT

3

Alternatively, if you are modifying an existing column, use `ALTER TABLE` to remove the invalid specifiers.

ALTER TABLE your_table_name MODIFY COLUMN your_float_column FLOAT;

4

Re-execute the corrected statement.

🔗

Related Errors

5 related errors

MariaDB Error 1427: Invalid Numeric Precision/Scale