Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A stored procedure stops with 'Result consisted of more than one row'

ErrorERROR 1172 (42000): Result consisted of more than one row

Short answer

Error 1172 means a SELECT ... INTO statement returned more than one row, and a variable can hold only one. Make the query match exactly one row by filtering on a unique column, add LIMIT 1 when any one row will do, or read all rows with a cursor loop.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the SELECT part of the failing statement on its own, without the INTO clause, and count the rows it returns.

    -- the statement inside your procedure, minus INTO
    SELECT id, name FROM customers WHERE email = 'a@example.com';
  2. 2Match what you see, then jump to its fix

    1. 1The plain SELECT shows two or more rows for the same input value.The WHERE clause matches more than one rowFix
    2. 2The procedure wants one value, for example the latest order, and the table has many matching rows.Any single row is acceptable, but the query returns manyFix
    3. 3The procedure has to process each matching row, so no single-row query can be right.You need every row, not oneFix

What does the error look like?

  • ERROR 1172 (42000): Result consisted of more than one row

Cause 1 of 3

The WHERE clause matches more than one row

1Check

Group by the filter column and look for a count above 1. If the column is meant to be unique, the data or the missing unique index is the real problem.

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING n > 1;

2Fix

Filter on a column that identifies one row, such as the primary key, or add a unique index so the data cannot hold duplicates.

-- inside the procedure
SELECT name INTO v_name FROM customers WHERE id = p_id;

-- stop duplicates for good (after cleaning the existing ones)
ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);

3Verify

Call the procedure again with the input that failed.

CALL get_customer_name(42);

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

Any single row is acceptable, but the query returns many

1Check

Decide which row you want. Without ORDER BY the choice is arbitrary, so LIMIT 1 alone may return a different row each time.

SELECT id, total FROM orders WHERE customer_id = 7 ORDER BY created_at DESC;

2Fix

Add ORDER BY to choose the row and LIMIT 1 so the result is a single row. The MySQL manual recommends LIMIT 1 for a statement that may retrieve several rows.

SELECT id, total INTO v_id, v_total
FROM orders
WHERE customer_id = p_customer_id
ORDER BY created_at DESC
LIMIT 1;

3Verify

Call the procedure for a customer who has several orders.

CALL latest_order(7);

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

You need every row, not one

1Check

If the loop body needs each row in turn (insert elsewhere, call another routine), a variable list cannot hold the result.

SELECT COUNT(*) FROM orders WHERE customer_id = 7;

2Fix

Declare a cursor, fetch one row at a time, and leave the loop when the NOT FOUND handler sets a flag. Declare the cursor after the variables and before the handler.

DELIMITER //
CREATE PROCEDURE copy_orders(IN p_customer_id INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_total DECIMAL(10,2);
  DECLARE cur CURSOR FOR
    SELECT id, total FROM orders WHERE customer_id = p_customer_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_total;
    IF done THEN LEAVE read_loop; END IF;
    INSERT INTO order_archive (order_id, total) VALUES (v_id, v_total);
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

3Verify

Call the procedure and check the target table. If the work can be one set-based statement, INSERT ... SELECT is simpler and faster than a cursor.

CALL copy_orders(7);
SELECT COUNT(*) FROM order_archive;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Put a unique index on every column a procedure treats as unique, so duplicates are rejected on insert instead of found later.
  • Always pair LIMIT 1 with an ORDER BY when you read one row out of many.
  • Keep the no-row case in mind: SELECT ... INTO with no rows raises warning 1329 and leaves the variable unchanged, so initialise variables first.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.