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.
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