MySQL

The mysql module allows you to run queries against the local MySQL server instance. You can also connect to remote databases.

Database Required

Before you can use the MySQL module, you will need to create a MySQL database. See the Administration section for more information on connecting to your database.

Client-Side MySQL

Most data handling can be handled directly on the client-side, without the need to create a server-side api. See the client-side MySQL module.

Query String

query

core.mysql.query( db_name, query_str )
--OR
core.mysql.q( db_name, query_str )

Parameters

Name	Description	Type	Required
db_name	The database name to issue commands against.	String	Y
query_str	The MySQL query to send to the database.	String	Y

Remote Databases

To connect to remote databases see Remote Databases.

Result Values

The following outlines the result values returned with each query type.

SELECT

A table array of records. If no records are found, returns an empty table.

INSERT

A number representing the insertion id.

UPDATE

A number indicating the amount of updated rows.

DELETE

A number indicating the amount of deleted rows.

Other Commands

Any other commands will be returned as table. It is recommended that you manage your databases using an external tool. See Administration below.

Tip

Use the core.sf (string format) utility method to make queries easier to build, and type safe.

Select Example

local query = core.sf("SELECT * FROM orders LIMIT %d", 100)

local result, err = core.mysql.query("clients", query)

if not result then
  core.log(err)
end

local record
for i=1, #result do
  record = result[i] --a record as a table
  core.log(record.id)
end

Insert Example

local columns = "name, age, email"
local values = {"Jimmy", 22, "me@somemail.com"}

--escape string values
values = core.mysql.escapeAll(values)

local query = core.sf("INSERT INTO users (%s) VALUES (%s, %d, %s);", 
  columns, 
  unpack(values)
)

local result, err = core.mysql.query("clients", query)

if not result then
  core.log(err)
end

core.log("inserted id is: ", result)

EZ Query Methods

EZ query methods provide an alternative way to construct common query types. For more complex queries, use the core.mysql.query method above.

Client-Side API

You can access the EZ query methods directly using the client-side MySQL module.

select

Select multiple records from a database table.

core.mysql.select(db_name, select_tbl)

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
select_tbl	The select table options (see below).	Table	Y

Select Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
columns	Array of columns to select as strings.	Table	N
where	Any additional WHERE clause to apply.	String or Table	N
orderby	The sorting attributes. See Orderby below.	Table	N
limit	Limit the records returned. See Limit below.	Number or Table	N
distinct	Filter out duplicate column values. Default: false	Boolean	N

Orderby

The orderby key should be a table filled with column = direction pairs. The direction can be either "ASC" for ascending order or "DESC" for descending order. See example 2 below.

Limit

To limit the rows returned, supply a number value to the limit key. To offset the limit, supply a table array of number values. For example, to return rows 6-15: limit = {5, 10}.

Returns

A table array of records, or nil and an error.

Example 1

-- Select all records from the 'scores' table
local result, err = core.mysql.select("leaderboard", {
  tbl = "scores"
})

if not result then
  core.log(err)
else
  -- `result` contains a table array of records
  local record
  for i=1, #result do
    record = result[i]
    core.log(record.player..' scored '..record.score)
  end
end

Example 2

-- Select 10 scores greater than 10, in descending order
local result, err = core.mysql.select("leaderboard", {
  tbl = "scores",
  columns = { "score" },
  where = "`score` > 10",
  limit = 10,
  orderby = {
    score = "DESC"
  }
})

if not result then
  core.log(err)
else
  -- `result` contains a table array of records
  local record
  for i=1, #result do
    record = result[i]
    core.log(record.player..' scored '..record.score)
  end
end

selectOne

Select and return a single record from a database table.

core.mysql.selectOne(db_name, select_tbl)

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
select_tbl	The select table options (see below).	Table	Y

Select Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
where	The WHERE clause to apply.	String or Table	Y
columns	Array of columns to select as strings.	Table	N

