Query splitter seems has a bug

[expired user #5341]'s profile image [expired user #5341] posted 13 years ago in Import/Export Permalink
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.
[expired user #5341]'s profile image [expired user #5341] posted 13 years ago Permalink
Oh, no. Sorry. Database imported successfully. But the above script has only tables and log message is not readable.
When import fails seems like SQL_MODE and other variables were not restored and the next execution fails with this message:
"variable "

Else one thing prevents me from simple testing: there is no "IF NOT EXISTS" in "CREATE PROCEDURE ..." query in the script, but there is in "CREATE TABLE ...".
[expired user #5341]'s profile image [expired user #5341] posted 13 years ago Permalink
Ah, I found that "IF NOT EXISTS" can't be used with "CREATE PROCEDURE ...". In this case you can add checkbox to export dialog to enable dropping of procedures and functions those are exist with "DROP PROCEDURE IF EXISTS ..." query.
ansgar's profile image ansgar posted 13 years ago Permalink
There is a checkbox for "Tables: [ ] DROP" which is basically what you want or am I again misunderstanding something? It's a bit difficult to get what you mean when you refer to other posts and post huge code. Instead, you could roll on from the beginning while keeping it simple. I have the gutt feeling I am not very helpful currently.
[expired user #5341]'s profile image [expired user #5341] posted 13 years ago Permalink
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 */;


[expired user #5341]'s profile image [expired user #5341] posted 13 years ago Permalink
Topic closed - see for details.

It was happened because delimiter was set to ';;' instead of ';' and my script was incorrectly interpreted and this led to some strange errors from mysql.
ansgar's profile image ansgar posted 13 years ago Permalink
Phew lucky us we fixed that. Thanks for the consistant updates!

Please login to leave a reply, or register at first.