Edgewall Software
Modify

Opened 9 years ago

Closed 8 years ago

Last modified 7 years ago

#10661 closed defect (duplicate)

0.11.7 -> 0.12.3 upgrade with MySQL backend wedged

Reported by: anatoly techtonik <techtonik@…> Owned by:
Priority: normal Milestone:
Component: database backend Version: 0.12.3
Severity: critical Keywords: mysql
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:

Description

I've tried to upgrade Trac instance from 0.11.7 to 0.12.3 with MySQL backend and it wedged. How can I fix that?

~/inst/bin/trac-admin /home/trex/fartrac upgrade
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA2\xD0\xB8\xD0\xBF...' for column 'message' at row 2
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA2\xD0\xB8\xD0\xBF...' for column 'message' at row 113
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD1\x88 \xD1\x91\xD1...' for column 'message' at row 224
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xEF\x8B\xA5\xEF\x87\xB2' for column 'message' at row 335
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBE\xD1\x87\xD0\xB5...' for column 'message' at row 391
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBD\xD0\xB5 \xD1...' for column 'message' at row 402
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA0\xD0\x95\xD0\xAF...' for column 'message' at row 446
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBE\xD0\xBF\xD0\xB5...' for column 'message' at row 535
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD1\x80\xD1\x83\xD1\x81...' for column 'message' at row 557
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD1\x83\xD0\xB6\xD0\xB5...' for column 'message' at row 1094
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xB8\xD0\xB7\xD0\xBC...' for column 'message' at row 1138
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBF\xD1\x80\xD0\xB8...' for column 'message' at row 1259
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x97\xD0\xB4\xD0\xB5...' for column 'message' at row 2280
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x98\xD1\x81\xD0\xBF...' for column 'message' at row 2291
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA1\xD0\xBB\xD0\xB5...' for column 'message' at row 3247
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBE\xD0\xBF\xD0\xB5...' for column 'message' at row 3429
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBE\xD0\xBF\xD0\xB5...' for column 'message' at row 3557
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x92\xD0\xBE\xD0\xB7...' for column 'message' at row 3752
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x92\xD0\xBE\xD0\xB7...' for column 'message' at row 3754
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9D\xD0\xB5 \xD1...' for column 'message' at row 3763
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9A\xD0\xBD\xD0\xBE...' for column 'message' at row 3767
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9D\xD0\xB5\xD1\x82...' for column 'message' at row 3770
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBF\xD0\xB5\xD1\x80...' for column 'message' at row 3779
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xB2 \xD0\xBE\xD0...' for column 'message' at row 3783
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9E\xD1\x88\xD0\xB8...' for column 'message' at row 3785
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD1\x80\xD0\xB8...' for column 'message' at row 3809
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x97\xD0\xB0\xD0\xBF...' for column 'message' at row 3820
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xBE\xD0\xB8...' for column 'message' at row 3840
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x93\xD0\xBB\xD1\x8E...' for column 'message' at row 3842
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xB2\xD0\xBE\xD0\xB7...' for column 'message' at row 3849
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xB0\xD1\x80...' for column 'message' at row 3853
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBD\xD0\xB5\xD0\xBF...' for column 'message' at row 3855
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xB7\xD0\xB0\xD0\xB4...' for column 'message' at row 3871
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x98\xD1\x81\xD0\xBF...' for column 'message' at row 3878
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA0\xD0\xB2\xD1\x91...' for column 'message' at row 3885
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9D\xD0\xB5\xD0\xBA...' for column 'message' at row 3890
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xBE\xD0\xB7...' for column 'message' at row 3942
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xB0\xD0\xB4...' for column 'message' at row 3964
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9E\xD1\x88\xD0\xB8...' for column 'message' at row 3970
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x98\xD0\xBD\xD0\xB4...' for column 'message' at row 4004
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xB0\xD0\xB4...' for column 'message' at row 4008
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xB0\xD0\xB4...' for column 'message' at row 4009
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA4\xD0\xB0\xD1\x80...' for column 'message' at row 4016
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x92\xD1\x8B\xD0\xB2...' for column 'message' at row 4034
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x81' in...' for column 'message' at row 4039
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xB0\xD0\xB4...' for column 'message' at row 4040
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9E\xD1\x82\xD1\x81...' for column 'message' at row 4047
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9A\xD0\xB0\xD0\xB2...' for column 'message' at row 4048
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x81" in...' for column 'message' at row 4060
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9E\xD1\x82\xD1\x81...' for column 'message' at row 4070
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x94\xD0\xB5\xD1\x84...' for column 'message' at row 4077
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9D\xD0\xB5 \xD1...' for column 'message' at row 4083
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9D\xD0\xB5 \xD1...' for column 'message' at row 4084
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBE\xD1\x88\xD0\xB8...' for column 'message' at row 4118
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xA0\xD0\xB5\xD0\xB3...' for column 'message' at row 4134
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x81" in...' for column 'message' at row 4155
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9F\xD0\xBE\xD1\x81...' for column 'message' at row 4196
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD1\x83\xD1\x81\xD1\x82...' for column 'message' at row 4230
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xB2 unr...' for column 'message' at row 4235
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD1\x81\xD1\x81\xD1\x8B...' for column 'message' at row 4246
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBF\xD1\x80\xD0\xBE...' for column 'message' at row 4249
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\x9D\xD0\xB5\xD0\xBA...' for column 'message' at row 4265
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBF\xD1\x80\xD0\xB8...' for column 'message' at row 4276
  return self.cursor.execute(sql)