Returns

A single record as a table, or nil and an error.

Special Response

Unlike the select method, the result is returned as a single record as opposed to an array of records.

Example

local record, err = core.mysql.selectOne("app", {
  tbl = "users",
  where = { user_id = user_id },
  columns = { "name" }
})

selectBatch

Query multiple tables of a database and return the individual results in a keyed response.

core.mysql.selectBatch(db_name, batch_tbl)

Tip

This method offers optimization when used on the client-side. See the selectBatch client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
batch_tbl	A table array of EZ Query select tables, excluding the db key. (see select). Each select table must also include a key property for the result table (see example below).	Table	Y

Special Response

If a limit key is set to 1, the results will be a single table record, and not a table array. See selectOne.

Returns

A keyed table with the results of each select query as a table array of records. If limit in any of the query entries is set to 1, then a single table with the record data is returned (not an array).

Example

local batch_select = {
  {
    tbl = "products",
    where = { type = 'Bike' },
    key = "bikes"
  },
  {
    tbl = "products",
    where = { type = 'Shoes' },
    key = "shoes"
  },
  {
    tbl = "locations",
    where = { name = "Happy Toys" },
    limit = 1, --Return as non-array record
    key = "store"
  }
}

local result, err = core.mysql.selectBatch("store", batch_select)

if not result then
  core.log(err)
else
  core.log(result.bikes) --Array of "Bike" records
  core.log(result.shoes) --Array of "Shoe" records
  core.log(result.store) --Table "Store" record
end

Query Errors

If any of the queries in the batch result in an error, the results key for that particular query will contain an error key. Best practice is to check for this key before accessing the results.

-- Assuming batch call as shown in the example above

if not result then
  core.log(err)
else
  if result.bikes.error then
    --an error occurred on this particular query
    core.log(result.bikes.error)
  else
    --loop over the records
    for i=1, #result.bikes do
      core.log(result.bikes[i].name)
    end
  end
end

selectMerge

Select from multiple databases and tables and return the results in a keyed table.

core.mysql.selectMerge(merge_tbl)

Tip

This method offers optimization when used on the client-side. See the selectMerge client-side method.

Parameters

Name	Description	Type	Required
merge_tbl	A table array of EZ Query select tables, with an additonal db key. (see select). Each select table should also include a key property for the result table (see example below).	Table	Y

Returns

A key/value based table containing the key names assigned in the query tables. Each key section will contain a table array of returned records. If limit in any of the query entries is set to 1, then a single table with the record data is returned (not an array). See also Errors below.

Example

local res, err, code = core.mysql.selectMerge({
  {
    db = "locations",
    tbl = "spots",
    key = "locs"
  },
  {
    db = "products",
    tbl = "parts",
    where = { part_id = 20 },
    key = "part"
  },
  {
    db = "stores",
    tbl = "toy",
    limit = 1, --Return as non-array record
    where = { name = "Happy Toys" },
    key = "store"
  }
})

Errors

If any of the database queries fail, the successful queries will still be returned in their respective key names. On failed queries, the result will also contain an errors key, containing a table array describing any errors.

Example Response

{
  errors = {
    {
      db = products
      error = Table 'products.parts' doesn't exist
      index = 2
      status = 1146
    }
  },
  locs = {
    {
      id = 1
      latitude = 80
      longitude = 200
      user_id = "d23b8738-4d28-41ed-a967-98e83e855a38"
    },
    {
      id = 3
      latitude = -64
      longitude = 200
      user_id = "d23b8738-4d28-41ed-a967-98e83e855a39"
    }
  },
  store = {
    name = "Happy Toys",
    city = "San Diego"
  }
}

selectCount

Get a record count based on passed in query.

core.mysql.selectCount(db_name, count_tbl)

Parameters

Name	Description	Type	Required
db_name	The database to run the count query against.	String	Y
count_tbl	The count table options (see below).	Table	Y

