News: Persistent "Non UTF-8 charset for MySQL/MariaDB" warning

[netwomble]

IMPORTANT

Read and tick the following checkbox after you have created the issue or place an x inside the brackets ;)

• [X] I have read the CONTRIBUTING.md and followed the provided tips

Explain the Problem

What problem did you encounter?
The message:
Non UTF-8 charset for MySQL/MariaDB database detected! Learn how to convert your database to utf8mb4 (make a backup beforehand)
is showing, even after following the steps in the linked page.

Steps to Reproduce

Explain what you did to encounter the issue

1. Upgrade to Nextcloud 12
2. Re-install News app
3. Follow instructions on linked page to fix error
4. Use news app
5. Error still persists.

System Information

• News app version: 11.0.5
• Nextcloud version: 12.0.0
• PHP version: PHP 7.0.15-0ubuntu0.16.04.4
• Database and version: Ver 15.1 Distrib 10.1.20-MariaDB
• Browser and version: Version 58.0.3029.110 (64-bit) Windows (also firefox on linux)
• Distribution and version: Ubuntu 16.04.1 LTS

Contents of nextcloud/data/nextcloud.log

{"reqId":"gns4FnhxMLW90LgIQ8PR","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpy.min.js.map","message":"S
css is disabled for \/var\/www\/nextcloud\/core\/css\/server.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3029.110 Safari\/537.36","
version":"12.0.0.29"}
{"reqId":"gns4FnhxMLW90LgIQ8PR","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpy.min.js.map","message":"S
css is disabled for \/var\/www\/nextcloud\/core\/css\/share.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3029.110 Safari\/537.36","v
ersion":"12.0.0.29"}
{"reqId":"ZSu3PMJZpcxfqgv4gJ8B","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular-animate\/angular-ani
mate.min.js.map","message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/server.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58
.0.3029.110 Safari\/537.36","version":"12.0.0.29"}
{"reqId":"ZSu3PMJZpcxfqgv4gJ8B","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular-animate\/angular-ani
mate.min.js.map","message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/share.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.
0.3029.110 Safari\/537.36","version":"12.0.0.29"}
{"reqId":"FtSWrep3MmrX1xyhZIzg","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.php6-shim\/es6-shim.map","mes
sage":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/server.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3029.110 Safari\/5
37.36","version":"12.0.0.29"}
{"reqId":"FtSWrep3MmrX1xyhZIzg","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.php6-shim\/es6-shim.map","mes
sage":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/share.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3029.110 Safari\/53
7.36","version":"12.0.0.29"}
{"reqId":"a2wYwtV8WVwiOsWEVyss","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular\/angular.min.js.map"
,"message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/server.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3029.110 Safa
ri\/537.36","version":"12.0.0.29"}
{"reqId":"a2wYwtV8WVwiOsWEVyss","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular\/angular.min.js.map"
,"message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/share.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3029.110 Safar
i\/537.36","version":"12.0.0.29"}
{"reqId":"YRaIywGllMELvilzZ2xj","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular-route\/angular-route
.min.js.map","message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/server.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.3
029.110 Safari\/537.36","version":"12.0.0.29"}
{"reqId":"YRaIywGllMELvilzZ2xj","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular-route\/angular-route
.min.js.map","message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/share.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/58.0.30
29.110 Safari\/537.36","version":"12.0.0.29"}
{"reqId":"A8kfwIMy7YFuv7xMYv2N","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular-sanitize\/angular-sa
nitize.min.js.map","message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/server.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/
58.0.3029.110 Safari\/537.36","version":"12.0.0.29"}
{"reqId":"A8kfwIMy7YFuv7xMYv2N","level":0,"time":"2017-06-04T13:43:54+00:00","remoteAddr":"192.168.10.25","user":"netwomble","app":"core","method":"GET","url":"\/core\/templates\/404.phpgular-sanitize\/angular-sa
nitize.min.js.map","message":"Scss is disabled for \/var\/www\/nextcloud\/core\/css\/share.scss, ignoring","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/5
8.0.3029.110 Safari\/537.36","version":"12.0.0.29"}

Contents of Browser Error Console

Read http://ggnome.com/wiki/Using_The_Browser_Error_Console if you are unsure what to put here

Mixed Content messages from my feeds, nothing else.

I probably need to fix one of my columns/tables but I have no idea which.

Is it possible to actually print the tables and columns that are causing the issues so that I can double check them? The instructions to convert to 4-byte UTF are complicated, and I imagine they are easy to mess up.

[FiSh-MoB]

Same problem here.
converted all from utf8_general_ci (or something like this) to utf8mb4_unicode_ci
error still persists. can't find any inside my db structure. structure dump is here:
https://s.itmohr.de/index.php/s/L6rZ457PzKyqBWS
PHPINFO: https://s.itmohr.de/i1.php

[clorenz150]

The linked description doesn't work for me.
As I understand News 11.0.5 uses now the utf8mb4 detection of Nextcloud.
To sattisfy the test you need to follow the steps at:
Nextcloud: Enabling MySQL 4-byte support
The warning of News should be changed to point to that page.

[sonologic]

Indeed, the warning should point to that page instead of the current generic explanation. Can you create a pull request for that @clorenz150 ?

@netwomble & @FiSh-MoB , does following the instructions on https://docs.nextcloud.com/server/12/admin_manual/configuration_database/mysql_4byte_support.html fix it for you (it did for me)?

[FiSh-MoB]

i can't edit my mysqld.conf, because it is hosted by an ISP.
anyhow those two commands solved my problem, thank you for your help @sonologic

$ sudo -u www-data occ config:system:set mysql.utf8mb4 --type boolean --value="true"
$ sudo -u www-data occ maintenance:repair

error gone for me!

[netwomble]

MariaDB for me. I had already edited the my.cnf to no avail, but those two commands above sorted it out.

Thanks!

[pixelipo]

what if I don't need emoji support? I don't want to change to 4-byte encoding unless I really have to. Is there a way to disable the warning?

[sonologic]

Out of curiosity, why do you not want to change to 4-byte encoding?

There is currently no way to disable the warning. Do note that not just emoji's require 4-byte utf8, other characters need 4 bytes as well. So basically, having 4-byte utf8 support is mandatory to be able to represent the full range of UTF characters. Emoji just happen to be one subclass of them.

Also characters that don't need 4 bytes will still be stored in 3, 2 or 1 byte(s) as was the case before.

[pixelipo]

Because of my ignorance regarding the topic, I guess. :wink: Also because in wary of making such risky operations on production db.

But thanks for clarifications! I I've red few more articles and figured it's worth doing.

Still, I don't think it's a good practice to have undismissable warning popovers. A good compromise would be to hide warning after 5 seconds, but show it every time News app is opened.

[BernhardPosselt]

@pixelipo its not up to you to decide if you need it, feeds will simply ship it and your update process will break with an exception because you cant add all utf-8 characters into mysql utf-8 tables

[BernhardPosselt]

Still, I don't think it's a good practice to have undismissable warning popovers. A good compromise would be to hide warning after 5 seconds, but show it every time News app is opened.

PS: It's supposed to be annoying because you guys fill the issue tracker with the same bug reports all the time :)

[xshadow]

I tried to run the proposed solution, but it broke my database:

https://docs.nextcloud.com/server/12/admin_manual/configuration_database/mysql_4byte_support.html

mysql> SET GLOBAL innodb_file_format = barracuda; Query OK, 0 rows affected (0,00 sec)

mysql> ALTER DATABASE syscloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 1 row affected (0,00 sec)

cloud@cloud:/www$ php occ config:system:set mysql.utf8mb4 --type boolean --value="true"
cloud@cloud:/www$ php occ maintenance:repair

    Repair MySQL collation
        Change row format for accounts ...
        Change collation for accounts ...
        Change row format for activity ...
        Change collation for activity ...
        Change row format for activity_mq ...
        Change collation for activity_mq ...
        Change row format for addressbookchanges ...
        Change collation for addressbookchanges ...
        Change row format for addressbooks ...
        Change collation for addressbooks ...

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'ALTER TABLE addressbooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

maintenance:repair [--include-expensive]

[BernhardPosselt]

MySql has a shorter restriction on indices (768 characters). 256 characters fit just fine with 3byte utf-8 (3 * 256) but won't for 4 byte utf-8 (4 * 256). The offending apps should shorten their character lengths from 255 to something like 180 characters (or you should do that manually).

[smoebody]

i had to run these statements on my database:

ALTER TABLE `oc_addressbooks` CHANGE `uri` `uri` VARCHAR(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL;
ALTER TABLE `oc_addressbooks` CHANGE `principaluri` `principaluri` VARCHAR(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL;
ALTER TABLE `oc_admin_sections` CHANGE `class` `class` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_admin_settings` CHANGE `class` `class` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_authtoken` CHANGE `token` `token` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_bookmarks_tags` CHANGE `tag` `tag` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_calendarobjects` CHANGE `uri` `uri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_calendars` CHANGE `principaluri` `principaluri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_calendars` CHANGE `uri` `uri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_calendarsubscriptions` CHANGE `uri` `uri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_calendarsubscriptions` CHANGE `principaluri` `principaluri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_dav_shares` CHANGE `principaluri` `principaluri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_dav_shares` CHANGE `type` `type` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_dav_shares` CHANGE `publicuri` `publicuri` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL;
ALTER TABLE `oc_ldap_group_mapping` CHANGE `ldap_dn` `ldap_dn` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_ldap_group_mapping` CHANGE `owncloud_name` `owncloud_name` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_ldap_group_members` CHANGE `owncloudname` `owncloudname` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_ldap_user_mapping` CHANGE `ldap_dn` `ldap_dn` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_ldap_user_mapping` CHANGE `owncloud_name` `owncloud_name` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_mimetypes` CHANGE `mimetype` `mimetype` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `oc_systemtag_group` CHANGE `gid` `gid` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
ALTER TABLE `oc_trusted_servers` CHANGE `url_hash` `url_hash` VARCHAR(180) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'sha1 hash of the url without the protocol';

I am only using some of the commonly used apps. There might be more tables to fix individually...

[tetzlav]

Thx, you made my day!

[Nibbels]

It sais 767bytes max. 767/4 = 191,75 so 191 is working - why go as low as 180?
Am I missing something?

[smoebody]

No reason. 191 did not work for me so i took the 180. At the end i configured the service so i did not have to bother with the keys myself since the next automatic upgrade messed it all up again.

[BernhardPosselt]

180 to avoid off by one errors but you are ofc correct

[bplessis]

It would seem the best solution is to upgrade mariadb to a 10.2 release.

However if not possible it would be probably less intrusive to only reduce the index size instead of the field size:

alter table oc_addressbooks drop key addressbook_index, add unique key `addressbook_index` (principaluri(180),uri(180));
alter table oc_admin_sections drop key admin_sections_class, add unique key admin_sections_class (class(180));

[BernhardPosselt]

Another solution btw is also to go for large indices (although I didn't manage to do that at work yet). See https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix

[stormsh]

Hi there, what happens to all the other non nextcloud db's already in existance if I change the Global MySQL Server variables, innodb_large_prefix, innodb_file_format, innodb_file_per_table to different settings than default?
Or is it possible to change them for just 1 db?

Thx 4 F1.

[bplessis]

Can't say for large_prefix / file_format but
i know that file_per_table only impact newly created table (or re-created with alter), it should be the same for file_format according to documentation.
As large_prefix is only compatible with the new barracuda file format it won't impact old antelope tables

[stormsh]

Ok, so these three options either apply only on newly generated db's or don't affect allready existing ones. Correct?

[bplessis]

Well, that's a nice tautology but yes that is correct

[crankycaleb]

This may get frowned upon, but for anyone looking for this and finding in search results as I did, here goes how you can eliminate the annoying message itself:

Make a backup copy of your /lib/Service/StatusService.php (inside wherever your news app folder is) and then edit it. Find the line towards the bottom
'incorrectDbCharset' => !$this->connection->supports4ByteText()
and get rid of it. Save and refresh your browser and the message is gone. This is for folks that don't want to mess with their database for one app.

[stormsh]

@crankycaleb : Mr. @BernhardPosselt might never talk to me again, but I will thank you anyway. ;)

[Nibbels]

I as well just cannot upgrade my database. It is MariaDB 5.5.60-1.el7_5

So I once did the trick with the "180 characters" in db_structure.xml. But I then had to do that every time I updated nextcloud. That prevented myself from updating NC quite often, because that is a real pain to do.
And now the db_structure.xml seemed to be vanished within NC13 probably because of doctrine migrations(?)

(Yes thats all just stupid.)

All for those special chars. So I downgraded from 4Byte-Support to non-4-byte by cleaning up my /config/config.php and "sudo .. php occ maintenance:repair".
I will now just accept problems within the news app.

Thanks @crankycaleb for your hint. I'll go with that for now!