PubSub-4.0.0 with MySQL 5.6 fails to load (#67)

Activities

Wojciech Kapcia (Tigase) opened 7 years ago

While using Server version: 5.6.37-log MySQL Community Server (GPL) it's impossible to load PubSub schema

Loading schema from file(s): database/mysql-pubsub-schema-4.0.0.sql, URI: jdbc:mysql://…
Failed to execute query: alter table tig_pubsub_items modify `id` varchar(1000) character set utf8mb4 collate utf8mb4_bin, modify `data` mediumtext character set utf8mb4 collate utf8mb4_bin

=====
Failure: Index column size too large. The maximum column size is 767 bytes.
=====

Andrzej Wójcik (Tigase) commented 7 years ago

I'm not sure if we should change schema in this way, as in this case index will be destroyed - deleted every time we will run upgrade-schema and as this table may have a lot of values, the creation of a new index will be costly.

In my opinion, it would be better to check the state of the variable and size of an indexed field. I would propose to replace this part of SQL schema with the following one:

-- QUERY START:
call TigExecuteIf(
    (select * 
from information_schema.statistics s1 
inner join information_schema.statistics s2 on s1.table_schema = s2.table_schema and s1.table_name = s2.table_name and s1.index_name = s2.index_name
join (select @@GLOBAL.innodb_large_prefix as val) x
where     
    s1.table_schema = database() 
    and s1.table_name = 'tig_pubsub_items' 
    and s1.column_name = 'node_id'
    and s2.column_name = 'id'
    and ((s2.sub_part = 255
    and x.val = 0) or (s2.sub_part <> 255 and x.val = 1))),
    "drop index `node_id` on tig_pubsub_items"
);
-- QUERY END:

-- QUERY START:
alter table tig_pubsub_items
    modify `id` varchar(1000) character set utf8mb4 collate utf8mb4_bin,
    modify `data` mediumtext character set utf8mb4 collate utf8mb4_bin;
-- QUERY END:

-- QUERY START:
call TigExecuteIf(
    (select 1 from (select @@GLOBAL.innodb_large_prefix as val) x where x.val = 0),
    "create index node_id on tig_pubsub_items ( node_id, id(190) ) using hash;"
);
-- QUERY END:
-- QUERY START:
call TigExecuteIf(
    (select 1 from (select @@GLOBAL.innodb_large_prefix as val) x where x.val = 1),
    "create index node_id on tig_pubsub_items ( node_id, id(255) ) using hash;"
);
-- QUERY END:

It should work OK on 5.6 and 5.7. I've checked single queries but not the whole change to the SQL schema, so it may contain some syntax errors.

Wojciech Kapcia (Tigase) commented 7 years ago

Thank you for the suggestion. I've incorporated it with small modifications to correctly re-create indexes under right conditions.

Type	Bug
Priority	Normal
Assignee	Wojciech Kapcia (Tigase)
RedmineID	6425
Spent time	6h 45m

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-pubsub#67