Pooled Connections

[mcmcgrath13]

@quinnj I'm thinking about building out some infrastructure for pooling connections (similar to the js mysql package). A few questions:

1. Is this already part of the overhaul? (I'm exited about the upcoming changes!)
2. Should this live here or somewhere else?
3. Does something for this already exist? either formally as a package or as sample code that could be packaged?

[quinnj]

Hey @mcmcgrath13! Thanks for opening this issue. It's funny because I've definitely been thinking about this over the last little while.

1. I don't have anything related to pooling in the incoming overhaul
2. I'm fine with some pooling code living here, but I've also wondered about the possibility of having a generic Pooling.jl package that could provide generic functionality; in particular, it's been on my list for a while to do proper connection pooling in HTTP.jl, which has a really poorly implemented version right now. I haven't thought through all the details of whether that's practical (sharing pooling functionality between MySQL and HTTP), but I'd like to hope we could do something like that, since I imagine there's a host of other scenarios where a nice pooling API would come in handy.
3. Like I said, we have some very HTTP-specific code there, but it's not much to go off. I found https://github.com/mariadb-corporation/mariadb-connector-python/blob/master/src/mariadb_pooling.c while doing the overhaul, which I was planning on perusing for ideas and anything mysql-specific, so that's at least somewhere to start.

I'd love to see efforts here and am happy to chat on slack or in this issue about design ideas; just let me know what you're thinking!

[quinnj]

I'm going to start playing around w/ prototyping a generic ConnectionPooling.jl package.

[Cyvadra]

might be useful:

  DataFrames, MySQL, Dates
  # ]add [email protected]

  addslashes(x)   = x
  function addslashes(x::String)
    x = replace(x,r"\\+(['\"\\])"=>s"\1")
    x = replace(x,r"(['\"\\])"=>s"\\\1")
    end
  addslashes(x::T) where T <: Real = x
  addslashes(x::Array)    = json(x)
  addslashes(x::DateTime) = Dates.format(x,"yyyy-mm-dd HH:MM:SS")

  MYSQL_NUM_CONNECTIONS   = 10


# Mysql连接
  function mysql_init()
    mysql_conn = MySQL.DBInterface.connect(MySQL.Connection, serv.address, serv.user, serv.pass; db=serv.db, port=serv.port, reconnect=true)
    MySQL.DBInterface.execute(mysql_conn,"set names utf8")
    mysql_conn
    end
  mysql_conn_pool   = [ mysql_init() for i in 1:MYSQL_NUM_CONNECTIONS ]
  mysql_conn        = mysql_conn_pool[1]
  function mysql_query(sql::String)
    tmp_conn  = mysql_conn_pool[rand(1:MYSQL_NUM_CONNECTIONS)]
    try
      MySQL.DBInterface.execute(tmp_conn,sql) |> DataFrame
    catch e
      println()
      @show tmp_conn
      @show e
      @warn sql
      return nothing
    end
    end

[jerlich]

I don't think you want pooling to be done randomly at the query level. Because things like last_insert_id() will fail. right?