3

I am confused. From references I have seen online, the command to execute a text file script is this:

mysql> --user=root --password=admin --database=zero <query.sql

However when I ran this, the command line said theres an error with mySQL syntax (error 1064). I saved the query.sql script file within the C:program files...\MYSQL\MYSQL Server5.1.. (whichever folder directory that contains the mySQL command line terminal .exe)

I then did this:

 mysql> USE db1 \g
 mysql> source <query.sql \g

It also doesnt work; command line gave me the same error. mySQL version I have is different than other versions I have seen. As you can see, you have to add '\g' at the end of every query.

Please help, and let me know if the description is not very clear..thx

EDITED: So this is the code I have inside the query.sql:

CREATE TABLE IF NOT EXISTS 'db1'(
'id' int(255) NOT NULL auto_increment,
'date' date NOT NULL,
'title' varchar(255) NOT NULL,
'introtext' text NOT NULL,
'maintext' text NOT NULL,
PRIMARY KEY ('id')
)
Benny Tjia
  • 4,853
  • 10
  • 39
  • 48
  • 1
    The error message isn't saying you're executing the script wrong, it says a statement in the file has a syntax error. For us to say exactly why it's wrong and how to fix it, please reduce the file to a [minimal sample](http://sscce.org/) and include it in your question. – outis May 22 '11 at 22:00
  • 1064: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d. Errors starting with a '1' are errors server side. Your script is working fine, or your error would have started with a '2'. Like @outis said it's probably a syntax error in your query. – Johan May 22 '11 at 22:05
  • @outis : That was what I thought the first time. But I then doubted it since I pretty much copy-paste the exact SQL code from a tutorial practice blog.. – Benny Tjia May 22 '11 at 22:13
  • 1
    @Benny: Remove the quotes: `'db1'` and only use backquotes `\`db1\`` if necessary. – ypercubeᵀᴹ May 22 '11 at 22:15
  • @BennyTjia: "pretty much" means it isn't exactly the same. Even if you pasted the exact text you read, there still might be issues due to character encodings or differences between what you're using and what the author was using (e.g. different RDBMSs, different versions). – outis May 22 '11 at 23:13
  • @Benny: Which MySQL version do you have? – ypercubeᵀᴹ May 22 '11 at 23:36
  • @ypercube: version is 5.5.11..In default there are 3 or 4 databases (cant remember) when I finished installing; mysql, info..schema, performance schema. But if i go to folder 'data' within my program files, there are only 2 entries, mysql and performance schema..I tried specifying the script directory => mysql> source – Benny Tjia May 22 '11 at 23:54
  • The information_schema is a special metadat database with only views, that's why you can't see any files. – ypercubeᵀᴹ May 23 '11 at 00:11
  • 1
    I suggest you use Query Browser or Workbench for start, so you have a graphical interface and can use File->Open Script. – ypercubeᵀᴹ May 23 '11 at 00:13
  • Use `mysql> source C:\blabla...\query.sql` – ypercubeᵀᴹ May 23 '11 at 00:20
  • @ypercube: I am getting tired of that and decide to use workbench. yeah GUI would actually be nice, but I was trying to practice without it. I actually have had GUI (phpMyAdmin) installed, but Workbench I think is way better and easier to use..+1 for workbench I guess:) – Benny Tjia May 24 '11 at 04:33

3 Answers3

5

You can run an SQL file from within the client with:

\. query.sql

Alternatively, if you're not already in the client, you can use the following from the command line:

mysql --user=root --password=admin --database=zero < query.sql
rid
  • 61,078
  • 31
  • 152
  • 193
  • right, I also had tried that before..for this one, I had "error:2 ERROR:no query specified". – Benny Tjia May 22 '11 at 22:11
  • 2
    You should not escape the table and field names with single quotes ('), but rather with backquotes (`). – rid May 22 '11 at 22:14
2

Remove the quotes: 'db1'. Use backquotes where necessary, like for field called date to identify it from type date. And add a ; at the end of the statement:

CREATE TABLE IF NOT EXISTS db1(
  id int(255) NOT NULL auto_increment,
  `date` date NOT NULL,
  title varchar(255) NOT NULL,
  introtext text NOT NULL,
  maintext text NOT NULL,
  PRIMARY KEY (id)
) ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • @BennyTjia: To expand on the point, single quotes in MySQL delimit values, while backticks delimit [identifiers](http://dev.mysql.com/doc/refman/5.1/en/identifiers.html). `'db1'` in the statement is a string, while (after changing quotes to backticks) `\`db1\`` would be a table named "db1". – outis May 22 '11 at 23:10
  • I tried changing the code, and now the error I have is this-> ERROR: Failed to open file ' – Benny Tjia May 22 '11 at 23:28
  • 1
    @BennyTjia, wherever you save the file, as long as you use the full path to the file while importing, you should be fine. To import a file from *inside* the client, you should *not* use the character `<`, but rather the path to the file directly. Refer to my answer for how to import an SQL file containing correct syntax. – rid May 23 '11 at 11:53
  • @ypercube I have a similar question. Can you please help me with it? I am trying to run external sql files that create and load tables in an MySQL database. Here is the link: http://stackoverflow.com/questions/22411995/access-from-local-infile-denied – CodeMed Mar 14 '14 at 20:56
0

You need to specify the DB so a statement like:-

USE (DATABASE NAME)

So above replace the (DATABASE NAME) with the name of the Database

King O
  • 1