Bug（バグ） #1483: マイグレート後のDB構造が新規構築時と一致しない - opCommunityTopicPlugin - OpenPNE Issue Tracking System

Bug（バグ） #1483

マイグレート後のDB構造が新規構築時と一致しない

Added by Masato Nagasawa over 12 years ago. Updated over 11 years ago.

Status:

Fixed（完了）

Priority:

High（高め）

Assignee:

Masato Nagasawa

Target version:

1.0.2

Start date:

2010-08-05

Due date:

% Done:

100%

3.6 で発生するか:

Yes

［QA］バグ通知済:

Yes

3.8 で発生するか:

Unknown (未調査)

opCommunityTopicPlugin 絞込条件:

Description

Overview (現象)¶

post, file に関してのインデックスとカスケーディングデリートの情報がマイグレート時に付加されていない。

新規インストール時¶

mysql> SHOW CREATE TABLE community_topic_comment_image\G
*************************** 1. row ***************************
       Table: community_topic_comment_image
Create Table: CREATE TABLE `community_topic_comment_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `file_id` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number_idx` (`id`,`number`),
  KEY `post_id_idx` (`post_id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `community_topic_comment_image_file_id_file_id` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_topic_comment_image_post_id_community_topic_comment_id` FOREIGN KEY (`post_id`) REFERENCES `community_topic_comment` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

マイグレート時¶

mysql> SHOW CREATE TABLE community_topic_comment_image\G
*************************** 1. row ***************************
       Table: community_topic_comment_image
Create Table: CREATE TABLE `community_topic_comment_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `file_id` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number` (`id`,`number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Causes (原因)¶

マイグレーション側で新規インストール時とテーブル構造を合わせていないため。

Way to fix (修正内容)¶

post_id のインデックスを追加
file_id のインデックスを追加
post_id の外部キー制約を追加
file_id の外部キー制約を追加

文字コードが一致していないが、
文字列が入るカラムが存在しないため対応は行わない。

Related issues

History

#1 Updated by Rimpei Ogawa over 12 years ago

3.6 で発生するか set to Yes

#2 Updated by Kousuke Ebihara over 12 years ago

Priority changed from Normal（通常） to High（高め）

#3 Updated by Kousuke Ebihara about 12 years ago

［QA］バグ通知済 set to No

#4 Updated by Kousuke Ebihara about 12 years ago

［QA］バグ通知済 changed from No to Yes

#5 Updated by Masato Nagasawa over 11 years ago

Status changed from New（新規） to Accepted（着手）
Assignee set to Masato Nagasawa

#6 Updated by Masato Nagasawa over 11 years ago

修正後のテーブル構造

mysql> SHOW CREATE TABLE community_topic_image\G
*************************** 1. row ***************************
       Table: community_topic_image
Create Table: CREATE TABLE `community_topic_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `file_id` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number` (`id`,`number`),
  KEY `post_id_idx` (`post_id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `community_topic_image_post_id_community_topic_id` FOREIGN KEY (`post_id`) REFERENCES `community_topic` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_topic_image_file_id_file_id` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE community_topic_comment_image\G
*************************** 1. row ***************************
       Table: community_topic_comment_image
Create Table: CREATE TABLE `community_topic_comment_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `file_id` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number` (`id`,`number`),
  KEY `post_id_idx` (`post_id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `community_topic_comment_image_post_id_community_topic_comment_id` FOREIGN KEY (`post_id`) REFERENCES `community_topic_comment` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_topic_comment_image_file_id_file_id` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> SHOW CREATE TABLE community_event_image\G
*************************** 1. row ***************************
       Table: community_event_image
Create Table: CREATE TABLE `community_event_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `file_id` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number` (`id`,`number`),
  KEY `post_id_idx` (`post_id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `community_event_image_post_id_community_event_id` FOREIGN KEY (`post_id`) REFERENCES `community_event` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_event_image_file_id_file_id` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> SHOW CREATE TABLE community_event_comment_image\G
*************************** 1. row ***************************
       Table: community_event_comment_image
Create Table: CREATE TABLE `community_event_comment_image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `file_id` int(11) DEFAULT NULL,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_number` (`id`,`number`),
  KEY `post_id_idx` (`post_id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `community_event_comment_image_post_id_community_event_comment_id` FOREIGN KEY (`post_id`) REFERENCES `community_event_comment` (`id`) ON DELETE CASCADE,
  CONSTRAINT `community_event_comment_image_file_id_file_id` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1