Fwd: Problems with Mysql foreign keys using GlusterFS as DB location.

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

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

--

С уважением,

Сторчевой Илья,

системный администратор
--------------------------------------------

skype i.storchevoy-lm

тел. 8-952-530-4151

_______________________________________________
Gluster-users mailing list
Gluster-users@xxxxxxxxxxx
http://www.gluster.org/mailman/listinfo/gluster-users