Create table statement missing Primary Key

[basementjack]

Hi,

I have an access database with a field used as a counter
in Access it's defined as a primary key, type=autoNumber

when I run Bullzip Access to mysql,
it comes across as

CREATE TABLE `PaperDocumentTracking` (
`Counter` INTEGER NOT NULL AUTO_INCREMENT,
) ENGINE=myisam DEFAULT CHARSET=utf8;

When it should come across as

CREATE TABLE `PaperDocumentTracking` (
`Counter` INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`Counter`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

Without the Primary Key statement, mySql 5.1.37 is throwing an error
This is using Access to MySql version 3.0.0.138
Selecting MyISAM as the Storage Engine
Selecting only one table from a list of about 30
Most importantly - 'Transfer indexes' is checked -as is 'Default value properties' and 'Auto number properties'
(Everything on the transfer options screen is checked, except 'Drop and recreate destination Database')

[jr]

Is the Autonumber field also the primary key in your access table? If I have it as a primary key in Access then my tests show that it will be a primary key in MySQL also.

Maybe you don't have it as primary key in Access? Would there be a reason for that?

Regards,
Jacob

[jr]

You could try to send a sample Access database to info at bullzip dot com so that I can try to reproduce the problem.

/Jacob

[nanasy2]

Wow. Finaly i found the solution to my problem. You post turned out useful!!!!

[jcastillo]

I have the same problem.
I have a msaccess table with pk in a autoincrement field and the sql generated is not correct.

SETTINGS
--------
Moving data directly to MySQL server

TABLES
------------
- Creating 'TUR_DATOS' failed
- SQL: CREATE TABLE `TUR_DATOS` ( `Id` INTEGER NOT NULL AUTO_INCREMENT, `CEMPTITU` VARCHAR(255), `EMPRESA` VARCHAR(255), `CODCEMPTITU` VARCHAR(255), `FFACTDES` DATETIME, `FORIPREF` DATETIME, `FFACTHAS` DATETIME, `CONS-ACT` DOUBLE NULL, `TANOMAL1` VARCHAR(255), `CONS-REA` DOUBLE NULL, `CTARIFA` VARCHAR(255), `VNUMMESF` DOUBLE NULL, `CONTREXT` DOUBLE NULL, `KPERFACT` DOUBLE NULL, `SEL-ACT` VARCHAR(255), `TARIFA` VARCHAR(255), `OOSS P` VARCHAR(255), `SEL-REA` VARCHAR(255), `CODE` VARCHAR(255), `TANOMAL2` VARCHAR(255), `TANOMAL3` VARCHAR(255), `TANOMAL4` VARCHAR(255), `CIF` VARCHAR(255), `TANOMAL5` VARCHAR(255), `POTENCIA` DOUBLE NULL, `Carterizado` VARCHAR(255), `TIPOCLTE` VARCHAR(255), `DISTRIB` VARCHAR(255), `PRODUCTO` VARCHAR(255), `CONTROLA` VARCHAR(255), `FECHAOR` VARCHAR(255), `ORG_OF` VARCHAR(255), `Días` VARCHAR(255), `CPROVINC` VARCHAR(255), `LINNEG` VARCHAR(255), `CCENTRPOB` VARCHAR(255), `CPOBLAC` VARCHAR(255), `SEGMENTO` VARCHAR(255), `TCLTE` VARCHAR(255), `CEMPTSEC` VARCHAR(255), `FECHA DE LOS DATOS` DATETIME, `ESTADO` VARCHAR(255), `TPROD` VARCHAR(255), `TELEMEDIDA` VARCHAR(255), `ANTIGUEDAD PREFA` VARCHAR(255), `TIPOLECT` VARCHAR(255), `RESOLUTOR` VARCHAR(255), `SEGMENTO AGRUPADO` VARCHAR(255), `CONTRATA` VARCHAR(255), `Fecha` DATETIME, `Usuario` VARCHAR(255), `Gestión` VARCHAR(255), `FECHAREGISTRO` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `OBSERVACIONES` LONGTEXT, `NumGestionesRealizadas` INTEGER DEFAULT 0, `GrabadoPor` VARCHAR(50), `Fichero` VARCHAR(255), `AsignadoA` VARCHAR(50), `CerradoAutomatico` TINYINT(1) DEFAULT 0, `Estado_Gestion` VARCHAR(255) DEFAULT 'No Gestionada', `UsuarioSCE` VARCHAR(255), `EstadoCambiadoPorFicheroNuevo` TINYINT(1) DEFAULT 0, `EstadoAntesDeCambio` VARCHAR(50), `IDOrigenDeEstado` INTEGER DEFAULT 0, `CONTRATO_FDESDE_FHASTA` VARCHAR(255), `BorradaPorLlegarEnNuevoFichero` TINYINT(1) DEFAULT 0, `FHInicioGestion` DATETIME, `FHFinGestion` DATETIME, `SegundosGestion` INTEGER, `TiempoGestion` DATETIME, `FHInicioGestionGlobal` DATETIME, `FHFinGestionGlobal` DATETIME, `SegundosGestionGlobal` INTEGER, `TiempoGestionGlobal` DATETIME) ENGINE=myisam DEFAULT CHARSET=utf8
- Error: -2147467259 (80004005) [MySQL][ODBC 5.1 Driver][mysqld-5.5.18]Incorrect table definition; there can be only one auto column and it must be defined as a key
- 0 records moved


you can see the field id....it would be instead `Id` INTEGER NOT NULL AUTO_INCREMENT,

`Id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

There is something rare....last week the export works well...the so is the same (w7 64), ACCESS is same (.mdb w2k3) and mysql 5.5.18 y 3.0.0.148 bullet msaccess to mysql app.

The only option not checked is drop database.

any idea?

[mgrimes]

Hi all,

I am experiencing the same problem. I've confirmed that the fields are marked as indexed and primary keys in access. The "transfer indexes", "create tables" and "auto number properties" boxes are checked. There is not KEY or PRIMARY designation in the dump file. I am running version 4.0.0.192 and trying to convert an accdb (2007) version access database.

Any help would be greatly appreciated.

Thanks!
Mark

P.S. We would be willing to donate to support the development, but we can't use the software if it can't preserve the keys. We have even looked at purchasing the commercial version from avangate, but we experienced numerous issues with it (crashing, "garbage in field names", etc).

[maxyak]

I am seeing identical behavior to that reported by the others. I have an Access database (accdb) with several tables. Last week, this tool worked great. This week, it's omitting the primary key declarations of some of the tables. The only difference between then and now is that I renamed the tables for which the primary keys are being omitted. I still have no problems with the other tables. I even tried deleting one of the affected tables and recreating it from scratch inside Access - no luck.

The obvious and not-to-difficult work-around is to save to a dump file and manually add the key declarations, but I thought you'd like the additional info.

Have you come up with a fix for this since this was reported?

[maxyak]

FYI, a reboot of my machine solved the problem I was having. Somebody must have cached the old schema instead of returning the changes I'd made.