SQL errors 'utf8mb4_ general_ci' is not valid for CHARACTER SET 'utf8'

ℹ️ Support
1

Hello,

I have updated my Nextcloud from 15.0.5 to 15.0.7 and then i get a message in the tab overview in the WebUI like “utf8_mb4 not activated but needed for smileys for help look into our documentations” then i’ve look there https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html and make that:
[mysqld]
innodb_file_per_table=1
have I added in my /etc/mysql/mariadb.cnf and to my /etc/mysql/my.cnf
then i typed this in my mysql:
ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
then have I done this:
sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value=“true”
sudo -u www-data php occ maintenance:repair
and the other sql commands on this site.

Nextcloud version (eg, 15.0.7):
Operating system and version (eg, Raspbian 9):
Apache or nginx version (eg, Apache 2.4.25):
PHP version (eg, 7.3.3):

The issue who I facing is:
Every time i will do sudo -u www-data php occ files:scan -all I get this error:
SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION ‘utf8mb4_
general_ci’ is not valid for CHARACTER SET ‘utf8’

Is this the first time you’ve seen this error? (Y/N):
Yes is the first time is this error.

I think it’s a SQL problem because if I delete this entry ‘mysql.utf8mb4’ => true, in my config.php the error is away.

Thanks for all answers.

2

i think rasbian has an older version of mariadb. So you need to follow the second guide on that page and change a few more settings.

3

Thanks, I’ve tried but nothing changed.

4

Hi, I have the same issue. Is there any solution yet?

5

You must make it manually convert and then the commands are worked

6

Hi Lucki1000,
I have the same problem. Can you show how you convert manually ?

Thanks,

7

I am also facing the same error on my Nextcloud setup and unable to see the users in gui.
I checked the tables in nextcloud database oc_addressbooks has 4 columns with varchar(255) is that the reason?
I get following error while doing maintenance repair:
Nextcloud is in maintenance mode - no apps have been loaded

  • Repair MySQL collation
    • Change row format for oc_addressbooks …
    • Change collation for oc_addressbooks …
    • ERROR: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.
8

I also have a similar error:

$ occ user:list
In ExceptionConverter.php line 114:
                                                                                                                    
  An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION  
   'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8mb3

I could not fix this problem.

The problem is probably related to your Mariadb version. Maybe MariaDB 10.6: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED · Issue #25436 · nextcloud/server · GitHub helps.

9

I thought you should look what’s your current charset is with this command(DBNAME you must replace with your nextcloud db):

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "DBNAME";