Bug #37355 Restoring table with autoincremental PK,PK values -1 and 0: 0 converts to 1

• Submitted: 11 Jun 2008 20:29
• Modified: 11 Jun 2008 22:35
• Reporter: Brian Braun
• Status: Won't fix
• Category: MySQL Server: General
• Severity: S1 (Critical)
• Version: 4.1.14
• OS: Any
• CPU Architecture: Any

[11 Jun 2008 20:29] Brian Braun

Description:
I have a table with an autoincremental PK. Several record, some of them have a primary key values of -1,0,2,3,.........,n.
When restoring the table (executing a script), the PK "0" converts to "1".

How to repeat:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for countries
-- ----------------------------
DROP TABLE IF EXISTS `countries`;
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';

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `countries` VALUES ('-1', 'Impossible to know', '**', '**', 'N', null, 'N', '0', null, 'N', 'N');
INSERT INTO `countries` VALUES ('0', 'Unknown', '??', '???', 'N', null, 'N', '0', null, 'N', 'N');
INSERT INTO `countries` VALUES ('2', 'Zambia', 'ZM', 'ZMB', 'Y', null, 'N', '0', null, 'N', 'N');
INSERT INTO `countries` VALUES ('3', 'Yemen', 'YE', 'YEM', 'Y', null, 'N', '0', null, 'N', 'N');

[11 Jun 2008 22:35] MySQL Verification Team

Thank you for the bug report. Setting to "Won't fix" since the bug is not reproducible on 5.0, and 4.1 is now in the "end of life" state.

C:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.62-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source c:/dbs/script.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.13 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.05 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.08 sec)

mysql> select id from countries;
+----+
| id |
+----+
| -1 |
|  0 |
|  2 |
|  3 |
+----+
4 rows in set (0.00 sec)

mysql>

[11 Jun 2008 23:01] Peter Laursen

I posted here:
http://bugs.mysql.com/bug.php?id=37356