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',
    '[email protected]',
    '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
– Rick James, Commented Jan 29, 2020 at 22:14

Farhan · Accepted Answer · 2020-01-23 13:26:59Z

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, 2020 at 13:26

Farhan

2532 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related