On Wed, 25 Aug 2021, 15:00 Michael Caplan, <michael@eggplant.ws> wrote:

Hi there.

I'm going through an upgrade process I have done before with earlier
versions of mariaDB and Mysql, but running into an issue. My goal is to
create a new slave including upgrade process from MariaDB 10.2.22
(serverOld) to 10.5.12 on a new server (serverNew)

(as inspired by the brighter mind at
https://www.stephenrlang.com/2016/08/setting-up-mysql-master-slave-replication-with-rsync/
)

  1. rsync /var/lib/mysql from serverOld to serverNew
  2. On serverOld: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
  3. rerun rsync from serverOld to serverNew
  4. Release read lock from serverOld
  5. Start mariadb on serverNew
  6. run mysql_upgrade on serverNew
  7. Celebrate and have a nap

In actual practice step #5 failed numerous times before what I think is
now an okay serverNew

# Innodb settings between serverOld and serverNew

#serverOld
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 48M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 128M

#serverNew
innodb-flush-method            = O_DIRECT
innodb-log-file-size           = 48M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 128M

The only difference is innodb-log-files-in-group setting is removed in
serverNew, as it has been depricated and removed in 10.5

# Upgrade after crash?

When attempting to start serverNew, it would fail with the following error:

[ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
created with MariaDB 10.2.22.
[ERROR] InnoDB: Plugin initialization aborted with error Generic error

the log file on serverOld showed no evidence of crashing. None the
less, I restarted serverOld, and reran the process above. Still the same
issue step #5.

# Remove ib_logfile* files

I then tried forcing the recreation of the redo logs by removing them
and then starting up serverNew. This resulted in the following scary
errors:

[ERROR] InnoDB: Page [page id: space=0, page number=1984] log sequence
number 285722576186 is in the future! Current system log sequence number
259573448177.
[ERROR] InnoDB: Your database may be corrupt or you may have copied the
InnoDB tablespace but not the InnoDB log files. Please refer to
https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information
about forcing recovery.

# innodb_fast_shutdown = 0

The next attempt started with serverOld being restarted and
innodb_fast_shutdown = 0 being set beforehand. I reran the above steps
and still got stuck on step #5 with the following error:

[ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
created with MariaDB 10.2.22.
[ERROR] InnoDB: Plugin initialization aborted with error Generic error

This time, however, when I removed the ib_logfile* files, we seemed to
have an okay startup:

[Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk
size = 134217728
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
[Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
[Note] InnoDB: New log file created, LSN=288830457728
[Note] InnoDB: 128 rollback segments are active.
[Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
[Note] InnoDB: Creating shared tablespace for temporary tables
[Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing
the file full; Please wait ...
[Note] InnoDB: File './ibtmp1' size is now 12 MB.
[Note] InnoDB: 10.5.12 started; log sequence number 0; transaction id
3037161218
[Note] Plugin 'FEEDBACK' is disabled.
[Note] Recovering after a crash using /var/lib/mysql/mysql-bin
[Note] Starting crash recovery...
[Note] Crash recovery finished.
[Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
[Note] Server socket created on IP: '::'.
[ERROR] Incorrect definition of table mysql.event: expected column
'sql_mode' at position 14 to have type
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'),
found type
set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU
[ERROR] mariadbd: Event Scheduler: An error occurred when initializing
system tables. Disabling the Event Scheduler.
[Note] Reading of all Master_info entries succeeded
[Note] Added new Master_info '' to hash table
[Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.5.12-MariaDB-1:10.5.12+maria~focal-log' socket:
'/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution

Running mysql_upgrade completed without issue.

I'm not feeling confident that this upgrade is actually okay. removing
the ib_logfile* seems questionable as a requirement to get the upgrade done.

Anyone have similar troubles? Recommendations on what my process should be?

Thanks,

Mike

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp