[lxc-devel] [lxd/master] Improve database handling
freeekanayaka on Github
lxc-bot at linuxcontainers.org
Mon Apr 16 16:37:23 UTC 2018
A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 410 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20180416/47bf911f/attachment.bin>
-------------- next part --------------
From 449c3868c5e0d23a7e456631ebe93fb603ce789e Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Mon, 16 Apr 2018 15:14:37 +0000
Subject: [PATCH 1/4] Handle empty query strings
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
lxd/api_internal.go | 3 +++
1 file changed, 3 insertions(+)
diff --git a/lxd/api_internal.go b/lxd/api_internal.go
index 5f234537d..93ec99ad7 100644
--- a/lxd/api_internal.go
+++ b/lxd/api_internal.go
@@ -107,6 +107,9 @@ func internalSQL(d *Daemon, r *http.Request) Response {
if err != nil {
return BadRequest(err)
}
+ if req.Query == "" {
+ return BadRequest(fmt.Errorf("No query provided"))
+ }
db := d.cluster.DB()
result := internalSQLResult{}
if strings.HasPrefix(strings.ToUpper(req.Query), "SELECT") {
From bf99c6837db2d8381068877cf16e95a3585f37f5 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Mon, 16 Apr 2018 15:21:25 +0000
Subject: [PATCH 2/4] Support reading queries from standard in
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
lxd/main_sql.go | 15 +++++++++++++++
test/main.sh | 1 +
test/suites/sql.sh | 12 ++++++++++++
3 files changed, 28 insertions(+)
create mode 100644 test/suites/sql.sh
diff --git a/lxd/main_sql.go b/lxd/main_sql.go
index b466cf7a1..ed961e4f1 100644
--- a/lxd/main_sql.go
+++ b/lxd/main_sql.go
@@ -3,10 +3,13 @@ package main
import (
"encoding/json"
"fmt"
+ "io/ioutil"
+ "os"
"strconv"
"strings"
"time"
+ "github.com/pkg/errors"
"github.com/spf13/cobra"
"github.com/lxc/lxd/client"
@@ -23,6 +26,9 @@ func (c *cmdSql) Command() *cobra.Command {
cmd.Long = `Description:
Execute a SQL query against the LXD database
+ If <query> is the special value "-", than the query is read from
+ standard input.
+
This internal command is mostly useful for debugging and disaster
recovery. The LXD team will occasionally provide hotfixes to users as a
set of database queries to fix some data inconsistency.
@@ -49,6 +55,15 @@ func (c *cmdSql) Run(cmd *cobra.Command, args []string) error {
query := args[0]
+ if query == "-" {
+ // Read from stdin
+ bytes, err := ioutil.ReadAll(os.Stdin)
+ if err != nil {
+ return errors.Wrap(err, "Failed to read from stdin")
+ }
+ query = string(bytes)
+ }
+
// Connect to LXD
d, err := lxd.ConnectLXDUnix("", nil)
if err != nil {
diff --git a/test/main.sh b/test/main.sh
index be52c3b9e..ee8c4a89f 100755
--- a/test/main.sh
+++ b/test/main.sh
@@ -204,6 +204,7 @@ run_test test_clustering_containers "clustering containers"
run_test test_clustering_storage "clustering storage"
run_test test_clustering_network "clustering network"
#run_test test_clustering_upgrade "clustering upgrade"
+run_test test_sql "lxd sql"
# shellcheck disable=SC2034
TEST_RESULT=success
diff --git a/test/suites/sql.sh b/test/suites/sql.sh
new file mode 100644
index 000000000..c0f50fb01
--- /dev/null
+++ b/test/suites/sql.sh
@@ -0,0 +1,12 @@
+# Test the lxd sql command.
+test_sql() {
+ # Invalid arguments
+ ! lxd sql ""
+ ! lxd sql
+
+ # Single query
+ lxd sql "SELECT * FROM config" | grep "core.trust_password"
+
+ # Standard input
+ echo "SELECT * FROM config" | lxd sql - | grep "core.trust_password"
+}
From 0940f8f8c34a1f4607544c664d5fb8572ea6162a Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Mon, 16 Apr 2018 15:52:05 +0000
Subject: [PATCH 3/4] Support passing multiple queries
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
lxd/api_internal.go | 99 +++++++++++++++++++++++++++++++++--------------------
lxd/main_sql.go | 96 ++++++++++++++++++++++++++++-----------------------
test/suites/sql.sh | 7 ++--
3 files changed, 121 insertions(+), 81 deletions(-)
diff --git a/lxd/api_internal.go b/lxd/api_internal.go
index 93ec99ad7..daf29e66c 100644
--- a/lxd/api_internal.go
+++ b/lxd/api_internal.go
@@ -11,6 +11,7 @@ import (
"strings"
"github.com/gorilla/mux"
+ "github.com/pkg/errors"
"gopkg.in/yaml.v2"
"github.com/lxc/lxd/lxd/db"
@@ -94,7 +95,12 @@ type internalSQLPost struct {
Query string `json:"query" yaml:"query"`
}
+type internalSQLBatch struct {
+ Results []internalSQLResult
+}
+
type internalSQLResult struct {
+ Type string `json:"type" yaml:"type"`
Columns []string `json:"columns" yaml:"columns"`
Rows [][]interface{} `json:"rows" yaml:"rows"`
RowsAffected int64 `json:"rows_affected" yaml:"rows_affected"`
@@ -111,53 +117,72 @@ func internalSQL(d *Daemon, r *http.Request) Response {
return BadRequest(fmt.Errorf("No query provided"))
}
db := d.cluster.DB()
- result := internalSQLResult{}
- if strings.HasPrefix(strings.ToUpper(req.Query), "SELECT") {
- rows, err := db.Query(req.Query)
- if err != nil {
- return SmartError(err)
+ batch := internalSQLBatch{}
+ for _, query := range strings.Split(req.Query, ";") {
+ query = strings.TrimLeft(query, " ")
+ result := internalSQLResult{}
+ if strings.HasPrefix(strings.ToUpper(query), "SELECT") {
+ err = internalSQLSelect(db, query, &result)
+ } else {
+ err = internalSQLExec(db, query, &result)
}
- defer rows.Close()
- result.Columns, err = rows.Columns()
if err != nil {
return SmartError(err)
}
- for rows.Next() {
- row := make([]interface{}, len(result.Columns))
- rowPointers := make([]interface{}, len(result.Columns))
- for i := range row {
- rowPointers[i] = &row[i]
- }
- err := rows.Scan(rowPointers...)
- if err != nil {
- return SmartError(err)
- }
- for i, column := range row {
- // Convert bytes to string. This is safe as
- // long as we don't have any BLOB column type.
- data, ok := column.([]byte)
- if ok {
- row[i] = string(data)
- }
- }
- result.Rows = append(result.Rows, row)
- }
- err = rows.Err()
- if err != nil {
- return SmartError(err)
+ batch.Results = append(batch.Results, result)
+ }
+ return SyncResponse(true, batch)
+}
+
+func internalSQLSelect(db *sql.DB, query string, result *internalSQLResult) error {
+ result.Type = "select"
+ rows, err := db.Query(query)
+ if err != nil {
+ return errors.Wrap(err, "failed to execute query")
+ }
+ defer rows.Close()
+ result.Columns, err = rows.Columns()
+ if err != nil {
+ return errors.Wrap(err, "failed to fetch colume names")
+ }
+ for rows.Next() {
+ row := make([]interface{}, len(result.Columns))
+ rowPointers := make([]interface{}, len(result.Columns))
+ for i := range row {
+ rowPointers[i] = &row[i]
}
- } else {
- r, err := db.Exec(req.Query)
+ err := rows.Scan(rowPointers...)
if err != nil {
- return SmartError(err)
+ return errors.Wrap(err, "failed to scan row")
}
- result.RowsAffected, err = r.RowsAffected()
- if err != nil {
- return SmartError(err)
+ for i, column := range row {
+ // Convert bytes to string. This is safe as
+ // long as we don't have any BLOB column type.
+ data, ok := column.([]byte)
+ if ok {
+ row[i] = string(data)
+ }
}
+ result.Rows = append(result.Rows, row)
+ }
+ err = rows.Err()
+ if err != nil {
+ return errors.Wrap(err, "rows error")
+ }
+ return nil
+}
+func internalSQLExec(db *sql.DB, query string, result *internalSQLResult) error {
+ result.Type = "exec"
+ r, err := db.Exec(query)
+ if err != nil {
+ return errors.Wrapf(err, "failed to exec query")
+ }
+ result.RowsAffected, err = r.RowsAffected()
+ if err != nil {
+ return errors.Wrap(err, "failed to fetch affected rows")
}
- return SyncResponse(true, result)
+ return nil
}
var internalShutdownCmd = Command{name: "shutdown", put: internalShutdown}
diff --git a/lxd/main_sql.go b/lxd/main_sql.go
index ed961e4f1..7f37eae95 100644
--- a/lxd/main_sql.go
+++ b/lxd/main_sql.go
@@ -78,54 +78,66 @@ func (c *cmdSql) Run(cmd *cobra.Command, args []string) error {
return err
}
- result := internalSQLResult{}
- err = json.Unmarshal(response.Metadata, &result)
+ batch := internalSQLBatch{}
+ err = json.Unmarshal(response.Metadata, &batch)
if err != nil {
return err
}
- if strings.HasPrefix(strings.ToUpper(query), "SELECT") {
- // Print results in tabular format
- widths := make([]int, len(result.Columns))
- for i, column := range result.Columns {
- widths[i] = len(column)
+ for i, result := range batch.Results {
+ if len(batch.Results) > 1 {
+ fmt.Printf("=> Query %d:\n\n", i)
}
- for _, row := range result.Rows {
- for i, v := range row {
- width := 10
- switch v := v.(type) {
- case string:
- width = len(v)
- case int:
- width = 6
- case int64:
- width = 6
- case time.Time:
- width = 12
- }
- if width > widths[i] {
- widths[i] = width
- }
- }
- }
- format := "|"
- separator := "+"
- columns := make([]interface{}, len(result.Columns))
- for i, column := range result.Columns {
- format += " %-" + strconv.Itoa(widths[i]) + "v |"
- columns[i] = column
- separator += strings.Repeat("-", widths[i]+2) + "+"
+ if result.Type == "select" {
+ sqlPrintSelectResult(result)
+ } else {
+ fmt.Printf("Rows affected: %d\n", result.RowsAffected)
}
- format += "\n"
- separator += "\n"
- fmt.Printf(separator)
- fmt.Printf(fmt.Sprintf(format, columns...))
- fmt.Printf(separator)
- for _, row := range result.Rows {
- fmt.Printf(format, row...)
+ if len(batch.Results) > 1 {
+ fmt.Printf("\n")
}
- fmt.Printf(separator)
- } else {
- fmt.Printf("Rows affected: %d\n", result.RowsAffected)
}
return nil
}
+
+func sqlPrintSelectResult(result internalSQLResult) {
+ // Print results in tabular format
+ widths := make([]int, len(result.Columns))
+ for i, column := range result.Columns {
+ widths[i] = len(column)
+ }
+ for _, row := range result.Rows {
+ for i, v := range row {
+ width := 10
+ switch v := v.(type) {
+ case string:
+ width = len(v)
+ case int:
+ width = 6
+ case int64:
+ width = 6
+ case time.Time:
+ width = 12
+ }
+ if width > widths[i] {
+ widths[i] = width
+ }
+ }
+ }
+ format := "|"
+ separator := "+"
+ columns := make([]interface{}, len(result.Columns))
+ for i, column := range result.Columns {
+ format += " %-" + strconv.Itoa(widths[i]) + "v |"
+ columns[i] = column
+ separator += strings.Repeat("-", widths[i]+2) + "+"
+ }
+ format += "\n"
+ separator += "\n"
+ fmt.Printf(separator)
+ fmt.Printf(fmt.Sprintf(format, columns...))
+ fmt.Printf(separator)
+ for _, row := range result.Rows {
+ fmt.Printf(format, row...)
+ }
+ fmt.Printf(separator)
+}
diff --git a/test/suites/sql.sh b/test/suites/sql.sh
index c0f50fb01..9a687ce51 100644
--- a/test/suites/sql.sh
+++ b/test/suites/sql.sh
@@ -5,8 +5,11 @@ test_sql() {
! lxd sql
# Single query
- lxd sql "SELECT * FROM config" | grep "core.trust_password"
+ lxd sql "SELECT * FROM config" | grep -q "core.trust_password"
# Standard input
- echo "SELECT * FROM config" | lxd sql - | grep "core.trust_password"
+ echo "SELECT * FROM config" | lxd sql - | grep -q "core.trust_password"
+
+ # Multiple queries
+ lxd sql "SELECT * FROM config; SELECT * FROM containers" | grep -q "=> Query 0"
}
From 938d10eaa97078e320479f14b01cfb67d23b2630 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Mon, 16 Apr 2018 16:05:05 +0000
Subject: [PATCH 4/4] Support querying both local and global database
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
lxd/api_internal.go | 17 +++++++++++++++--
lxd/main_sql.go | 30 ++++++++++++++++++++++++------
test/suites/sql.sh | 14 +++++++++-----
3 files changed, 48 insertions(+), 13 deletions(-)
diff --git a/lxd/api_internal.go b/lxd/api_internal.go
index daf29e66c..450436383 100644
--- a/lxd/api_internal.go
+++ b/lxd/api_internal.go
@@ -92,7 +92,8 @@ func internalContainerOnStop(d *Daemon, r *http.Request) Response {
}
type internalSQLPost struct {
- Query string `json:"query" yaml:"query"`
+ Database string `json:"database" yaml:"database"`
+ Query string `json:"query" yaml:"query"`
}
type internalSQLBatch struct {
@@ -113,10 +114,22 @@ func internalSQL(d *Daemon, r *http.Request) Response {
if err != nil {
return BadRequest(err)
}
+
+ if !shared.StringInSlice(req.Database, []string{"local", "global"}) {
+ return BadRequest(fmt.Errorf("Invalid database"))
+ }
+
if req.Query == "" {
return BadRequest(fmt.Errorf("No query provided"))
}
- db := d.cluster.DB()
+
+ var db *sql.DB
+ if req.Database == "global" {
+ db = d.cluster.DB()
+ } else {
+ db = d.db.DB()
+ }
+
batch := internalSQLBatch{}
for _, query := range strings.Split(req.Query, ";") {
query = strings.TrimLeft(query, " ")
diff --git a/lxd/main_sql.go b/lxd/main_sql.go
index 7f37eae95..be0198e4f 100644
--- a/lxd/main_sql.go
+++ b/lxd/main_sql.go
@@ -13,6 +13,7 @@ import (
"github.com/spf13/cobra"
"github.com/lxc/lxd/client"
+ "github.com/lxc/lxd/shared"
)
type cmdSql struct {
@@ -21,10 +22,19 @@ type cmdSql struct {
func (c *cmdSql) Command() *cobra.Command {
cmd := &cobra.Command{}
- cmd.Use = "sql <query>"
- cmd.Short = "Execute a SQL query against the LXD database"
+ cmd.Use = "sql <local|global> <query>"
+ cmd.Short = "Execute a SQL query against the LXD local or global database"
cmd.Long = `Description:
- Execute a SQL query against the LXD database
+ Execute a SQL query against the LXD local or global database
+
+ The local database is specific to the LXD cluster member you target the
+ command to, and contains member-specific data (such as the member address).
+
+ The global database is common to all LXD members in the cluster, and contains
+ cluster-specific data (such as profiles, containers, etc).
+
+ If you are running a non-clustered LXD instance, the same applies, as that
+ instance forms effectively a single-member cluster.
If <query> is the special value "-", than the query is read from
standard input.
@@ -43,7 +53,7 @@ func (c *cmdSql) Command() *cobra.Command {
}
func (c *cmdSql) Run(cmd *cobra.Command, args []string) error {
- if len(args) != 1 {
+ if len(args) != 2 {
cmd.Help()
if len(args) == 0 {
@@ -53,7 +63,14 @@ func (c *cmdSql) Run(cmd *cobra.Command, args []string) error {
return fmt.Errorf("Missing required arguments")
}
- query := args[0]
+ database := args[0]
+ query := args[1]
+
+ if !shared.StringInSlice(database, []string{"local", "global"}) {
+ cmd.Help()
+
+ return fmt.Errorf("Invalid database type")
+ }
if query == "-" {
// Read from stdin
@@ -71,7 +88,8 @@ func (c *cmdSql) Run(cmd *cobra.Command, args []string) error {
}
data := internalSQLPost{
- Query: query,
+ Database: database,
+ Query: query,
}
response, _, err := d.RawQuery("POST", "/internal/sql", data, "")
if err != nil {
diff --git a/test/suites/sql.sh b/test/suites/sql.sh
index 9a687ce51..b158722e1 100644
--- a/test/suites/sql.sh
+++ b/test/suites/sql.sh
@@ -1,15 +1,19 @@
# Test the lxd sql command.
test_sql() {
# Invalid arguments
- ! lxd sql ""
! lxd sql
+ ! lxd sql foo "SELECT * FROM CONFIG"
+ ! lxd sql global ""
- # Single query
- lxd sql "SELECT * FROM config" | grep -q "core.trust_password"
+ # Local database
+ lxd sql local "SELECT * FROM config" | grep -q "core.https_address"
+
+ # Global database
+ lxd sql global "SELECT * FROM config" | grep -q "core.trust_password"
# Standard input
- echo "SELECT * FROM config" | lxd sql - | grep -q "core.trust_password"
+ echo "SELECT * FROM config" | lxd sql global - | grep -q "core.trust_password"
# Multiple queries
- lxd sql "SELECT * FROM config; SELECT * FROM containers" | grep -q "=> Query 0"
+ lxd sql global "SELECT * FROM config; SELECT * FROM containers" | grep -q "=> Query 0"
}
More information about the lxc-devel
mailing list