MySQL 8 Server does not start after Upgrade from 5.7.40

S

sidney2017

Hello,

I upgrade MySQL-Server 5.7.40 to MySQL-Server 8.0 under FreeBSD 13.1.

service mysql-server start throws following errors:

Code: 
2023-07-04T23:29:35.6NZ mysqld_safe Logging to '/var/log/mysql/error.log'.
2023-07-04T23:29:35.6NZ mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2023-07-04T23:29:35.281219Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.32) starting as process 67302
2023-07-04T23:29:35.468311Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-07-04T23:29:35.468340Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-07-04T23:29:35.590292Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 5.7.40, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2023-07-04T23:29:35.590329Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-07-04T23:29:35.590747Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2023-07-04T23:29:35.590914Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-07-04T23:29:35.590939Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-07-04T23:29:35.593030Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.32)  Source distribution.

Then I deleted ib_logfile0 and ib_logfile1 as described on http://dev.mysql.com/doc/refman/8.0/en/upgrading.html and restarted MySQL Server 8:

Code: 
2023-07-04T23:36:03.932139Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932155Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932165Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_1.ibd' OS error: 71
2023-07-04T23:36:03.932176Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_1` because it could not be opened.
2023-07-04T23:36:03.932200Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932210Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932219Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_2.ibd' OS error: 71
2023-07-04T23:36:03.932232Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_2` because it could not be opened.
2023-07-04T23:36:03.932256Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932265Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932274Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_3.ibd' OS error: 71
2023-07-04T23:36:03.932286Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_3` because it could not be opened.
2023-07-04T23:36:03.932310Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932319Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932328Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_4.ibd' OS error: 71
2023-07-04T23:36:03.932341Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_4` because it could not be opened.
2023-07-04T23:36:03.932363Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932374Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932382Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_5.ibd' OS error: 71
2023-07-04T23:36:03.932395Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_5` because it could not be opened.
2023-07-04T23:36:03.932421Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932430Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932439Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_6.ibd' OS error: 71
2023-07-04T23:36:03.932452Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_0000000000007540_INDEX_6` because it could not be opened.
2023-07-04T23:36:03.932476Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932485Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932494Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_BEING_DELETED.ibd' OS error: 71
2023-07-04T23:36:03.932506Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_BEING_DELETED` because it could not be opened.
2023-07-04T23:36:03.932529Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932539Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932548Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_BEING_DELETED_CACHE.ibd' OS error: 71
2023-07-04T23:36:03.932560Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_BEING_DELETED_CACHE` because it could not be opened.
2023-07-04T23:36:03.932586Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932596Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932604Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_CONFIG.ibd' OS error: 71
2023-07-04T23:36:03.932617Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_CONFIG` because it could not be opened.
2023-07-04T23:36:03.932642Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932652Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932660Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_DELETED.ibd' OS error: 71
2023-07-04T23:36:03.932672Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_DELETED` because it could not be opened.
2023-07-04T23:36:03.932697Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932707Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932716Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fc4_DELETED_CACHE.ibd' OS error: 71
2023-07-04T23:36:03.932728Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fc4_DELETED_CACHE` because it could not be opened.
2023-07-04T23:36:03.932754Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-07-04T23:36:03.932764Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-07-04T23:36:03.932772Z 1 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fdf_0000000000007568_INDEX_1.ibd' OS error: 71
2023-07-04T23:36:03.932785Z 1 [Warning] [MY-012019] [InnoDB] Ignoring tablespace `clipbucket/FTS_0000000000002fdf_0000000000007568_INDEX_1` because it could not be opened.
2023-07-04T23:36:03.932809Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.

...
...
2023-07-04T23:36:03.992868Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-07-04T23:36:06.673260Z 1 [ERROR] [MY-011006] [Server] Got error 197 from SE while migrating tablespaces.
2023-07-04T23:36:06.689616Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-07-04T23:36:06.689652Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-07-04T23:36:11.137744Z 0 [System] [MY-010910] [Server] /usr/local/libexec/mysqld: Shutdown complete (mysqld 8.0.32)  Source distribution

Unfortunately I had removed "clipbucket" database because I did not need it (only a test).

I read that MySQL Server 8 would do the necessary upgrade procedures itself at startup and that a mysql_upgrade was no longer necessary. What can/must I do so that MySQL Server 8 starts up with the existing databases (directories) and does not bother with any information about databases that no longer exist?

Thanks in advance and kind regards
Sidney2017
 
Obviously my problem is the same like described here:

MySQL Bugs: #97295: Ubuntu 19.10 upgrade from 5.7.27 to 8.0.17 failed

bugs.mysql.com bugs.mysql.com

But I do not know how to solve the problem!

MySQL Server 8 should simply ignore the missing databases/tables. These are not needed anyway.

But even a service mysql-server start --innodb-force-recovery=1 or service mysql-server start --upgrade=FORCE does not lead to success.

But apparently there is no start parameter that tells MySQL Server 8 to ignore the non-existing databases/tables.

Kind regards
Sidney2017
 
The upgrade process has worked fine for alll my upgrades.

It obviously didn’t like you manually deleting files.

The person at the link you posted seemed to fix it by going back to MySQL 5.7 and dropping the databases/tables and then redoing the upgrade to 8.0.

But you’ve also manually deleted things so that process might not work.

You might be best off creating a new machine with 5.7 installed and copying across the databases from the sad machine onto there. See if you can at least get MySQL 5.7 going with your database(s).
 
Hello, thank you very much!

However, I am surprised that there should not be a start parameter that simply tells the MySQL server to ignore the databases/tables that cannot be found and start with those that are present.

Kind regards
Sidney2017

P. S.:
I had also previously backed up the databases as SQL dumps. But unfortunately when importing via "Adminer" into a freshly set up MySQL-8 database (/var/db/mysql deleted, then that is recreated) there are also errors.
 
Hi,

as a test, I moved one of the databases to a VM running MariaDB 10.5.20 and then restarted MariaDB there. When I view the test database with Adminer or phpMyAdmin, all tables of the test database are visible, but as soon as I click on one of these tables XY to view the content with phpMyAdmin, I always get the message

"Table XY doesn't exist in engine".

I really don't know what to do anymore!

Kind regards
Sidney2017
 
You might have the .frm schema files but be lacking the actual binary data files.

You may have deleted those when you purged other files.

I wouldn’t advise trying taking MySQL databases to MariaDB now - you really want to try and get back to the exact same version of MySQL you were working with originally to simplify things.

And always check your backups … always test major system upgrades on copies of data.

Your problems probably won’t now be solved with a MySQL flag to ignore database/tables - you may have been too vigorous in deleting stuff.
 
sidney2017 said:
I had also previously backed up the databases as SQL dumps. But unfortunately when importing via "Adminer" into a freshly set up MySQL-8 database (/var/db/mysql deleted, then that is recreated) there are also errors.
Click to expand...
What if you try and import the dumps into MySQL 5.7?

What are the errors?

Is this all on a clean/fresh machine? You don’t want to do anything on the problematic machine to prevent it getting more confused.
 
VladiBG said:
dba.stackexchange.com

Mysql warning: InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set

Whenever my MySQL 5.7 starts I get the warning InnoDB: Ignoring tablespace `foo`.`bar` because the DISCARD flag is set bar is a table I experimented with long time ago with import tablespace, but...
dba.stackexchange.com dba.stackexchange.com

OR

If you have backup of the database restore those tables.
Click to expand...
Click to expand...
Hi,

I have a MySQL dump of all the databases, but strangely enough it only has all the create table statements in it and not the ones that also create the individual databases for those tables.

I then tried, for example, to create the database manually and then to create the saved create-table statements. Again, there were errors.

Let's take the database "database-test" as an example. If I look at it with phpMyAdmin on the other server with MariaDB, I see for example the files u1c4q_action_log_config.frm and u1c4q_action_log_config.ibd in the directory /mysql/database-test.

Then in phpMyAdmin I click on display the table "u1c4q_action_log_config", I get an error message "database-test.u1c4q_associations' doesn't exist in engine".

The question is therefore what is meant by "database-test.u1c4q_associations' doesn't exist in engine"?

Kind regards
Sidney2017
 
Hi,


What I don't understand: I made a backup of MySQL databases with Webmin as well as with Adminer (export). But there doesn't seem to be in any of these MySQL dumps the respective command for creating the database in which the tables are restored. Is it unusual that MySQL dumps automatically include the identifiers of the databases for the tables? Well, you can also create them manually, but I'm still a bit surprised about this.

What I don't understand: I made a backup of MySQL databases with Webmin as well as with Adminer (export). But there doesn't seem to be in any of these MySQL dumps the respective command for creating the database in which the tables are restored. Is it unusual that MySQL dumps automatically include the identifiers of the databases for the tables? Well, you can also create them manually, but I'm still a bit surprised about this.

Kind regards
Sidney2017


Code: 
-- MySQL dump 10.13  Distrib 8.0.32, for FreeBSD13.1 (amd64)
--
-- Host: localhost    Database: clipbucket
-- ------------------------------------------------------
-- Server version    5.7.40-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `cb_action_log`
--

DROP TABLE IF EXISTS `cb_action_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cb_action_log` (
  `action_id` int(255) NOT NULL AUTO_INCREMENT,
  `action_type` varchar(60) NOT NULL,
  `action_username` varchar(60) NOT NULL,
  `action_userid` int(30) NOT NULL,
  `action_useremail` varchar(200) NOT NULL,
  `action_userlevel` int(11) NOT NULL,
  `action_ip` varchar(15) NOT NULL,
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `action_success` enum('yes','no') DEFAULT NULL,
  `action_details` text NOT NULL,
  `action_obj_id` int(255) NOT NULL,
  `action_done_id` int(255) NOT NULL,
  PRIMARY KEY (`action_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cb_action_log`
--

LOCK TABLES `cb_action_log` WRITE;
/*!40000 ALTER TABLE `cb_action_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `cb_action_log` ENABLE KEYS */;
UNLOCK TABLES;


