Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#4261 Export bug. Need to declare primary keys in the same statement of auto_increment declarations

Latest_Git
fixed
Marc Delisle
None
5
2015-01-13
2014-01-29
Stefano Gargiulo
No

To reproduce:

create this table:

DROP TABLE IF EXISTS jossec123_assets;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE jossec123_assets (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
parent_id int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set parent.',
lft int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
rgt int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
level int(10) unsigned NOT NULL COMMENT 'The cached level in the nested tree.',
name varchar(50) NOT NULL COMMENT 'The unique name for the asset.\n',
title varchar(100) NOT NULL COMMENT 'The descriptive title for the asset.',
rules varchar(5120) NOT NULL COMMENT 'JSON encoded access control.',
PRIMARY KEY (id),
UNIQUE KEY idx_asset_name (name),
KEY idx_lft_rgt (lft,rgt),
KEY idx_parent_id (parent_id)
) ENGINE=InnoDB AUTO_INCREMENT=219 DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client /;

Export it via phpmyadmin and you get:


--
-- Table structure for table jossec123_assets
--

CREATE TABLE IF NOT EXISTS jossec123_assets (
id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
parent_id int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set parent.',
lft int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
rgt int(11) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
level int(10) unsigned NOT NULL COMMENT 'The cached level in the nested tree.',
name varchar(50) NOT NULL COMMENT 'The unique name for the asset.\n',
title varchar(100) NOT NULL COMMENT 'The descriptive title for the asset.',
rules varchar(5120) NOT NULL COMMENT 'JSON encoded access control.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=219 ;

Notice that there is an AUTO_INCREMENT but not a PRIMARY KEY in the create statement.

I know now index are added at end, in fact i've this at the end of the sql export file:

--
-- Indexes for table jossec123_assets
--
ALTER TABLE jossec123_assets
ADD PRIMARY KEY (id), ADD UNIQUE KEY idx_asset_name (name), ADD UNIQUE KEY id (id), ADD UNIQUE KEY id_2 (id), ADD KEY idx_lft_rgt (lft,rgt), ADD KEY idx_parent_id (parent_id);

But the sad is that if i try to upload it in another phpmyadmin or mysql server that last statement never gets executed because auto_increment column need to be declared also as a key in the atomic create table operation and the import operation fails with this error:

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Discussion

  • A quick solution may be to offer an option to disable the creation of index at end, i know that is juice for performance of the import operation (index are updated only one time at end) but please offer also an option to export in mysqldump style (classic way)

     
    Last edit: Stefano Gargiulo 2014-01-29
  • Hi Stefano,
    I think you are using a older version of pma. This is fixed in master branch of the git repository. You will get that in the next release. I have also attached a example output of pma export. Please check

     
    Attachments
  • I was using the master on github, i don't know if it is in sync but it seemed.

    Now i'm using latest stable release,
    Anyway nice to hear that it's fixed and in a good way.

     
  • I checked your table. Seems like problem exists with current fix because your table has comments. Try the same table without comments in auto increment field . Anyway this should be fixed. I will look in to it. Thanks for pointing out

     
    Last edit: Viduranga Wijesooriya 2014-02-04
  • I have proposed a code to fix it. Please check
    https://github.com/phpmyadmin/phpmyadmin/pull/891

    PS - Though this is a bug fix, previous fix is not present in QA_4_1 branch. So have to merge directly to the master branch.

     
  • Marc Delisle
    Marc Delisle
    2014-02-04

    • assigned_to: Marc Delisle
     
  • Marc Delisle
    Marc Delisle
    2014-02-04

    Fixed by the proposed pull request. Note that we are losing the next auto_increment value when importing.

     
    Last edit: Marc Delisle 2014-02-04
  • Marc Delisle
    Marc Delisle
    2014-02-04

    • status: open --> fixed
     
  • I noticed that, but table definition contains the auto_increment value. I'm not sure why it's not applying to the table. I think we need to run second query "ALTER TABLE tablename AUTO_INCREMENT=xxx;"

     
  • Marc Delisle
    Marc Delisle
    2014-02-04

    I guess that, the table definition not having a primary key with the auto_increment at this point, the value does not apply. I agree with your suggestion, please test it and propose a fix if you have time.

     
  • Thanks for going deeper on this. The table with comments comes from a standard Joomla 2.5 installation, so i think many users may be affected by this issue.

     
  • playmono
    playmono
    2014-06-02

    This problem started again in 4.2.2 version. This ticket should reassign to 'open'.

     
  • Marc Delisle
    Marc Delisle
    2014-06-12

    @playmono: please test in 4.2.3.