saeed saeed - 2 months ago 14
MySQL Question

mysql source command gives error 2 and error 17?

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?

Answer

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:

  1. insufficient permissions on that directory
  2. 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.