Count Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
where	The WHERE clause to apply.	String or Table	N
column	The column to use as the counting key. Defaults to "id".	String	N

If the where key is not included, the result is the total records in the provided table.

Returns

The amount of records counted as a number, or nil, error, and error code.

Example

local count, err = core.mysql.selectCount("products", {
  tbl = "toys",
  where = { color = "Red" }
})

core.log("Total red toys "..count)

insert

Insert a single record into a database table.

core.mysql.insert(db_name, insert_tbl)

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
insert_tbl	The insert table options (see below).	Table	Y

Insert Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
values	A table of column = value pairs.	Table	Y

Important

String values are automatically run through mysql.escape. Do not double-escape values.

Returns

The record id as a number, or nil and an error.

Example

local result, err = core.mysql.insert("leaderboard", {
  tbl = "scores",
  values = {
    score = 200, 
    player = "Sandy"
  }
})

if not result then
  core.log(err)
else
  -- `result` contains the id for the inserted record
  core.log(result)
end

insertMany

Insert records into a single table of a database.

core.mysql.insertMany(db_name, insert_tbl)

Tip

This method offers optimization when used on the client-side. See the insertMany client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
insert_tbl	The insert table options (see below).	Table	Y

Insert Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
records	A table array of values tables. See the insert method above.	Table	Y

Important

String values are automatically run through mysql.escape. Do not double-escape values.

Returns

An indexed table array of tables containing either an id key; with the id of the newly created record, or an error key; containing the error string, or nil and an error.

The result table is indexed the same order as the records table that was supplied to the call.

Example

local records_arr = {
  {
    name = "Car",
    color = "red"
  },
  {
    name = "Teddy Bear",
    color = "brown"
  }
}


local result, err = core.mysql.insertMany("products", {
  tbl = "toys",
  records = records_arr
})

if not result then
  core.log(err)
else
  for i=1, #result do
    local entry = result[i]

    if entry.error then
      core.log("error in insert entry #"..i..": "..entry.error)
    else
      core.log("inserted record with id: "..entry.id.." for entry #"..i)
    end
  end
end

insertBatch

Insert records into multiple tables of a database.

core.mysql.insertBatch(db_name, batch_tbl)

Tip

This method offers optimization when used on the client-side. See the insertBatch client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
batch_tbl	A table array of tables with the tbl and values keys from the insert method.	Table	Y

Important

String values are automatically run through mysql.escape. Do not double-escape values.

Returns

An indexed table array of tables containing either an id key; with the id of the newly created record, or an error key; containing the error string, or nil and an error.

The result table is indexed the same order as the batch table that was supplied to the call.

Example

local batch_insert = {
  {
    tbl = "shoes"
    values = {
      name = "Adidas",
      size = 12
    }
  },
  {
    tbl = "toys",
    values = {
      name = "Car",
      age_group = 10
    }
  },
  {
    tbl = "toys",
    values = {
      name = "Bear",
      age_group = 5,
      color = "Brown"
    }
  }
}

local result, err = core.mysql.insertBatch("products", batch_insert)

if not result then
  core.log(err)
else
  for i=1, #result do
    local entry = result[i]

    if entry.error then
      core.log("error in insert entry #"..i..": "..entry.error)
    else
      core.log("inserted record with id: "..entry.id.." for entry #"..i)
    end
  end
end

update

Update record(s) in a database table.

core.mysql.update(db_name, update_tbl)

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
update_tbl	The update table options (see below).	Table	Y

Update Table Parameters

Name	Description	Type	Required
tbl	The name of the table to operate on.	String	Y
values	A table of column = value pairs.	Table	Y
where	Where the columns should be updated.	String or Table	N

Important

String values are automatically run through mysql.escape. Do not double-escape values.

Returns

The number of records updated, or nil and an error.

Example

