Search code examples
mysqldatabasecreate-table

Does MySQL retain unique IDs between connections

I would like to know if MySQL retains unique IDs it has automatically assigned between connections. I copied the creation script of a table from the test environment to the production environment, and when testing if it works correctly, noticed that a new record was inserted into this table (production) using the next ID after that of the last record inserted into the same table in the test environment. Is this normal behavior? It's not an issue, as much as it is peculiar.

Here is the creation script below:

CREATE TABLE `audit_logs` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_date` date DEFAULT NULL,
  `workflow_id` int(11) DEFAULT NULL,
  `workflow_step` int(2) DEFAULT NULL,
  `workflow_step_status` int(2) DEFAULT NULL,
  `step_modified_by` varchar(45) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `job` varchar(6) DEFAULT NULL,
  `area` varchar(45) DEFAULT NULL,
  `fixture` varchar(45) DEFAULT NULL,
  `unit` varchar(45) DEFAULT NULL,
  `comments` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`log_id`),
  KEY `workflow_id` (`workflow_id`),
  KEY `FK_ItemAuditLog` (`item_id`),
  CONSTRAINT `FK_ItemAuditLog` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`),
  CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`workflow_id`) REFERENCES `workflows` (`workflow_id`)
) ENGINE=InnoDB AUTO_INCREMENT=192 DEFAULT CHARSET=utf8;
Solution

  • Pretty sure your problems comes from this line;

    AUTO_INCREMENT=192

    Which results in that the unique ID will start at 192 on the first insert in the table on the new server.