XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. 2.5.x Support 
  4.  / 2.5.10 - User profile won't install when using utf8mb4
Register
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_id` int(12) unsigned NOT NULL auto_increment, `cat_id` smallint(5) unsigned NOT NULL default '0', `field_type` varchar(30) NOT NULL default '', `field_valuetype` tinyint(2) unsigned NOT NULL default '0', `field_name` varchar(255) NOT NULL default '', `field_title` varchar(255) NOT NULL default '', `field_description` text, `field_required` tinyint(1) unsigned NOT NULL default '0', `field_maxlength` smallint(6) unsigned NOT NULL default '0', `field_weight` smallint(6) unsigned NOT NULL default '0', `field_default` text, `field_notnull` tinyint(1) unsigned NOT NULL default '0', `field_edit` tinyint(1) unsigned NOT NULL default '0', `field_show` tinyint(1) unsigned NOT NULL default '0', `field_config` tinyint(1) unsigned NOT NULL default '0', `field_options` text, `step_id` smallint(3) unsigned 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 long; max 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: 11420

  • 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.11 | 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: 11420

  • 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.11 | 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: 11420

  • Since: 2004/4/23


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

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

174 user(s) are online (148 user(s) are browsing Support Forums)

Members: 0

Guests: 174

more...

Donat-O-Meter

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

Latest GitHub Commits