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

Activities

wojciech.kapcia@tigase.net 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.net 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.net
RedmineID	6425
Spent time	6h 45m

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/tigase-pubsub#67