Bug #33094 Error in upgrading from 5.0 to 5.1 when table contains triggers

Submitted: 8 Dec 2007 23:19 Modified: 29 Jan 2009 4:29
Reporter: Christian Winther Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S1 (Critical)
Version:5.1.22 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: trigger, upgrade

[8 Dec 2007 23:19] Christian Winther 
Description:
I think I have found a bug. After upgradeing from 5.0 to 5.1 rc some of my databases got prefixed with #mysql50# - after some googling I found that you could run the command: mysqlcheck --check-upgrade --fix-db-names --fix-table-names --all-databases

Most of my tables to named correctly after this, however, all databases containing triggers cannot be auto renamed.

I get this error: Error: Trigger in wrong schema

5.1.22 RC (FreeBSD) but should aplly for everyone

How to repeat:
setup a table on 5.0
add a trigger on the table
upgrade mysql to 5.1.22 RC
run "mysql_upgrade"
run "mysqlcheck --check-upgrade --fix-db-names --fix-table-names --all-databases"

tail $host.err.log
[9 Dec 2007 7:09] Sveta Smirnova 
Thank you for the report.

Verified as described.
[9 Dec 2007 7:10] Sveta Smirnova 
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; doesn't help
[8 Oct 2008 10:32] Konstantin Osipov 
A bug in tablename-to-filename encoding.
[8 Dec 2008 15:59] Mark Leith 
As a work around:

o mysqldump --no-data --no-create-info --triggers (i.e. just dump your triggers)
o Drop all of your current triggers in 5.0 with DROP TRIGGER
o Upgrade to 5.1, run mysql_upgrade etc.
o Import all of your triggers
[11 Dec 2008 2:18] Omer Barnir 
Also see bug#41385
[16 Dec 2008 18:21] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61808

2737 Ramil Kalimullin	2008-12-16
      Fix for
      bug#33094: Error in upgrading from 5.0 to 5.1 when table contains triggers
      and
      #41385: Crash when attempting to repair a #mysql50# upgraded table with
      triggers.
      
      Problem:
      1. trigger code didn't assume a table name may have
      a "#mysql50#" prefix, that may lead to a failing ASSERT().
      2. mysqlcheck --fix-table-name doesn't use UTF8 as a default
      character set that results in (parsing) errors for tables with
      non-latin symbols in their names.
      
      Fix:
      1. properly handle table names with "#mysql50#" prefix.
      2. handle --default-character-set mysqlcheck option;
      if mysqlcheck is launched with --fix-table-name or --fix-db-name
      set default character set to UTF8.
      
      Note: if given --fix-table-name or --fix-db-name option,
      --default-character-set mysqlcheck option is ignored and
      default character set is UTF8.
      
      There's no test case from the bug report as it may fail on 
      non-utf8 filesystems.
[19 Dec 2008 9:12] Alexander Barkov 
Ramil,

Using of CYRILLIC LETTER SHORT I is dangerous.

CREATE TABLE `#mysql50#й`  bypasses tablename-to-filename
encoding and therefore will try to create the file "й.frm",
which will fail on Asian Windows machines, and will
fail on Mac OSX.

- To test that upgrade works please use `#mysql50#@` as table name.

- I have no good idea how to test "mysqlcheck --default-character-set=utf8 --databases test".
[19 Dec 2008 10:46] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62069

2765 Ramil Kalimullin	2008-12-19
      Fix for
      bug#33094: Error in upgrading from 5.0 to 5.1 when table contains
      triggers
      and
      #41385: Crash when attempting to repair a #mysql50# upgraded table
      #with
      triggers.
      
      Problem:
      1. trigger code didn't assume a table name may have
      a "#mysql50#" prefix, that may lead to a failing ASSERT().
      2. mysqlcheck --fix-table-name doesn't use UTF8 as a default
      character set that results in (parsing) errors for tables with
      non-latin symbols in their names.
      
      Fix:
      1. properly handle table names with "#mysql50#" prefix.
      2. handle --default-character-set mysqlcheck option;
      if mysqlcheck is launched with --fix-table-name or --fix-db-name
      set default character set to UTF8 if no --default-character-set 
      option given.
      
      Note: if given --fix-table-name or --fix-db-name option,
      without --default-character-set mysqlcheck option
      default character set is UTF8.
      
      There's no test case from the bug report as it may fail on
      some filesystems.
