MySQL error 1292 on a statement that compares text with a number
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'grape'Short answer
MySQL compares a string with a number by turning both into floating-point numbers. A non-numeric string such as grape cannot be converted cleanly, and in strict mode a data-change statement fails with error 1292. Quote the number so it is compared as text, or cast one side on purpose.
Fix it fast
1Find out which cause you have
Read the value inside the quotes in the error. It is the string MySQL tried to turn into a number, and it points at the column in your condition.
SHOW WARNINGS;2Match what you see, then jump to its fix
- 1
The quoted value in the error is a word from a VARCHAR column, and the condition is col = 0 or col = 5A text column is compared with a numberFix - 2
Both sides are columns, for example orders.ref (VARCHAR) joined to items.id (INT), and the quoted value in the error is not a numberA JOIN or condition compares a text column with a numeric columnFix
- 1
What does the error look like?
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'grape'
Cause 1 of 2
A text column is compared with a number
1Check
Look at the types of both sides of every comparison in the WHERE and ON clauses.
SHOW COLUMNS FROM fruit;2Fix
Write the literal as a string when the column is text. In a plain SELECT the same mistake only gives warnings, and the manual notes the result: a non-numeric string like 'x6' becomes 0, so it equals 0.
-- fails with error 1292 when c3 is a VARCHAR
CREATE TABLE fruit2 SELECT * FROM fruit WHERE c3 = 0;
-- compares text with text
CREATE TABLE fruit2 SELECT * FROM fruit WHERE c3 = '0';3Verify
The statement runs and copies only the rows you expected.
SELECT * FROM fruit WHERE c3 = '0';Confirmed by dev.mysql.com
Cause 2 of 2
A JOIN or condition compares a text column with a numeric column
1Check
Mixed types make MySQL compare as floating point, and it cannot use the index on the string column. List the column types of both tables.
SHOW COLUMNS FROM orders;
SHOW COLUMNS FROM items;2Fix
Make the types match. If you cannot change the schema, cast the text side, but only when every value in it is digits: a non-numeric value still converts to 0.
UPDATE orders o JOIN items i ON CAST(o.ref AS UNSIGNED) = i.id
SET o.item_name = i.name;3Verify
The statement no longer reports error 1292.
SHOW WARNINGS;Confirmed by dev.mysql.com
How do I stop it coming back?
- Give columns that join each other the same type.
- Always quote string literals, even when they look like numbers.
- Run a SELECT with the same WHERE before every UPDATE or DELETE, and read the warnings.
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