Announcement

vemi2709

Member

Registered: 2016-10-10

Posts: 9

utf8mb4_general_ci problem

Hi, when I try to update database to version 2.85 (or 2.86), I eventually get the following info:

    lh_chat_paid - [hash] column type/collation is not correct
    lh_users_session - [device_token] column type/collation is not correct
    lh_canned_msg - [title] column type/collation is not correct
    lh_chat_online_user_footprint - [vtime] column type/collation is not correct
    lh_faq - [ url ] column type/collation is not correct
    lh_users - [xmpp_username] column type/collation is not correct

Queries which will be executed on update

    ALTER TABLE `lh_chat_paid` CHANGE `hash` `hash` varchar(250) COLLATE 'utf8mb4_general_ci' NOT NULL;
    ALTER TABLE `lh_users_session` CHANGE `device_token` `device_token` varchar(255) COLLATE 'utf8mb4_general_ci' NOT NULL;
    ALTER TABLE `lh_canned_msg` CHANGE `title` `title` varchar(250) COLLATE 'utf8mb4_general_ci' NOT NULL;
    ALTER TABLE `lh_chat_online_user_footprint` CHANGE `vtime` `vtime` varchar(250) COLLATE 'utf8mb4_general_ci' NOT NULL;
    ALTER TABLE `lh_faq` CHANGE `url` `url` varchar(250) COLLATE 'utf8mb4_general_ci' NOT NULL;
    ALTER TABLE `lh_users` CHANGE `xmpp_username` `xmpp_username` varchar(200) COLLATE 'utf8mb4_general_ci' NOT NULL;

If I try to make the changes with phpMyAdmin, it gives

error 1709 index column size too large. the maximum column size is 767 bytes

If the column sizes are reduced to 191 chars, I can execute the above commands successfully in phpMyAdmin.
Is there a workaround for this problem?

I have
innodb_file_format              = Barracuda
innodb_large_prefix             = 1
in my.cnf (MariaDB 5.5.56)

-VeM-

remdex

Administrator

From: Lithuania

Registered: 2012-09-23

Posts: 3,661

Website

Re: utf8mb4_general_ci problem

Hi,

This seems like server issue. Like here related issue. Perhaps it can help you
https://github.com/go-gitea/gitea/issues/2979

vemi2709

Member

Registered: 2016-10-10

Posts: 9

Re: utf8mb4_general_ci problem

Thanx , this solved the problem:

ALTER TABLE lh_chat_paid ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_users_session ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_canned_msg ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_chat_online_user_footprint ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_faq ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_users ROW_FORMAT=DYNAMIC;

remdex

Administrator

From: Lithuania

Registered: 2012-09-23

Posts: 3,661

Website

Re: utf8mb4_general_ci problem

Also it seems it's related to older mysql versions. Since 5.7 it should work out of the box.

adamlasenza

Member

Registered: 2015-05-05

Posts: 2

Re: utf8mb4_general_ci problem

Just run this in your MySQL query

SET GLOBAL innodb_file_format=barracuda; SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE lh_chat_paid ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_users_session ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_canned_msg ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_chat_online_user_footprint ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_faq ROW_FORMAT=DYNAMIC;
ALTER TABLE lh_users ROW_FORMAT=DYNAMIC;
SHOW WARNINGS LIMIT 12;

Then just update the database through the live helper chat update page, worked for me.

rapafleutz

Member

Registered: 2018-04-19

Posts: 50

Re: utf8mb4_general_ci problem

Hi people. I'm trying to update from 2.97 to 3.02 version, and i got a very similar problem. When i try to update de database, i got this message:

Database structure check

    "lh_chat_paid - [hash] column type/collation is not correct"
    "lh_users - [username] column type/collation is not correct, [password] column type/collation is not correct, [session_id] column type/collation is not correct, [operation_admin] column type/collation is not correct,  [time_zone] column type/collation is not correct, [name] column type/collation is not correct, [surname] column type/collation is not correct, [chat_nickname] column type/collation is not correct, [filepath] column type/collation is not correct, [filename] column type/collation is not correct, [job_title] column type/collation is not correct, [xmpp_username] column type/collation is not correct, [skype] column type/collation is not correct, [departments_ids] column type/collation is not correct"

Queries which will be executed on update

    ALTER TABLE `lh_chat_paid` CHANGE `hash` `hash` varchar(250) COLLATE 'utf8mb4_unicode_ci' NOT NULL;
    ALTER TABLE `lh_users` CHANGE `username` `username` varchar(40) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `password` `password` varchar(200) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `session_id` `session_id` varchar(40) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `operation_admin` `operation_admin` text COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `email` `email` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `time_zone` `time_zone` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `name` `name` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `surname` `surname` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `chat_nickname` `chat_nickname` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `filepath` `filepath` varchar(200) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `filename` `filename` varchar(200) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `job_title` `job_title` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `xmpp_username` `xmpp_username` varchar(200) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `skype` `skype` varchar(50) COLLATE 'utf8mb4_unicode_ci' NOT NULL, CHANGE `departments_ids` `departments_ids` varchar(100) COLLATE 'utf8mb4_unicode_ci' NOT NULL;

I try the solution proposed, but it not work.
PS: Sorry for the really big text on this post.