Bug 1490398 - mysql_upgrade fails when the same stored procedure name to uppercase and lowercase database names exists.
Summary: mysql_upgrade fails when the same stored procedure name to uppercase and lowe...
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Enterprise Linux 7
Classification: Red Hat
Component: mariadb
Version: 7.3
Hardware: All
OS: Linux
medium
medium
Target Milestone: rc
: ---
Assignee: Michal Schorm
QA Contact: Anna Khaitovich
URL:
Whiteboard:
Depends On:
Blocks: 1346768
TreeView+ depends on / blocked
 
Reported: 2017-09-11 13:11 UTC by Masahiro Matsuya
Modified: 2019-08-06 13:18 UTC (History)
5 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Cause: mysql_upgrade used to convert all columns of mysql.db to utf8_general_ci and then back to utf8_bin. In two separate ALTER's. This failed if UNIQUE indexes in mysql.db contained entries that differ only in the letter case. Consequence: mysql_upgrade fails when the same stored procedure name to uppercase and lowercase database names exists Fix: Conversion is not done. Result: mysql_upgrade does not fail any more.
Clone Of:
Environment:
Last Closed: 2019-08-06 13:18:16 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Product Errata RHSA-2019:2327 None None None 2019-08-06 13:18:44 UTC

Description Masahiro Matsuya 2017-09-11 13:11:31 UTC
Description of problem:

A customer saw the Duplicate entry error with mysql_upgrade command.
The error is output with the following SQL executed in mysql_upgrade command.

   ALTER TABLE proc CONVERT TO CHARACTER SET utf8;

This can be reproduced with the following SQL easily

------------------------------
use test
DELIMITER |

CREATE PROCEDURE getStudents()
BEGIN
 SELECT * FROM students;
END|

DELIMITER ;
create database TEST;
use TEST
DELIMITER |

CREATE PROCEDURE students_copy_proc (IN ref_note VARCHAR(255))
BEGIN
 REPLACE INTO students_backup
 SELECT *, ref_note FROM students;
END|

DELIMITER ;
use mysql
select db,name,type from proc;

ALTER TABLE proc CONVERT TO CHARACTER SET utf8;
--------------------------------------

# mysql -uroot < /tmp/repro.sql
db	name	type
TEST	getStudents	PROCEDURE
test	getStudents	PROCEDURE
ERROR 1062 (23000) at line 27: Duplicate entry 'TEST-getStudents-PROCEDURE' for key 'PRIMARY'

The same error is output even by mysql_upgrade command.


Version-Release number of selected component (if applicable):
Red Hat Enterprise Linux 7.4

How reproducible:
Always

Steps to Reproduce:
See above

Actual results:

ERROR 1062 (23000) at line 27: Duplicate entry 'TEST-getStudents-PROCEDURE' for key 'PRIMARY'

Expected results:

mysql_upgrade doesn't fail


Additional info:

Comment 1 Masahiro Matsuya 2017-09-11 13:14:18 UTC
Correction to the sql in C#0 to reproduce the problem.

# cat /tmp/repro.sql 

use test
DELIMITER |

CREATE PROCEDURE getStudents()
BEGIN
 SELECT * FROM students;
END|

DELIMITER ;
create database TEST;
use TEST
DELIMITER |

CREATE PROCEDURE getStudents()
BEGIN
 SELECT * FROM students;
END|

DELIMITER ;
use mysql
select db,name,type from proc;

ALTER TABLE proc CONVERT TO CHARACTER SET utf8;

Comment 2 Michal Schorm 2017-09-25 12:40:58 UTC
This issue is reproducible also in Fedora with all versions of MariaDB, including the latest released upstream version - 10.2.8 - in Rawhide

Comment 3 Michal Schorm 2017-09-26 12:50:04 UTC
I created a bug on upstream side, where I'm going to solve it primarly.
  https://jira.mariadb.org/browse/MDEV-13912

I'll inform you here, once I'll get any usefull patch or a workaround for the customer.

Comment 4 Michal Schorm 2017-09-26 14:29:54 UTC
Upstream knows about this issue. They recently released patches for 10.x versions.

Unfortunatelly they did not patch this issue in 5.x version and the patch for 10.x version is only partially applicable, which does not resolve the issue fully.

I'll cooperate with upstream do delive patch for the 5.x verison too.

Comment 6 Michal Schorm 2017-10-11 12:55:42 UTC
So far, upstream plans to fix this in next - 5.5.58 - version.

Comment 8 Michal Schorm 2018-01-03 22:22:58 UTC
The issue was still not fully fixed.
Even though 5.5.58 released, the issue still persist.

Comment 9 Michal Schorm 2018-05-10 13:07:59 UTC
The issue was still not fixed.
Even though 5.5.60 released, the issue still persist.

Comment 12 Michal Schorm 2018-10-30 14:35:59 UTC
The upstream claims the issue with mysql_upgrade is fixed in the 5.5.62 release.

I have to dig deeper inside it however, because the SQL reproducer from #c1 still apply.

Comment 13 Michal Schorm 2018-10-30 14:48:51 UTC
I confirm it fixed the issue with mysql_upgrade.

Solution: Rebase to 5.5.62

Comment 19 Michal Schorm 2019-05-27 11:02:25 UTC
You test the wrong thing. The issues is that *mysql_upgrade* utility fails.

The SQL command itself will fail both times - beforea nd after the fix.

But when you run 'mysql_upgrade' afterwards, it will produce:

-----------------------------------------
# BEFORE:

# 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
TEST
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
Phase 4/4: Running 'mysql_fix_privilege_tables'
ERROR 1062 (23000) at line 577: Duplicate entry 'TEST-getStudents-PROCEDURE' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

# echo $?
1

-----------------------------------------
# AFTER:

# 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
TEST
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
Phase 4/4: Running 'mysql_fix_privilege_tables'
OK

# echo $?
0

Comment 22 errata-xmlrpc 2019-08-06 13:18:16 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHSA-2019:2327


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