To run a .sql -file in MySQL

mysql sql mysql-error-1146
Léo Léopold Hertz 준영 picture Léo Léopold Hertz 준영 · Jul 18, 2009 · Viewed 10.7k times · Source

This question is based on this thread.

I run unsuccessfully

sudo mysql 
\. /users/cs/SO_db/posts.sql

I get the error

ERROR 1146 (42S02): Table 'personal.posts' doesn't exist

MySQL's manual says

A five-character SQLSTATE value ('42S02'). The values are specified by ANSI SQL and ODBC and are more standardized. Not all MySQL error numbers are mapped to SQLSTATE error codes. The value 'HY000' (general error) is used for unmapped errors.

and

Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE)

Message: Table '%s.%s' doesn't exist

How can you solve the error message?

Answer

Nathan picture Nathan · Jul 18, 2009

The SQL script you have loaded makes reference to a database and/or table which does not exist in the database.

Typically one would not call the mysql tool with sudo, as the system user privileges are different from MySQL users.

To execute an SQL script through mysql I would try something like:

cat somefile.sql | mysql -u <mysqluser> -p <mysqldb>

This command would load 'somefile.sql' into mysql tool, connecting to a MySQL server on localhost as user <mysqluser> and selecting the database <mysqldb>. The mysql tool will prompt for <mysqluser>'s access password before executing the script.

Related questions

Mysql 1050 Error "Table already exists" when in fact, it does not

I'm adding this table: CREATE TABLE contenttype ( contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT, class VARBINARY(50) NOT NULL, packageid INT UNSIGNED NOT NULL, canplace ENUM('0','1') NOT NULL DEFAULT '0', cansearch ENUM('0','1') …

Read More
In MySQL: How to pass a table name as stored procedure and/or function argument?

For instance, this does not work: DELIMITER // CREATE PROCEDURE countRows(tbl_name VARCHAR(40)) BEGIN SELECT COUNT(*) as ct FROM tbl_name; END // DELIMITER ; CALL countRows('my_table_name'); Produces: ERROR 1146 (42S02): Table 'test.tbl_name' doesn't exist However, this works …

Read More
restoring a MySQL database

I have created a file named ab.sql using the mysqldump utility of a database named library. It worked fine. Now i am trying to restore it using mysqlimport. My database already exists. But i want to override it. I …

Read More