[Question] Best practice to avoid "DATABASE_ALREADY_EXISTS" with session mode in chdb-go

[yidigo]

I'm using chdb-go (v3.4.1 of the underlying chdb library) in session mode to perform a task that involves reading data from a Parquet file and inserting it into another table using SQL, like Execute an INSERT INTO ... SELECT ... FROM file('...', 'Parquet') query.

Using the project https://github.com/chdb-io/chdb-go to do some sql.

I've noticed that after running this process for a while, I consistently encounter the following error:

clickhouse error (status 500): Code: 82. DB::Exception: Database _temporary_and_external_tables already exists. (DATABASE_ALREADY_EXISTS)

I believe this is because the temporary database created by the session is not being properly cleaned up between invocations of my processing logic. I found a somewhat related issue (chdb/issues/267), but it doesn't offer a direct solution to this specific problem.

How to delete _temporary_and_external_tables database to avoid the error? Or recommend best practice for managing session resources and avoiding this kind of stateful conflict? Are there any other scenarios or configurations I should be aware of to prevent this issue, especially in long-running applications or concurrent environments?

Thank you for your great work on chdb.

[yidigo]

i turn on the debug mode and get info
chdb-server: pthread_mutex_lock.c:172: ___pthread_mutex_lock: Assertion `PTHREAD_MUTEX_TYPE (mutex) == PTHREAD_MUTEX_ERRORCHECK_NP' failed.

[yidigo]

I find some clues in in 03e0e51
for large numbers of queries, should the databases need to clean?

[wudidapaopao]

Hi @yidigo, could you please describe your processing logic in detail to facilitate better local reproduction on my end. Are there multiple times of creating and closing sessions in your code, or cases of abnormal session exits. Also, are there any errors or failures when creating new sessions or closing sessions.

After reviewing the code, I noticed that if an error occurs during session creation causing the _temporary_and_external_tables to not be cleaned up which may lead to a "DATABASE_ALREADY_EXISTS" error when attempting to create a new session next time.

[yidigo]

i use chdb in golang server and renew the session for each sql.but still occur the error. the max parallelism is 5. Only if i totally restart gonalg server, the service is back to normal. may i clean the cache by myself?

[wudidapaopao]

Hi @yidigo, currently, it's not possible to clear the cache on your own, and it might not be very appropriate to do so either.
In actual use, once a session is opened, try to reuse it for queries until you need to open a session with a new path. Additionally, there may be no need to open sessions or perform queries in parallel, because currently, only one session can be opened at the same time within a single process. Therefore, it's more appropriate to open a session and perform queries in a single thread. Finally, I would also like to confirm whether you have ever encountered a failure when opening a session.

[wudidapaopao]

i turn on the debug mode and get info chdb-server: pthread_mutex_lock.c:172: ___pthread_mutex_lock: Assertion `PTHREAD_MUTEX_TYPE (mutex) == PTHREAD_MUTEX_ERRORCHECK_NP' failed.

Regarding the error "pthread_mutex_lock.c:172: ___pthread_mutex_lock: Assertion `PTHREAD_MUTEX_TYPE (mutex) == PTHREAD_MUTEX_ERRORCHECK_NP' failed", could you please provide the error stack trace or any additional detailed information.

[yidigo]

how can i get more error stack trace ? when query i make the format for debug

[wudidapaopao]

Alternatively, could you please provide the code to reproduce the issue? I found that if a session fails to be created, it may cause the error "Database _temporary_and_external_tables already exists" when creating a session again later. However, I am not sure if there are any session creation failures in your scenario.

The best practice is to reuse sessions as much as possible, and have only one thread manage the creation, destruction, and querying of sessions.

[yidigo]

i run the service with these codes

package main

import (
	"bytes"
	"embed"
	"fmt"
	"io"
	"log"
	"net/http"
	"os"
	"strings"
	"time"

	"github.com/chdb-io/chdb-go/chdb"
)

//go:embed play.html
var content embed.FS

var (
	path string
)

