📋

Description

MySQL Error 1331 occurs when you attempt to declare a variable with a name that is already in use within the same scope of a stored program (e.g., stored procedure, function, or trigger). This error prevents the creation or execution of the program because all variable names within a given scope must be unique.

💬

Error Message

Duplicate variable: %s

🔍

Known Causes

3 known causes

⚠️

Repeated Variable Declaration

A `DECLARE` statement attempts to define a variable name that has already been used in the current scope within the stored program.

⚠️

Local Variable vs. Parameter Conflict

A local variable is declared with the same name as one of the input or output parameters defined for the stored procedure or function.

⚠️

Copy-Paste Errors

Accidental duplication of variable declarations when copying and pasting code blocks, leading to unintended redundant variable names.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Variable Declarations easy

Locate the specific variable name causing the conflict and remove the redundant declaration.

1

Examine the SQL statement or stored procedure code that is generating the error. Look for lines that declare variables using `SET` or `DECLARE`.

2

The error message `Duplicate variable: %s` will explicitly state the name of the variable that is declared more than once. Search your code for this variable name.

3

Once the duplicate declaration is found, remove one of them. Ensure you are keeping the declaration that is intended to be used.

-- Example of a duplicate declaration
-- DECLARE my_variable INT;
-- SET my_variable = 10;
-- DECLARE my_variable INT; -- This is the duplicate
-- SET my_variable = 20;

-- Corrected version
-- DECLARE my_variable INT;
-- SET my_variable = 10;
-- SET my_variable = 20; -- Reassigning is fine

4

Re-execute the SQL statement or stored procedure to confirm the error is resolved.

2. Scope of Variables in Stored Procedures medium

Understand and manage variable scope within stored procedures to avoid name collisions.

1

If the error occurs within a stored procedure, review the declaration of variables within different blocks (e.g., within loops, conditional statements, or even at the procedure level).

2

Ensure that variables declared within a specific block are not re-declared with the same name at a higher scope or within another nested block if they are intended to be distinct.

-- Example of a potential scope issue
DELIMITER //
CREATE PROCEDURE test_scope ()
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 5 DO
    -- DECLARE counter INT DEFAULT 0; -- This would cause error 1331 if uncommented
    SET counter = counter + 1;
    SELECT counter;
  END WHILE;
END //
DELIMITER ;

-- Corrected (if a new scope was intended for a different purpose)
DELIMITER //
CREATE PROCEDURE test_scope_different ()
BEGIN
  DECLARE outer_counter INT DEFAULT 0;
  WHILE outer_counter < 5 DO
    DECLARE inner_counter INT DEFAULT 0; -- Different scope, different name
    SET inner_counter = inner_counter + 1;
    SELECT outer_counter, inner_counter;
    SET outer_counter = outer_counter + 1;
  END WHILE;
END //
DELIMITER ;

3

Consider renaming variables to be more descriptive and unique, especially if they are used in different logical sections of a complex procedure.

3. Review User-Defined Variables in SQL Statements easy

Check for re-declarations of user-defined variables in complex SQL queries.

1

If you are using user-defined variables (variables prefixed with `@`), meticulously review your SQL query for any instances where the same user-defined variable is assigned a value more than once in a way that MySQL interprets as a re-declaration.

2

Often, this occurs within subqueries or within `SELECT` statements where a variable is set and then immediately used or set again.

-- Example of a potential issue
SET @my_val = 10;
SELECT @my_val AS initial_val, (@my_val := @my_val + 5) AS updated_val, @my_val AS final_val;

-- If the error occurs, it might be due to a more complex scenario like:
-- SELECT
--   (@var := column1) AS var_a,
--   (@var := column2) AS var_b  -- Duplicate declaration of @var
-- FROM your_table;

-- Corrected approach:
SELECT
  column1 AS var_a,
  column2 AS var_b
FROM your_table;

-- If you need to use a variable sequentially:
SET @prev_val = NULL;
SELECT
  (@current_val := column1),
  IF(@prev_val IS NULL, 0, @prev_val) AS previous_value,
  (@prev_val := @current_val)
FROM your_table;

3

Restructure the query to ensure variables are assigned only once in a logical flow, or use different variable names if multiple independent values are needed.

🔗

Related Errors

5 related errors

MySQL Error 1331: Duplicate Variable Declaration