Which collation to use for q2a ?

Question

The Installation Notes only tells me to create a Database, so i created an InnoDB Database with collation=utf8_general_ci ?

This worked, kind of. But from time to time I get a error:

[Fri Jan 19 13:59:50.136214 2018] [:error] [pid 22183] [client 192.168.1.97:51151] PHP Question2Answer MySQL query error 1366: Incorrect string value: '\\xC3...\\x09f...' for column 'params' at row 1 - Query: INSERT INTO qa_eventlog (datetime, ipaddress, userid, handle, cookieid, event, params) VALUES (NOW(), '192.164.12.18', '2', 'rainer', NULL, 'c_post', 'postid=85\tparentid=80\tparenttype=A\tparent=array(45)\tquestionid=77\tquestion=array(45)\tthread=array(2)\tcontent=Die korrekte L\xc3\xb6sung f\xc3\xbcr die Frage w\xc3...\tformat=\ttext=Die korrekte L\xc3\xb6sung f\xc3\xbcr die Frage w\xc3...\tcategoryid=14\tname=\tnotify=1\temail='), referer: https://faq.test.intern.net/77/kann-eigentlich-direkt-%c3%bcber-fragen-benachrichtigt-werden?show=84

I would say someone, (php, q2a, ?) is not sending correct utf8 encoded queries? ( https://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors/1168099#11013986 )

My main question is, what is the recommend collation for the Database? And if UTF8 is supported, is there some additional Settings I would need to tweak?

( Ubuntu 16LTS, Mysql 5.7.20, q2a 1.7.5 , php 7.0.22-0ubuntu0.16.04.1)

Answer 1

I was just answering what Scott mentioned. I can add a few comments.

Make sure you have multibyte string support: http://php.net/manual/en/mbstring.setup.php

Make sure the event logger table has this structure:

CREATE TABLE `qa_eventlog` (
  `datetime` datetime NOT NULL,
  `ipaddress` varchar(15) CHARACTER SET ascii DEFAULT NULL,
  `userid` int(10) unsigned DEFAULT NULL,
  `handle` varchar(20) DEFAULT NULL,
  `cookieid` bigint(20) unsigned DEFAULT NULL,
  `event` varchar(20) CHARACTER SET ascii NOT NULL,
  `params` varchar(800) NOT NULL,
  KEY `datetime` (`datetime`),
  KEY `ipaddress` (`ipaddress`),
  KEY `userid` (`userid`),
  KEY `event` (`event`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Finally, I am 99% sure this line is to blame: https://github.com/q2a/question2answer/blob/e17b0e3319e84d8a3f38ea56dc0f1a093fa96360/qa-plugin/event-logger/qa-event-logger.php#L148

However, I have no evidence. I couldn't replicate the issue at all. Anyway, if I'm right, and that is the issue, then the fix should be changing that line to:

return strtr($text, array("\t" => ' ', "\n" => ' ', "\r" => ' '));

Comments

The problem is, i cannot reproduce this for sure. Just adding "ü" ( or other special character ) to a comment will not allways fail. This just happens randomly. So i will watch it.
But thanks for the quick replies.

---

It is not random. I explained the issue. You need to cut the string in an inappropriate place. That's why I said "try adding that comment again". Same string should always break

---

@pupi What makes you think the problem is the strtr function? Shouldn’t your replacement function in exactly the same way?

---

Documentation says "With three arguments, strtr() will replace bytes; with two, it may replace longer substrings."

E.G. 1:
$ php -r "echo strtr('Äbc Äbc', 'Ä', 'a');"
a�bc a�bc

E.G. 2:
$ php -r "echo strtr('Äbc Äbc', array('Ä' => 'a'));"
abc abc

The behaviour is quite different. I believe the issue is generated only for each match. Which means if the matching character itself is not UTF-8 there should not be any issue. In this case, matching "\t\r\n" I guess it won't generate any issue, or at least it doesn't in my local environment. Considering this behaviour is probably a bit obscure I wouldn't be surprised there is a PHP implementation that could be a bit buggy or that works in a different way.

Anyway, as I later figured out, in one of the comments I mentioned the issue is the string containing the UTF-8 characters in a literal form from scratch. Not sure if that is generated by a PHP configuration or maybe a web server issue.

Answer 2

The collation for the database does not actually matter as each table has its own collation and those are set correctly in Q2A (to utf8_general_ci).

Q2A also does what that answer says and sets the mysql connection to utf8, so I’m not entirely sure what the problem could be. Maybe there is something else preventing the use of utf8.