ODER


Code: 
...
DROP TABLE IF EXISTS `wp_actionscheduler_actions`;
CREATE TABLE `wp_actionscheduler_actions` (
  `action_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hook` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `scheduled_date_gmt` datetime DEFAULT '0000-00-00 00:00:00',
  `scheduled_date_local` datetime DEFAULT '0000-00-00 00:00:00',
  `args` varchar(191) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `schedule` longtext COLLATE utf8mb4_unicode_520_ci,
  `group_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `attempts` int(11) NOT NULL DEFAULT '0',
  `last_attempt_gmt` datetime DEFAULT '0000-00-00 00:00:00',
  `last_attempt_local` datetime DEFAULT '0000-00-00 00:00:00',
  `claim_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `extended_args` varchar(8000) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  PRIMARY KEY (`action_id`),
  KEY `hook` (`hook`),
  KEY `status` (`status`),
  KEY `scheduled_date_gmt` (`scheduled_date_gmt`),
  KEY `args` (`args`),
  KEY `group_id` (`group_id`),
  KEY `last_attempt_gmt` (`last_attempt_gmt`),
  KEY `claim_id` (`claim_id`),
  KEY `claim_id_status_scheduled_date_gmt` (`claim_id`,`status`,`scheduled_date_gmt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

INSERT INTO `wp_actionscheduler_actions`
 
sidney2017 said:
I made a backup of MySQL databases with Webmin as well as with Adminer (export).
Click to expand...
Tip from the trenches. Don't test your backup procedures, test your restore procedures. You're not the first, and probably won't be the last, that's been diligently making backups only to find out they're not working when you actually need them. When you make backups regularly check if you can use those backups to restore the data. You don't want to find out they're broken at the worst possible moment.

sidney2017 said:
But there doesn't seem to be in any of these MySQL dumps the respective command for creating the database in which the tables are restored. Is it unusual that MySQL dumps automatically include the identifiers of the databases for the tables?
Click to expand...
It depends on how those dumps have been made. But it's pretty irrelevant if the database doesn't exist. Just "CREATE DATABASE <name of database>;" beforehand. Then restore using mysql -u root -p mydatabase < database_backup.sql.
 
Thank you SirDice!

A question, independent of the option/attempt to restore the MySQL dumps:

I have a VM set up with MySQL 5.7 now.

In
/var/db/mysql
of the VM there are on the one hand the database directories with the tables etc. and on the other hand files like ib_logfile0, ib_logfile1, ibdata1, ibtmp1, ib_buffer_pool as well as mysql-bin.index, mysql-bin.000023 and mysql-bin.000023.

In the past, if you copied the database directories under /var/db/mysql from one server A to another server B with identical MySQL server version and then restarted the MySQL server on B, you could access the databases copied to B without any problems.

What is the role of the above mentioned like ib_logfile0, ib_logfile1, ibdata1, ibtmp1, ib_buffer_pool as well as mysql-bin.index, mysql-bin.000023 and mysql-bin.000023 etc. which are already present in the root directory of /var/db/mysql on server B?

Is it better to delete them before I copy all from server A /var/db/mysl with all subdirectories including the ones located there like ib_logfile0, ib_logfile1, ibdata1, ibtmp1, ib_buffer_pool as well as mysql-bin.index, mysql-bin.000023 and mysql-bin.000023 etc. to server B?

Thanks in advance and kind regards
Sidney2017
 
SirDice said:
Tip from the trenches. Don't test your backup procedures, test your restore procedures. You're not the first, and probably won't be the last, that's been diligently making backups only to find out they're not working when you actually need them. When you make backups regularly check if you can use those backups to restore the data. You don't want to find out they're broken at the worst possible moment.
Click to expand...

I never had problems in the past with restore procedures!

The crucial point in this case is that the supposed automatic conversion (implicit mysql-upgrade) of the installed MySQL server V8 simply does not work correctly and, moreover, this MySQL server then unfortunately also causes problems when importing MySQL dumps, which I do have.

Kind regards
Sidney2017
 
sidney2017 said:
In the past, if you copied the database directories under /var/db/mysql from one server A to another server B with identical MySQL server version and then restarted the MySQL server on B, you could access the databases copied to B without any problems.
Click to expand...
That may have worked fine with MyISAM, it will not with any of the more modern engines. There are some ways around this but it does require some extra work besides just copying the files.

sidney2017 said:
What is the role of the above mentioned like ib_logfile0, ib_logfile1, ibdata1, ibtmp1, ib_buffer_pool as well as mysql-bin.index, mysql-bin.000023 and mysql-bin.000023 etc. which are already present in the root directory of /var/db/mysql on server B?
Click to expand...
ibdata1 is the system's table space. You do NOT want to delete those willy-nilly.

MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.1 The System Tablespace

dev.mysql.com dev.mysql.com

ib_logfile0 and ib_logfile1 are the redo log. Don't delete these either.

MySQL :: MySQL 5.7 Reference Manual :: 14.6.6 Redo Log

dev.mysql.com dev.mysql.com

mysql-bin.* are your binlogs.

MySQL :: MySQL 5.7 Reference Manual :: 5.4.4 The Binary Log

dev.mysql.com dev.mysql.com

sidney2017 said:
Is it better to delete them before I copy all from server A /var/db/mysl with all subdirectories including the ones located there like ib_logfile0, ib_logfile1, ibdata1, ibtmp1, ib_buffer_pool as well as mysql-bin.index, mysql-bin.000023 and mysql-bin.000023 etc. to server B?
Click to expand...
If you value your data, do NOT delete them.

Having done a dozen or so database migrations from 5.7 to 8.0; it really wasn't much more than stopping the service, installing MySQL 8.0, fix any issues in my.cnf and starting the new version. Migration happened automagically without any major issues.
forums.freebsd.org

Heads-up: MySQL default Version will switch to 8.0

This is important enough to also post here. I'm sure it'll still catch people by surprise. Moin moin MySQL 5.7 is already for some time EOL. For this reason MySQL 5.6 will be deleted from the ports on 6/30/2023 and MySQL 5.7 on 12/31/2023. I have requested an exp-run today to change the...
forums.freebsd.org forums.freebsd.org
 
Hi,

I would like to report on the current interim status: I have set up a MySQL server 5.7.4 under FreeBSD 13.1 with the help of a VM "B".

I copied /var/db/mysql from the server "A", on which the whole thing with MySQL Server 8.0 does not run, to /var/db of the VM "B" and started the MySQL_Server 5.7.4 there.

Like MySQL Server 8, MySQL Server 5.7.4 also complains about the missing database "clipbucket", but continues to run, which would also be desirable with regard to MySQL Server 8, and displays with Adminer/phpMyAdmin all other databases/tables properly.

Now I would be interested if and how it is now possible to delete the criticized "clipbucket" database, which physically no longer exists?

Thanks and kind regards
Sidney2017

Code: 
2023-07-05T18:57:19.586471Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-07-05T18:57:19.586474Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `clipbucket/FTS_0000000000002fec_0000000000007590_INDEX_3`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2023-07-05T18:57:19.586486Z 0 [Warning] InnoDB: Ignoring tablespace `clipbucket/FTS_0000000000002fec_0000000000007590_INDEX_3` because it could not be opened.
2023-07-05T18:57:19.586500Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-07-05T18:57:19.586511Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-07-05T18:57:19.586515Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-07-05T18:57:19.586519Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './clipbucket/FTS_0000000000002fec_0000000000007590_INDEX_4.ibd' OS error: 71
2023-07-05T18:57:19.586523Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-07-05T18:57:19.586526Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2023-07-05T18:57:19.586529Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-07-05T18:57:19.586533Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `clipbucket/FTS_0000000000002fec_0000000000007590_INDEX_4`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2023-07-05T18:57:19.586538Z 0 [Warning] InnoDB: Ignoring tablespace `clipbucket/FTS_0000000000002fec_0000000000007590_INDEX_4` because it could not be opened.
2023-07-05T18:57:19.586551Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2023-07-05T18:57:19.586555Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
...
...
 
Oh, don´t you already use the latest FreeBSD Version generated by Bing Chat Bot? ?

Thanks for your hint, I corrected it!

Kind regards
Sidney2017
 
sidney2017 said:
I copied /var/db/mysql from the server "A", on which the whole thing with MySQL Server 8.0 does not run, to /var/db of the VM "B" and started the MySQL_Server 5.7.4 there.

Like MySQL Server 8, MySQL Server 5.7.4 also complains about the missing database "clipbucket", but continues to run, which would also be desirable with regard to MySQL Server 8, and displays with Adminer/phpMyAdmin all other databases/tables properly.
Click to expand...
That sounds promising - if you can tidy the database under 5.7.4 (assume you mean 5.7.4x e.g. 5.7.40 or 5.7.42?) and make sure NO errors there you can then try the MySQL upgrade process on there.

Maybe do a database dump on 5.7.4x once you've got the databases/tables fixed (i.e. BEFORE trying the upgrade to 8.0) - if that works OK then you can try importing those dumps in MySQL 8.0 as a plan B if the 5.7 to 8.0 in-place upgrade doesn't seem to work.
 
Hi,

since I wanted to get rid of that nasty clipbucket database etc. I deleted the /var/db/mysql folder on server A to force MySQL Server 8 to create a fresh mysql database.

I then imported the databases from the virtual machine (using MySQL 5.7.4) after exporting them.

Now everything is running like a charm!

Many thanks to all who have given hints!

Greetings
Sidney2017
 
As others have said the MySQL 5.7 to 8.0 upgrade-in-place usually works like a charm but looks like it can be upset in some scenarios so plan B is to go to restore from database dumps as you've done.
 
Hi,

another important lesson for me was that one should make sure that the MySQL dumps also contain the commands for generating the respective database and not only the tables. Otherwise, you will have to create and name the databases manually later and then also consider into which of these databases the tables have to be restored.

In my case, both the MySQL server backups created with adminer.php and the MySQL dump via Webmin always showed only the tables by default.

When exporting from the VM with MySQL 5.7.4X, I had to explicitly activate the backup of the database names beforehand.

Kind regards
Sidney2017
 
It's arguable if you want to create it or not. If you want to restore the same sql backup along side with the working/production database for test purposes then you don't want to have the create statement inside the .sql backup file otherwise you will have to parse the file using sed(1) and remove all create statements from it which may take some time on the big backups.
 
Hi VladiBG,

deleting create statements using reg. expressions in an editor is an easy thing to do. But in the event of a database crash, it takes a lot of time and effort to find out the names of the database identifiers in which all the tables saved with the MysQL dump belong and to create them manually beforehand.

Therefore, it is clear to me what my MySQL dumps will look like in the future.

Kind regards
Sidney2017
 
Hello,

however, there is a problem when switching from MySQL 5.7.4 to MySQL 8 if the necessary adjustments are not made automatically during the installation of MySQL 8. The user database from MySQL 5.7.4 cannot be easily re-imported into MySQL 8 as a MySQL dump, because the table structure of USERS -> Password looks somewhat different: In MySQL 8, the table STRUCTURE says plugin char(64) [caching_sha2_password], whereas in MySQL 5.7.4 it says plugin char(64) [mysql_native_password]. After the SQL import on the MySQL 8 server, however, almost all users have "mysql_native_password" in the "Plugin" column, contrary to the structure definition above. Among other things, Nextcloud suddenly stopped working for me because the relevant user from Nextcloud-config.php was no longer accepted with his account data. However, I was able to solve this quickly by uninstalling and reinstalling the Nextcloud package.

This could probably be the reason why the message "[Warning] [MY-010319] [Server] Found invalid password for user: '92787_1.usrXY@localhost'; Ignoring user" is displayed when MySQL Server 8 is started. Possibly the entries for mysql.infoschema, mysql.session and mysql.sys are no longer correct in the MysQL 8 user DB.

Kind regards
Sidney2017
 
You must log in or register to reply here.
Back
Top