How do I run a sql script on windows?

Question

I tried

mysqlsh -h localhost -u mixtape-dating -p -f setup.sql

and setup.sql consists of

USE MixtapeDating;

-- Tables
-- The Playlist table gets featured on the front page. 
CREATE TABLE Playlist (
    Id INT PRIMARY KEY AUTO_INCREMENT, 
    Title VARCHAR(255),
    Email VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- The PlaylistItem gets featured at /playlist/:id
CREATE TABLE PlaylistItem (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    PlaylistId INT, 
    Title VARCHAR(255),
    Link VARCHAR(255)
);

-- Stored Procedures
-- Insert playlist
CREATE PROCEDURE InsertPlaylist
(
    IN Title VARCHAR(255),
    IN Email VARCHAR(255),
    OUT PlaylistId INT
)
BEGIN
    INSERT INTO Playlist (Title, Email)
    VALUES (@Title, @Email);
    SELECT LAST_INSERT_ID() AS @PlaylistId;
END

CREATE PROCEDURE InsertPlaylistItem
(
    IN PlaylistId INT, 
    IN Title VARCHAR(255),
    IN Link VARCHAR(255)
)
BEGIN
    INSERT INTO PlaylistItem (PlaylistId, Title, Link)
    VALUES (@PlaylistId, @Title, @Link);
END

-- Fetch all playlists
CREATE PROCEDURE GetPlaylists
BEGIN
    SELECT Id, Title, Email 
    FROM Playlist;
END

-- Fetch all playlist items for a playlist
CREATE PROCEDURE GetPlaylist
(IN Id INT)
BEGIN
    SELECT PlaylistId, Id, Title, Link
    FROM PlaylistItem
    WHERE Id = @Id; 
END

But I get the following error:

C:\Users\moore\Desktop\playlist-dating>mysqlsh -h localhost -u mixtape-dating -p -f setup.sql
Enter password: ****************
SyntaxError: Unexpected identifier at setup.sql:1:4
in USE MixtapeDating;
       ^^^^^^^^^^^^^

What am I doing wrong?

Not too familiar with windows command prompt, but if you're on windows 10, you can use the ubuntu subsystem. — Goose
– Goose, Commented Jul 18, 2017 at 21:28
Why not simply install MySQL Workbench (dev.mysql.com/downloads/file/?id=468295) and debug sql file like a boss ? (: — num8er
– num8er, Commented Jul 18, 2017 at 22:55

kgr · Accepted Answer · 2018-05-25 10:18:43Z

1

mysqlsh starts default in javascript mode. If you want to run SQL statements you need to switch to SQL mode. You can do this adding --sql argument to mysqlsh.

mysqlsh -h localhost -u mixtape-dating -p -f setup.sql --sql

answered May 25, 2018 at 10:18

kgr

7896 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul T. · Accepted Answer · 2017-07-18 22:50:33Z

0

Should be able to do:

mysql -u username -p databasename < file.sql

I don't know that I've ever seen mysqlsh used for this purpose?

answered Jul 18, 2017 at 22:50

Paul T.

5,02712 gold badges48 silver badges64 bronze badges

5 Comments

Paul T. Over a year ago

Is the database named MixtapeDating, or should there be a hyphen like the user's name? If the name is correct, try using backticks to surround the DB name in the file.

Joshua M. Moore Over a year ago

The database name is correct, I tried the backticks, to no avail.

Paul T. Over a year ago

Can you try commenting-out, or temporarily delete, the USE line altogether. Then try this command: mysql -u mixtape-dating -p MixtapeDating < setup.sql. I'm curious to know if the first table create statement will complain as well.

Paul T. Over a year ago

Are you sure it says database already exists? If so, then I don't understand why you're having so much trouble, as it's not this difficult. That command says to import the sql to the specified database MixtapeDating, which MUST already exist (it does NOT create the DB). Unless there's more to the script that we don't know (such as containing a create database ... statement) then, yes, it WILL complain. All you have are create tables and procedures, based on what you originally posted, so more detail may be needed.

kgr Over a year ago

mysqlsh is a new MySQL Shell. MySQL Shell (part of MySQL Server), an interactive JavaScript, Python and SQL console interface, supporting development and administration for the MySQL Server. It provides built in scriptable APIs that support the creation and management of MySQL InnoDB clusters, as well as a modern fluent CRUD API for the MySQL Document Store. Connections to MySQL server can use X Protocol or classic MySQL protocol.

kgr · Accepted Answer · 2018-06-13 23:37:24Z

Your setup.sql has some issues. First of all, default delimiter is ; therefore shell interpret your create procedure partially. Change your delimiter to something else, for example to //. Also variables which start with @ are User-Defined Variables and are not applicable to use inside procedures.

Fixed setup.sql. You can run this script using: mysqlsh -h localhost -u mixtape-dating -p -f setup.sql.

-- DROP SCHEMA IF EXISTS MixtapeDating;
-- CREATE SCHEMA MixtapeDating;
USE MixtapeDating;

-- Tables
-- The Playlist table gets featured on the front page. 
CREATE TABLE Playlist (
    Id INT PRIMARY KEY AUTO_INCREMENT, 
    Title VARCHAR(255),
    Email VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- The PlaylistItem gets featured at /playlist/:id
CREATE TABLE PlaylistItem (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    PlaylistId INT, 
    Title VARCHAR(255),
    Link VARCHAR(255)
);

DELIMITER //

-- Stored Procedures
-- Insert playlist
CREATE PROCEDURE InsertPlaylist
(
    IN Title_ VARCHAR(255),
    IN Email_ VARCHAR(255),
    OUT PlaylistId_ INT
)
BEGIN
    INSERT INTO Playlist (Title, Email)
    VALUES (Title_, Email_);
    SELECT LAST_INSERT_ID() AS PlaylistId_;
END
//

CREATE PROCEDURE InsertPlaylistItem
(
    IN PlaylistId_ INT, 
    IN Title_ VARCHAR(255),
    IN Link_ VARCHAR(255)
)
BEGIN
    INSERT INTO PlaylistItem (PlaylistId, Title, Link)
    VALUES (PlaylistId_, Title_, Link_);
END
//

-- Fetch all playlists
CREATE PROCEDURE GetPlaylists()
BEGIN
    SELECT Id, Title, Email 
    FROM Playlist;
END
//

-- Fetch all playlist items for a playlist
CREATE PROCEDURE GetPlaylist (IN Id_ INT)
BEGIN
    SELECT PlaylistId, Id, Title, Link
    FROM PlaylistItem
    WHERE Id = Id_;
END
//

Collectives™ on Stack Overflow

How do I run a sql script on windows?

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related