[Gluster-users] Mysql missed foreign keys using GlusterFS as DB location

[Илья Сторчевой (Ярмарка Мастеров - www.livemaster.ru)]

Hello.
I have some LXC containers on two servers, each container placed in
personal glusterfs replication node. Gluster used as failover: when one
server is down i have possibility run container on second server without
loosing data.
In one of containers I have a Atlassian Confluence software, that using
mysql 5.6, and when I try to update my Confluence to last version I got the
error that updater failed to execute mysql query "alter table
CONTENTPROPERTIES add constraint FK984C5E4C8DD41734 foreign key (CONTENTID)
references CONTENT (CONTENTID)".
>From error log I see that the alter query fails because foreign key already
been in DB, but updater don't get information about this. I have a long
discussion with Atlassian support, and we found the solution: updater get
information from "show create table" and when updater can't get that
information they try to create new foreign key.
Using this information go to mysql console in Confluence container and
execute "show create table" for one of confluence DB table and have no
foreign_keys from result, but when I execute queries for look foreign_keys
in information_schema i got the foreigh_keys info:
*mysql> show create table confluence.CONTENTPROPERTIES;*
CONTENTPROPERTIES | CREATE TABLE `CONTENTPROPERTIES` (
  `PROPERTYID` bigint(20) NOT NULL,
  `PROPERTYNAME` varchar(255) COLLATE utf8_bin NOT NULL,
  `STRINGVAL` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `LONGVAL` bigint(20) DEFAULT NULL,
  `DATEVAL` datetime DEFAULT NULL,
  `CONTENTID` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`PROPERTYID`),
  KEY `content_prop_str_idx` (`STRINGVAL`),
  KEY `content_prop_long_idx` (`LONGVAL`),
  KEY `c_contentproperties_idx` (`CONTENTID`),
  KEY `content_prop_name_idx` (`PROPERTYNAME`),
  KEY `content_prop_date_idx` (`DATEVAL`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

*mysql> SELECT * FROM information_schema.TABLE_CONSTRAINTS  WHERE
information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' AND
information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'CONTENTPROPERTIES'\G*
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: confluence
   CONSTRAINT_NAME: FK984C5E4C8DD41734
      TABLE_SCHEMA: confluence
        TABLE_NAME: CONTENTPROPERTIES
   CONSTRAINT_TYPE: FOREIGN KEY

So, when i don't use glusterFS for container error not ocured and update
proceed successfully and "show create table" shows a fireign_keys info

Do you have same error or do you know how to fix that problem with mysql
when using GlusterFS?

PS:
OS Debian 8
GlusterFS version 3.7.18-1

*gluster volume info GFSconfluence*
Volume Name: GFSconfluence
Type: Replicate
Volume ID: fda9cd44-2439-4ab2-9e2e-34b2f54dd14a
Status: Started
Number of Bricks: 1 x 2 = 2
Transport-type: tcp
Bricks:
Brick1: 192.168.255.3:/lxc/confluence/glusterfs
Brick2: 192.168.255.4:/lxc/confluence/glusterfs
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.gluster.org/pipermail/gluster-users/attachments/20161215/87314845/attachment.html>