func init() {
	path = os.Getenv("DATA_PATH")
	if path == "" {
		path = ".chdb_data"
	}
}

type server struct {
	sess *chdb.Session
}

func newServer(path string) *server {
	s := &server{}
	sess, err := chdb.NewSession(path)
	if err != nil {
		log.Fatalf("Error creating session: %v", err)
	}
	s.sess = sess
	//s.sess.Query("set max_memory_usage=")
	return s
}


func (s *server) handleRootPost(w http.ResponseWriter, r *http.Request) {
	t1 := time.Now()
	query := r.URL.Query().Get("query")
	format := r.URL.Query().Get("default_format")
	if format == "" {
		format = "TSV"
	}
	database := r.URL.Query().Get("database")
	var body string
	if r.Body != nil {
		bodyBytes, err := io.ReadAll(r.Body)
		if err != nil {
			http.Error(w, "Error reading request body", http.StatusBadRequest)
			return
		}
		body = string(bodyBytes)
	}
	if body != "" {
		body = strings.Join(strings.Fields(body), " ")
		if query != "" {
			query += " "
		}
		query += body
	}
	if database != "" {
		query = "USE " + database + "; " + query
	}
	//log.Printf("Query: %s\n", query)
	output, err := s.sess.Query(query, format)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		//http.Error(w, err.Error(), http.StatusServiceUnavailable)
		if strings.Index(err.Error(), "_temporary_and_external_tables") >= 0 {
			os.Exit(1)
		}
		return
	}
	fmt.Fprintf(w, "%s", output)
	elapsed := time.Since(t1)
	if len(query) > 500 {
		fmt.Printf("%s |  %s\n", elapsed, query[:500])
	} else {
		fmt.Printf("%s |  %s\n", elapsed, query)
	}

}

func (s *server) handlePing(w http.ResponseWriter, r *http.Request) {
	fmt.Fprintln(w, "Ok")
}

func (s *server) handlePlay(w http.ResponseWriter, r *http.Request) {
	data, err := content.ReadFile("play.html")
	if err != nil {
		http.Error(w, "Unable to open play.html", http.StatusInternalServerError)
		return
	}
	http.ServeContent(w, r, "play.html", time.Now(), bytes.NewReader(data))
}

func (s *server) handleNotFound(w http.ResponseWriter, r *http.Request) {
	data, err := content.ReadFile("play.html")
	if err != nil {
		http.Error(w, "Unable to open play.html", http.StatusInternalServerError)
		return
	}
	http.ServeContent(w, r, "play.html", time.Now(), bytes.NewReader(data))
}

func main() {
	s := newServer(path)
	mux := http.NewServeMux()

	// Register specific handlers
	mux.HandleFunc("/play", s.handlePlay)
	mux.HandleFunc("/ping", s.handlePing)

	// Catch-all handler: place this last
	mux.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
		switch r.URL.Path {
		case "/":
			if r.Method == http.MethodPost {
				s.handleRootPost(w, r)
				return
			}
			http.Redirect(w, r, "/play", http.StatusFound)
		default:
			s.handleNotFound(w, r) // Use this for 404 responses
		}
	})

	log.Println("Listening on :59011")
	log.Fatal(http.ListenAndServe(":59011", mux))
}

and do the query like for every ten minutes like this

generateSql := fmt.Sprintf("INSERT INTO FUNCTION file('%s', 'Parquet')\nSELECT * FROM file('%s', 'Parquet')\nUNION ALL\nSELECT * FROM file('%s', 'Parquet');drop database default", tmpPath, p.FilePath1, p.FilePath2)


result, err := utils.ExecuteCHQueryValues(taskConfig, generateSql)

[wudidapaopao]

Is there a certain probability of encountering the error "Database _temporary_and_external_tables already exists. (DATABASE_ALREADY_EXISTS)" after this code has been running for a period of time and are there any additional error log messages?

The following PR fixes a potential similar issue: when a session fails to be created due to reasons such as insufficient path permissions or other factors, all subsequent attempts to create sessions will fail.
#359