A stored procedure stops with 'Result consisted of more than one row'
ERROR 1172 (42000): Result consisted of more than one rowShort 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.
Fix it fast
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';2Match what you see, then jump to its fix
- 1
The plain SELECT shows two or more rows for the same input value.The WHERE clause matches more than one rowFix - 2
The 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
The procedure has to process each matching row, so no single-row query can be right.You need every row, not oneFix
- 1
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix