Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value

3
votes

This is the error I am getting.

PDOException: Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\xB3' for column 'string' at row 1'

I know the problem here. The column table is using utf8. The utf8 encoding cannot store 4 byte characters so it errors. A way to get around this could be to use utf8mb4 instead.

My question instead is about the error message. We are using MariaDB. Why does it say "Invalid datetime format" in the error message? This is a text field. There is nothing to do with datetime in the query being run.

This "Invalid datetime format" comes up often for a variety of errors but seems unrelated to the actual source of the error.

1
mysqlpdomariadb
because a mysql date/time string is yyyy-mm-dd hh:mm:ss. Given your \xF0, you're passing something not even remotely CLOSE to that form. And since you haven't shown your actual query, we can't do anything to help you. - Marc B
What Marc B. said. Don't send an utf8 encoded string as a datetime parameter, that's all. - Mjh
If you're positively sure that no dates have been harmed in the execution of this query, it might be you've hit some of those strange bugs that make our lives more interesting. How reproducible is it? Could you provide a self-contained example? - Álvaro González
Let's see SHOW CREATE TABLE and the SQL directly involved in the error message. - Rick James

1 Answers

3
votes

Yes, I've seen this before.

I suppose it's a bug on the mariadb side, where all errors on the incorrect values are dubbed as "incorrect datetime value".

I see no harm here though beside a little confusion. Just keep in mind the issue and treat it as "incorrect text value"