[MDEV-16845] Failure upon upgrade from 5.7.23 to 10.2 and 10.3, pagesize 4K - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.2.17, 10.3.9
Component/s: Storage Engine - InnoDB
Labels:
None

Description

https://travis-ci.org/elenst/travis-tests/jobs/409014461

# 2018-07-28T04:59:50 [24239] Running mysql_upgrade:

# 2018-07-28T04:59:50 [24239]   "/home/travis/server/bin/mysql_upgrade" --host=127.0.0.1 --port=19300 -uroot

ERROR 1071 (42000) at line 596: Specified key was too long; max key length is 768 bytes

ERROR 1071 (42000) at line 600: Specified key was too long; max key length is 768 bytes

ERROR 1071 (42000) at line 603: Specified key was too long; max key length is 768 bytes

FATAL ERROR: Upgrade failed

# 2018-07-28T04:59:51 [24239][ERROR] mysql_upgrade failed

Nothing special is needed on MySQL side, apart from bootstrap with page_size 4K. The pre-created datadir is attached. The exact steps to create it were these:

start MySQL 5.7.23 in initialize mode:

/data/bld/mysql-5.7/bin/mysqld --no-defaults --basedir=/data/bld/mysql-5.7 --datadir=/data/bld/mysql-5.7/data --log-error=/data/bld/mysql-5.7/data/log.err --loose-lc-messages-dir= --loose-language= --port=3306 --socket=/tmp/mysql.sock --tmpdir=/tmp --loose-core-file --initialize --innodb-page-size=4K

start the same server in normal mode, with skip-grant-tables:

/data/bld/mysql-5.7/bin/mysqld --no-defaults --basedir=/data/bld/mysql-5.7 --datadir=/data/bld/mysql-5.7/data --log-error=/data/bld/mysql-5.7/data/log.err --loose-lc-messages-dir= --loose-language= --port=3306 --socket=/tmp/mysql.sock --tmpdir=/tmp --loose-core-file --innodb-page-size=4K --skip-grant-tables

execute

MySQL [(none)]> flush privileges;

Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> set password for root@localhost = '';

Query OK, 0 rows affected (0.00 sec)

shut down the server normally.

Note: Upon initialization, MySQL sets one-time password which it writes to the error log. MySQL one-time passwords don't work for me reliably on some reason, that's why I reset it using skip-grant-tables. It's not important for the upgrade issue.

To reproduce the upgrade problem, start recent MariaDB 10.2 on the created datadir (which you either created as above, or unpacked from the attachment). Don't forget innodb_page_size=4K.

The server should complain in the error log but start all right. Then run mysql_upgrade with root user without parameters, like

bin/mysql_upgrade -uroot --protocol=tcp

It complains a lot while running, but the real (fatal) problem happens on phase 4/7:

Phase 4/7: Running 'mysql_fix_privilege_tables'

ERROR 1071 (42000) at line 596: Specified key was too long; max key length is 768 bytes

ERROR 1071 (42000) at line 600: Specified key was too long; max key length is 768 bytes

ERROR 1071 (42000) at line 603: Specified key was too long; max key length is 768 bytes

FATAL ERROR: Upgrade failed

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

data.tar.gz
479 kB
2018-08-01 12:45

Issue Links

relates to

MDEV-14637 Latching order violation during btr_cur_pessimistic_delete()

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2018-07-29 16:37

Elena Stepanova made changes - 2018-07-29 16:38

Field	Original Value	New Value
Summary	[Draft] Failure upon upgrade from 5.7.23 to 10.2	[Draft] Failure upon upgrade from 5.7.23 to 10.2, pagesize 4K

Elena Stepanova made changes - 2018-07-29 16:52

Affects Version/s

10.3 [ 22126 ]

Elena Stepanova made changes - 2018-07-29 16:52

Summary

[Draft] Failure upon upgrade from 5.7.23 to 10.2, pagesize 4K

[Draft] Failure upon upgrade from 5.7.23 to 10.2 and 10.3, pagesize 4K

Marko Mäkelä added a comment - 2018-07-31 17:46 - edited

I think that we must fix this as part of ~~MDEV-14637~~. What are the failing SQL statements?

Marko Mäkelä added a comment - 2018-07-31 17:46 - edited I think that we must fix this as part of MDEV-14637 . What are the failing SQL statements?

Marko Mäkelä made changes - 2018-07-31 17:48

Link

This issue relates to ~~MDEV-14637~~ [ ~~MDEV-14637~~ ]

Elena Stepanova added a comment - 2018-07-31 18:25 - edited

alter table mysql.innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp;

alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp;

mysql_upgrade runs the statement for innodb_table_stats twice, hence 3 errors.

The SQL error can be reproduced by manually executing the same statements, mysql_upgrade doesn't do anything special to achieve it.

Elena Stepanova added a comment - 2018-07-31 18:25 - edited alter table mysql.innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp ; alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp ; mysql_upgrade runs the statement for innodb_table_stats twice, hence 3 errors. The SQL error can be reproduced by manually executing the same statements, mysql_upgrade doesn't do anything special to achieve it.

