MySQL Bugs: #55249: Engineer ALTER scripts incorrectly assumes tables in source script are ordered

Bug #55249	Engineer ALTER scripts incorrectly assumes tables in source script are ordered
Submitted:	14 Jul 2010 9:33	Modified:	14 Jul 2010 11:19
Reporter:	Hylke van der Schaaf	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.25	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	alter

Description:
When generating an ALTER script it assumes that tables in the source script are in logical order with respect to foreign keys.

If they are not in this order, table renames fail, because the generated ALTER script changes the foreign key before renaming the table. This will of course fail.

phpMyAdmin generates scripts with the tables in alphabetical order, and adds keys at the end of the script. Workbench can import this without problems, but then fails to generate proper ALTER scripts.

How to repeat:
Import the following script, rename "car_brands" to "car_brand" and generate an ALTER script.

-- phpMyAdmin SQL Dump
-- version 2.11.8.1deb5+lenny4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 14, 2010 at 11:15 AM
-- Server version: 5.0.51
-- PHP Version: 5.2.6-1+lenny8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `datamanagement_group_0`
--
USE `datamanagement_group_0`;
-- --------------------------------------------------------

--
-- Table structure for table `car`
--

CREATE TABLE IF NOT EXISTS `car` (
  `car_id` int(11) NOT NULL,
  `car_brand_id` int(11) NOT NULL,
  `color` varchar(10) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`car_id`),
  KEY `fk_car_car_brands` (`car_brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='A list of cars';

-- --------------------------------------------------------

--
-- Table structure for table `car_brands`
--

CREATE TABLE IF NOT EXISTS `car_brands` (
  `car_brand_id` int(11) NOT NULL,
  `name` varchar(45) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`car_brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='A list of car brands';

--
-- Constraints for dumped tables
--

--
-- Constraints for table `car`
--
ALTER TABLE `car`
  ADD CONSTRAINT `fk_car_car_brands` FOREIGN KEY (`car_brand_id`) REFERENCES `car_brands` (`car_brand_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Suggested fix:
Do not take table order from the source script, but "calculate" it from the target situation.

Many thanks for writing a bug report.

Consider, PHPMyAdmin is not a tool made by us.

The bug is not related to us ... it would be related to PHPMyAdmin.

When you take scripts from our tools like scripts from mysqldump or old MySQL Administrator or data dumps from Workbench all will work fine.

The problem here is that PHPMyAdmin is not taking care of our dumping algorithm.

In that case I would consider it a documentation or user-interface bug, because there is nothing that indicates the source script has to be of a very specific style. The phpMyAdmin output is valid SQL, it is MySQL compatible and there are no warnings at all to indicate it might cause problems.