local result, err = core.mysql.update("leaderboard", {
  tbl = "scores",
  values = {
    score = 230
  },
  where = { id = 20 }
})

if not result then
  core.log(err)
else
  -- `result` contains the number of records updated
  core.log(result)
end

updateMany

Update records in a single table of a database.

core.mysql.updateMany(db_name, update_tbl)

Tip

This method offers optimization when used on the client-side. See the updateMany client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
update_tbl	The update table options (see below).	Table	Y

Update Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
update	A table array of tables with the values and where keys from the update method.	Table	Y

Important

String values are automatically run through mysql.escape. Do not double-escape values.

Returns

An indexed table array of tables containing either an updated key; with the number of records updated, or an error key; containing the error string, or nil and an error.

The result table is indexed the same order as the update table that was supplied to the call.

Example

local update_arr = {
  {
    values = {
      name = "Nike"
    },
    where = { name="Adidas" }
  },
  {
    values = {
      cost = "1.99"
    },
    where = { id = 3 }
  }
}


local result, err = core.mysql.updateMany("products", {
  tbl = "shoes",
  update = update_arr
})

if not result then
  core.log(err)
else
  for i=1, #result do
    local entry = result[i]

    if entry.error then
      core.log("error in update entry #"..i..": "..entry.error)
    else
      core.log("updated "..entry.updated.." records for entry #"..i)
    end
  end
end

updateBatch

Update records in multiple tables of a database.

core.mysql.updateBatch(db_name, batch_tbl)

Tip

This method offers optimization when used on the client-side. See the updateBatch client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
batch_tbl	A table array of tables with the tbl, values and where keys from the update method	Table	Y

Important

String values are automatically run through mysql.escape. Do not double-escape values.

Returns

An indexed table array of tables containing either an updated key; with the number of records updated, or an error key; containing the error string, or nil and an error.

The result table is indexed the same order as the batch table that was supplied to the call.

Example

local batch_update = {
  {
    tbl = "shoes",
    values = {
      name = "Adidas"
    },
    where = { id = 3 }
  },
  {
    tbl = "toys",
    values = {
      name = "Raggedy Ann",
      gender = "female"
    },
    where = { name = "Raggedy Andy" }
  },
  {
    tbl = "toys",
    values = {
      company = "Tonka"
    },
    where = { id = 4 }
  }
}

local result, err = core.mysql.updateBatch("products", batch_update)

if not result then
  core.log(err)
else
  for i=1, #result do
    local entry = result[i]

    if entry.error then
      core.log("error in update entry #"..i..": "..entry.error)
    else
      core.log("updated "..entry.updated.." records for entry #"..i)
    end
  end
end

delete

Delete record(s) from a database table.

core.mysql.delete(db_name, delete_tbl)

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
delete_tbl	The delete table options (see below).	Table	Y

Delete Table Keys

Name	Description	Type	Required
tbl	The name of the table to operate on.	String	Y
where	A WHERE clause to limit deletions to.	String or Table	N
force	Disables safety check for missing where key.	Boolean	N

Force Delete

To run the delete command without a where clause, you must set force to true.

Returns

The number of records deleted, or nil and an error.

Example

Delete using a where clause

local result, err = core.mysql.delete("leaderboard", {
  tbl = "scores",
  where = "`score` < 10"
})

if not result then
  core.log(err)
else
  -- `result` contains the number of records deleted
  core.log(result)
end

Delete all records from the table

local result, err = core.mysql.delete("leaderboard", {
  tbl = "scores",
  force = true
})

if not result then
  core.log(err)
else
  -- `result` contains the number of records deleted
  core.log(result)
end

deleteMany

Delete records from a single table of a database.

core.mysql.deleteMany(db_name, delete_tbl)

Tip

This method offers optimization when used on the client-side. See the deleteMany client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
delete_tbl	The delete table options (see below).	Table	Y

Delete Table Keys

Name	Description	Type	Required
tbl	Name of the table to operate on.	String	Y
delete	A table array of tables with the where key from the delete method.	Table	Y

Returns

An indexed table array of tables containing either a deleted key; with the number of records deleted, or an error key; containing the error string, or nil and an error.

The result table is indexed the same order as the update table that was supplied to the call.

Example

local delete_arr = {
  {
    where = { id = 34 }
  },
  {
    where = { color= "Red" }
  }
}


local result, err = core.mysql.deleteMany("products", {
  tbl = "shoes",
  delete = delete_arr
})

if not result then
  core.log(err)
else
  for i=1, #result do
    local entry = result[i]

    if entry.error then
      core.log("error in delete entry #"..i..": "..entry.error)
    else
      core.log("deleted "..entry.deleted.." records for entry #"..i)
    end
  end
end

deleteBatch

Delete records from multiple tables of a database.

core.mysql.deleteBatch(db_name, batch_tbl)

Tip

This method offers optimization when used on the client-side. See the deleteBatch client-side method.

Parameters

Name	Description	Type	Required
db_name	The database to run the query against.	String	Y
batch_tbl	A table array of tables with the tbl and where keys from the delete method.	Table	Y

Returns

An indexed table array of tables containing either a deleted key; with the number of records deleted, or an error key; containing the error string, or nil and an error.

The result table is indexed the same order as the batch table that was supplied to the call.

Example

local batch_delete = {
  {
    tbl = "toys",
    where = { id = 2 }
  },
  {
    tbl = "shoes",
    where = { kind = "running" }
  }
}

local result, err = core.mysql.deleteBatch("products", batch_delete)

if not result then
  core.log(err)
else
  for i=1, #result do
    local entry = result[i]

    if entry.error then
      core.log("error in delete entry #"..i..": "..entry.error)
    else
      core.log("deleted "..entry.deleted.." records for entry #"..i)
    end
  end
end

Utilities

escape

Escape a string value to be sql safe.

core.mysql.escape(unescaped_str)

Parameters

Name	Description	Requried
unescaped_str	The string value to escape.	Y

Returns

A sql safe escaped string.

Example

local str = core.mysql.escape("Eat at Joe's")

Escaping Values

The returned value is enclosed in single quotes. Do not wrap the value with additional quotes or it may cause problems with your query. Many EZ Query methods automatically use mysql.escape on values, be sure to check the documentation for each method.

escapeAll

Escape all string values in a table array to be sql safe.

core.mysql.escapeAll(tbl_values)

Parameters

Name	Description	Requried
tbl_values	A table array of mixed value types. String values will be escaped.	Y

Returns

Returns a table array of the escaped string values.

Example

local values = {
  'A special "thing" here.',
  24,
  "Joe's Place"
}

values = core.mysql.escapeAll( values )

timestamp

MySQL compatible UTC based timestamp.

core.mysql.timestamp([seconds])

Parameters

Name	Description	Type	Required
seconds	UNIX timestamp.	Number	N

Returns

UTC timestamp based on provided UNIX time. Otherwise, returns current UTC timestamp.

localTimestamp

MySQL compatible timestamp based on the local server time.

core.mysql.localTimestamp([seconds])

Parameters

Name	Description	Type	Required
seconds	UNIX timestamp.	Number	N

Returns

Local timestamp based on provided UNIX time. Otherwise, returns current local timestamp.

parseTimestamp

A utility method to convert a MySQL timestamp to Unix time.

core.mysql.parseTimestamp( mysql_ts )

Parameters

Name	Description	Type	Required
mysql_ts	A MySQL timestamp in the form of XXXX-XX-XX XX:XX:XX.	String	Y

Returns

Unix timestamp based on provided MySQL timestamp.

date

MySQL compatible UTC based date.

core.mysql.date([seconds])

Parameters

Name	Description	Type	Required
seconds	UNIX timestamp.	Number	N

Returns