Elena Stepanova made changes - 2018-07-31 18:25

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Assignee	Elena Stepanova [ elenst ]	Marko Mäkelä [ marko ]
Summary	[Draft] Failure upon upgrade from 5.7.23 to 10.2 and 10.3, pagesize 4K	Failure upon upgrade from 5.7.23 to 10.2 and 10.3, pagesize 4K

Elena Stepanova made changes - 2018-08-01 12:45

Attachment

data.tar.gz [ 45970 ]

Elena Stepanova made changes - 2018-08-01 12:51

Description

https://travis-ci.org/elenst/travis-tests/jobs/409014461
{noformat}
# 2018-07-28T04:59:50 [24239] Running mysql_upgrade:
# 2018-07-28T04:59:50 [24239] "/home/travis/server/bin/mysql_upgrade" --host=127.0.0.1 --port=19300 -uroot
ERROR 1071 (42000) at line 596: Specified key was too long; max key length is 768 bytes
ERROR 1071 (42000) at line 600: Specified key was too long; max key length is 768 bytes
ERROR 1071 (42000) at line 603: Specified key was too long; max key length is 768 bytes
FATAL ERROR: Upgrade failed
# 2018-07-28T04:59:51 [24239][ERROR] mysql_upgrade failed
{noformat}

Nothing special is needed on MySQL side, apart from bootstrap with page_size 4K. The pre-created datadir is attached. The exact steps to create it were these:
- start MySQL 5.7.23 in initialize mode:
{noformat}
/data/bld/mysql-5.7/bin/mysqld --no-defaults --basedir=/data/bld/mysql-5.7 --datadir=/data/bld/mysql-5.7/data --log-error=/data/bld/mysql-5.7/data/log.err --loose-lc-messages-dir= --loose-language= --port=3306 --socket=/tmp/mysql.sock --tmpdir=/tmp --loose-core-file --initialize --innodb-page-size=4K
{noformat}
- start the same server in normal mode, with {{skip-grant-tables}}:
{noformat}
/data/bld/mysql-5.7/bin/mysqld --no-defaults --basedir=/data/bld/mysql-5.7 --datadir=/data/bld/mysql-5.7/data --log-error=/data/bld/mysql-5.7/data/log.err --loose-lc-messages-dir= --loose-language= --port=3306 --socket=/tmp/mysql.sock --tmpdir=/tmp --loose-core-file --innodb-page-size=4K --skip-grant-tables
{noformat}
- execute
{code:sql}
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> set password for root@localhost = '';
Query OK, 0 rows affected (0.00 sec)
{code}
- shut down the server normally.

_Note: Upon initialization, MySQL sets one-time password which it writes to the error log. MySQL one-time passwords don't work for me reliably on some reason, that's why I reset it using {{skip-grant-tables}}. It's not important for the upgrade issue._

To reproduce the upgrade problem, start recent MariaDB 10.2 on the created datadir (which you either created as above, or unpacked from the attachment). Don't forget {{innodb_page_size=4K}}.

The server should complain in the error log but start all right. Then run {{mysql_upgrade}} with root user without parameters, like
{noformat}
bin/mysql_upgrade -uroot --protocol=tcp
{noformat}

It complains a lot while running, but the real (fatal) problem happens on phase 4/7:
{noformat}
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1071 (42000) at line 596: Specified key was too long; max key length is 768 bytes
ERROR 1071 (42000) at line 600: Specified key was too long; max key length is 768 bytes
ERROR 1071 (42000) at line 603: Specified key was too long; max key length is 768 bytes
FATAL ERROR: Upgrade failed
{noformat}

Marko Mäkelä added a comment - 2018-08-02 15:32

The dataset works with bb-10.2-marko, with the second commit of ~~MDEV-14637~~ which (among other things) makes ha_innobase::max_supported_key_length() return a longer limit for innodb_page_size=4k.

Marko Mäkelä added a comment - 2018-08-02 15:32 The dataset works with bb-10.2-marko, with the second commit of MDEV-14637 which (among other things) makes ha_innobase::max_supported_key_length() return a longer limit for innodb_page_size=4k .

Marko Mäkelä added a comment - 2018-08-03 05:43

Fixed as part of ~~MDEV-14637~~.

Marko Mäkelä added a comment - 2018-08-03 05:43 Fixed as part of MDEV-14637 .

Marko Mäkelä made changes - 2018-08-03 05:43

issue.field.resolutiondate

2018-08-03 05:43:05.0

2018-08-03 05:43:05.431

Marko Mäkelä made changes - 2018-08-03 05:43

Fix Version/s		10.2.17 [ 23111 ]
Fix Version/s		10.3.9 [ 23114 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88634 ]

MariaDB v4 [ 154723 ]

People

Assignee:: Marko Mäkelä

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-07-29 16:37

Updated:: 2018-08-05 22:47

Resolved:: 2018-08-03 05:43

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration