"Internal Server Error" When Trying to Save Emoji

Help / Discuss
#1

A colleague just got an “Internal Server Error” when trying to save a page, the cause if which turns out to be an Emoji she inserted into the text: :house:

Did I somehow misconfigure my MariaDB concerning Unicode or similar?

System information and stack trace from the logs:

# dpkg -l | egrep '^ii.*(mariadb|xwiki)'
ii  libmariadb3:amd64                     1:10.3.27-0+deb10u1                    amd64        MariaDB database client library
ii  mariadb-client-10.3                   1:10.3.27-0+deb10u1                    amd64        MariaDB database client binaries
ii  mariadb-client-core-10.3              1:10.3.27-0+deb10u1                    amd64        MariaDB database core client binaries
ii  mariadb-common                        1:10.3.27-0+deb10u1                    all          MariaDB common metapackage
ii  mariadb-server                        1:10.3.27-0+deb10u1                    all          MariaDB database server (metapackage depending on the latest version)
ii  mariadb-server-10.3                   1:10.3.27-0+deb10u1                    amd64        MariaDB database server binaries
ii  mariadb-server-core-10.3              1:10.3.27-0+deb10u1                    amd64        MariaDB database core server files
ii  xwiki-common                          12.10.4                                all          XWiki is a free wiki software platform written in Java with a design emphasis
ii  xwiki-mysql-common                    12.10.4                                all          XWiki is a free wiki software platform written in Java with a design emphasis
ii  xwiki-tomcat9-common                  12.10.4                                all          XWiki is a free wiki software platform written in Java with a design emphasis
ii  xwiki-tomcat9-mysql                   12.10.4                                all          XWiki is a free wiki software platform written in Java with a design emphasis

[2021-03-03 11:33:43] [info] com.xpn.xwiki.XWikiException: Error number 3201 in 3: Exception while saving document xwiki:xxxxxxxxxxx.WebHome
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiDoc(XWikiHibernateStore.java:694)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.store.XWikiCacheStore.saveXWikiDoc(XWikiCacheStore.java:258)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.store.XWikiCacheStore.saveXWikiDoc(XWikiCacheStore.java:218)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.XWiki.saveDocument(XWiki.java:2056)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.web.SaveAction.save(SaveAction.java:270)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.web.SaveAction.action(SaveAction.java:522)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.web.XWikiAction.execute(XWikiAction.java:555)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.web.XWikiAction.execute(XWikiAction.java:250)
[2021-03-03 11:33:43] [info] #011at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
[2021-03-03 11:33:43] [info] #011at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
[2021-03-03 11:33:43] [info] #011at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
[2021-03-03 11:33:43] [info] #011at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
[2021-03-03 11:33:43] [info] #011at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
[2021-03-03 11:33:43] [info] #011at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.web.ActionFilter.doFilter(ActionFilter.java:122)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at org.xwiki.wysiwyg.filter.ConversionFilter.doFilter(ConversionFilter.java:106)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at org.xwiki.container.servlet.filters.internal.SetHTTPHeaderFilter.doFilter(SetHTTPHeaderFilter.java:63)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at org.xwiki.container.servlet.filters.internal.SavedRequestRestorerFilter.doFilter(SavedRequestRestorerFilter.java:208)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at org.xwiki.container.servlet.filters.internal.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:111)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at org.xwiki.resource.servlet.RoutingFilter.doFilter(RoutingFilter.java:132)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:688)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
[2021-03-03 11:33:43] [info] #011at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
[2021-03-03 11:33:43] [info] #011at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:415)
[2021-03-03 11:33:43] [info] #011at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
[2021-03-03 11:33:43] [info] #011at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
[2021-03-03 11:33:43] [info] #011at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639)
[2021-03-03 11:33:43] [info] #011at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
[2021-03-03 11:33:43] [info] #011at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
[2021-03-03 11:33:43] [info] #011at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
[2021-03-03 11:33:43] [info] #011at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
[2021-03-03 11:33:43] [info] #011at java.base/java.lang.Thread.run(Thread.java:834)
[2021-03-03 11:33:43] [info] Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute statement
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1366)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
[2021-03-03 11:33:43] [info] #011at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
[2021-03-03 11:33:43] [info] #011at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
[2021-03-03 11:33:43] [info] #011at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.internal.store.hibernate.HibernateStore.endTransaction(HibernateStore.java:846)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.store.XWikiHibernateBaseStore.endTransaction(XWikiHibernateBaseStore.java:603)
[2021-03-03 11:33:43] [info] #011at com.xpn.xwiki.store.XWikiHibernateStore.saveXWikiDoc(XWikiHibernateStore.java:685)
[2021-03-03 11:33:43] [info] #011... 50 common frames omitted
[2021-03-03 11:33:43] [info] Caused by: org.hibernate.exception.DataException: could not execute statement
[2021-03-03 11:33:43] [info] #011at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)
[2021-03-03 11:33:43] [info] #011at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
[2021-03-03 11:33:43] [info] #011at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3449)
[2021-03-03 11:33:43] [info] #011at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3311)
[2021-03-03 11:33:43] [info] #011at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3725)
[2021-03-03 11:33:43] [info] #011at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:201)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
[2021-03-03 11:33:43] [info] #011at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
[2021-03-03 11:33:43] [info] #011at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
[2021-03-03 11:33:43] [info] #011at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
[2021-03-03 11:33:43] [info] #011at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
[2021-03-03 11:33:43] [info] #011at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
[2021-03-03 11:33:43] [info] #011... 61 common frames omitted
[2021-03-03 11:33:43] [info] Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect string value: '\xF0\x9F\x8F\xA0 i...' for column `xwiki_db`.`xwikircs`.`XWR_PATCH` at row 1
[2021-03-03 11:33:43] [info] #011at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
[2021-03-03 11:33:43] [info] #011at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
[2021-03-03 11:33:43] [info] #011at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
[2021-03-03 11:33:43] [info] #011at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
[2021-03-03 11:33:43] [info] #011at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
[2021-03-03 11:33:43] [info] #011at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
[2021-03-03 11:33:43] [info] #011at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
[2021-03-03 11:33:43] [info] #011at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
[2021-03-03 11:33:43] [info] #011at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
[2021-03-03 11:33:43] [info] #011at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
[2021-03-03 11:33:43] [info] #011... 73 common frames omitted
#2

