With Perfect/Swift mySQL Must You Have a Single Database Connection Per Request?

Question

With Perfect/mySQL (https://github.com/PerfectlySoft/Perfect-MySQL), the examples I've seen suggest that you cannot reuse a single database connection across multiple connections. That is, you need a single database connection per HTTP request.

Examples I've seen are here: https://github.com/PerfectlySoft/Perfect-MySQL and here: https://perfect.org/docs/MySQL.html

Is this right? You must create a new db connection per request with Perfect/mySQL?

PerfectlyRock · Accepted Answer · 2017-08-22T15:02:38.287

You can still reuse the connection for multiple times, but please note that the connection is not thread-safe, so must add thread locks on the same connection

import MySQL
import PerfectThread
#if os(Linux)
import Glibc
#else
import Darwin
#endif

let mysql = MySQL()
let lock = Threading.Lock()
var jobs = 10

func now(_ id: Int) {
  print("Job Now #", id)
  lock.doWithLock {
    let x = mysql.query(statement: "SELECT now() as time")
    guard x, let y = mysql.storeResults(),
    let row = y.next() else {
    print(mysql.errorMessage())
      return
    }
    print(row[0] ?? "Now() FAILED")
    y.close()
    jobs -= 1
  }
}

func user(_ id: Int) {
  print("Job Usr #", id)
  lock.doWithLock {
    let x = mysql.query(statement: "select User from user")
    guard x, let y = mysql.storeResults(),
      let row = y.next() else {
       print(mysql.errorMessage())
       return
   }
    print(row[0] ?? "User() FAILED")
    y.close()
    jobs -= 1
  }
}

_ = mysql.setOption(.MYSQL_SET_CHARSET_NAME, "utf8mb4")
guard mysql.connect(host: "127.0.0.1", user: "root", password: "your pass", db: "mysql") else {
  print(mysql.errorMessage())
  exit(0)
}

jobs = 10
for id in 0 ..< 5 {
  Threading.dispatch {
    now(id)
  }
  Threading.dispatch {
    user(id)
  }
}

while jobs > 0 {
  sleep(1)
}

mysql.close()

In this example, the both functions are sharing the same connection and running for a few times. That's OK. However, if each function is running in a separated thread, then it will cause a failure.

Thanks for your response to this. I don't know of your involvement with the Perfect project, but do you know if there are plans to make this interface thread-safe? For server purposes, I'd like to have a single connection open only, which I think would be more efficient than opening a new connection per REST request — Chris Prince, Aug 19 '17 at 17:49
Actually, you don't have to wait for any updates from Perfect - adding thread lock is super easy, please check the gist below, which you can use multiple threads on the same connection safely: https://gist.github.com/RockfordWei/6f7ab9d66e975ce83b09db5b21c2d726 — PerfectlyRock, Aug 22 '17 at 14:57
@PerfectlyRock. I'm so new to database that I have to ask that `import MySQL`is part of what connector pack? I'm asking because I've found it in a GitHub repo and it doesn't come with any info.. Thank in advance — Vincenzo, Jan 07 '19 at 15:43

With Perfect/Swift mySQL Must You Have a Single Database Connection Per Request?

1 Answers1