[19 Dec 2008 11:32] Alexander Barkov 
The patch looks fine for me.
[13 Jan 2009 8:38] Dmitry Lenev 
Review for this patch was sent by an e-mail. IMO some changes required to fix the problem originally described in this report.
[13 Jan 2009 20:10] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63171

2737 Ramil Kalimullin	2009-01-14
      Fix for
      bug#33094: Error in upgrading from 5.0 to 5.1 when table contains
      triggers
      and
      #41385: Crash when attempting to repair a #mysql50# upgraded table
      #with
      triggers.
      
      Problem:
      1. trigger code didn't assume a table name may have
      a "#mysql50#" prefix, that may lead to a failing ASSERT().
      2. "ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME" failed
      for databases with "#mysql50#" prefix if any trigger.
      3. mysqlcheck --fix-table-name didn't use UTF8 as a default
      character set that resulted in (parsing) errors for tables with
      non-latin symbols in their names and definitions of triggers.
      
      Fix:
      1. properly handle table/database names with "#mysql50#" prefix.
      2. handle --default-character-set mysqlcheck option;
      if mysqlcheck is launched with --fix-table-name or --fix-db-name
      set default character set to UTF8 if no --default-character-set
      option given.
      
      Note: if given --fix-table-name or --fix-db-name option,
      without --default-character-set mysqlcheck option
      default character set is UTF8.
[14 Jan 2009 15:43] Dmitry Lenev 
Approved version of patch which was later pushed into mysql-5.1-bugteam after discussion on IRC.
[14 Jan 2009 17:39] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63226

2749 Ramil Kalimullin	2009-01-14
      Fix for
      bug#33094: Error in upgrading from 5.0 to 5.1 when table contains
      triggers
      and
      #41385: Crash when attempting to repair a #mysql50# upgraded table
      with triggers.
      
      Problem:
      1. trigger code didn't assume a table name may have
      a "#mysql50#" prefix, that may lead to a failing ASSERT().
      2. "ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME" failed
      for databases with "#mysql50#" prefix if any trigger.
      3. mysqlcheck --fix-table-name didn't use UTF8 as a default
      character set that resulted in (parsing) errors for tables with
      non-latin symbols in their names and definitions of triggers.
      
      Fix:
      1. properly handle table/database names with "#mysql50#" prefix.
      2. handle --default-character-set mysqlcheck option;
      if mysqlcheck is launched with --fix-table-name or --fix-db-name
      set default character set to UTF8 if no --default-character-set
      option given.
      
      Note: if given --fix-table-name or --fix-db-name option,
      without --default-character-set mysqlcheck option
      default character set is UTF8.
[14 Jan 2009 22:19] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63269

2733 Ramil Kalimullin	2009-01-15
      bug#33094: Error in upgrading from 5.0 to 5.1 when table contains triggers
      
      Post-fix test failure: fixed mysqlcheck.test on Windows platforms.
[14 Jan 2009 22:19] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63270

2733 Ramil Kalimullin	2009-01-15
      bug#33094: Error in upgrading from 5.0 to 5.1 when table contains triggers
      
      Post-fix test failure: fixed mysqlcheck.test on Windows platforms.
[15 Jan 2009 6:24] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63317

2733 He Zhenxing	2009-01-15 [merge]
      auto merge
[15 Jan 2009 6:37] Bugs System 
Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:ramil@mysql.com-20090114205425-69dru3s5f19t82i3) (merge vers: 5.1.31) (pib:6)
[19 Jan 2009 11:23] Bugs System 
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:01] Bugs System 
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:07] Bugs System 
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[20 Jan 2009 18:54] Bugs System 
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:timothy.smith@sun.com-20090116165151-xtp5e4z6qsmxyvy0) (merge vers: 6.0.10-alpha) (pib:6)
[29 Jan 2009 4:29] Paul DuBois 
Noted in 5.1.31, 6.0.10 changelogs.

Three conditions were discovered that could cause an upgrade from
MySQL 5.0 to 5.1 to fail: 1) Triggers associated with a table that
had a #mysql50# prefix in the name could cause assertion failure. 2)
ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME failed for databases
that had a #mysql50# prefix if there were triggers in the database.
3) mysqlcheck --fix-table-name didn't use UTF8 as the default
character set, resulting in parsing errors for tables with non-latin
symbols in their names and trigger definitions.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.