/home/trex/inst/lib/python2.6/site-packages/trac/db/util.py:66: Warning: Incorrect string value: '\xD0\xBF\xD1\x80\xD0\xBE...' for column 'message' at row 4278
  return self.cursor.execute(sql)
IntegrityError: (1062, "Duplicate entry '4116-trunk/plugins/newarc.ex/Framework/Include/FarLng.hpp-E' for key 'PRIMARY'")
$
$ ~/inst/bin/trac-admin /home/trex/fartrac upgrade
OperationalError: (1050, "Table 'repository' already exists")

It still says Run "trac-admin /home/trex/fartrac upgrade" on the site page.

Attachments (1)

T10661 MySQL InnoDB.patch (978 bytes ) - added by Peter Suter 9 years ago.
Explicitly create InnoDB tables in MySQL backend

Download all attachments as: .zip

Change History (13)

comment:1 by Remy Blank, 9 years ago

Anatoly, you should know that we don't use tickets here for InstallationIssues. You will also be much more likely to find someone to help you on the MailingList, as we only have very little real-world experience with MySQL. Then again, you may have hit a real issue in Trac, I'm not sure.

But at least I can explain what happened. The warnings look like character set and collation issues, where data copied from the source (temporary) table and written to the table with the new schema doesn't fit the encoding set for that table. The duplicate entry error is most likely due to having the same path twice in a single changeset, with different case (upper- and lowercase). This again points to a bad column or table collation specification (more precisely, a case-insensitive one, where it should be case-sensitive).

You should now have two tables of revisions, the old one named "rev_old" and the new one named "revision". Can you please check that the character set and collation is identical for both, and set to "utf8" and "utf8_bin", respectively? This should match the values that you set for the database.

