1
fabou78
2.5.10 - User profile won't install when using utf8mb4
  • 2019/9/3 10:39

  • fabou78

  • Friend of XOOPS

  • Posts: 43

  • Since: 2012/6/16


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.

2
Mamba
Re: 2.5.10 - User profile won't install when using utf8mb4
  • 2019/9/3 11:50

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


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

3
fabou78
Re: 2.5.10 - User profile won't install when using utf8mb4
  • 2019/9/3 14:01

  • fabou78

  • Friend of XOOPS

  • Posts: 43

  • Since: 2012/6/16


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?

4
Mamba
Re: 2.5.10 - User profile won
  • 2019/9/3 20:34

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


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

5
fabou78
Re: 2.5.10 - User profile won
  • 2019/9/5 15:20

  • fabou78

  • Friend of XOOPS

  • Posts: 43

  • Since: 2012/6/16


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.

6
Mamba
Re: 2.5.10 - User profile won
  • 2019/9/6 3:01

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


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

Login

Who's Online

192 user(s) are online (103 user(s) are browsing Support Forums)


Members: 0


Guests: 192


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits