MySQL : syntax error : 'source' (source) is not valid input at this position

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
--  Sample employee database 
--  See changelog table for details
--  Original data created by Fusheng Wang and Carlo Zaniolo
--  http://www.cs.aau.dk/TimeCenter/software.htm
--  http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
--  Current schema by Giuseppe Maxia 
--  Data conversion from XML to relational by Patrick Crews
-- This work is licensed under the 
-- Creative Commons Attribution-Share Alike 3.0 Unported License. 
-- To view a copy of this license, visit 
-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to 
-- Creative Commons, 171 Second Street, Suite 300, San Francisco, 
-- California, 94105, USA.
--  DISCLAIMER
--  To the best of our knowledge, this data is fabricated, and
--  it does not correspond to real people. 
--  Any similarity to existing people is purely coincidental.
DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;
SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';
DROP TABLE IF EXISTS dept_emp,
                     dept_manager,
                     titles,
                     salaries, 
                     employees, 
                     departments;
/*!50503 set default_storage_engine = InnoDB */;
/*!50503 select CONCAT('storage engine: ', @@default_storage_engine) as INFO */;
CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
CREATE TABLE dept_manager (
   emp_no       INT             NOT NULL,
   dept_no      CHAR(4)         NOT NULL,
   from_date    DATE            NOT NULL,
   to_date      DATE            NOT NULL,
   FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
   FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
   PRIMARY KEY (emp_no,dept_no)
CREATE TABLE dept_emp (
    emp_no      INT             NOT NULL,
    dept_no     CHAR(4)         NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
CREATE TABLE titles (
    emp_no      INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    # FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
/*!50130
ALTER TABLE titles
partition by range (to_days(from_date))
    partition p01 values less than (to_days('1985-12-31')),
    partition p02 values less than (to_days('1986-12-31')),
    partition p03 values less than (to_days('1987-12-31')),
    partition p04 values less than (to_days('1988-12-31')),
    partition p05 values less than (to_days('1989-12-31')),
    partition p06 values less than (to_days('1990-12-31')),
    partition p07 values less than (to_days('1991-12-31')),
    partition p08 values less than (to_days('1992-12-31')),
    partition p09 values less than (to_days('1993-12-31')),
    partition p10 values less than (to_days('1994-12-31')),
    partition p11 values less than (to_days('1995-12-31')),
    partition p12 values less than (to_days('1996-12-31')),
    partition p13 values less than (to_days('1997-12-31')),
    partition p14 values less than (to_days('1998-12-31')),
    partition p15 values less than (to_days('1999-12-31')),
    partition p16 values less than (to_days('2000-12-31')),
    partition p17 values less than (to_days('2001-12-31')),
    partition p18 values less than (to_days('2002-12-31')),
    partition p19 values less than (to_days('3000-12-31'))
) */;
CREATE TABLE salaries (
    emp_no      INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    # FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
/*!50130
ALTER TABLE salaries
partition by range (to_days(from_date))
    partition p01 values less than (to_days('1985-01-01')),
    partition p02 values less than (to_days('1986-01-01')),
    partition p03 values less than (to_days('1987-01-01')),
    partition p04 values less than (to_days('1988-01-01')),
    partition p05 values less than (to_days('1989-01-01')),
    partition p06 values less than (to_days('1990-01-01')),
    partition p07 values less than (to_days('1991-01-01')),
    partition p08 values less than (to_days('1992-01-01')),
    partition p09 values less than (to_days('1993-01-01')),
    partition p10 values less than (to_days('1994-01-01')),
    partition p11 values less than (to_days('1995-01-01')),
    partition p12 values less than (to_days('1996-01-01')),
    partition p13 values less than (to_days('1997-01-01')),
    partition p14 values less than (to_days('1998-01-01')),
    partition p15 values less than (to_days('1999-01-01')),
    partition p16 values less than (to_days('2000-01-01')),
    partition p17 values less than (to_days('2001-01-01')),
    partition p18 values less than (to_days('2001-02-01')),
    partition p19 values less than (to_days('2001-03-01')),
    partition p20 values less than (to_days('2001-04-01')),
    partition p21 values less than (to_days('2001-05-01')),
    partition p22 values less than (to_days('2001-06-01')),
    partition p23 values less than (to_days('2001-07-01')),
    partition p24 values less than (to_days('2001-08-01')),
    partition p25 values less than (to_days('2001-09-01')),
    partition p26 values less than (to_days('2001-10-01')),
    partition p27 values less than (to_days('2001-11-01')),
    partition p28 values less than (to_days('2001-12-01')),
    partition p29 values less than (to_days('2002-01-01')),
    partition p30 values less than (to_days('2002-02-01')),
    partition p31 values less than (to_days('2002-03-01')),
    partition p32 values less than (to_days('2002-04-01')),
    partition p33 values less than (to_days('2002-05-01')),
    partition p34 values less than (to_days('2002-06-01')),
    partition p35 values less than (to_days('2002-07-01')),
    partition p36 values less than (to_days('2002-08-01')),
    partition p37 values less than (to_days('2002-09-01')),
    partition p38 values less than (to_days('2002-10-01')),
    partition p39 values less than (to_days('2002-11-01')),
    partition p40 values less than (to_days('2002-12-01')),
    partition p41 values less than (to_days('3000-01-01'))
CREATE OR REPLACE VIEW dept_emp_latest_date AS
    SELECT emp_no, MAX(from_date) AS from_date, MAX(to_date) AS to_date
    FROM dept_emp
    GROUP BY emp_no;
# shows only the current department for each employee
CREATE OR REPLACE VIEW current_dept_emp AS
    SELECT l.emp_no, dept_no, l.from_date, l.to_date
    FROM dept_emp d
        INNER JOIN dept_emp_latest_date l
        ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;
flush /*!50503 binary */ logs;
SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries1.dump ;
source load_salaries2.dump ;
source load_salaries3.dump ;
source show_elapsed.sql ;
I'm a newbie. I'm downloading a huge mysql database sample for learning purposes. Using mysql workbench, when i open above script, it will highlight all the 'source load_******.dump;' statements, with the error 'syntax error:'source'(source) is not valid input at this position'. Can someone help me with this problem.
Btw, I download this database from: https://github.com/datacharmer/test_db
                Well do those .dump files actually exist?  The source command tells MySQL to load a SQL script from that file.  If you have those files locally, perhaps you need to change the path to where they are actually located.
– Tim Biegeleisen
                Jul 21, 2017 at 1:40
                I read the readme file, it doesn't say anything about changing the path/location. Everything are in the same folder.
– Shahril
                Jul 21, 2017 at 1:53
                Read here.  I can't give you an exact answer without sitting at your MySQL Workbench, but my hunch is that MySQL is looking in a location for those .dump files where nothing is there.  Make the files visible to MySQL and the script should run.  The readme doesn't have to tell you this, if it expects you to know how MySQL works.
– Tim Biegeleisen
                Jul 21, 2017 at 1:56
The source command is not a MySQL statement, but something only handled by the MySQL client. MySQL Workbench does not handle this (as it is focused on pure MySQL code).
To import the entire set remove the source commands from the main file and then manually import these files like you did with the main dump. A bit tedious, but at least a way to load all files.
Remove the following lines because its trying to import other files
SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries1.dump ;
source load_salaries2.dump ;
source load_salaries3.dump ;
source show_elapsed.sql ;
Or import from commend line
mysql -u username -p database_name < file.sql
Make sure the following files should be in same folder
load_departments.dump 
load_employees.dump 
load_dept_emp.dump 
load_dept_manager.dump 
load_titles.dump 
load_salaries1.dump 
load_salaries2.dump 
load_salaries3.dump 
show_elapsed.sql 
                I believe I cannot remove the lines because I do want to import the files that contain the data for all the table in the database. All the file are in the same folder. I've done some experiments and I believe all the script run correctly except for the 'source load_xxxx.dump' line. Is there anything wrong with the syntax?
– Shahril
                Jul 21, 2017 at 2:41
                if you are running, for example your folder /home/sql/ update the file path of sql and dump also
– Kalaikumar Thangasamy
                Jul 21, 2017 at 4:13
I encountered this issue today as well. After trying all the above methods and still meeting errors, I decided to download the older MySQL Workbench version, and it worked all well!
Current latest version: 8.0.24
My downloaded version: 8.0.20
Still don't know why it worked.
I had the same issue and I solved by watching this video: https://www.youtube.com/watch?v=-ksz8J6FQ0A
Basically, you can use the command prompt to install employees database :)
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.