UTC date based on provided UNIX time. Otherwise, returns current UTC date.

localDate

MySQL compatible date based on the local server date.

core.mysql.localDate([seconds])

Parameters

Name	Description	Type	Required
seconds	UNIX timestamp.	Number	N

Returns

Local date based on provided UNIX time. Otherwise, returns current local date.

logQueries

A special method that will enable the logging of the query strings output by MySQL methods (or other methods that use a database) until it is toggled off.

core.mysql.logQueries(state)

Parameters

Name	Description	Type	Required
state	Set the query logging on or off. Default is true.	Boolean	N

Warning

This should only be used for debugging purposes or you'll end up with a lot of log entries.

Example

core.mysql.logQueries() --start logging queries

local res, err, code = core.mysql.select("products", {
  tbl = "toys",
  where { id = 20 }
})

-- The following output will be added to the Coronium log file
-- SELECT * FROM `toys` WHERE `id`=20;

core.mysql.logQueries(false) --stop logging queries
--Any MySQL methods run after will not be written to the log.

MySQL NULL Type

When using the EZ Query methods, you can set a column value to a valid MySQL NULL type by passing a string value set to 'NULL'. This only works if the column allows NULL values.

Example

local res, err, code = core.mysql.update("products", {
  tbl = "toys",
  values = {
    color = 'NULL'
  },
  where = { color = "red" }
})

The WHERE Key

Many of the MySQL (and other) modules use a where key to specify the "WHERE" clause for a database query. Depending on what data type and structure you provide this key, a couple different things can happen automagically.

String Based

When passing a string to the where key, you are on your own to create a valid MySQL query string. The query string is interpreted as-is.

You never include the literal "WHERE" in the where key value.

Examples

where = "`color`='Red'"

where = "`kind`='Truck' AND `color`='Red'"

where = "`kind`='Truck' OR `kind`='Car'"

Table Based

The advantage of using a table based where key is that all of the values are properly formatted to make a valid and type-safe MySQL query.

Important

String values are automatically run through mysql.escape. Do not double-escape values.

To reproduce the three string based examples above as table based:

where = { color = "Red" }

where = { kind = "Truck", color = "Red" } --AND

where = { kind = { "Truck", "Car" } } --OR

Ordered Table Queries

With "ordered" query tables you can replicate some more complex queries:

where = "`color='Red' AND `model`='Ford' AND kind='Truck' OR kind='Car'"

To reproduce the query above, put the entries in a table array (order matters):

where = {
  { color = "Red", model = "Ford" },
  { kind = { "Truck", "Car" } }
}

For an all OR query like:

where = "`color='Red' OR `model`='Ford' OR kind='Truck' OR kind='Car'"

You can use an ordered table with a single entry:

where = {
  { color = { "Red" }, model = { "Ford" }, kind = { "Truck", "Car" } }
}

At this time multiple entries in an "ordered" where table are combined using 'AND', which may cause problems with more complicated queries, so you'll need to fall back to the string method.

Table type queries also do not support conditionals, so the following cannot be replicated with a table based query:

where = "`score` > 100"

Advanced Methods

While the MySQL module methods above are fairly performant, they do automatically manage the database connections, and create the raw query strings, which creates a slight hit on speed.

By managing the database connection directly you can get the best performance from your queries, especially if you are performing multiple queries in your API methods.

Closing Connections

Always use dbClose at the end of your session, or you'll leave a connection open, using up resources.

dbConnect

Create a new connection to a database.

core.mysql.dbConnect( db_name )

Returns

A new database connection object, or nil, and an error.

Parameters

Name	Description	Type	Required
db_name	The database to connect to. You can also use a connection table. See the Connection Table section in Remote Databases.	String	Y

Example

local db_conn, err = core.mysql.dbConnect("products")

dbQuery

Perform a query using the database connection (see dbConnect). You can, and should, run multiple query calls using the same database connection.

