I have a MySQL database with 6 tables and about 2 million rows all together.
I want to migrate all the data into MongoDB.
I decided to do this by converting the SQL tables into JSON and importing it to MongoDB.
I wrote a program in Golang to extract the data and output it as JSON.
This is the main function of the program:
func main() {
// Open a database connection
var err error
db, err = sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/employees")
checkErr(err)
// Check if reachable
if err = db.Ping(); err != nil {
log.Fatal("Database is unreachable:", err)
}
// Populate variables with data
err = populateVars()
checkErr(err)
// Marshal variables into JSON
binaryJSON, err := json.Marshal(collection)
checkErr(err)
// Write JSON to a file
err = writeStringToFile("/home/user01/Temporary/sql2data.json", string(binaryJSON))
checkErr(err)
}
The problem is that the output is inconsistent.
Every time I run the program, the resulting file has a different size and some random fields are missing.
What could be causing this?
It doesn't seem like it's a problem with the logic of the program since everything executes without errors, and most fields are populated just fine.
Could I be reading the information too fast, so that some things get lost occasionally?
Or is there something else that I'm missing?
Edit:
Most of the work happens inside the populateVars() function call.
It has multiple blocks of code that execute a given SQL query and populate struct variables according to the schema.
This is one such block:
rows, err = db.Query("SELECT emp_no, dept_emp.dept_no, dept_name, from_date, to_date FROM dept_emp JOIN departments ON departments.dept_no = dept_emp.dept_no;")
checkErr(err)
i := 0
for rows.Next() {
var id int
var depNumber string
var depName string
var fromDate string
var toDate string
var position = "Employee"
err = rows.Scan(&id, &depNumber, &depName, &fromDate, &toDate,)
// For debugging purposes:
fmt.Println(id, depNumber, depName, fromDate, toDate, position, i)
if err != nil {
return err
}
for i := range collection {
if collection[i].ID == id {
collection[i].Departments = append(collection[i].Departments, Department{DepartmentNumber: depNumber, DepartmentName: depName, FromDate: fromDate, ToDate: toDate, Position: position})
// For debugging purposes:
fmt.Println(collection[i].Departments)
}
}
i++
}
Here's a GitHub link to the whole program: https://github.com/dchmie01/mysql_to_json/blob/master/main.go
Edit 2:
It seems like the issue has to do with query timeout.
Each query takes about 10 min to execute but at about 6 minutes in, I get this error, and the program stops executing the query:
[mysql] 2017/04/29 17:35:16 packets.go:66: unexpected EOF
[mysql] 2017/04/29 17:35:16 packets.go:412: busy buffer
2017/04/29 17:35:16 driver: bad connection
And in the MySQL log file it says:
2017-04-29T16:28:49.975805Z 102 [Note] Aborted connection 102 to db: 'employees' user: 'root' host: 'localhost' (Got timeout writing communication packets)
So far I tried playing around with MySQL variables to disable any timeouts that might be present, but no luck.
I think the issue might be with the mysql driver for Go.
collectionthe same as the number ofrowsreturned from the query? That is, did you preallocate the collection to a know number of returned rows? And do all of your queries, whose result you use to populate the samecollectionslice, return the same number of rows?