xoops forums

fabou78

Friend of XOOPS
Posted on: 9/3 10:39
fabou78
fabou78 (Show more)
Friend of XOOPS
Posts: 39
Since: 2012/6/16
#1

2.5.10 - User profile won't install when using utf8mb4

Setup of the server:
XOOPS Version XOOPS 2.5.10
PHP Version 7.2.22
mySQL Version 5.7.27-0ubuntu0.16.04.1 - (Ubuntu)
OS Linux Ubuntu Server

Creating database at install won't work with my current provider so I need to create database beforehand. The default charset for my provider is utf8mb4_unicode_ci.

When using this, I can't install the user profile module and it is showing the following MySQL related error.
0.000255 CREATE TABLE profile_field ( `field_idint(12unsigned NOT NULL auto_increment, `cat_idsmallint(5unsigned NOT NULL default '0', `field_typevarchar(30NOT NULL default '', `field_valuetypetinyint(2unsigned NOT NULL default '0', `field_namevarchar(255NOT NULL default '', `field_titlevarchar(255NOT NULL default '', `field_descriptiontext, `field_requiredtinyint(1unsigned NOT NULL default '0', `field_maxlengthsmallint(6unsigned NOT NULL default '0', `field_weightsmallint(6unsigned NOT NULL default '0', `field_defaulttext, `field_notnulltinyint(1unsigned NOT NULL default '0', `field_edittinyint(1unsigned NOT NULL default '0', `field_showtinyint(1unsigned NOT NULL default '0', `field_configtinyint(1unsigned NOT NULL default '0', `field_optionstext, `step_idsmallint(3unsigned NOT NULL default '0'PRIMARY KEY (`field_id`), UNIQUE KEY `field_name` (`field_name`), KEY `step` (`step_id`, `field_weight`) ) ENGINE=MyISAM
Error number
1071
Error message
Specified key was too longmax key length is 1000 bytes



If I force the database into utf8_general_ci the installation would work fine.

I have read somewhere that since MySQL 5.5.3 is advised.

Is there no support yet for XOOPS runing on utf8mb4_unicode_ci?

I have little understanding of the issue nor the effect of changing character set on database but I ham fully admin on my server so can run any commands for debug if required.

My aim is to reproduce an environment as close as possible to our hosting service for future website migration.

Mamba

Moderator
Posted on: 9/3 11:50
Mamba
Mamba (Show more)
Moderator
Posts: 10797
Since: 2004/4/23
#2

Re: 2.5.10 - User profile won't install when using utf8mb4

The utf8mb4_unicode_ci is supported, but it looks like it needs more space for the Unique Key than the database has.

So in the Profile's MySQL file (/sql/mysql.sql) change line 30 to something like:

UNIQUE KEY `field_name` (`field_name`(200)),


and try again.

If the problem is still there, go lower with the number, let's say:
175, 150, 125, 100

That should fix the issue.
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

fabou78

Friend of XOOPS
Posted on: 9/3 14:01
fabou78
fabou78 (Show more)
Friend of XOOPS
Posts: 39
Since: 2012/6/16
#3

Re: 2.5.10 - User profile won't install when using utf8mb4

Doing the change as requested moved the problem to table "profile_regstep" (line 43 in same file you mentioned)

So I have then changed
`step_name`       varchar(255)
to
`step_name`       varchar(200)
(line 45) and it installed the module fine.

Do you have an idea on what is going on?

Mamba

Moderator
Posted on: 9/3 20:34
Mamba
Mamba (Show more)
Moderator
Posts: 10797
Since: 2004/4/23
#4

Re: 2.5.10 - User profile won

No, try changing the line 51 to:

KEY `sort` (`step_order`, `step_name`(240))


The issue is with the MySQL key length limitations: it's 767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables.
In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.

The indexes worked fine with utf8_unicode_ci, since it uses 3 bytes per character, but when you're using utf8mb4_unicode_ci, you're adding extra byte, as it is using 4 bytes, and you're going over the limit.

MySQL 5.7 avoids the problem. Consider upgrading
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

fabou78

Friend of XOOPS
Posted on: 9/5 15:20
fabou78
fabou78 (Show more)
Friend of XOOPS
Posts: 39
Since: 2012/6/16
#5

Re: 2.5.10 - User profile won

Thanks Mamba for your help got it sorted now.

I can update mysql on my server but my hosting provider won't so need to stick to the version I have.

Mamba

Moderator
Posted on: 9/6 3:01
Mamba
Mamba (Show more)
Moderator
Posts: 10797
Since: 2004/4/23
#6

Re: 2.5.10 - User profile won

I'm glad that things got sorted out!
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs