Why am I getting "table doesn't exist" when trying to execute a create table command?

Question

I am building an application that uses among the others things docker and mysql. Inside the mysql container I run the following command to import a dump of the database:

root@112345678956:/# cd a-folder/
root@112345678956:/# mysql -uroot -proot dbname < dump.sql

When the command runs, after few seconds I receive the following error message:

ERROR 1146 (42S02) at line 113: Table 'dbname.blockedseat' doesn't exist

The following is the SQL code from line 113 in the dump.sql file that contains a create table command:

113 CREATE TABLE IF NOT EXISTS `BlockedSeat` (
114  `id` int(11) NOT NULL AUTO_INCREMENT,
115  `purchase_id` int(11) DEFAULT NULL,
116  `subscription_id` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
117  `seatId` int(11) DEFAULT NULL,
118  `seatCode` int(11) DEFAULT NULL,
119  `reduction` int(11) DEFAULT NULL,
120  `description_it` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
121  `description_en` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
122  `price` decimal(6,2) NOT NULL,
123  `payment_method` smallint(6) DEFAULT NULL,
124  `points` int(11) NOT NULL,
125  `blockedAt` datetime NOT NULL,
126  `expiresAt` datetime NOT NULL,
127  `transaction` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
128  `height` smallint(6) DEFAULT NULL,
129  `promotion` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
130  `priceList_id` int(11) DEFAULT NULL,
131  `status` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
132  `gift_id` int(11) DEFAULT NULL,
133  `code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
134  PRIMARY KEY (`id`)
135 );

(In the code above, 113 is the row number of the editor)

Hope this question is clear.

Probably include more lines of code from dump.sql for more clarity into the problem? — Amit Dash, Jun 14 '21 at 09:37
I'm not sure that your create table statement generates this error message. I would double-check that line number! — Shadow, Jun 14 '21 at 09:40
The line number in the source file and the line number detected by mysql is not always exactly the same. — Shadow, Jun 14 '21 at 10:05
You should also take a look at the following quetsion and its answers here on SO: https://stackoverflow.com/questions/6342201/bug-1146-table-xxx-xxxxx-doesnt-exist — Shadow, Jun 14 '21 at 10:08
There are multiple answer to the linked question, pls take a look at all of them, not just the accepted one! — Shadow, Jun 14 '21 at 10:14
Removing "IF NOT EXISTS" from all table creations it works. Nonsense for me! — sensorario, Jun 14 '21 at 12:08

Why am I getting "table doesn't exist" when trying to execute a create table command?

0 Answers0