Error importing .sql file into MySQL database with Terminal on Mac

Question

In the open-source Wikibooks textbook for MySQL, the guide to insert a lot of information into a MySQL database is as follows:

Create a blank .txt file and copy/paste this information into it, saving it as tenPeople.sql.

INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mary", "Jones", 21, "female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jill", "Harrington", 19, "female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Bob", "Mill", 26, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Alfred", "Jinks", 23, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Sandra", "Tussel", 31, "female");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Mike", "Habraha", 45, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("John", "Murry", 22, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Jake", "Mechowsky", 34, "male");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Hobrah", "Hinbrah", 24, "hermaphrodite");
INSERT INTO peopleInfo (firstName, lastName, age, gender) values ("Laura", "Smith", 17, "female");

Excellent. Now we want to get all these people in our table. exit MySQL and go to the directory where you saved the tenPeople.sql file. Once there, to get all the data into your database, type:

mysql -u ted -p people <tenPeople.sql and enter your password.

https://en.wikibooks.org/wiki/MySQL/MySQL_Practical_Guide

I have been trying to complete the last step, but receive the following error in Terminal:

root@debian:~# -u root -p people <~/Documents/tenPeople.sql             
-bash: /root/Documents/tenPeople.sql: No such file or directory

Here, root is my MySQL username (as opposed to "ted" in the example). I have verified that tenPeople.sql is in fact in /Users/ttoshiro/Documents/tenPeople.sql and yet no matter what file path I put in, I keep getting an error message saying that it doesn't exist. If it matters, I am using XAMPP, and tried the following:

root@debian:~# /Applications/XAMPP/xamppfiles/bin/mysql -u root -p people < /Users/ttoshiro/Documents/tenPeople.sql

But I receive the exact same error message in this instance as well. I also tried to start MySQL directly with:

/Applications/XAMPP/xamppfiles/bin/mysql -u root -p people < /Users/ttoshiro/Documents/tenPeople.sql

In this instance, I receive an error claiming that the database 'people' is unknown: ERROR 1049 (42000): Unknown database 'people'. This doesn't make sense because, when typing in \u people in Terminal, I can clearly see a list of tables in the database 'people'. So, clearly the database must exist.

What am I doing wrong? Is there another way to upload .sql files containing large amounts of information using XAMPP? Why don't file paths work the same when running as root?

Where did you actually save the file? Does `ls ~/...` show the file? — tadman, Dec 11 '20 at 19:41
You should not need to run this as root, MySQL does not care what UNIX user you are, and in fact, running things as root is seriously counter-productive here. — tadman, Dec 11 '20 at 19:42
@tadman `ls ~/...` does show the file; it is definitely there. I attempted your suggestion but received a different error (now edited into the post). — ttoshiro, Dec 11 '20 at 20:07
What else does that `.sql` contain? Does it have a `CREATE DATABASE` call? — tadman, Dec 11 '20 at 20:21
The 'people' database and the 'peopleInfo' table that it contains were already created prior to the (attempted) insertion of the .sql file. Ideally, I think I should be able to run the command `mysql -u ted -p people < tenPeople.sql`, maybe with a more granular file path at the end. — ttoshiro, Dec 11 '20 at 20:54
If you're having a lot of trouble with the command-line, consider trying [MySQL Workbench](https://www.mysql.com/products/workbench/) first. — tadman, Dec 11 '20 at 21:05

score 1 · Answer 1 · answered Dec 11 '20 at 19:50

I suspect that typos are responsible for the file not being found. In your info text you specify the file at /Users/ttoshiro/Documents/tenPeople.sql

Then, in terminal, you point to /Users/totoro/Documents/tenPeople.sql

According to your prompt (that is the view characters before the cursor in terminal), you probably are root, as the last symbol is a #, which usually indicates that. This is why on your first try the terminal went to /root when you were using ~ (which points to the user's home dir, which for root is usually /root).

To rule out any errors I would suggest you cd to /Users/ttoshiro/Documents or /Users/totoro/Documents (whichever is correct) and do mysql -u root -p people < tenPeople.sql

Also, I think the recommendation from Wikibooks for MySQL is questionable, when you are working with larger amounts of data in a busy database. At least you should use transactions and/or multi inserts (that is, multiple rows per insert statement). What people usually use when bulk loading data is LOAD DATA INFILE. I normally use MariaDB and their docs are pretty good and should mostly apply to MySQL as well.

For phpmyadmin there exist other ways, you can also upload the file containing the SQL to the server. However, this adds overhead as PHP and Apache will work on the file, before it actually goes to MySQL (which might be acceptible in your case, as the data seem small).

Lastly, I would suggest to name files containing SQL with the .sql extension, in general.

Oops, that's an editing issue. I wanted to change my username on my computer to my username on StackOverflow to reduce any confusion. I do also think the Wikibooks source is questionable, but unfortunately it is what my class uses. Do you know of a better method for uploading an .sql file to the server in Terminal? — ttoshiro, Dec 11 '20 at 20:05
The method is ok, if the task is to use terminal and process the sql. If you like to try out `LOAD DATA INFILE` check this out: https://stackoverflow.com/a/53813762/1240678 (you need to reformat your txt to csv). What you could do alternatively is just log in to the mysql console (just omit < tenPeople.sql in your CLI command). Then you should see the mysql prompt, something like `MySQL [(none)]>`. Select the database the peopleInfo table is stored in with `USE ;` Then, just paste the lines from the .sql into the prompt. MySQL will process the SQL and insert the rows into the table. — Tyreal, Dec 11 '20 at 20:52

Error importing .sql file into MySQL database with Terminal on Mac

1 Answers1