News: Converting to utf8mb4

[GanimanSwift]

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

Get error message when using the News app to convert character sets to utf8mb4. The error message provides a link to stack exchange to learn how to convert your character sets. The information in the link does help you convert all of your character sets, with a lot of manual work... but the message persists even after converting all character sets. This can be accomplished much easier with built in commands - I do not know why the error message does not simply provide these commands. Rather than link to an unsupported stack exchange thread, why not provide the supported commands which are not well documented and hard to find:

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

These commands do the job for you, without manually altering tables, and saves a lot of time. It does not make sense for a Nextcloud app to link to information that does not provide the built in Nextcloud commands. Please update the error message and save a lot of people a lot of time.

Steps to Reproduce

Explain what you did to encounter the issue

1. Have regular utf8 mysql character set
2. Install news app
3. Get message
4. Follow the link to stack exchange in the error message
5. Waste a lot of time and have a giant head ache, and still see the error message after all of your work

[BernhardPosselt]

Please create a pull request and adjust the warnings here https://github.com/nextcloud/news/blob/master/templates/part.content.warnings.php#L32

[sprnza]

When I try to use this guide I get these errors:

 - Repair MySQL collation
     - Change row format for oc_accounts ...
     - Change collation for oc_accounts ...
     - Change row format for oc_activity ...
     - Change collation for oc_activity ...


  [Doctrine\DBAL\Exception\DriverException]                                                                                                      
  An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':                       
  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs,  
   is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs                                  



  [Doctrine\DBAL\Driver\PDOException]                                                                                                            
  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs,  
   is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs                                  



  [PDOException]                                                                                                                                 
  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs,  
   is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs        

[sprnza]

I understand that something's wrong with the 11118th row size. But I don't know how to fix it.

[sprnza]

Actually the guide linked in the notification doesn't work for me either. Please help :)

[Dark-Fenix]

Hi,

I am facing the same issue than @sprnza :

sudo -u www-data php /var/www/nextcloud.XXXXX.XX/occ maintenance:repair
 - Repair MySQL collation
     - Change row format for oc_addressbooks ...
     - Change collation for oc_addressbooks ...

  [Doctrine\DBAL\Exception\DriverException]                                                                                     
  An exception occurred while executing 'ALTER TABLE `oc_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

I am currently running :
Nextcloud 12.0.3.3, PHP 7.0.24-1~dotdeb+8.1 and MySQL 15.1 Distrib 10.0.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 under Debian 8.9.

After some Googling, I found it could be related to my "old" version of MariaDB : I am upgrading tonight to 10.1.26 (on Debian 9.2), as 10.2.2 is not available yet. Hope it'll help :s

Update : No, still failing :'(

[sprnza]

Hi @Dark-Fenix!
Fortunately I had this issue about oc_activity table which is not important and I was able to solve it just "dropping" the table via mysql command line. I'm not sure if this "solution" applicable in your case because it's oc_addressbooks for you.

[Dark-Fenix]

Hi @sprnza

I solved my problem upgrading to MariaDB 10.2.9 from their official repo. I don't know how to fix it with older versions, as I am not a database expert, but it is enough for me :)

[sushidave]

Agree, the error message is too hard for non-techies.
Do all db tables need to be altered or just the ones of the News app?

[jaltek]

@Dark-Fenix @sprnza

767 bytes is the prefix limit for InnoDB tables.
In my case I only added the following lines to my mysql/mariadb config file:

innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=true

After that I restarted the database and executed the above mentioned commands. Worked like a charm :)

Side-facts

innodb_large_prefix is enabled by default in MySQL 5.7. This change coincides with the default value change for innodb_file_format, which is set to Barracuda by default in MySQL 5.7. Together, these default value changes allow larger index key prefixes to be created when using DYNAMIC or COMPRESSED row format. If either option is set to a non-default value, index key prefixes larger than 767 bytes are silently truncated.

References:

https://stackoverflow.com/a/1814594
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix
https://dba.stackexchange.com/questions/16208/why-using-innodb-file-per-table

[bcutter]

Oh man... freshly switched to Nextcloud today and ran into this "hint/warning" in the news app... I spent almost 4 hours now and reimporting a modified SQL backup still hits a lot of errors (767 bytes issues with varchar(256) etc.) ! Not a good start. I´m now on the way to fully switched to utf8mb4 for whole MySQL server and all databases and all Nextcloud tables. But still not sure if all that varchar-hacking will lead to some inconsistency or side effects in future...

Long words short summary: wish I´d either never seen those "warning" in News app or found this GitHub issue before spending so much time for probably very little advantage.

Update: At least
a) running both occ commands in addition worked without any issues (had enough before manually...)
b) the warning message is gone now.

I think I´m a "good" (bad?) example for technical users made insecure by such warning messages. Strongly vote for either removing it or at least providing good information, especially all the power already is in the occ commands.

[SMillerDev]

Isn't this fixed by 328a7d4c2fe10f026bf1628ba5e355d6b346076e?

[Jakethethird]

For the Nextcloud snap, this is easily solved detailed here and in the link in that thread here - by kyrofa.

Perhaps the solution works for other types of installation, or at least it can help someone figure it out.

[mr-gosh]

@Dark-Fenix @sprnza

767 bytes is the prefix limit for InnoDB tables.
In my case I only added the following lines to my mysql/mariadb config file:

innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=true

this did not work for me - but i found that changing

innodb_large_prefix=1

did the trick!