0

We run a Plesk server, for our sins. We yesterday did an update from MariaDB 10.5.17 to 10.11.14

SYSTEM:

  • AlmaLinux 9.6
  • Plesk Obisidan 18.0.73

An issue that has been identified with this is that there are a bunch of "phantom" tables, that can't be replaced yet don't appear to any of the users on the system.

NOTE:
I am aware that Plesk uses "admin" as the core SQL user rather than "root". I have tried without success to find/reset the root password on the MariaDB, but this doesn't appear to have worked (will maybe retry another approach in just a sec).

While the stems here are for a specific table / DB, the wider issues effects all DBs on the server to some extent or another, including the Plesk PSA.

1 :

Website PHP/MySQL interaction states:

Error: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'someone_pictures.gallerynames' doesn't exist

However, the IDE before refresh shows:

enter image description here

After refresh the IDE shows:

enter image description here

2 :

Further, logging in to the MySQL using the CLI:

MariaDB [someone_pictures]> SHOW TABLES; +-----------------------------+ | Tables_in_someone_pictures | +-----------------------------+ | counter | | newgallery | | news | +-----------------------------+

I also checked the user's privileges and they're all fine:

MariaDB [mysql]> SHOW GRANTS FOR 'someone_theUser'@'localhost'; +-----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for someone_theUser@localhost | +-----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `someone_theUser`@`localhost` IDENTIFIED BY PASSWORD '::::::' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `someone_pictures`.* TO `someone_theUser`@`localhost`

3 :

So this table doesn't seem to exist, I therefore create it from a dump (from yesterday, before the update). The command is run in the correct Database:

MariaDB [someone_pictures]> CREATE TABLE `gallerynames` ( -> `ID` mediumint(6) NOT NULL AUTO_INCREMENT, -> `dirnum` varchar(20) DEFAULT NULL, -> `gname` varchar(255) DEFAULT NULL, -> `gsummary` varchar(255) DEFAULT NULL, -> `gshow` varchar(4) NOT NULL DEFAULT 'HIDE', -> `content` mediumtext DEFAULT NULL, -> `listing` int(8) DEFAULT NULL COMMENT 'even numbers.', -> PRIMARY KEY (`ID`), -> UNIQUE KEY `dirnum_2` (`dirnum`), -> KEY `listing` (`listing`) -> ) ENGINE=InnoDB AUTO_INCREMENT=786 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='names of the galleries';

But this returns:

ERROR 1005 (HY000): Can't create table someone_pictures.gallerynames (errno: 184 "Tablespace already exists")

So the table space already exists?

  • I had originally re-imported a 1Gb backup of the entire server SQL and that seemed to have worked ok mostly, the backup was auto-created by the Plesk update system prior to updating from 10.5.17 to 10.11.14 . Some of these tables seem to be "phantom".

  • I have tried "Discard Tablespace" But this fails to do anything, seems to be possibly a limiter on the Plesk "admin" user maybe?

  • I have also tried to clear all the files from the /var/lib/mysql/ directory to "clear" the MariaDB and then re-instantiate the files from an SQL backup- This seems to have worked in as far as the Database content is there, but the "phantom" tablespaces still remain.

Help please:

I see the issue as two fold:

  1. Can the "root" user "see" Tablespace and edit it directly? (eg is it worth logging in as "root" rather than as Plesk "admin"?

  2. If not, this How DISCARD the tablespace on mariadb while table is not found? Q&A references deleting the tablespace data...

BUT, we have already deleted the /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 as well as the /var/lib/mysql/ibdata1 files and this hasn't resolved the issue.

Is this a good idea:

My plan is now, something like this:

  1. wipe the files from /var/lib/mysql/ entirely.
  2. Stop MariaDB
  3. Reinstall MariaDB 10.11.14 from the command line. See How to reset MariaDB into a "fresh install" state?
  4. Set the Root login and password and record this outside and well away from Plesk "Admin" user.
  5. Log in directly to MariaDB CLI (not using Plesk db)
  6. Reinstate the SQL dump we have from before the update.

Is this a good idea? I don't know enough to know if I'm digging further down this hole or actually digging up and out this hole.

Improve this question

2 Answers 2

Reset to default
1

we have already deleted the /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1 as well as the /var/lib/mysql/ibdata1 files and this hasn't resolved the issue.

ib_logfile0 is a redo log there https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-redo-log applies:

Directly editing or moving the redo logs can cause corruption, and should never normally be attempted.

Your original missing tables would seem to be a result of this. MariaDB doesn't create file so people can have fun deleting them, they have a data storage purpose.

Because you've screwed up your database with such a reckless action, yes, restoring completely from backup is your only sane option.

Improve this answer
4
  • 1
    Thanks. We were advised by Plesk on their forums deleting these files would "reset" MariaDB install. Also that correlated with this DBA Q&A which states the same. Commented Oct 30 at 8:23
  • I looked though Plesk support and found some ib_logfile0/1 references, none that would give cause to delete them. I've submitted content to get some of these articles clarified. Thanks for doing your own answer. Commented Oct 31 at 2:52
  • For Ref; Here's one of the forum references: talk.plesk.com/threads/… Commented Oct 31 at 9:10
  • 1
    They copied from their support area, but I've now left a comment on how to fix this properly. Commented Nov 2 at 23:13
1

We fixed this by reinstalling the entire MariaDB install:

First by listing processes using 3306 and then kill these:

# lsof -i TCP:3306 # kill -9 <number>

And then ceasing MariaDB:

# sudo systemctl stop mariadb

Then by removing the contents of the /var/lib/mysql folder.

# rm -r /var/lib/mysql

Then reinstall from: ( see https://www.dbi-services.com/blog/how-to-install-a-specific-version-of-mariadb/ )

# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.11.14"

Then

# chmod +x mariadb_repo_setup

We finally checked the status

# systemctl status mariadb.service --no-pager --full

And then entering mysql and re-imported the datadump to the /va/lib/mysql/ directory:

# mysql -u root -p

Then install data from dump file:

mysql # < /our/psa/dumps/mysql.pre-rdbms-upgrade.20251028-104555.sql

Afterwards rebooting the server and everything worked. ( We needed to reboot twice but this may not have been a MariaDB/SQL issue )

EDIT:

Please also read Dan Black's answer and comment notes below.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.