MySQL Bugs: #15863: Toolkit generates NULL primary keys which MySQL won't accept

Bug #15863	Toolkit generates NULL primary keys which MySQL won't accept
Submitted:	19 Dec 2005 17:55	Modified:	14 Jun 2006 13:09
Reporter:	Mark Hale	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.21rc	OS:	Windows (Windows XP Professional)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
When migrating a Microsoft Access database and saving the list of CREATE TABLE statements to a .sql file, the Migration Toolkit makes CREATE TABLE statements with the primary key on the correct field, but creates the field as allowing NULL, and MySQL server does not let you have primary key fields that allow NULL.

How to repeat:
Migrate a Microsoft Access database to MySQL, and choose the option to save the CREATE TABLE statements. Then attempt to use the file to create the tables.

Which type of column you have on Access's db as primary key?

Thanks in advance.

I have attached an Access DB with some of the tables that are causing the problem. I use the Migration Toolkit on this file and just keep hitting Next, then tick the box to save create statements.

Sorry I was not able for to find the Access's DB you said had
attached.
If you are not able to attach the description of the table columns
will be enough.

Thanks in advance.

Sorry I thought it had uploaded properly but apparently not. It seems to be there now. Thanks.

Thank you for the bug report and feedback.
Indeed the script is created as reported:

CREATE DATABASE IF NOT EXISTS `db1`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `db1`.`data`;
CREATE TABLE `db1`.`data` (
  `Stock_Code` VARCHAR(30) NULL,
  `Visible_Flag` VARCHAR(50) NULL,
<cut>

However the table is created simultaneously correctly:

win5018>> select * from data;
Empty set (0.00 sec)

win5018>> show create table db1.data\G
*************************** 1. row ***************************
       Table: data
Create Table: CREATE TABLE `data` (
  `Stock_Code` varchar(30) NOT NULL default '',
  `Visible_Flag` varchar(50) default NULL,
  `Description` varchar(255) default NULL,

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html