How to Fix MySQL Error 1172: Result Consisted of More Than One Row

ERROR 1172 (42000): Result consisted of more than one row is raised when a SELECT ... INTO statement inside a stored program returns more than one row, but the target is a scalar variable (or set of scalar variables) that can only hold a single row. The error symbol is ER_TOO_MANY_ROWS.

Impact

This error terminates the current stored procedure, function, or trigger at the point of the failing SELECT INTO. Any work done earlier in the same stored program may or may not be rolled back depending on whether the caller wrapped the call in an explicit transaction. In the common case — no explicit transaction — partial side-effects (inserts, updates, deletes executed before the failing statement) remain committed, which can leave data in an inconsistent state.

Applications typically surface this as an unhandled exception propagated from the database driver. ORMs such as SQLAlchemy, Hibernate, or ActiveRecord will re-raise it as a generic database error; the underlying SQLSTATE 42000 (syntax error or access violation class) means it will not be caught by handlers that only watch for NOT FOUND or data-integrity conditions.

Common Causes

Unfiltered lookup in a SELECT INTO variable — the WHERE clause is missing or too broad, matching multiple rows when the developer expected exactly one (e.g., selecting by a non-unique column).
Race condition or stale assumption about uniqueness — a column that was effectively unique at development time has received duplicate values due to a missing UNIQUE constraint, leading to failures in production.
Cursor-less iteration attempt — a developer unfamiliar with MySQL stored-procedure syntax tries to iterate over a result set by assigning it to variables without declaring a cursor, expecting the last row to win; MySQL instead raises the error on the second row.
Subquery used as a scalar expression — a correlated subquery inside an assignment resolves to multiple rows under certain data conditions that were not anticipated during development.
SELECT INTO OUTFILE vs. SELECT INTO variable confusion — rare, but sometimes developers accidentally write a variable-style SELECT INTO when they intended a file export, causing a broad query to hit the variable path.

Troubleshooting and Resolution Steps

Reproduce the query in isolation. Extract the SELECT from the stored program and run it directly to see how many rows it returns:

-- Example: reproduce the offending query
SELECT order_id, total
FROM   orders
WHERE  customer_id = 42;
-- If this returns more than one row, the stored procedure will error

Add a LIMIT 1 if one row is genuinely sufficient. If the business logic only needs one representative row (e.g., the most recent record), constrain the query:

-- Inside the stored procedure
SELECT order_id, total
INTO   v_order_id, v_total
FROM   orders
WHERE  customer_id = p_customer_id
ORDER BY created_at DESC
LIMIT 1;

Add or enforce a UNIQUE constraint when the column should be unique. If the WHERE predicate targets a column that must be unique, add the constraint so the assumption is enforced at the schema level:
```
ALTER TABLE orders ADD UNIQUE KEY uq_orders_ref (reference_number);
```
Verify existing duplicates first:
```
SELECT reference_number, COUNT(*) AS cnt
FROM   orders
GROUP BY reference_number
HAVING cnt > 1;
```

Use a cursor when you need to process multiple rows. If multiple rows are expected and each must be processed, declare a cursor instead of a scalar SELECT INTO:

DELIMITER $$
CREATE PROCEDURE process_customer_orders(IN p_customer_id INT)
BEGIN
  DECLARE v_done     INT DEFAULT FALSE;
  DECLARE v_order_id INT;
  DECLARE v_total    DECIMAL(10,2);

  DECLARE cur CURSOR FOR
    SELECT order_id, total
    FROM   orders
    WHERE  customer_id = p_customer_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_order_id, v_total;
    IF v_done THEN LEAVE read_loop; END IF;
    -- process each row here
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

Handle the error gracefully with a condition handler. If the calling code can tolerate a "too many rows" result and should simply skip processing, declare a handler:
```
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
  SET v_order_id = NULL;  -- or log to an error table
```
Use this as a defensive measure only; prefer fixing the query so it returns exactly one row.

Audit the stored procedure for unguarded SELECT INTO statements. Search all routines that contain the pattern:

SELECT routine_name, routine_definition
FROM   information_schema.routines
WHERE  routine_schema = DATABASE()
  AND  routine_definition LIKE '%SELECT%INTO%';

Additional Information

The SQLSTATE for this error is 42000, which is the generic "syntax error or access rule violation" class. This is somewhat misleading — the error is a runtime data condition, not a syntax problem — but it is consistent with the SQL standard's treatment of cardinality violations in scalar contexts.
Error 1172 is the inverse of error 1329 (ER_SP_FETCH_NO_DATA / No data - zero rows fetched), which fires when a SELECT INTO matches no rows. Robust stored procedures should handle both conditions.
MySQL does not raise 1172 for regular client queries — only for SELECT ... INTO @var or SELECT ... INTO local_var inside stored programs. A bare multi-row SELECT from the CLI or an application returns all rows normally.
In MySQL 8.0+ the SIGNAL statement can be used inside stored programs to convert a 1172 condition into a domain-specific error with a meaningful message before re-raising it.
When using connection poolers such as ProxySQL or application-level retry logic, note that 1172 is not a transient error — retrying the same call with the same data will always reproduce it. Fix the procedure or the data.

Frequently Asked Questions

Why does MySQL raise this error only inside stored procedures, not in regular queries? A regular SELECT returns a result set of any size to the client, which handles multiple rows natively. Inside a stored program, SELECT ... INTO assigns columns directly to scalar variables that hold exactly one value. MySQL raises 1172 as soon as the second row is fetched, because there is nowhere to put it.

Will adding LIMIT 1 always fix the problem? It will suppress the error, but it may hide a logic bug. If your query returning multiple rows indicates a data integrity problem (e.g., duplicate records that should not exist), LIMIT 1 papers over the symptom. Fix the root cause — add the missing UNIQUE constraint, or deduplicate existing rows — before or alongside the LIMIT 1 change.

Does this error automatically roll back my transaction? Not by default. MySQL stored procedures do not implicitly wrap each statement in a transaction. If the SELECT INTO fails mid-procedure, any DML executed earlier in the same procedure call that was not inside an explicit START TRANSACTION ... ROLLBACK block remains committed. Always use explicit transactions around multi-statement procedures that must be atomic.

Can I catch this error in application code instead of fixing the stored procedure? Yes, but it is not recommended as a permanent solution. The SQLSTATE 42000 is a broad class; catching it at the application layer risks swallowing unrelated errors. If you need application-level handling, catch it specifically by error number (e.g., errno == 1172 in Python's mysql.connector) and treat it as a fatal logic error that should be investigated, not silently retried.