[xwiki-users] 4-byte UTF and XWiki (MySQL utf8mb4 encoding)
I have an XWiki installation, and I noticed that emoji's were not getting saved correctly and were rendering as question marks in XWiki. This is caused by MySQL's default encoding which is "utf8" and not "utf8mb4" (4-byte UTF to store the full unicode character set) To fix this, I am in the process of converting all my MySQL database tables into utf8mb4 encoding, which is turning out to be quite a lot of manual work. When XWiki creates the database tables for MySQL, can it use utf8mb4 encoding by default? Is this something I could open a bug request for?
Some more information: On my Mac, when I try to set up XWiki with MySQL, I ran the following: # Install mysql and start brew update brew install mysql mysql.server start # Now I can connect as "mysql -uroot" # Create a new MySQL database for XWiki # see http://platform.xwiki.org/xwiki/bin/view/AdminGuide/InstallationMySQL mysql -u root -e "create database xwiki default character set utf8mb4 collate utf8mb4_unicode_ci" mysql -u root -e "grant all privileges on *.* to xwiki@localhost identified by 'xwiki'" # Configure hibernate.cfg.xml # This is only the mysql section <property name="connection.url">jdbc:mysql://localhost/xwiki</property> <property name="connection.username">xwiki</property> <property name="connection.password">xwiki</property> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property> <property name="connection.useUnicode">true</property> <property name="connection.characterEncoding">UTF-8</property> <property name="dbcp.poolPreparedStatements">true</property> <property name="dbcp.maxOpenPreparedStatements">20</property> <mapping resource="xwiki.hbm.xml"/> <mapping resource="feeds.hbm.xml"/> <mapping resource="activitystream.hbm.xml"/> <mapping resource="instance.hbm.xml"/> <mapping resource="mailsender.hbm.xml"/> Now when I start XWiki, I get the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 It looks like whatever creates the XWiki tables is not handling utf8mb4 (see also https://mathiasbynens.be/notes/mysql-utf8mb4 ). On Wed, Apr 6, 2016 at 5:17 PM, Debajit Adhikary <[email protected]> wrote:
I have an XWiki installation, and I noticed that emoji's were not getting saved correctly and were rendering as question marks in XWiki. This is caused by MySQL's default encoding which is "utf8" and not "utf8mb4" (4-byte UTF to store the full unicode character set)
To fix this, I am in the process of converting all my MySQL database tables into utf8mb4 encoding, which is turning out to be quite a lot of manual work.
When XWiki creates the database tables for MySQL, can it use utf8mb4 encoding by default? Is this something I could open a bug request for?
XWiki use utf8_bin for subwikis (and recommend it for main wiki in the documentation) right now. Since we use than in the hope it support everything you should definitely open a jira issue. On Thu, Apr 7, 2016 at 2:17 AM, Debajit Adhikary <[email protected]> wrote:
I have an XWiki installation, and I noticed that emoji's were not getting saved correctly and were rendering as question marks in XWiki. This is caused by MySQL's default encoding which is "utf8" and not "utf8mb4" (4-byte UTF to store the full unicode character set)
To fix this, I am in the process of converting all my MySQL database tables into utf8mb4 encoding, which is turning out to be quite a lot of manual work.
When XWiki creates the database tables for MySQL, can it use utf8mb4 encoding by default? Is this something I could open a bug request for? _______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
-- Thomas Mortagne
It will be almost impossible to use utf8mb4 with XWiki, because of the terrible way mysql is designed. Here's the reason why: mysql imposes several hard limits on the maximum size of several things: length of indexes, length of rows. These lengths, unfortunately, are not computed as the maximum size of the actual data, but of the theoretical data that could be placed in there. This means that the size of a VARCHAR(255) isn't 255 bytes, but 255 chars * maximum number of bytes that such a char could have (3 for utf8, 4 for utf8mb4). You could make it work if you change the schema to reduce the length of several fields, but you would do that at your own risk. On 04/06/2016 08:58 PM, Debajit Adhikary wrote:
Some more information:
On my Mac, when I try to set up XWiki with MySQL, I ran the following:
# Install mysql and start
brew update brew install mysql mysql.server start # Now I can connect as "mysql -uroot"
# Create a new MySQL database for XWiki # see http://platform.xwiki.org/xwiki/bin/view/AdminGuide/InstallationMySQL
mysql -u root -e "create database xwiki default character set utf8mb4 collate utf8mb4_unicode_ci"
mysql -u root -e "grant all privileges on *.* to xwiki@localhost identified by 'xwiki'"
# Configure hibernate.cfg.xml # This is only the mysql section
<property name="connection.url">jdbc:mysql://localhost/xwiki</property> <property name="connection.username">xwiki</property> <property name="connection.password">xwiki</property> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property> <property name="connection.useUnicode">true</property> <property name="connection.characterEncoding">UTF-8</property> <property name="dbcp.poolPreparedStatements">true</property> <property name="dbcp.maxOpenPreparedStatements">20</property> <mapping resource="xwiki.hbm.xml"/> <mapping resource="feeds.hbm.xml"/> <mapping resource="activitystream.hbm.xml"/> <mapping resource="instance.hbm.xml"/> <mapping resource="mailsender.hbm.xml"/>
Now when I start XWiki, I get the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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
It looks like whatever creates the XWiki tables is not handling utf8mb4 (see also https://mathiasbynens.be/notes/mysql-utf8mb4 ).
On Wed, Apr 6, 2016 at 5:17 PM, Debajit Adhikary <[email protected]> wrote:
I have an XWiki installation, and I noticed that emoji's were not getting saved correctly and were rendering as question marks in XWiki. This is caused by MySQL's default encoding which is "utf8" and not "utf8mb4" (4-byte UTF to store the full unicode character set)
To fix this, I am in the process of converting all my MySQL database tables into utf8mb4 encoding, which is turning out to be quite a lot of manual work.
When XWiki creates the database tables for MySQL, can it use utf8mb4 encoding by default? Is this something I could open a bug request for?
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
-- Sergiu Dumitriu http://purl.org/net/sergiu/
I have changed my MySQL database columns to support utf8mb4 (4-byte unicode), and I can render chinese characters etc correctly now. When I upgrade my XWiki to a newer version in the future, will this cause issues in the upgrade? (I am guessing there may be database schema changes in a future XWiki version) On Thu, Apr 7, 2016 at 7:08 PM, Sergiu Dumitriu <[email protected]> wrote:
It will be almost impossible to use utf8mb4 with XWiki, because of the terrible way mysql is designed. Here's the reason why:
mysql imposes several hard limits on the maximum size of several things: length of indexes, length of rows. These lengths, unfortunately, are not computed as the maximum size of the actual data, but of the theoretical data that could be placed in there. This means that the size of a VARCHAR(255) isn't 255 bytes, but 255 chars * maximum number of bytes that such a char could have (3 for utf8, 4 for utf8mb4).
You could make it work if you change the schema to reduce the length of several fields, but you would do that at your own risk.
On 04/06/2016 08:58 PM, Debajit Adhikary wrote:
Some more information:
On my Mac, when I try to set up XWiki with MySQL, I ran the following:
# Install mysql and start
brew update brew install mysql mysql.server start # Now I can connect as "mysql -uroot"
# Create a new MySQL database for XWiki # see http://platform.xwiki.org/xwiki/bin/view/AdminGuide/InstallationMySQL
mysql -u root -e "create database xwiki default character set utf8mb4 collate utf8mb4_unicode_ci"
mysql -u root -e "grant all privileges on *.* to xwiki@localhost identified by 'xwiki'"
# Configure hibernate.cfg.xml # This is only the mysql section
<property name="connection.url">jdbc:mysql://localhost/xwiki</property> <property name="connection.username">xwiki</property> <property name="connection.password">xwiki</property> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property> <property name="connection.useUnicode">true</property> <property name="connection.characterEncoding">UTF-8</property> <property name="dbcp.poolPreparedStatements">true</property> <property name="dbcp.maxOpenPreparedStatements">20</property> <mapping resource="xwiki.hbm.xml"/> <mapping resource="feeds.hbm.xml"/> <mapping resource="activitystream.hbm.xml"/> <mapping resource="instance.hbm.xml"/> <mapping resource="mailsender.hbm.xml"/>
Now when I start XWiki, I get the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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
It looks like whatever creates the XWiki tables is not handling utf8mb4 (see also https://mathiasbynens.be/notes/mysql-utf8mb4 ).
On Wed, Apr 6, 2016 at 5:17 PM, Debajit Adhikary <[email protected]
wrote:
I have an XWiki installation, and I noticed that emoji's were not getting saved correctly and were rendering as question marks in XWiki. This is caused by MySQL's default encoding which is "utf8" and not "utf8mb4" (4-byte UTF to store the full unicode character set)
To fix this, I am in the process of converting all my MySQL database tables into utf8mb4 encoding, which is turning out to be quite a lot of manual work.
When XWiki creates the database tables for MySQL, can it use utf8mb4 encoding by default? Is this something I could open a bug request for?
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
-- Sergiu Dumitriu http://purl.org/net/sergiu/ _______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
On Fri, Apr 8, 2016 at 8:51 PM, Debajit Adhikary <[email protected]> wrote:
I have changed my MySQL database columns to support utf8mb4 (4-byte unicode), and I can render chinese characters etc correctly now.
Chinese characters work fine with uft8 too.
When I upgrade my XWiki to a newer version in the future, will this cause issues in the upgrade? (I am guessing there may be database schema changes in a future XWiki version)
The migrator usually don't look at the encoding (unless specific encoding related migration) so it should be Ok.
On Thu, Apr 7, 2016 at 7:08 PM, Sergiu Dumitriu <[email protected]> wrote:
It will be almost impossible to use utf8mb4 with XWiki, because of the terrible way mysql is designed. Here's the reason why:
mysql imposes several hard limits on the maximum size of several things: length of indexes, length of rows. These lengths, unfortunately, are not computed as the maximum size of the actual data, but of the theoretical data that could be placed in there. This means that the size of a VARCHAR(255) isn't 255 bytes, but 255 chars * maximum number of bytes that such a char could have (3 for utf8, 4 for utf8mb4).
You could make it work if you change the schema to reduce the length of several fields, but you would do that at your own risk.
On 04/06/2016 08:58 PM, Debajit Adhikary wrote:
Some more information:
On my Mac, when I try to set up XWiki with MySQL, I ran the following:
# Install mysql and start
brew update brew install mysql mysql.server start # Now I can connect as "mysql -uroot"
# Create a new MySQL database for XWiki # see http://platform.xwiki.org/xwiki/bin/view/AdminGuide/InstallationMySQL
mysql -u root -e "create database xwiki default character set utf8mb4 collate utf8mb4_unicode_ci"
mysql -u root -e "grant all privileges on *.* to xwiki@localhost identified by 'xwiki'"
# Configure hibernate.cfg.xml # This is only the mysql section
<property name="connection.url">jdbc:mysql://localhost/xwiki</property> <property name="connection.username">xwiki</property> <property name="connection.password">xwiki</property> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property> <property name="connection.useUnicode">true</property> <property name="connection.characterEncoding">UTF-8</property> <property name="dbcp.poolPreparedStatements">true</property> <property name="dbcp.maxOpenPreparedStatements">20</property> <mapping resource="xwiki.hbm.xml"/> <mapping resource="feeds.hbm.xml"/> <mapping resource="activitystream.hbm.xml"/> <mapping resource="instance.hbm.xml"/> <mapping resource="mailsender.hbm.xml"/>
Now when I start XWiki, I get the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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
It looks like whatever creates the XWiki tables is not handling utf8mb4 (see also https://mathiasbynens.be/notes/mysql-utf8mb4 ).
On Wed, Apr 6, 2016 at 5:17 PM, Debajit Adhikary <[email protected]
wrote:
I have an XWiki installation, and I noticed that emoji's were not getting saved correctly and were rendering as question marks in XWiki. This is caused by MySQL's default encoding which is "utf8" and not "utf8mb4" (4-byte UTF to store the full unicode character set)
To fix this, I am in the process of converting all my MySQL database tables into utf8mb4 encoding, which is turning out to be quite a lot of manual work.
When XWiki creates the database tables for MySQL, can it use utf8mb4 encoding by default? Is this something I could open a bug request for?
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
-- Sergiu Dumitriu http://purl.org/net/sergiu/ _______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
_______________________________________________ users mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/users
-- Thomas Mortagne
participants (3)
-
Debajit Adhikary -
Sergiu Dumitriu -
Thomas Mortagne