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.

Can a transaction avoid inserting duplicate values in my database?

I have the following table named values:

id serial user_id integer store_id integer identifier VARCHAR(255) created timestamp without time zone DEFAULT NOW() 

But my application has inserted following values:

id user_id store_id identifier created
1 1 1 123cdwe 2021-11-11 13:00:00
1 1 1 Ggrseza 2021-11-11 13:00:00

And that values seem wrong. What I suspect is a replication lag between master and slave alongside with an application termination right just after writing. The application is a web one and I suspect that happens whilst user ferfeshed whilst saving.

Would a transaction could solve the problem or this problem should be tackled at application level?

Altering mysql database column causes error in another column

First of all sorry everybody if this question sounds too basic.

I have a WordPress database and a table wp_comments.

In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:00.

I need to change this value to CURRENT_TIMESTAMP, so for example I run the following query:

ALTER TABLE `wp_comments` MODIFY `comment_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP; 

So, comment_date (NOT comment_date_gmt) is the column to modify.

Now, when I run this query I get the following error:

Error 1067: Invalid default value for comment_date_gmt 

Why is this happening? What could I do?

[UPDATE]

This is the table:

CREATE TABLE `wp_comments` (   `comment_ID` bigint(20) UNSIGNED NOT NULL,   `comment_post_ID` bigint(20) UNSIGNED NOT NULL DEFAULT '0',   `comment_author` tinytext NOT NULL,   `comment_author_email` varchar(100) NOT NULL DEFAULT '',   `comment_author_url` varchar(200) NOT NULL DEFAULT '',   `comment_author_IP` varchar(100) NOT NULL DEFAULT '',   `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',   `comment_content` text NOT NULL,   `comment_karma` int(11) NOT NULL DEFAULT '0',   `comment_approved` varchar(20) NOT NULL DEFAULT '1',   `comment_agent` varchar(255) NOT NULL DEFAULT '',   `comment_type` varchar(20) NOT NULL DEFAULT 'comment',   `comment_parent` bigint(20) UNSIGNED NOT NULL DEFAULT '0',   `user_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  ALTER TABLE `wp_comments`   ADD PRIMARY KEY (`comment_ID`),   ADD KEY `comment_post_ID` (`comment_post_ID`),   ADD KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),   ADD KEY `comment_date_gmt` (`comment_date_gmt`),   ADD KEY `comment_parent` (`comment_parent`),   ADD KEY `comment_author_email` (`comment_author_email`(10)); 

MySQL version is 5.7.33

[UPDATE]

I don’t know WHY but I know HOW I solved this issue:

ALTER TABLE `wp_comments` MODIFY `comment_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFY `comment_date_gmt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 

This way, by modifying both fields together, I get no errors.

It works, but I’m still quite curious, so if have any idea to share… 🙂

Database diagram for school management app

I am currently making a management software for a small artistic school: a small intranet in PHP / MySQL.

I’m trying to draw database architecture before I start coding because I don’t want to make bad choices right from the beginning.

My diagram can be found below.

The software requirements are as follows:

  • Register a student for a school year and assign them to one or more courses
  • Register a student for a unique event (Concert, Masterclass, internship …)
  • Register teachers and assign them to one or more courses
  • Manage the student financial side, the number of payments and the total cost of the training
  • And later there will be other needs like saving marks, schedules, and maybe other things …

I tried to transcribe it all on my diagram, but here are some explanations:

  • A student (student table) is unique and can register for several years. This table contains all the generic information (name, email, phone …)
  • A student registration (enrollment table) can follow several courses (course table) and several events (events table)
  • A student can pay for their training in one or more installments (funding table)
  • A course is given by a single teacher (trainer table) and an event is given by a single speaker (speaker table)

Is this the best way to keep a student’s information over several school years? Should I add some optimizations?

Database diagram

Strategies when DBCC CheckDB REPAIR_ALLOW_DATA_LOSS can’t repair a database

To get it out of the way, in this scenario we are repairing a number database where no backups were taken from before the corruption, so restoring a backup is not an option. Not my database 🙂

Upon running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS we get thousands of errors that look like this:

Msg 8928, Level 16, State 1, Line 7 Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24911) could not be processed.  See other errors for details.         Repairing this error requires other errors to be corrected first. Msg 8965, Level 16, State 1, Line 7 Table error: Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data). The off-row data node at page (1:24911), slot 0, text ID 265289728 is referenced by page (1:24820), slot 0, but was not seen in the scan.         Repairing this error requires other errors to be corrected first. Msg 8928, Level 16, State 1, Line 7 Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24912) could not be processed.  See other errors for details.         Repairing this error requires other errors to be corrected first. Msg 8965, Level 16, State 1, Line 7 Table error: Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data). The off-row data node at page (1:24912), slot 0, text ID 265289728 is referenced by page (1:24820), slot 0, but was not seen in the scan.         Repairing this error requires other errors to be corrected first. Msg 8928, Level 16, State 1, Line 7 Object ID 2105058535, index ID 1, partition ID 72057594038779904, alloc unit ID 72057594039762944 (type LOB data): Page (1:24913) could not be processed.  See other errors for details.         Repairing this error requires other errors to be corrected first. 

Running it repeatedly does not reduce the errors, so it looks like DBCC cannot repair this.

My next thought was to try and identify and delete the problematic rows in the table. When I tried deleting a known-problematic row though, it also errored, so my current thought is to extract known-good rows into a new table with the same schema as the old, drop the old table and rename the new one to match the old one.

The problem with this is that SQL Server, instead of providing a catchable error, simply drops the connection whenever a problem row is encountered, so I can’t seem to find a programmatic way to identify the ‘good’ rows.

Is there any way in T-SQL to force it to provide a nice catchable error so I can iterate through the table and pull out the good rows, or some ‘advanced’ mode of DBCC CHECKDB that might be able to repair it that isn’t obvious anywhere on the web?

What is the point of installing integration services without database engine?

My understanding is that the database engine feature of sql server enables us to create databases.

In the SSIS documentation, https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver15#install-integration-services, it is mentioned that:

On the Feature Selection page, under Shared Features, select Integration Services.

Under Instance Features, optionally select Database Engine Services to host the SSIS Catalog database, SSISDB, to store, manage, run, and monitor SSIS packages.

What is the purpose that one would not select the database engine service while installing integration services?

It is clear from the documentation quote above that – if we do not select the database engine, then we cannot host the SSIS catalog db (ssisdb). So what is the purpose of such an install?