The error leaves the database in an inconsistent state, because schema mutations aren't transactional. There's not much we can do about this, unfortunately. Your best bet is to restore from a backup (you do have a backup, don't you?), ensure that your database and tables have the right settings, and try again.

comment:2 by anatoly techtonik <techtonik@…>, 9 years ago

I am aware it's not a support tracker, but this one is not an InstallationIssue, but the one caused by upgrade. I upgraded from one stable Trac version to other stable version and it failed. So, the outcome of this ticket should be additional tests for MySQL backend run prior to DB migrations that don't allow migrations to continue (and leave Trac environment in wedged state) unless all collation or whatever requirements are satisfied.

And no, I don't have DB backup. trac-admin . hotcopy was not available, so I've skipped this step. The data doesn't seem critical too me. I am troubleshooting the issue at the moment and will update MySqlDb as I go. Stay tuned. We definitely need to revisit #8089.

comment:3 by anatoly techtonik <techtonik@…>, 9 years ago

I see that cache and repository tables are created with MyISAM table types after running:

SELECT table_schema, table_name, engine FROM information_schema.tables
    WHERE table_schema='tracdb';

It makes sense to enforce table type to InnoDB upon creation.

by Peter Suter, 9 years ago

Attachment: T10661 MySQL InnoDB.patch added

Explicitly create InnoDB tables in MySQL backend

in reply to:  3 comment:4 by Peter Suter, 9 years ago

Replying to anatoly techtonik <techtonik@…>:

It makes sense to enforce table type to InnoDB upon creation.

The above attached patch explicitly specifies to use InnoDB. According to the documentation this is redundant since MySQL 5.5, but shouldn't hurt.

I only tested creating a new environment still works with Trac trunk, Python 2.5 MySQL 5.5, and MySQLdb Python bindings 1.2.2.

comment:5 by Christian Boos, 9 years ago

Two questions:

  • is there a way to specify the default engine at the db level? I suppose temporary tables should be InnoDB as well, to avoid similar problems to the one reported here
  • I suppose InnoDB is supported in MariaDB as well (or at least they map that type to one of their type, XtraDB?), but I don't see it in the list of engines; I also don't know if it's always there or optional

comment:6 by Peter Suter, 9 years ago

According to MySQL documentation:

You can set the default storage engine for the current session by setting the default_storage_engine variable: SET default_storage_engine=NDBCLUSTER; As of MySQL 5.6.3, the storage engine for TEMPORARY tables created with CREATE TEMPORARY TABLE can be set separately from the engine for permanent tables by setting the default_tmp_storage_engine, either at startup or at runtime. Before MySQL 5.6.3, default_storage_engine sets the engine for both permanent and TEMPORARY tables.

If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table using the default storage engine.

By default, a warning is generated whenever CREATE TABLE or ALTER TABLE cannot use the default storage engine. To prevent confusing, unintended behavior if the desired engine is unavailable, enable the NO_ENGINE_SUBSTITUTION SQL mode. If the desired engine is unavailable, this setting produces an error instead of a warning, and the table is not created or altered.

So I think we want all of these:

SET sql_mode='NO_ENGINE_SUBSTITUTION';
SET default_storage_engine=INNODB;
SET default_tmp_storage_engine=INNODB;

MariaDB: The XtraDB page you linked says:

It is fully backwards compatible, and it identifies itself to MariaDB as "ENGINE=InnoDB"

so I assume this would work the same way there.

comment:7 by anatoly techtonik <techtonik@…>, 9 years ago

Update. Default DB encoding was not UTF8. Can we set these as well? It may be the reason of failure if there were any temporary tables created. I will try check columns collations in existing tables later.

SHOW VARIABLES WHERE variable_name IN ('character_set_database', 'collation_database'); 
+------------------------+-------------------+
| Variable_name          | Value             |
+------------------------+-------------------+
| character_set_database | latin1            |
| collation_database     | latin1_swedish_ci |
+------------------------+-------------------+

in reply to:  6 comment:8 by Christian Boos, 9 years ago

Replying to psuter:

MariaDB: The XtraDB page you linked says:

It is fully backwards compatible, and it identifies itself to MariaDB as "ENGINE=InnoDB"

so I assume this would work the same way there.

Right, but I was not sure if that engine would be always available (plugin or not?). It seems it is, though.

But reading again MySqlDb, I see we already mandate the use of either InnoDB or NDB, as those were the two valid choices for MySQL. The "anything but MyISAM" rule gives even a much larger choice on MariaDB, given the list I linked to (well, CSV probably won't do either ;-) ).

So what if someone wants to use NDB with MySQL or PBXT with MariaDB? Maybe the better approach would be to do a "safety check" (creating a (temp) table, verify the encoding; try to insert a few rows in a transaction (with upper/lower case conflicts); rollback and check if all rows are gone).

comment:9 by anatoly techtonik <techtonik@…>, 9 years ago

I've came back to this Trac instance, updated virtualenv to version 0.12.4, restored DB from an old backup, and now I am getting:

$ ~/inst/bin/trac-admin /home/trex/fartrac upgrade
OperationalError: (1050, "Table 'cache' already exists")

comment:10 by anatoly techtonik <techtonik@…>, 9 years ago

The issue is rather critical - I can see that SF users experience it as well. https://sourceforge.net/p/forge/community-docs/Migrating%20Trac%20from%20Hosted%20Apps/

If the fix for checking and fixing table collations already in 1.0+, it should be backported to LTS.

comment:11 by Christian Boos, 9 years ago

Milestone: unscheduled

comment:12 by Peter Suter, 8 years ago

Milestone: unscheduled
Resolution: duplicate
Status: newclosed

Superseded by #10993.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.