Seems like you are using “utf8” (which, despite its name does not fully support UTF8 and especially not “recent” additions like Emojis) for the tables encoding instead of “utf8mb4”.

#3

Mh, after reading your reply I had something in my mind that I had trouble with utf8mb4 and Xwiki in the past - however, I still tried to convert, as documented at https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationMySQL/#HConvertadatabasefromlatin128orcollationutf8_ci29toutf8mb42Futf8mb4_bin .

And indeed:

root@xxx-host-xxx:~# db=xxx_dbname_xxx
root@xxx-host-xxx:~# to_character_set=utf8mb4
root@xxx-host-xxx:~# to_collation=utf8mb4_bin
root@xxx-host-xxx:~# mysql_cmd="mysql"
root@xxx-host-xxx:~# $mysql_cmd -e "ALTER DATABASE $db CHARACTER SET $to_character_set COLLATE $to_collation;"
root@xxx-host-xxx:~# TBL_LIST=$($mysql_cmd -N -s -r -e "use $db;show tables;")
root@xxx-host-xxx:~# echo $TBL_LIST 
DATABASECHANGELOG DATABASECHANGELOGLOCK activitystream_events activitystream_events_status activitystream_events_targets feeds_aggregatorgroup feeds_aggregatorurl feeds_aggregatorurlgroups feeds_feedentry feeds_feedentrytags feeds_keyword mailsender_events notification_filter_prefs xwikiattachment xwikiattachment_archive xwikiattachment_content xwikiattrecyclebin xwikicomments xwikidates xwikidbversion xwikidoc xwikidoubles xwikifloats xwikiid xwikiintegers xwikilargestrings xwikilinks xwikilistitems xwikilists xwikilock xwikilongs xwikiobjects xwikipreferences xwikiproperties xwikircs xwikirecyclebin xwikispace xwikistatsdoc xwikistatsreferer xwikistatsvisit xwikistrings
root@xxx-host-xxx:~# for tbl_name in $TBL_LIST
> do
> $mysql_cmd -e "alter table $db.$tbl_name convert to character set $to_character_set collate $to_collation;"
> done
ERROR 1118 (42000) at line 1: 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
ERROR 1832 (HY000) at line 1: Cannot change column 'ases_eventid': used in a foreign key constraint 'FKmwu6uxkc2jl3yvej4wi1p0pv0'
ERROR 1832 (HY000) at line 1: Cannot change column 'eventId': used in a foreign key constraint 'FK2bj2ghquiq79toomkvugw7iie'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWS_NAME': used in a foreign key constraint 'FKDEAEAB5D3433FD87'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWD_NAME': used in a foreign key constraint 'FK5A1CD9A1A947AA5'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWF_NAME': used in a foreign key constraint 'FKFB291FBF1DFF14A1'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWI_NAME': used in a foreign key constraint 'FK7F8AB31D231EFB9B'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FK6661970F283EE295'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKC0862BA3FB72A11'
ERROR 1833 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKC0862BA3FB72A11' of table 'xwiki_db.xwikilistitems'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKDF25AE4F283EE295'
ERROR 1833 (HY000) at line 1: Cannot change column 'XWP_NAME': used in a foreign key constraint 'FK2780715A3433FD87' of table 'xwiki_db.xwikistrings'
ERROR 1118 (42000) at line 1: 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
ERROR 1832 (HY000) at line 1: Cannot change column 'XWS_NAME': used in a foreign key constraint 'FK2780715A3433FD87'
root@xxx-host-xxx:~# dpkg -l mariadb-server
Gewünscht=Unbekannt/Installieren/R=Entfernen/P=Vollständig Löschen/Halten
| Status=Nicht/Installiert/Config/U=Entpackt/halb konFiguriert/
         Halb installiert/Trigger erWartet/Trigger anhängig
