mysql source command gives error 2 and error 17?

Question

When I try this command in cmd:

SOURCE D:\books.sql;

I get this error:

ERROR: Failed to open file 'D:\books.sql;', error: 2

I searched in the internet for this and so far I found out that this error somehow means that there is no such file or directory!!! but I'm pretty sure that my file is right there! thanks.

here's the code inside the books.sql:

DROP DATABASE IF EXISTS books;

CREATE DATABASE books;

USE books;

CREATE TABLE Authors
(
   AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   FirstName varchar(30) NOT NULL,
   LastName varchar(30) NOT NULL
)                 ;
CREATE TABLE Titles
(
   ISBN varchar(20) NOT NULL PRIMARY KEY,
   Title varchar(100) NOT NULL,
   EditionNumber int NOT NULL,
   Copyright varchar(4) NOT NULL
)             ;

CREATE TABLE AuthorISBN
(
   AuthorID int NOT NULL,
   ISBN varchar(20) NOT NULL,
   FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
   FOREIGN KEY (ISBN) References Titles(ISBN)
)            ;

INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel')           ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel')     ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Abbey','Deitel')     ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Michael','Morgano')    ;
INSERT INTO Authors (FirstName,LastName) VALUES ('Eric','Kern')     ;


INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011')          ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132152134')     ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132152134') ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132151421','Visual C# 2010 How to Program',4,'2011')  ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132151421')      ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132151421')      ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132575663','Java How to Program',9,'2012')   ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132575663')                                               ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132575663')                                       ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132662361','C++ How to Program',8,'2012')     ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132662361')                                                  ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132662361')                                                 ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',6,'2010')     ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168')                                             ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168')                                       ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010')        ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'013705842X')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'013705842X')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'013705842X')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'013705842X')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (5,'013705842X')                                                    ;

INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012')        ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132121360')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132121360')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0132121360')                                                    ;
INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0132121360')                                                    ;

so in the middle I tested it without semicolon and here's the result:

ERROR 1010 (HY000): Error dropping database (can't rmdir '.\books\', errno: 17)

it seems this error occurs because of line 1. what to do now?

I don't think it be about the code. I grabbed the code from a book. but to make sure I'll add it. thanks. — saeed foroughi, Jul 29 '13 at 04:45
if by it you mean "source books.sql" yes. I ran it from cmd (I mentioned it in the first line). — saeed foroughi, Jul 29 '13 at 04:49
I looked at http://stackoverflow.com/questions/14684063/mysql-source-error-2. there were no satisfying answer there! I also googled it and there were nothing useful! — saeed foroughi, Jul 29 '13 at 04:50
@saeed in the question you have mentioned both book.sql and books.sql Are you sure you are using the correct filename? — Verma, Jul 29 '13 at 04:52
@blazure mysql is not case sensitive but I guess sql is so you should try SOURCE. — saeed foroughi, Jul 29 '13 at 04:55
ok right now I figured out something. if I use this command with ; I get error 2 otherwise I get errorno 17, which is related to the drop command at the first line!!. — saeed foroughi, Jul 29 '13 at 05:00
@saeed see if this helps... http://stackoverflow.com/questions/4584458/error-dropping-database-cant-rmdir-test-errno-17 — Verma, Jul 29 '13 at 05:21
thanks I got the solution I was doing it by a user. then I tested root and I got the result. but funny thing is I gave the user the permission to use drop command!!! thank you all :) — saeed foroughi, Jul 29 '13 at 05:27

score 1 · Accepted Answer · answered Jul 29 '13 at 05:36

Since you have answered the "source question" by yourself, I´ll try and help with the second part.

Internally mysql databases are represented by a directory and within this directory the database files. Now when you drop a database, mysql first removes the files and as a last step the directory will be removed.

There are now two scenarios which can lead to error 17:

insufficient permissions on that directory
remaining files which are not managed by mysql

So I would do the following steps: First check your permissions on that directory, if your are sure that they allright, try to run your example again. If this doesn´t work, check the directory path/to/mysql/booksfor any files which are not related to mysql and delete them manually. After this you should be able to execute the DROP DATABASE IF EXISTS books; command.

thanks yes that's the solution. I tried to grant the drop command to my user but somehow I did it wrong and didn't notice! — saeed foroughi, Jul 29 '13 at 05:38

mysql source command gives error 2 and error 17?

1 Answers1