SQL syntax error when executing MySQL script using Go during Heroku deployment

I am deploying an API made using Go and MySQl for the database to Heroku. I am following this guide on it and set up everything but now I am trying to execute a MySQL script to set up the tables with some dummy data. But I am constantly getting errors saying that the script is wrong even though I have used it locally with no issues. I have already connected the MySQL database to the Heroku environment and starting the database throws no errors.

Here are the logs showing the error when deploying it to Heroku:

2021-06-05T12:49:16.442359+00:00 app[web.1]: ERROR	2021/06/05 12:49:16 main.go:45: Error executing SQL script : Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE assessment;

schema.sql

CREATE DATABASE assessment;

USE assessment;

CREATE TABLE users (
	id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
	email VARCHAR(255) NOT NULL
);
INSERT INTO `users` VALUES (1,"test1@gmail.com");
INSERT INTO `users` VALUES (2,"test2@gmail.com");
INSERT INTO `users` VALUES (3,"test3@gmail.com");
INSERT INTO `users` VALUES (4,"test4@gmail.com");
INSERT INTO `users` VALUES (5,"test5@gmail.com");

CREATE TABLE features (
	feature_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
	user_id INTEGER NOT NULL,
	feature_name VARCHAR(100) NOT NULL,
	can_access BOOLEAN NOT NULL DEFAULT FALSE,
	FOREIGN KEY (user_id) REFERENCES users(id) 
);

INSERT INTO `features` VALUES (1, 1, "automated-investing", 1);
INSERT INTO `features` VALUES (2, 1, "crypto", 0);
INSERT INTO `features` VALUES (3, 2, "crypto", 0);
INSERT INTO `features` VALUES (4, 3, "automated-investing", 0);
INSERT INTO `features` VALUES (5, 4, "automated-investing", 1);
INSERT INTO `features` VALUES (7, 1, "financial-tracking", 1);
INSERT INTO `features` VALUES (8, 2, "financial-tracking", 0);
INSERT INTO `features` VALUES (9, 3, "financial-tracking", 1);
INSERT INTO `features` VALUES (10, 4, "financial-tracking", 0);

main.go

package main

import (
	"io/ioutil"
	"log"
	"net/http"
	"os"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/gorilla/mux"
	"github.com/joho/godotenv"

	"github.com/yudhiesh/api/controller"
	"github.com/yudhiesh/api/middleware"
)

func main() {
	router := mux.NewRouter()
	router.Use(middleware.ResponseHeaders)
	router.Use(middleware.LogRequest)
	errorLog := log.New(os.Stderr, "ERROR\t", log.Ldate|log.Ltime|log.Lshortfile)
	infoLog := log.New(os.Stdout, "INFO\t", log.Ldate|log.Ltime)
	err := godotenv.Load(".env")
	port := os.Getenv("PORT")
	if port == "" {
		errorLog.Fatal("$PORT is not set")
	}
	dsn := os.Getenv("DSN")
	if dsn == "" {
		errorLog.Fatal("$DATABASE_URL is not set")
	}
	db, err := controller.OpenDB(dsn)
	if err != nil {
		errorLog.Fatal(err)
	}
	defer db.Close()
	c, ioErr := ioutil.ReadFile("./schema.sql")
	sqlScript := string(c)
	if ioErr != nil {
		errorLog.Fatalf("Error loading SQL schema : %s", ioErr)
	}
	_, err = db.Exec(sqlScript)
	if err != nil {
        // FAILS HERE!
		errorLog.Fatalf("Error executing SQL script : %s", err)
	}
	app := &controller.Application{
		DB:       db,
		ErrorLog: errorLog,
		InfoLog:  infoLog,
	}
	addr := ":" + port
	server := &http.Server{
		Handler:      router,
		Addr:         addr,
		WriteTimeout: 15 * time.Second,
		ReadTimeout:  15 * time.Second,
	}
	router.HandleFunc("/feature", app.GetCanAccess).Methods("GET")
	router.HandleFunc("/feature", app.InsertFeature).Methods("POST")
	http.Handle("/", router)
	infoLog.Printf("Connected to port %s", port)
	errorLog.Fatal(server.ListenAndServe())
}

Most interfaces to run queries against MySQL do not support multi-query. In other words, they don't allow multiple SQL statements separated by semicolons, they only support one SQL statement per call.

In your case it returned a syntax error on USE ... because given that the input is parsed as a single statement, there is no USE in the CREATE DATABASE statement.

CREATE DATABASE assessment; USE assessment;

This means if you want to process a file of many SQL statements, you can't just do what you're doing and treat the entire file as one string to pass to a single call of Exec():

c, ioErr := ioutil.ReadFile("./schema.sql")
sqlScript := string(c)
...
_, err = db.Exec(sqlScript)

You must split the content of that file into individual SQL statements, and then loop over those statements, running each one at a time.

This is more complicated than it sounds, because:

• SQL scripts may contain a DELIMITER statement that changes the character between statements from semicolon to something else.

• There might be semicolons inside string literals or inside comments.

• Some statements such as CREATE PROCEDURE, CREATE TRIGGER, etc. may contain semicolons between statements in the body of the routine. You don't want these semicolons to be the end of the statement, you want to include all the content to the end of the routine definition.

• The DELIMITER statement itself can't be executed by the MySQL Server. It only control the client. So you must treat it as an exception that isn't sent to the server in your loop. In fact, there are a bunch of other mysql client builtin commands which must be treated similarly if you find them in an SQL script.

If you eventually code all this logic, you've basically reimplemented the MySQL command-line client in Go.

It would be far quicker and simpler if you skip past all that coding work, and just run the MySQL command-line client using os.exec.Command. Think about it — it'll save you weeks of coding work, duplicating all the nuanced features of running SQL scripts that is already implemented in the MySQL client.

Ok so I decided to directly connect to the mysql database using the credentials given from Heroku.

This returns the URL to the database.

heroku config | grep CLEARDB_DATABASE_URL

It looks something like this:

mysql://alphanum-username:alphanum-password@us-cdbr-iron-east-01.cleardb.net/heroku_alphanum_name?reconnect=true

Then connect to it using mysql -u username -h host -p the host is from the URL which in this case is us-cdbr-iron-east-01.cleardb.net. Password is from alphanum-password.

I ran the same query but got this error which means I did not have access to the database.

ERROR 1044 (42000): Access denied for user 'bbf806af82a4c9'@'%' to database 'assessment'

Running show databases; shows that there are specific databases you can connect to as a user.

mysql> SHOW DATABASES;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| herokusdfhsdfhsdfsfsdf |
+------------------------+

From here I ran USE herokusdfhsdfhsdfsfsdf and ran the rest of my query with no issue.