Илья Сторчевой (Ярмарка Мастеров - www.livemaster.ru)
2016-Dec-14 09:02 UTC
[Gluster-users] Problems with Mysql foreign keys using GlusterFS as DB location.
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 alreadybeen 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/20161214/e22ec0b1/attachment.html>
Илья Сторчевой (Ярмарка Мастеров - www.livemaster.ru)
2016-Dec-15 09:33 UTC
[Gluster-users] Fwd: Problems with Mysql foreign keys using GlusterFS as DB location.
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 alreadybeen 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* -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://www.gluster.org/pipermail/gluster-users/attachments/20161215/c7084091/attachment.html>