📋

Description

This error indicates that you have attempted to define a numeric column (e.g., DECIMAL, NUMERIC) with a total number of digits (precision) that exceeds the maximum allowed by MariaDB. It typically occurs during table creation or alteration when the specified precision value is out of bounds for the chosen data type.

💬

Error Message

Too big precision %d specified for column '%s'. Maximum is %lu.

🔍

Known Causes

3 known causes

⚠️

Exceeding Data Type Limits

Different numeric data types in MariaDB (like DECIMAL or NUMERIC) have specific maximum precision values that cannot be surpassed.

⚠️

Misunderstanding Precision vs. Scale

Users sometimes confuse precision (total number of digits) with scale (number of digits after the decimal point), leading to an incorrect, oversized precision value.

⚠️

Typographical Error in Definition

A simple mistake or typo when specifying the precision value in a `CREATE TABLE` or `ALTER TABLE` statement can lead to an invalid, too-large number.

🛠️

Solutions

3 solutions available

1. Reduce Column Precision easy

Adjust the defined precision of the affected column to a valid value.

1

Identify the table and column causing the error. The error message will typically specify the column name and the excessive precision value.

2

Determine the maximum allowed precision for the data type. For `DECIMAL` and `NUMERIC` types, this is typically 65. Check your MariaDB version's documentation for specifics if unsure.

3

Modify the table definition to reduce the precision to a valid value. For example, if the error is for `DECIMAL(70, 2)`, change it to `DECIMAL(65, 2)` or a lower appropriate value.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name DECIMAL(65, 2);

4

Re-run the operation that caused the error (e.g., `CREATE TABLE` or `ALTER TABLE`).

2. Adjust Data Type and Precision medium

Re-evaluate the data type and precision requirements for the column.

1

Analyze the actual data that will be stored in the column. Determine the maximum possible value and the required number of decimal places.

2

If the required precision exceeds the maximum allowed for `DECIMAL` or `NUMERIC`, consider if a different data type might be more suitable. For very large numbers, you might need to store them as strings or use a different approach if exact precision is not strictly required.

3

If a `DECIMAL` or `NUMERIC` type is still necessary, define it with a precision that is within MariaDB's limits and sufficient for your data. For example, `DECIMAL(30, 6)` might be appropriate for many use cases.

CREATE TABLE your_table_name (your_column_name DECIMAL(30, 6));

4

If modifying an existing table, use the `ALTER TABLE` statement with the appropriate precision.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name DECIMAL(30, 6);

3. Review Table Schema Definition easy

Carefully examine the entire `CREATE TABLE` or `ALTER TABLE` statement for precision errors.

1

Locate the SQL statement that is failing due to Error 1426.

2

Scan through all column definitions, paying close attention to `DECIMAL` and `NUMERIC` data types. Ensure that the precision and scale are correctly specified and within the acceptable range for your MariaDB version.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    large_decimal DECIMAL(65, 30), -- This might be valid depending on your data
    another_decimal DECIMAL(70, 5)  -- This is likely to cause Error 1426
);

3

Correct any instances where the precision value is too high. For example, change `DECIMAL(70, 5)` to `DECIMAL(65, 5)` or adjust both precision and scale as needed.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    large_decimal DECIMAL(65, 30),
    another_decimal DECIMAL(65, 5)  -- Corrected precision
);

4

Execute the corrected SQL statement.

🔗

Related Errors

5 related errors

MariaDB Error 1426: Excessive Column Precision Specified