MySql - Error Code: 1932. Table doesn't exist in engine

Question

I am getting the error below on a mysql restart after adding an enum field to my existing table. Locks table and only a complete database restore fixes issue as I can't drop the table either.

Error Code: 1932. Table 'users' doesn't exist in engine

My table and data is as follows:

CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `name` varchar(191) NOT NULL,
  `email` varchar(191) NOT NULL,
  `password` varchar(191) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
);

INSERT INTO `users` VALUES 
(
    '11111111-1111-1111-1111-111111111111',
    'John Doe',
    'user@email.com',
    'password',
    NOW(),
    NOW()
);

Table above works until line below is executed and mysql is restarted.

ALTER TABLE users ADD `role` enum('test1', 'test2') AFTER `password`

I have also done a diff on the table structure of a before and after. Only line with comment gets added and locks the table.

CREATE TABLE `users2` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` enum('test1','test2') COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- only diff
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users1_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This is where it gets weird. Line below adds role to the end of the table and doesn't lock the table.

ALTER TABLE users ADD `role` enum('test1', 'test2'); -- AFTER `password`

Seems the AFTER is the issue. Doesn't matter if I try a different position.

Any suggestions would be appreciated.

I'm confused -- Are you asking about "locking" the table? Or about err 1932? And does there error occur after the `ALTER...AFTER...` or after the `ALTER ... (on end)`? — Rick James, Jan 29 '20 at 22:14

score 1 · Answer 1 · answered Jan 23 '20 at 13:26

1

You can also assign the default value while adding the column.

ALTER TABLE users ADD `role` enum('test1', 'test2') DEFAULT 'test1' AFTER `password`;

So, NULL values will not allow and all the rows will be assigned with 'test1'.

answered Jan 23 '20 at 13:26

Farhan

253
2
9

MySql - Error Code: 1932. Table doesn't exist in engine

1 Answers1

Linked