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
Triage: Triaged: D2 (Serious) / R2 (Low) / E3 (Medium)

[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.