This service will be undergoing maintenance at 00:00 UTC, 2017-10-23 It is expected to last about 30 minutes
Bug 1290718 - mysql_upgrade mixing up table list
mysql_upgrade mixing up table list
Status: CLOSED CURRENTRELEASE
Product: Red Hat Enterprise Linux 7
Classification: Red Hat
Component: mariadb (Show other bugs)
7.1
x86_64 Linux
unspecified Severity low
: rc
: ---
Assigned To: Michal Schorm
qe-baseos-daemons
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2015-12-11 03:41 EST by Maxime Veroone
Modified: 2017-01-17 06:53 EST (History)
3 users (show)

See Also:
Fixed In Version: mariadb-5.5.52-1.el7
Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2017-01-17 06:53:56 EST
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
mysql directory with files from mysql-3.23 and mysql/mysql/ directory from mariadb-5.5 (572.47 KB, application/x-gzip)
2015-12-21 08:51 EST, Maxime Veroone
no flags Details

  None (edit)
Description Maxime Veroone 2015-12-11 03:41:40 EST
Description of problem:
(Sorry for the title, I couldn't come up with a better one)
When ran over a mariadb server running from files copied from a previous versino of MySQL, mariadb first list the tables to repair, that works.
Then it tries to repair them, but looks for all tables in the last database it saw.

Version-Release number of selected component (if applicable):
mariadb-5.5.44-1.el7_1


How reproducible:
At least with bases from mysql 3.23 and 4.1 (haven't tried with mysql 5)


Steps to Reproduce:
0. Have an Old MySQL server with 3 tables  a.x a.y b.z
1. Rsync/scp /var/lib/mysql from an older version of mysql to the server
2. Start mariadb
3. run mysql_upgrade -p


Actual results:
[root@archive ~]# mysql_upgrade -p
Enter password: 
Phase 1/4: Fixing views
Phase 2/4: Fixing table and database names
Phase 3/4: Checking and upgrading tables
Processing databases
a
a.x  Needs Upgrade
a.y  Needs Upgrade
b
b.z  Needs Upgrade

Repairing tables
b.x
Error  : Table 'b.x' doesn't exist
status : Operation failed
b.y
Error  : Table 'b.y' doesn't exist
status : Operation failed
b.z                     OK
Phase 4/4: Running 'mysql_fix_privilege_tables'
OK


Expected results:
[root@archive ~]# mysql_upgrade -p
Enter password: 
Phase 1/4: Fixing views
Phase 2/4: Fixing table and database names
Phase 3/4: Checking and upgrading tables
Processing databases
a
a.x  Needs Upgrade
a.y  Needs Upgrade
b
b.z  Needs Upgrade

Repairing tables
a.x                     OK
a.y                     OK
b.z                     OK
Phase 4/4: Running 'mysql_fix_privilege_tables'
OK


Additional info:
Workaround : 
for base in a b ; do mysqlcheck --check-upgrade --auto-repair $base; done
Comment 2 Honza Horak 2015-12-21 06:03:57 EST
If I understand correctly, what you do is upgrading from mysql 3.23 and 4.0 to mariadb 5.5. First thing to realize is that this is not considered to be a supported upgrade path, since you should upgrade from latest previous release every-time, according to the doc:
"""
Upgrading more than one release level is supported, but only if you upgrade one release level at a time. For example, if you currently are running MySQL 5.0 and wish to upgrade to a newer series, upgrade to MySQL 5.1 first before upgrading to MySQL 5.5, and so forth. For information on upgrading to MySQL 5.1 see the MySQL 5.1 Reference Manual. 
"""
(https://dev.mysql.com/doc/refman/5.5/en/upgrading.html)

Anyway, what you describe here is not how the upgrade script should behave and it may point to some issue which will be reproducible in upgrade from higher version as well. Could you, please, provide a dump of the tables, so we can reproduce this problem?
Comment 3 Maxime Veroone 2015-12-21 06:22:40 EST
Actually, I do it in one step from 3.23 to 5.5, and it happened doing 4.1->5.5 too. I know this isn't the documented upgrading protocol, but it eventually works, except that I have to use the workaround for the last step, aka the "mysql_upgrade"

I am unable to provide you with the tables as they hold corporate private data, although I can confirm that this was 100% reproducible every time I had to migrate old MySQL servers no matter how data was stored on the origin (merge tables, innodb, myisam, etc.)

I'll try to install mysql 3.23 on an old unused RHEL3 server and try to reproduce with dummy tables, but I have to find time to do so.
Comment 4 Maxime Veroone 2015-12-21 08:49:38 EST
I did find the time, steps :

1) Install mysql-server-3.23 (from centOS3.5) in a RHEL3 machine
2) Create DB a & b, and their tables a.x a.y b.z
3) install mariadb-5.5 on a newer rhel/centos 7
4) rsync mysql directory over to newer server
5) remove mysql/ and test/ directories
6) sudo -u mysql mysql_install_db
7) start mariadb and run msyql_upgrade :

# mysql_upgrade 
Phase 1/4: Fixing views
Phase 2/4: Fixing table and database names
Phase 3/4: Checking and upgrading tables
Processing databases
information_schema
a
a.x                                                Needs upgrade
a.y                                                Needs upgrade
b
b.z                                                Needs upgrade
mysql
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
performance_schema
test

Repairing tables
test.x
Error    : Table 'test.x' doesn't exist
status   : Operation failed
test.y
Error    : Table 'test.y' doesn't exist
status   : Operation failed
test.z
Error    : Table 'test.z' doesn't exist
status   : Operation failed
Phase 4/4: Running 'mysql_fix_privilege_tables'
OK


mysql directory from after step 6 provided as attachement
Comment 5 Maxime Veroone 2015-12-21 08:51 EST
Created attachment 1108304 [details]
mysql directory with files from mysql-3.23 and mysql/mysql/ directory from mariadb-5.5

Copy full content to /var/lib/ , start mariadb and try to run mysql_upgrade
Comment 6 Honza Horak 2015-12-21 12:32:40 EST
Thank Maxime, I can confirm I can reproduce it with the attached data as described. It is possible to reproduce it even with mariadb 10.0.
Comment 7 Jakub Dorňák 2016-07-08 11:00:55 EDT
I haven't managed to solve it yet, so I've filed an upstream bug:
https://jira.mariadb.org/browse/MDEV-10348
Comment 8 Honza Horak 2016-10-27 01:48:42 EDT
According to https://jira.mariadb.org/browse/MDEV-9440 this issue should be fixed in mariadb 5.5.51 and later, so it will get fixed with next rebase (not sure when this happens but I believe it is just matter of time).
Comment 10 Honza Horak 2017-01-17 06:53:56 EST
Closing as per comment #8.

Note You need to log in before you can comment on or make changes to this bug.