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

If i let HeideSQL create a SQL for that Table it looks like below, event has a explicit 'ascii_general_ci' collation, no idea why ( the table itself has the MYISAM + utf_general_ci stuff).


CREATE TABLE `qa_eventlog` (
    `datetime` DATETIME NOT NULL,
    `ipaddress` VARCHAR(15) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `userid` INT(10) UNSIGNED NULL DEFAULT NULL,
    `handle` VARCHAR(20) NULL DEFAULT NULL,
    `cookieid` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `event` VARCHAR(20) NOT NULL COLLATE 'ascii_general_ci',
    `params` VARCHAR(800) NOT NULL,
    INDEX `datetime` (`datetime`),
    INDEX `ipaddress` (`ipaddress`),
    INDEX `userid` (`userid`),
    INDEX `event` (`event`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

---

The table looks good. It is the `params` field the one you're having issues with. Things to note:
 * "\xc3\xbc" seems to be character "ü"
 * The last character is being cut

That's not happening to me because I see the ü being inserted when I try to insert something like "Die korrekte Lüsung für die Frage würrenca". I get the impression that something is not configured in your sever to work in UTF-8.

1. What's the output of `echo mb_internal_encoding();`
2. If it is not "UTF-8", does using `mb_internal_encoding('UTF-8');` fix the issue?
3. If you check directly in the database, how does the `content` field look like for posts? (do they display the "ü" character or do they display "\xc3\xbc"?)

---

Did lead to "Uncaught Error: Call to undefined function mb_internal_encoding()" after installing php-mbstring via apt : "UTF-8". I guess this will fix it ?

---

It should. You don't have multibyte string support. I can't confirm that is the issue but it could be related. Also remember to restart your web server. Then try adding that comment again and see if it fails again

---

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.