|/ Fehler?=(kein)/R=Neuinstallation notwendig (Status, Fehler: GROSS=schlecht)
||/ Name           Version             Architektur  Beschreibung
+++-==============-===================-============-=====================================================================
ii  mariadb-server 1:10.3.27-0+deb10u1 all          MariaDB database server (metapackage depending on the latest version)
root@xxx-host-xxx:~#

I now re-migrated everything to utf8, and Xwiki still seems to work, but how to properly migrate to utf8mb4 then - or what else can I do to avoid the “internal server error”?

Additional information: I’m using Barracuda file format, one file per table. Row Format as indicated by information schema is “Dynamic”, so Barracuda actually appears to be in use, as intended.
innodb_log_file_size is 128M.

#4

ERROR 1118 (42000) at line 1: 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

Looks like https://jira.xwiki.org/browse/XWIKI-17200. Basically before moving to utf8mb4 you will also need to change the type of a few columns to stay under the max row size.

#5

Thanks, I had a look at this issue.

If I ALTER the columns as described in the issue, could this cause problems during future Xwiki upgrades / DM migrations, or is it a safe change to do?

#6

What those alter will do is actually putting your database in a more standard state (what is expected of a recent XWiki version database) so it’s actually safer for future upgrades that not doing it.

#7

Ok - so now I altered the columns mentioned in XWIKI-17200 to be LONGTEXT and then ran the recoding-script again.
The “Row size too large” errors are gone, as hoped, but the “foreign key constraint” errors still persist - MariaDB apparently cannot recode a column if it’s used in a foreign key constraint, so it stays utf8 instead of utf8mb4.

Still ok and shall/can I keep it this way, or should I somehow try to also recode those columns?

Another option might be to dump the DB, adjust charset definitions in the dump file and then reload everything - however this looks like a sledgehammer approach to me…

root@example:~# mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2087
Server version: 10.3.27-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE dbnamexx;          
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [dbnamexx]> 
MariaDB [dbnamexx]> ALTER TABLE activitystream_events MODIFY ase_url LONGTEXT, MODIFY ase_title LONGTEXT, MODIFY ase_body LONGTEXT, MODIFY ase_param1 LONGTEXT, MODIFY ase_param2 LONGTEXT, MODIFY ase_param3 LONGTEXT, MODIFY ase_param4 LONGTEXT, MODIFY ase_param5 LONGTEXT;
Query OK, 34229 rows affected (1.630 sec)              
Records: 34229  Duplicates: 0  Warnings: 0

