CODEWITHSUNDEEP
mysqlmariadbphpcommon-table-expressionprepared-statement
Francesco Piraneo G.
Francesco Piraneo G.

Reputation: 870

MariaDB: No database selected error on some query

I have a php code that rise a query against a MariaDB (using MariaDB 10.5.11 on debian 11) table; I use php-mysql prepared queries for this task as reported in the code below:

if($this->dbcon->begin_transaction() === false) {
        $this->errNum = -1;
        $this->errText = "Unable to start transaction: " . $this->dbcon->errno . " - " . $this->dbcon->error;
        return false;
}

try {
    $query = file_get_contents("recursivelyRemoveShares.sql");   // (1) If replaced with a SELECT works fine!

    if($query === false) {
        $this->errNum = -1;
        $this->errText = "Unable to read query (0)";
        return false;
    }

    $stmt = $this->dbcon->prepare($query);      // Err 1046: No database selected
    if($stmt === false) {
        $this->errNum = -1;
        $this->errText = "Unable to prepare statement: " . $this->dbcon->errno . " - " . $this->dbcon->error;
        return false;
    }
    
    $stmt->bind_param("s", $uuid);
    $stmt->execute();

    // Commit transaction
    $this->dbcon->commit();
} catch (Exception $ex) {
    // Rollback transaction if something goes wrong
    $this->dbcon->rollback();
    
    $this->errNum = $this->dbcon->errno;
    $this->errText = $this->dbcon->error;
    return false;
}

When running $stmt = $this->dbcon->prepare($query); the database raise an Err 1046: No database selected; however I did some other operations before that executed successfully, using the same DB connection.

This is the query I read with file_get_contents:

DELETE FROM `shares` WHERE `itemuuid` in (
  WITH RECURSIVE files_paths (id, parent) AS
  (
    SELECT uuid, parentuuid
      FROM core_data
      WHERE uuid = ?
    UNION ALL
    SELECT e.uuid, e.parentuuid
      FROM files_paths AS ep JOIN core_data AS e
        ON ep.id = e.parentuuid
  )
  SELECT id FROM files_paths
)

Note that is a recursive CTE query.

If I replace the $query with a SELECT query, all the code runs correctly (no error 1046 raisen).

Any help or idea is appreciated.

Monday Sept 13 edit!!

Same code tested on MySQL 8 works fine! Just removed MariaDB and installed MySQL8, no changes on php codes or SQL queries! Is this a MariaDB issue?

Upvotes: 0

Views: 702

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

This could possibly be a MariaDB issue. Here's why I believe so:

*** STAR WARS MUSIC START UP ***

*** SCREEN TILTS AND WORDS SCROLL UP AND SAYS ... ***

Over 10 years ago, I wrote an answer to the post Problem with MySQL subquery. In that post someone asked why a DELETE on a table with a subquery against the same table would not work. I had found something in the MySQL Internals Documentation that explains how a query can be rewritten in such a way that key values can virtually disappear while rows are being processed. In your case, I would speculate that one or more missing keys due to a transformation could be incorrectly interpreted as a "No Database Selected".

If your query works fine in MySQL 8.0, that points to the possibility that Oracle has actually fixed this behavior (or at least gotten a better handle on it). Keep in mind that MariaDB is a completely different codebase.

I have mentioned this crazy situation over the years in conjunction with doing DELETEs like this.

You may need to do one of two things:

  1. Upgrade to the latest MariaDB (File a Bug Report if Needed)
  2. Stick with MySQL 8.0

Upvotes: 1

Related Questions