Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+4 votes
380 views
in Q2A Core by

I'm currently updating themes and plugins. However, when I tried to post my first question, I received a notice/error saying, "database query failed when generating this page." Upon checking the error logs, I found a message indicating that there may be an issue with the Core. Despite this, the content (question) was successfully inserted.

"NOTICE: PHP message: PHP Question2Answer MySQL query error 1292: Truncated incorrect INTEGER value: '' - Query: INSERT INTO qa_options (title, content) VALUES ("cache_qcount", 1) ON DUPLICATE KEY UPDATE content = VALUES(content) + CAST(content AS UNSIGNED)"

[Mon Aug 21 11:59:25.908328 2023] [proxy_fcgi:error] [pid 1982] [client 172.18.0.6:60322] AH01071: Got error 'PHP message: PHP Question2Answer MySQL query error 1292: Truncated incorrect INTEGER value: '' - Query: INSERT INTO qa_options (title, content) VALUES ("cache_qcount", 1) ON DUPLICATE KEY UPDATE content = VALUES(content) + CAST(content AS UNSIGNED)', referer: https://qa8.ddev.site/index.php?qa=ask

Server Details
Q2A Version - 1.8.8
PHP Version - 8.2.3
MySQL Version - 10.4.28-MariaDB-1:10.4.28+maria~ubu2004-log

Does anyone have an idea if the issue is related to the version of the database, PHP, or core? Or should I investigate my theme code further?

Q2A version: 1.8.8
by
+1
Upon further investigation, I discovered that the problem was related to the version of the database. Once I installed MySQL 8.0, the error disappeared and everything began functioning smoothly.

Has anyone else experienced a similar issue with the version of MariaDB that I am currently using?
by
1. Can you double check this happened with 1.8.8?
2. If you create another database and try to replicate the issue, do you still face it?
3. If you can still replicate it, can you give me the exact steps to replicate it after you've created the super user?

I'm mainly asking because there is PHP code that is supposed to be preventing this issue, so I'm surprised it isn't doing so.
by
Hello @puppi1985, Please have a look at this video https://www.youtube.com/watch?v=XAg4KRyDgYI

Please let me know if you require any additional particular details.

1 Answer

+1 vote
by
selected by
 
Best answer

The video is good. But it doesn't help as much, though. I know the source of the issue, but I can't seem to replicate it as the PHP code is preventing it from happening.

In short, in order for this to happen you need to have a MySQL/MariaDB database that has STRICT_ALL_TABLES or STRICT_TRANS_TABLES set in the sql_mode user variable. You can assign that using this statement:

SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES';

However, this is not enough. There has to be run a query that is forcing a casting issue. The query is:

INSERT INTO qa_options (title, content) VALUES ("cache_qcount", 1) ON DUPLICATE KEY UPDATE content = VALUES(content) + CAST(content AS UNSIGNED);

The failing part is the CAST(content AS UNSIGNED) when the content present is an empty string (you can see that in the error log you posted). In order to prevent this from happening, I just added a single PHP line that should prevent this from happening by forcing a default of 0.

However, I can't think of any way in which an empty string would be inserted into the field. Unless you have updated the table manually after installation, could you record a video of the installation process and hit the qa_options table after every step of the installation process and also until you face the error with the following query?

SELECT title, content from qa_options where title = 'cache_qcount';

We are looking for the exact action that triggers the insertion of the empty string in the content column. Note even browsing a question list would insert a 0 in the field.

by
Thank you for providing the details. I will follow the steps you mentioned. I will set up the site with MariaDB again and will update you shortly.
by
Hello! I recently tested a new container with MariaDB 10.4 and PHP 8.2 and unexpectedly encountered no errors. To confirm, I tried the same process on an existing container where the error typically occurs, and the error persisted. However, after cleaning up the container and re-establishing the server while keeping the source intact, I was pleasantly surprised to find that the error disappeared.

It's possible that there was a glitch with my Docker/ddev or something else entirely. Unless I encounter the same error again, I kindly request that you disregard it as a false report.

I apologize for any inconvenience this may have caused.
by
No worries. Just let me know if you face it again in a new setup (with an empty DB)
...