MariaDB [dbnamexx]> ALTER TABLE xwikistatsreferer MODIFY XWR_REFERER LONGTEXT;
Query OK, 0 rows affected (0.056 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dbnamexx]> ALTER TABLE xwikistatsvisit MODIFY XWV_USER_AGENT LONGTEXT, MODIFY XWV_COOKIE LONGTEXT;
Query OK, 0 rows affected (0.077 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [dbnamexx]> ALTER TABLE xwikipreferences MODIFY XWP_LEFT_PANELS LONGTEXT, MODIFY XWP_RIGHT_PANELS LONGTEXT, MODIFY XWP_DOCUMENT_BUNDLES LONGTEXT;
Query OK, 56 rows affected (0.047 sec)             
Records: 56  Duplicates: 0  Warnings: 0

MariaDB [dbnamexx]> Bye
root@example:~# db=dbnamexx
root@example:~# to_collation=utf8mb4_bin
root@example:~# to_character_set=utf8mb4 
root@example:~# mysql_cmd="mysql"
root@example:~# $mysql_cmd -e "ALTER DATABASE $db CHARACTER SET $to_character_set COLLATE $to_collation;"
root@example:~# TBL_LIST=$($mysql_cmd -N -s -r -e "use $db;show tables;")
root@example:~# for tbl_name in $TBL_LIST; do $mysql_cmd -e "alter table $db.$tbl_name convert to character set $to_character_set collate $to_collation;"; done
ERROR 1833 (HY000) at line 1: Cannot change column 'ase_eventid': used in a foreign key constraint 'FK2bj2ghquiq79toomkvugw7iie' of table 'dbnamexx.activitystream_events_targets'
ERROR 1832 (HY000) at line 1: Cannot change column 'ases_eventid': used in a foreign key constraint 'FKmwu6uxkc2jl3yvej4wi1p0pv0'
ERROR 1832 (HY000) at line 1: Cannot change column 'eventId': used in a foreign key constraint 'FK2bj2ghquiq79toomkvugw7iie'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWS_NAME': used in a foreign key constraint 'FKDEAEAB5D3433FD87'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWD_NAME': used in a foreign key constraint 'FK5A1CD9A1A947AA5'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWF_NAME': used in a foreign key constraint 'FKFB291FBF1DFF14A1'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWI_NAME': used in a foreign key constraint 'FK7F8AB31D231EFB9B'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FK6661970F283EE295'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKC0862BA3FB72A11'
ERROR 1833 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKC0862BA3FB72A11' of table 'dbnamexx.xwikilistitems'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWL_NAME': used in a foreign key constraint 'FKDF25AE4F283EE295'
ERROR 1833 (HY000) at line 1: Cannot change column 'XWP_NAME': used in a foreign key constraint 'FK2780715A3433FD87' of table 'dbnamexx.xwikistrings'
ERROR 1832 (HY000) at line 1: Cannot change column 'XWS_NAME': used in a foreign key constraint 'FK2780715A3433FD87'
root@example:~# $mysql_cmd -e "USE $db;SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE();"
+---------------+--------------+-------------------------------+--------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                    | COLUMN_NAME                    | COLLATION_NAME |
+---------------+--------------+-------------------------------+--------------------------------+----------------+
(...)
| def           | dbnamexx     | activitystream_events_targets | eventId                        | utf8_bin       |
| def           | dbnamexx     | activitystream_events_targets | value                          | utf8_bin       |
| def           | dbnamexx     | xwikiproperties               | XWP_ID                         | NULL           |
| def           | dbnamexx     | xwikiproperties               | XWP_NAME                       | utf8_bin       |
| def           | dbnamexx     | xwikiproperties               | XWP_CLASSTYPE                  | utf8_bin       |
(...)
| def           | dbnamexx     | xwikilargestrings             | XWL_NAME                       | utf8_bin       |
| def           | dbnamexx     | xwikilargestrings             | XWL_VALUE                      | utf8_bin       |
(...)
| def           | dbnamexx     | xwikiintegers                 | XWI_NAME                       | utf8_bin       |
(...)
| def           | dbnamexx     | xwikilists                    | XWL_NAME                       | utf8_bin       |
(...)
| def           | dbnamexx     | xwikidates                    | XWS_NAME                       | utf8_bin       |
(...)
| def           | dbnamexx     | xwikilistitems                | XWL_NAME                       | utf8_bin       |
| def           | dbnamexx     | xwikilistitems                | XWL_VALUE                      | utf8_bin       |
(...)
| def           | dbnamexx     | activitystream_events         | ase_eventid                    | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_requestid                  | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_stream                     | utf8_bin       |
(...)
| def           | dbnamexx     | activitystream_events         | ase_type                       | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_application                | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_user                       | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_wiki                       | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_space                      | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_page                       | utf8_bin       |
(...)
| def           | dbnamexx     | activitystream_events         | ase_url                        | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_title                      | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_body                       | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_version                    | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_param1                     | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_param2                     | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_param3                     | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_param4                     | utf8_bin       |
| def           | dbnamexx     | activitystream_events         | ase_param5                     | utf8_bin       |
(...)
| def           | dbnamexx     | xwikistrings                  | XWS_NAME                       | utf8_bin       |
| def           | dbnamexx     | xwikistrings                  | XWS_VALUE                      | utf8_bin       |
(...)
| def           | dbnamexx     | xwikifloats                   | XWF_NAME                       | utf8_bin       |
(...)
| def           | dbnamexx     | xwikidoubles                  | XWD_NAME                       | utf8_bin       |
(...)
| def           | dbnamexx     | xwikilongs                    | XWL_NAME                       | utf8_bin       |
| def           | dbnamexx     | xwikilongs                    | XWL_VALUE                      | NULL           |
| def           | dbnamexx     | activitystream_events_status  | ases_eventid                   | utf8_bin       |
| def           | dbnamexx     | activitystream_events_status  | ases_entityid                  | utf8_bin       |
| def           | dbnamexx     | activitystream_events_status  | ases_read                      | NULL           |
+---------------+--------------+-------------------------------+--------------------------------+----------------+
#8

Having different encoding should not really be a problem, it’s just that those specific fields will be limited by what utf8mb3 (utf8) supports (which is mostly related to emoticons I think). But I would try a bit more since you might end up with new surprises in the future when we will assume that everything is always based on utf8mb4 (it kind of is already more and more).

#9

What do you mean by “try a bit more”?

I tried to convert all columns I could, the suggested script fails on quite a few as they are referenced in foreign key constraints.

So I’d probably have to remove those foreign key constraints, convert the columns and afterwards re-establish all constraints correctly - which seems to be very error prone to me if done manually, and I fear ending up with a damaged DB with incorrect, missing or undesired additional constraints in place…

So I executed the script recommended in the documentation, but it didn’t fully resolve the situation, only as far as shown above. Are there any additional suggested steps I could take, or do I have to take the “manual” route?

Another question is how many Xwiki users would actually do this, or even know about the underlying issues, instead of just running into such exceptions “for no reason”, which then leaves a bad impression…

I saw that some work was done on a DB migration issue in Jira - does this mean that such a migration will be performed automatically during an update in the future? Will this also work for columns which are target of foreign key constraints?

#10

In MySQL you can disable foreign key checks temporarily, see How to Disable Foreign Key Constraint Checks in MySQL.

The proposed script should definitely be improved.

If you are referring to https://jira.xwiki.org/browse/XWIKI-17200 this one is related to the type of the fields that were fixed in https://jira.xwiki.org/browse/XWIKI-15215. It was required to be able to do https://jira.xwiki.org/browse/XWIKI-18429 (which increase the maximum size of references and short strings to 768 instead of 255).

Currently, utf8 is still supported by XWiki and the encoding to use is still a user choice. I’m just saying that unless you have a specific reason to use utf8mb3 the best is always to use utf8mb4 because it’s the future. In practice the only issue you can have (and for a long time) with utf8mb3 is that emojis won’t work.

#11

Ok, apparently I confused something in regard to the DB migration, yes. I was referring to the colum type change, which has nothing to do with the encoding used.

Thanks for the hint with the flag to disable the key constraints, using this, recoding worked. I’m a bit uneasy that MySQL/MariaDB do not seem to provide a way to actually re-check those constraints after temporarily disabling them, opening the doors for “theoretically impossible” DB inconsistencies, but I just hope those didn’t creep in somehow during the few minutes it took to ALTER the affected tables… :wink: