Written by sh2ka
2 years ago
Category: Import/Export
35 posts
since Mon, 29 Nov 10
|
Discussion begins at the topic
http://www.heidisql.com/forum.php?t=357#p7577
but I noticed that it's not only "Foreign key ..." error.
Here is an log of "redmine"'s export script execution:
USE `w`;;
# -------------------------------------------------------- # Host: 127.0.0.1 # Server version: 5.0.67-community-nt # Server OS: Win32 # HeidiSQL version: 6.0.0.3630 # Date/time: 2011-01-17 15:19:40 # -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; # Dumping structure for table redmine.attachments CREATE TABLE IF NOT EXISTS `attachments` ( `id` int(11) NOT NULL auto_increment, `container_id` int(11) NOT NULL default '0', `container_type` varchar(30) NOT NULL default '', `filename` varchar(255) NOT NULL default '', `disk_filename` varchar(255) NOT NULL default '', `filesize` int(11) NOT NULL default '0', `content_type` varchar(255) default '', `digest` varchar(40) NOT NULL default '', `downloads` int(11) NOT NULL default '0', `author_id` int(11) NOT NULL default '0', `created_on` datetime default NULL, `description` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `index_attachments_on_container_id_and_container_type` (`container_id`,`container_type`), KEY `index_attachments_on_author_id` (`author_id`), KEY `index_attachments_on_created_on` (`created_on`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; # Dumping data for table redmine.attachments: ~11 rows (approximately) /*!40000 ALTER TABLE `attachments` DISABLE KEYS */; INSERT INTO `attachments` (`id`, `container_id`, `container_type`, `filename`, `disk_filename`, `filesize`, `content_type`, `digest`, `downloads`, `author_id`, `created_on`, `description`) VALUES (2, 8, 'Issue', 'screenshot.png', '100727033446_screenshot.png', 28087, 'image/png', '41f6300da09d25be03bb3e9b94b8f840', 0, 1, '2/* large SQL query (295,3 KB), snipped at 2 000 characters */
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,031 sec. */
Though Query Tab loads script well.
Refer to the above link to make this topic more clear.
|
Written by sh2ka
2 years ago
35 posts
since Mon, 29 Nov 10
|
I removed all the data from my script except the "devices" table creation. You can try 3 scripts:
this must work well
# --------------------------------------------------------
# Host: vesprom-2
# Server version: 5.0.67-community-nt
# Server OS: Win32
# HeidiSQL version: 6.0.0.3630
# Date/time: 2011-01-17 15:43:49
# --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
# Dumping structure for table device_server.devices
CREATE TABLE IF NOT EXISTS `devices` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT 'Идентификатор устройства',
`name` varchar(255) NOT NULL COMMENT 'Имя устройства',
`device_type` text NOT NULL COMMENT 'Тип устройства (vkt_6, vvts_4, ...)',
`config` text COMMENT 'Настройки устройства',
`device_group_id` int(10) unsigned NOT NULL COMMENT 'Идентификатор группы устройств',
`description` text COMMENT 'Описание устройства',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `devices_ibfk_1` (`device_group_id`),
CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`device_group_id`) REFERENCES `device_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Таблица устройств';
# Dumping data for table device_server.devices: ~3 rows (approximately)
/*!40000 ALTER TABLE `devices` DISABLE KEYS */;
INSERT INTO `devices` (`id`, `name`, `device_type`, `config`, `device_group_id`, `description`) VALUES
(2, 'Кран 23 (24)', 'ВKT "КОВШ" 6', '', 2, ''),
(3, 'Кран 23', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="50" maxFailCount="3" maxRepeatCount="3" address="1" type="vkt-6"/>\n', 3, ''),
(4, 'MBSlave_2', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="100" maxFailCount="3" maxRepeatCount="3" address="2" type="vkt-6"/>\n', 3, '');
/*!40000 ALTER TABLE `devices` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
this will take an error
# Dumping structure for table device_server.devices CREATE TABLE IF NOT EXISTS `devices` ( `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Идентификатор устройства', `name` varchar(255) NOT NULL COMMENT 'Имя устройства', `device_type` text NOT NULL COMMENT 'Тип устройства (vkt_6, vvts_4, ...)', `config` text COMMENT 'Настройки устройства', `device_group_id` int(10) unsigned NOT NULL COMMENT 'Идентификатор группы устройств', `description` text COMMENT 'Описание устройства', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `devices_ibfk_1` (`device_group_id`), CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`device_group_id`) REFERENCES `device_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Таблица устройств';;
/* SQL Error (1005): Can't create table '.\a\devices.frm' (errno: 150) Foreign key constraint is incorrectly formed */
# --------------------------------------------------------
# Host: vesprom-2
# Server version: 5.0.67-community-nt
# Server OS: Win32
# HeidiSQL version: 6.0.0.3630
# Date/time: 2011-01-17 15:43:49
# --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
DELIMITER ;
# Dumping structure for table device_server.devices
CREATE TABLE IF NOT EXISTS `devices` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT 'Идентификатор устройства',
`name` varchar(255) NOT NULL COMMENT 'Имя устройства',
`device_type` text NOT NULL COMMENT 'Тип устройства (vkt_6, vvts_4, ...)',
`config` text COMMENT 'Настройки устройства',
`device_group_id` int(10) unsigned NOT NULL COMMENT 'Идентификатор группы устройств',
`description` text COMMENT 'Описание устройства',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `devices_ibfk_1` (`device_group_id`),
CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`device_group_id`) REFERENCES `device_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Таблица устройств';
# Dumping data for table device_server.devices: ~3 rows (approximately)
/*!40000 ALTER TABLE `devices` DISABLE KEYS */;
INSERT INTO `devices` (`id`, `name`, `device_type`, `config`, `device_group_id`, `description`) VALUES
(2, 'Кран 23 (24)', 'ВKT "КОВШ" 6', '', 2, ''),
(3, 'Кран 23', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="50" maxFailCount="3" maxRepeatCount="3" address="1" type="vkt-6"/>\n', 3, ''),
(4, 'MBSlave_2', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="100" maxFailCount="3" maxRepeatCount="3" address="2" type="vkt-6"/>\n', 3, '');
/*!40000 ALTER TABLE `devices` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
and this takes an error:
# Dumping structure for table device_server.devices CREATE TABLE IF NOT EXISTS `devices` ( `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Идентификатор устройства', `name` varchar(255) NOT NULL COMMENT 'Имя устройства', `device_type` text NOT NULL COMMENT 'Тип устройства (vkt_6, vvts_4, ...)', `config` text COMMENT 'Настройки устройства', `device_group_id` int(10) unsigned NOT NULL COMMENT 'Идентификатор группы устройств', `description` text COMMENT 'Описание устройства', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Таблица устройств';;
# Dumping data for table device_server.devices: ~3 rows (approximately) /*!40000 ALTER TABLE `devices` DISABLE KEYS */;;
INSERT INTO `devices` (`id`, `name`, `device_type`, `config`, `device_group_id`, `description`) VALUES (2, 'Кран 23 (24)', 'ВKT "КОВШ" 6', '', 2, ''), (3, 'Кран 23', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="50" maxFailCount="3" maxRepeatCount="3" address="1" type="vkt-6"/>\n', 3, ''), (4, 'MBSlave_2', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="100" maxFailCount="3" maxRepeatCount="3" address="2" type="vkt-6"/>\n', 3, '');;
/*!40000 ALTER TABLE `devices` ENABLE KEYS */;;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
/* SQL Error (1231): Variable 'sql_mode' can't be set to the value of 'NULL' */
/* 3 rows affected, 0 rows found. Duration for 4 of 7 queries: 0,093 sec. */
# --------------------------------------------------------
# Host: vesprom-2
# Server version: 5.0.67-community-nt
# Server OS: Win32
# HeidiSQL version: 6.0.0.3630
# Date/time: 2011-01-17 15:43:49
# --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
DELIMITER ;
# Dumping structure for table device_server.devices
CREATE TABLE IF NOT EXISTS `devices` (
`id` int(10) unsigned NOT NULL auto_increment COMMENT 'Идентификатор устройства',
`name` varchar(255) NOT NULL COMMENT 'Имя устройства',
`device_type` text NOT NULL COMMENT 'Тип устройства (vkt_6, vvts_4, ...)',
`config` text COMMENT 'Настройки устройства',
`device_group_id` int(10) unsigned NOT NULL COMMENT 'Идентификатор группы устройств',
`description` text COMMENT 'Описание устройства',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Таблица устройств';
# Dumping data for table device_server.devices: ~3 rows (approximately)
/*!40000 ALTER TABLE `devices` DISABLE KEYS */;
INSERT INTO `devices` (`id`, `name`, `device_type`, `config`, `device_group_id`, `description`) VALUES
(2, 'Кран 23 (24)', 'ВKT "КОВШ" 6', '', 2, ''),
(3, 'Кран 23', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="50" maxFailCount="3" maxRepeatCount="3" address="1" type="vkt-6"/>\n', 3, ''),
(4, 'MBSlave_2', 'ВKT "КОВШ" 6', '<DeviceConfig waitTimeout="1000" curStateUpdatePeriod="100" maxFailCount="3" maxRepeatCount="3" address="2" type="vkt-6"/>\n', 3, '');
/*!40000 ALTER TABLE `devices` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
