MySQL SELECT ... INTO says Undeclared variable
ERROR 1327 (42000): Undeclared variable: totalShort answer
SELECT ... INTO can store into a user variable like @total or into a local variable declared with DECLARE inside a stored program. Error 1327 means the name is neither: a local variable was never declared, or its DECLARE sits in another block. Declare it first inside the same BEGIN ... END block, or use an @ variable.
Fix it fast
1Find out which cause you have
Find out whether the code runs as a plain script or inside a stored procedure, function or trigger. Only the second kind can have local variables.
SHOW CREATE PROCEDURE my_proc\G2Match what you see, then jump to its fix
- 1
ERROR 1327 (42000): Undeclared variable: total, inside CREATE PROCEDURE, FUNCTION or TRIGGERA local variable is used without DECLAREFix - 2
The variable is declared in your code but the error still appears at the SELECTThe DECLARE is in a different BEGIN ... END blockFix - 3
The SELECT ... INTO is inside a string passed to PREPAREA prepared statement uses a local variable in INTOFix - 4
ERROR 1172 (42000): Result consisted of more than one rowThe query returns more than one rowFix
- 1
What does the error look like?
ERROR 1327 (42000): Undeclared variable: totalERROR 1172 (42000): Result consisted of more than one row
Cause 1 of 4
A local variable is used without DECLARE
1Check
INTO accepts user-defined variables, parameters and stored program local variables. A local variable needs a DECLARE with a type.
SHOW CREATE PROCEDURE my_proc\G2Fix
Add a DECLARE at the start of the block, or change the name to a user variable with an @ prefix. In a plain script outside any stored program there are no local variables, so use the @ form.
CREATE PROCEDURE order_total(IN p_id INT)
BEGIN
DECLARE total DECIMAL(10,2);
SELECT SUM(amount) INTO total FROM orders WHERE customer_id = p_id;
SELECT total;
END3Verify
Call the procedure.
CALL order_total(1);Confirmed by dev.mysql.com
Cause 2 of 4
The DECLARE is in a different BEGIN ... END block
1Check
A local variable is visible in the block where it is declared and in blocks nested inside it, not in a block next to it or outside it.
SHOW CREATE PROCEDURE my_proc\G2Fix
Move the DECLARE to the outer block that contains every statement using it. Declarations must come before cursor and handler declarations.
BEGIN
DECLARE n INT DEFAULT 0;
BEGIN
SELECT COUNT(*) INTO n FROM orders;
END;
SELECT n;
END3Verify
Recreate the procedure and call it.
CALL my_proc();Confirmed by dev.mysql.com
Cause 3 of 4
A prepared statement uses a local variable in INTO
1Check
Inside a prepared statement only user-defined variables are permitted in INTO.
SELECT @@version;2Fix
Use an @ variable in the statement, then copy it into the local variable.
SET @sql = 'SELECT COUNT(*) INTO @cnt FROM orders';
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
SET total = @cnt;3Verify
SELECT @cnt returns the count.
SELECT @cnt;Confirmed by dev.mysql.com
Cause 4 of 4
The query returns more than one row
1Check
The query should return one row. With zero rows you only get warning 1329 and the variable keeps its old value.
SELECT COUNT(*) FROM orders WHERE customer_id = 1;2Fix
Make the condition unique, or add LIMIT 1 when any single row will do.
SELECT id INTO @oid FROM orders WHERE customer_id = 1 ORDER BY id DESC LIMIT 1;3Verify
The statement returns without error.
SELECT @oid;Confirmed by dev.mysql.com
How do I stop it coming back?
- Use a prefix such as v_ for local variables, so they cannot be confused with columns.
- Put INTO at the end of the SELECT, which is the preferred position.
- Check for zero rows: the variable silently keeps its previous value.
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