MySQL Bugs: #45194: mysql_upgrade deletes existing data in performance

Bug #45194	mysql_upgrade deletes existing data in performance_schema database/schema
Submitted:	29 May 2009 13:11	Modified:	17 Jun 2010 1:23
Reporter:	Jørgen Austvik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	6.0-perfschema	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
WL2360: "If there is already a user-created database named PERFORMANCE_SCHEMA,
then mysql_upgrade will fail."

...but it doesn't.

How to repeat:
1. Create a test for the upgrade/downgrade framework that adds a performance_Schema.t1 table
2. Run upgrade/downgrade testing with --pause
3. See that the p_s.t1 table exists with data before mysql_upgrade is run
4. See that the p_s.t1 table does not exists (but COND_INSTANCES etc) after mysql_upgrade is run

Also:

1. Start MySQL 5.1
2. Run:
   CREATE SCHEMA IF NOT EXISTS performance_schema;
   CREATE TABLE performance_schema.t1 (i INT);
   INSERT INTO performance_schema.t1 (i) VALUES (1), (2), (3);
3. Run:
mysql-6.0-perfschema/client/mysqlcheck --no-defaults --check-upgrade -S /tmp/systest2/var/master/tmp/master.sock -u root -e -A
(nothing happens)

Suggested fix:
Make mysqlcheck fail if a database/schema called performance_schema exists.

Thank you for the report.

Verified as described: mysql_upgrade completely removes data, mysqlcheck does not report problem.

Regarding to information_schema - stalled schema just shadowed by new information_schema. All data is in place:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
+--------------------+
5 rows in set (0.00 sec)

Just some additional information: If you do live upgrade, you do not get any warnings; your data just disappears. If you do dump/restore; you will get a warning stating that root does not have write access to performance_schema, but the restore will go through and the performance_schema.t1 table will be missing after restore.

Waiting for

Bug#49542 Select from Information_schema.tables fails when a database does not exist

to be fixed first, to resolve this issue.

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/102448

3122 Marc Alff	2010-03-05
      Bug#45194 mysql_upgrade deletes existing data in performance_schema database/schema
      
      Before this fix, mysql_upgrade would always drop and re create
      the performance_schema database.
      This in theory could destroy user data created using 5.1 or older versions.
      
      With this fix, mysql_upgrade checks the content of the
      performance_schema database before droping it.

Queued in:
- mysql-next-mr-bugfixing (5.6.99-m4)
- mysql-6.0-codebase-bugfixing

This change exposed a limitation on the size of the upgrade script,
causing a build break in 6.0-codebase-bugfixing:

See related bug#51828

This fix has been rolled back in 6.0-codebase-bugfixing.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100310133305-0jdlngbtrkoqzckh) (version source revid:alik@sun.com-20100310132404-uqarl0o0vlra2kjx) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100310132634-zpyjzn346sgrm59u) (version source revid:alik@sun.com-20100310132634-zpyjzn346sgrm59u) (merge vers: 5.5.3-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100310132718-l0tegumhbg8umgjd) (version source revid:alik@sun.com-20100310132252-kpi29r22kjtl493x) (pib:16)

Doc team:
- fix merged into 5.5.3, first P_S release, nothing to document
- *not* fixed in 6.0

Not in any released version. No changelog entry needed.

Setting report to Need Merge pending push to 6.0.x

To port to 6.0, implementing WL#5291 is a pre requisite.
Otherwise, build breaks like Bug#51828 will appear again.

Status update:

The report was in "need merge" status (see comments from March 10, 2010),
while waiting to merge the fix into the 6.0 code base.
This was due to a specific issue found in 6.0-codebase, but not in mysql-next-mr.

Now that the 6.0-codebase internal tree has been abandoned, 
there is nothing left to merge.

Changing to documenting so the doc team can review history.

Given that there is nothing to document (quoting previous comments)
"Not in any released version. No changelog entry needed.",
I suggest the doc team can close directly.

No changelog entry needed.