Column order in employees.dept_manager is incorrect

[Shlomi Noach]

 CREATE TABLE `dept_manager` (
  `dept_no` char(4) NOT NULL,
  `emp_no` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `emp_no` (`emp_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Since this is an InnoDB table, it reorders columns by compound PRIMARY KEY order; thus, emp_no should be first, dept_no second.

Why would anyone care? Because of EXPORT; LOAD DATA INFILE;
This assume the ordering of data is according to internal column order; alas - it is mixed.

Recommend: alter table, reorder columns.

[Giuseppe Maxia (giuseppe-maxia)]

Thanks for your bug report. Will do some benchmarks to confirm the issue and act accordingly