Truncated incorrect DOUBLE value
Kristian Köhntopp -
March 24, 2022
I record this for posteriority without much comment.
The error message “Truncated incorrect DOUBLE value” when issued by MySQL can be a confused parser, and masks unintended barely legal syntax:
kris@localhost [kris]> select * from testtable; +----+------+ | id | d | +----+------+ | 1 | eins | | 2 | zwei | | 3 | drei | +----+------+ 3 rows in set (0.00 sec) kris@localhost [kris]> update testtable set d="vier" and id = 4 where id = 3; ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'vier'
The actual problem here is the incorrect use of
and in the
set-clause of the
update statement when a comma was intended.
kris@localhost [kris]> update testtable set d="vier", id = 4 where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
and is possible and legal, if you wanted to produce a
Most people do not want this at this point in the statement, though.
The error message is particularly unhelpful if you have no access to the literal raw SQL string for whatever reason.
Next PostDebugging SQL in Python