Error 1146 and Error 1051 when dropping table and other commands

Question

Comment/Answer if you need clarification. I am currently having an issue with MySQL error 1146.

I have a schema called "cia_data" and have 3 files in my database--Query 1, SQL File 3, SQL File 5.

With the code shown in the attached video, I had two errors. The first error was with DROP TABLE new_table, which made sense as it said it was Unknown (error code 1051) and was executed a few times to ensure my imported table would not be counted. I then typed and executed SQL File 5 and had issues because of the table "not existing" even after I created it. Does anyone have a solution to this? I need to fix this error to move on in my project.

Links Please see this video for more information: https://www.youtube.com/watch?v=UitvpKdanzo MySQL forum: https://forums.mysql.com/read.php?152,705063,705069#msg-705069

These changes were made to try to fix the issue:

Three syntax errors ...

1. VISIBLE in the unique index

MySQL doesn't seem to like VISIBLE or INVISIBLE in CREATE TABLE, I'm unsure why. For creating an invisible index, this works ...

create table x( ..., unique index key_f(f), ...);
ALTER INDEX key_f INVISIBLE;

But in your case, VISIBLE is the default so it's not needed.

2. Missing semicolon at the end of the Create Table stmt

3. In the Insert stmt, SELECT instead of a left parenthesis at the beginning of the column list

I still have the errors 1051 and 1146 even after making the previously mentioned changes.

1051 - Unknown table 'cia_data.new_table'
1146 - Table 'cia_data.new_table' doesn't exist

Here is the text:

Query 1's Text

select * from `CIA_DATA`.`new_table`;

SQL File 3's Text

USE CIA_DATA;
DROP TABLE new_table;
CREATE TABLE new_table (
Water DECIMAL(3,1) NOT NULL,
Sanitation DECIMAL(3,1) NOT NULL,
GDP INT NOT NULL,
Life DECIMAL(3,1) NOT NULL,
Underweight DECIMAL(3,1) NOT NULL,
Literacy DECIMAL(3,1) NOT NULL,
Electricity DECIMAL(3,1) NOT NULL,
Country VARCHAR(45) NOT NULL,
PRIMARY KEY (GDP),
UNIQUE INDEX Country_UNIQUE (Country ASC) VISIBLE)
ENGINE = InnoDB

SELECT * FROM new_table;

SQL File 5's Text

desc CIA_DATA.new_table;

SELECT * FROM CIA_DATA.new_table;

INSERT INTO `CIA_DATA`.`new_table`
SELECT `Water`,
`Sanitation`,
`GDP`,
`Life`,
`Underweight`,
`Literacy`,
`Electricity`,
`Country`)
VALUES
(68.5,21.8,600,54.2,24.6,37.4,14,'Central_African_Republic');

COMMIT;

Edit:

Here is the corrected text for SQL File 5 for reference (using another country's data):

desc CIA_DATA.new_table;

SELECT * FROM CIA_DATA.new_table;

INSERT INTO CIA_DATA.new_table SELECT Water, Sanitation, GDP, Life, Underweight, Literacy, Electricity, Country) VALUES (68.5,21.8,600,54.2,24.6,37.4,14,'Central_African_Republic');

COMMIT;

Answer 1

Corrected text so I can close this thread:

desc CIA_DATA.new_table;

SELECT * FROM CIA_DATA.new_table;

INSERT INTO CIA_DATA.new_table SELECT Water, Sanitation, GDP, Life, Underweight, Literacy, Electricity, Country) VALUES (68.5,21.8,600,54.2,24.6,37.4,14,'Central_African_Republic');

COMMIT;