Форвард-инжиниринг: некоторые новые отношения терпят неудачу в заполненной базе данных (errno 150)

Question

Форвард-инжиниринг: некоторые новые отношения терпят неудачу в заполненной базе данных (errno 150)

Это еще одна ошибка 1005, ошибка 150 при создании потока таблицы. Я видел много из них, но никто не дает ответа на мой вопрос.

У меня есть база данных в MySQL Workbench, которая разработана и заполнена данными. Затем я создаю из представления модели новую таблицу. После этого я добавляю отношение:m между этой новой таблицей и существующей таблицей в моей базе данных.

Наконец, я пытаюсь синхронизировать свою базу данных. Сгенерированный код для создания двух новых таблиц:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE TABLE IF NOT EXISTS `mydb`.`SEntences` (
  `idSEntences` INT(11) NOT NULL,
  PRIMARY KEY (`idSEntences`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin;

CREATE TABLE IF NOT EXISTS `mydb`.`Tips_has_SEntences` (
  `Tips_idTips` VARCHAR(45) NOT NULL,
  `SEntences_idSEntences` INT(11) NOT NULL,
  PRIMARY KEY (`Tips_idTips`, `SEntences_idSEntences`),
  INDEX `fk_Tips_has_SEntences_SEntences1_idx` (`SEntences_idSEntences` ASC),
  INDEX `fk_Tips_has_SEntences_Tips1_idx` (`Tips_idTips` ASC),
  CONSTRAINT `fk_Tips_has_SEntences_Tips1`
    FOREIGN KEY (`Tips_idTips`)
    REFERENCES `mydb`.`Tips` (`idTips`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Tips_has_SEntences_SEntences1`
    FOREIGN KEY (`SEntences_idSEntences`)
    REFERENCES `mydb`.`SEntences` (`idSEntences`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Я получаю ошибку, с этим журналом:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'mydb.Tips_has_SEntences' (errno: 150)


CREATE TABLE IF NOT EXISTS `mydb`.`Tips_has_SEntences` (
  `Tips_idTips` VARCHAR(45) NOT NULL,
  `SEntences_idSEntences` INT(11) NOT NULL,
  PRIMARY KEY (`Tips_idTips`, `SEntences_idSEntences`),
  INDEX `fk_Tips_has_SEntences_SEntences1_idx` (`SEntences_idSEntences` ASC),
  INDEX `fk_Tips_has_SEntences_Tips1_idx` (`Tips_idTips` ASC),
  CONSTRAINT `fk_Tips_has_SEntences_Tips1`
    FOREIGN KEY (`Tips_idTips`)
    REFERENCES `mydb`.`Tips` (`idTips`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Tips_has_SEntences_SEntences1`
    FOREIGN KEY (`SEntences_idSEntences`)
    REFERENCES `mydb`.`SEntences` (`idSEntences`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin

SQL script execution finished: statements: 3 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Запустив mysql из терминала, с помощью команды SHOW ENGINE INNODB STATUS\G я получаю следующие выходные данные из самого последнего поля ошибки внешнего ключа:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
141007 14:18:53 Error in foreign key constraint of table mydb/Tips_has_SEntences:

    FOREIGN KEY (`Tips_idTips`)
    REFERENCES `mydb`.`Tips` (`idTips`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Tips_has_SEntences_SEntences1`
    FOREIGN KEY (`SEntences_idSEntences`)
    REFERENCES `mydb`.`SEntences` (`idSEntences`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_bin:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

После исследования моей проблемы, я не могу понять, что не так. Все типы данных одинаковы, внешние ключи находятся на первичных ключах (которые имеют первичные индексы на них) и т. Д. Я исследовал различные ответы на переполнение стека по этой ошибке и просмотрел эту страницу, связанную с одной из них: Ошибка MySQL 1005 Can't создать таблицу '.\mydb#sql-328_45.frm' (errno: 150) Я искал так тщательно, как мог, но ответ все еще мог быть там. В таком случае извините.

Может быть, это связано с тем, что в старой таблице (Tips) уже есть данные. Синхронизация отношения 1:n, который добавляет внешний ключ в старую таблицу (Советы), хотя странно работает. если бы проблема была с данными, это не сработало бы, как я обнаружил в этом посте: Как добавить внешний ключ в таблицу MySQL?

С другой стороны, добавление отношения 1:n, которое потребовало бы добавления внешнего ключа старой таблицы (Tips) к новой, генерирует те же ошибки и журналы (за исключением ссылки на таблицу, подобную этой: '. \ MYDB # SQL-328_45.frm ').

Должен отметить, что эти проблемы возникают для любой моей старой таблицы (таблица уже в базе данных с данными). Кроме того, я уверен, что обновил MySQL Workbench после того, как я заполнил базу данных данными, прежде чем я продолжил заполнять ее и пытался добавить эти новые таблицы. Сейчас это версия 6 и работает в Kubuntu, Linux.

В чем здесь проблема и как ее можно лечить? Идут ли до крайности, такие как экспорт базы данных, удаление и импорт ее единственным способом? Даже в таком случае, что мне делать именно? Имейте в виду, что это база данных с множеством записей, около 100000.

Если вам нужна дополнительная информация, я предоставлю их с этого момента.

Код создания таблицы советов:

SHOW CREATE TABLE mydb.Tips;

CREATE TABLE `Tips` (
  `idTips` varchar(45) CHARACTER SET latin1 NOT NULL,
  `like_count` int(10) unsigned DEFAULT NULL,
  `text` longtext CHARACTER SET latin1,
  `created_at` bigint(19) unsigned DEFAULT NULL,
  `url` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `todo_count` bigint(19) unsigned DEFAULT NULL,
  `save_count` bigint(19) unsigned DEFAULT NULL,
  `Venues_idVenues` varchar(45) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`idTips`),
  KEY `fk_Tips_Venues1` (`Venues_idVenues`),
  CONSTRAINT `fk_Tips_Venues1` FOREIGN KEY (`Venues_idVenues`) REFERENCES `Venues` (`idVenues`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

1

mysql database mysql-workbench mysql-error-1005

Источник

user3676220 07 окт '14 в 13:23

1 ответ

Решение

Другие вопросы по тегам mysql database mysql-workbench mysql-error-1005

user4262694 17 ноя '14 в 22:21 2014-11-17 22:21 · Accepted Answer · 2014-11-17 22:21

ТЛ; др

imho нужно синхронизировать определение Tips.idTips столбец и Tips_has_SEntences.Tips_idTips колонка.

В частности, набор символов и сопоставление столбцов не соответствуют.

разрешение

Итак, в коде выполните одно из следующих действий:

определить набор символов или сопоставление Tips_has_SEntences.Tips_idTips столбец явно.
```
`Tips_idTips` VARCHAR(45) CHARACTER SET latin1 NOT NULL,
```
или же
```
`Tips_idTips` VARCHAR(45) COLLATE latin1_swedish_ci NOT NULL,
```
сбросить явное сопоставление по умолчанию на Tips_has_SEntences Таблица.
```
COLLATE = latin1_bin -- remove this section
```
опустить явное определение набора символов на Tips.idTips колонка.
```
`idTips` varchar(45) NOT NULL,
```
указать latin1_bin сопоставление на Tips.idTips колонка.
```
`idTips` varchar(45) COLLATE latin1_bin NOT NULL,
```

Будьте осторожны: последние два варианта могут привести к потере данных, если при использовании ALTER TABLE запрос, наборы символов "to" и "from" несовместимы.
Вы должны быть в безопасности, так как latin1_bin а также latin1_swedish_ci сопоставление использовать тот же набор символов (latin1).

объяснение

эксперимент

Когда я отключил тревожный внешний ключ в Tips_has_SEntencesCREATE TABLE заявление:

--  CONSTRAINT `fk_Tips_has_SEntences_Tips1`
--    FOREIGN KEY (`Tips_idTips`)
--    REFERENCES `mydb`.`Tips` (`idTips`)
--    ON DELETE NO ACTION
--    ON UPDATE NO ACTION,

Запустил запросы и сделал SHOW CREATE TABLE на Tips а также Tips_has_SEntences Таблицы, в которых я нашел определение двух столбцов, к которым подключался внешний ключ, немного отличались.

    ...
    `idTips` varchar(45) CHARACTER SET latin1 NOT NULL,
    ...
    `Tips_idTips` varchar(45) COLLATE latin1_bin NOT NULL,
    ...

На Tips.idTips столбец набор символов явно установлен и на Tips_has_SEntences.Tips_idTips В столбце установлено сопоставление по умолчанию для таблицы.

Я предполагаю, что столбцы внешнего ключа нельзя легко сравнивать, и ссылочная целостность не поддерживается, когда параметры сортировки или наборы символов двух задействованных столбцов не совпадают.

Дальнейшее расследование

Затем я побежал SHOW FULL COLUMN чтобы показать детали столбцов в вопросе.

Field           Type            Collation          Null    Key
idTips          varchar(45)     latin1_swedish_ci  NO      PRI
...
Tips_idTips     varchar(45)     latin1_bin         NO      PRI

Почему разница в сопоставлении?

Когда я читал дальше о наборах символов и сопоставлениях по столбцам, документы MySQL говорили:

Если CHARACTER SET X указан без COLLATE, используется набор символов X и его сопоставление по умолчанию.
Если COLLATE Y указан без CHARACTER SET, используется набор символов, связанный с Y и сопоставлением Y.

Для просмотра параметров сортировки по умолчанию и связанных наборов символов я запустил SHOW COLLATION:

Collation               Charset Id      Default Compiled        Sortlen
...
latin1_german1_ci       latin1  5               Yes             1
latin1_swedish_ci       latin1  8       Yes     Yes             1
latin1_danish_ci        latin1  15              Yes             1
latin1_german2_ci       latin1  31              Yes             2
latin1_bin              latin1  47              Yes             1
latin1_general_ci       latin1  48              Yes             1
latin1_general_cs       latin1  49              Yes             1
latin1_spanish_ci       latin1  94              Yes             1
...

Теперь мы можем видеть, что MySQL разрешает сопоставление Tips.idTips столбец к latin1_swedish_ciпотому что это сопоставление по умолчанию для явно установленного latin1 набор символов.

Параметры сортировки будут разрешаться набором символов, а не наследоваться из таблицы, поскольку набор символов был явно установлен в столбце. Это именно так, как это было указано в документации, так что это, вероятно, желаемое поведение.

Взять это еще дальше

Теперь я знал, что сопоставление Tips.idTips колонка была latin1_swedish_ci, Таким образом, вместо явного изменения набора символов Tips_has_SEntences.Tips_idTips столбец, я думал, что я мог бы просто изменить сопоставление на latin1_swedish_ci,

`Tips_idTips` VARCHAR(45) COLLATE latin1_swedish_ci NOT NULL,

Это тоже работает.
что интересно SHOW CREATE TABLE теперь говорит, что столбец был / должен быть создан с этой строкой:

`Tips_idTips` varchar(45) CHARACTER SET latin1 NOT NULL,

Заключение

Наборы символов, сопоставление и внешние ключи вместе - ад.
Я предпочитаю целочисленные столбцы для внешних ключей вместо строковых / текстовых типов, когда у меня есть выбор. И теперь я знаю почему:)

Глядя на код, который вы разместили, кажется, что это проблема со всеми "старыми" таблицами в вашей базе данных, так как все текстовые столбцы имеют набор символов latin1 определено явно.