core.mysql.dbQuery( db_connection, query_str )

Returns

The query result, or nil, and an error. See the Result Values section of the query method.

Parameters

Name	Description	Type	Required
db_connection	The database connection returned from dbConnect.	Object	Y
query_str	The MySQL query to send to the database.	String	Y

Example

local result, err = core.mysql.dbQuery(db_conn, "SELECT * FROM `products`;")

dbClose

Close a previous database connection opened with dbConnect.

core.mysql.dbClose( db_connection )

Returns

A truthy value on success, or nil, and an error.

Parameters

Name	Description	Type	Required
db_connection	The database connection returned from dbConnect.	Object	Y

Example

local ok, err = core.mysql.dbClose(db_conn)

MySQL Timeout

For large queries you may need to adjust the timeout for the call. You can do this by passing a connection table in place of the database name and include the timeout parameter in milliseconds. The default is 2000 (2 seconds).

Example

local query = core.sf("SELECT * FROM `orders` LIMIT %d", 100)

local conn_tbl = {
  database = "clients",
  timeout = 10000 --10 secs
}

local result, err = core.mysql.query(conn_tbl, query)

EZ Query Methods

You can do the same for the EZ Query methods. Replace the database name with a connection table as shown above.

Remote Databases

You can connect to remote MySQL databases by using a connection table in place of the database name in the core.mysql.query and EZ Query methods.

Connection Table

Key	Description	Type	Required
database	The remote database name.	String	Y
user	The remote database user.	String	Y
password	The remote database password	String	Y
host	The remote database host address.	String	Y
port	The remote database port. Default: 3306	Number	N
timeout	The timeout in milliseconds for the query. Default: 2000	Number	N

Example

local conn_tbl = {
  database = "clients",
  user = "dbuser",
  password = "1234",
  host = "my.sql.host"
}

local results, err = core.mysql.query(conn_tbl, query_str)

Administration

You can and should manage your MySQL databases using a standalone tool. Below are some free resources for managing MySQL databases.

Screencast Available

Learn more about database administration in a screencast format by Clicking here.

• SequelPro (OSX)
• HeidiSQL (Windows)

To connect to the MySQL database, use the host address of the server, and the password that was set when installing Coronium Core.

Remote Access

By default, a fresh Coronium Core installation allows remote access to the MySQL database with a password so that you can use client side tools to edit your databases.

While this is convenient to the developer, it is not particularly the most secure way to run the database. Instead you should only activate remote access while you work on the database, and then deactivate it when you are done.

DigitalOcean

To activate/deactivate remote MySQL access use the mysql-remote tool by logging in with the coronium user and entering one of the following on the command line:

Activate remote access

sudo mysql-remote on

Deactivate remote access

sudo mysql-remote off

If you are having issues connecting with your MySQL client, make sure that you have remote access in an active state.

Amazon EC2

Disable/enable port 3306 as needed in your instance security group. For more information on editing your security group click here.

Configuration File

You can adjust the various MySQL server configuration settings in the coronium_mysql.cnf file. The file is located at:

/etc/mysql/coronium.conf.d/coronium_mysql.cnf

Configuration Changes

Changes to the configuration file can have adverse effects, do so at your own risk. See MySQL Server System Variables for the available configuration options.

This file can be edited by using the nano utility on the server:

sudo nano /etc/mysql/coronium.conf.d/coronium_mysql.cnf

Use the arrow keys on your keyboard to move the cursor. When your updates are complete, use ctrl-x and then press y to save the file.

You will need to restart the MySQL server daemon for the changes to take effect:

sudo monit restart mysqld

Check the Coronium server status to make sure that MySQL is running:

coronium status

Viewing Error Log

The MySQL server error log can be viewed using:

sudo tail -f /var/log/mysql/error.log

Use ctrl-c to exit.

Password Update

To change your MySQL password log in your Coronium Core server as and run:

sudo mysql-updatepw