Bug #81562 Workbench Migrate from Sybase to MySQL does not copy primary keys

• Submitted: 24 May 2016 8:12
• Modified: 25 May 2016 11:35
• Reporter: Kenny Gryp
• Status: Verified
• Category: MySQL Workbench: Migration
• Severity: S3 (Non-critical)
• Version: 6.3.6
• OS: Any
• CPU Architecture: Any

[24 May 2016 8:12] Kenny Gryp

Description:
PRIMARY KEYs are forgotten when using MySQL Workbench Migrate from Sybase to MySQL.

How to repeat:
Create a sybase table like this:

1> sp_help emp_mag
2> go
 Name                           Owner                          Object_type
 ------------------------------ ------------------------------ --------------------------------
 emp_mag                        dbo                            user table

(1 row affected)
 Data_located_on_segment        When_created
 ------------------------------ --------------------------
 default                               Nov  5 2013  9:46AM
 Column_name                    Type                           Length      Prec Scale Nulls Default_name                   Rule_name                      Access_Rule_name               Identity
 ------------------------------ ------------------------------ ----------- ---- ----- ----- ------------------------------ ------------------------------ ------------------------------ --------
 id_emp_mag                     smallint                                 2 NULL  NULL     0 NULL                           NULL                           NULL                                  1
 id_magasin                     char                                     3 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 login_emp                      varchar                                 50 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 mdp_emp                        varchar                                 40 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 nom_emp                        varchar                                 50 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 prenom_emp                     varchar                                 50 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 is_admin                       bit                                      1 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 is_actif                       bit                                      1 NULL  NULL     0 NULL                           NULL                           NULL                                  0
 email_emp                      varchar                                250 NULL  NULL     1 NULL                           NULL                           NULL                                  0
 lock_emp                       bit                                      1 NULL  NULL     0 emp_mag_lock_e_1337768792      NULL                           NULL                                  0
 nb_connect_emp                 int                                      4 NULL  NULL     0 emp_mag_nb_con_1353768849      NULL                           NULL                                  0
 index_name           index_description                                        index_keys                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       index_max_rows_per_page index_fillfactor index_reservepagegap index_created
 -------------------- -------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ---------------- -------------------- -------------------
 pk_emp_mag           clustered, unique located on default                      id_emp_mag                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            0                0                    0 Dec 17 2014  4:03PM

(1 row affected)
No defined keys for this object.
Object is not partitioned.
Lock scheme Datarows

 exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
 ------------ -------------- ---------- ----------------- ------------
            0           NULL       NULL                 0            0

(1 row affected)
 concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
 ------------------------- --------------------- -------------------
                        15                     0                   0
(return status = 0)

You can see that there is a clustered unique key named pk_emp_mag on id_emp_mag.

However, the MySQL Workbench conversion did not make a primary key at all:

*************************** 1. row ***************************
       Table: emp_mag
Create Table: CREATE TABLE `emp_mag` (
  `id_emp_mag` smallint(6) NOT NULL,
  `id_magasin` char(3) NOT NULL,
  `login_emp` varchar(50) NOT NULL,
  `mdp_emp` varchar(40) NOT NULL,
  `nom_emp` varchar(50) NOT NULL,
  `prenom_emp` varchar(50) NOT NULL,
  `is_admin` tinyint(4) NOT NULL,
  `is_actif` tinyint(4) NOT NULL,
  `email_emp` varchar(250) DEFAULT NULL,
  `lock_emp` tinyint(4) NOT NULL DEFAULT '0',
  `nb_connect_emp` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Suggested fix:
Create the proper primary keys

[24 May 2016 9:42] Kenny Gryp

Additionally, you can see that the Primary Key column has Identity =1 which means it is an AUTO_INCREMENT.

[25 May 2016 11:35] MySQL Verification Team

Hello Kenny Gryp,

Thank you for the report and feedback!

Thanks,
Umesh