Bug #37356 setting auto_increment column 0 fails!
Submitted: 11 Jun 2008 23:00 Modified: 11 Jun 2008 23:28
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.25 OS:Any
Assigned to: CPU Architecture:Any

[11 Jun 2008 23:00] Peter Laursen
this is a 'spinoff' from http://bugs.mysql.com/37355

I think Miguel missed the point here!

It is easily reproducable like this with all versions (here I used 5.1.25): 

CREATE TABLE `tablename1` (                            
              `id` int(11) NOT NULL AUTO_INCREMENT,                
              `t` varchar(50) DEFAULT NULL,                        
              PRIMARY KEY (`id`)                                   
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `tablename1`(`id`,`t`) values ( '0','n');

select * from `tablename1`;

/* returns

    id  t     
------  ------
     1  n     

I also think it is documented behaviour that a auto_increment column cannot be set to 0 (zero) and that this is *not a bug* (or a *documented bug* ?  :-)  ).

My point is only that *won't fix* with reference to 4.1 being at 'low support level' is not an appropriate categorisation here!

How to repeat:
see above!
[11 Jun 2008 23:28] MySQL Verification Team
Thank you for the bug report. Please see the create table statement in the bug mentioned:

CREATE TABLE `countries` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `code2` varchar(2) default NULL,
  `code3` varchar(3) default NULL,
  `paypal` char(1) NOT NULL default 'N',
  `belongs_to` int(11) default NULL,
  `has_enemies` char(1) NOT NULL default 'N',
  `discount` int(11) NOT NULL default '0',
  `private_comments` varchar(250) default NULL,
  `esi_paypal` char(1) NOT NULL default 'N',
  `paypal_antes_de_ampliar_cobertura` char(1) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `country_name_unique` (`name`),
  UNIQUE KEY `code2_unique` (`code2`),
  KEY `belongs_to` (`belongs_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 323584 kB';

I didn't tested on 4.1 because it is actually out of cycle life. So the 'test case' isn't repeatable and you are right about the '0' -> 1.