#10661 closed defect (duplicate)
0.11.7 -> 0.12.3 upgrade with MySQL backend wedged
| Reported by: | 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)
Change History (13)
comment:1 by , 12 years ago
comment:2 by , 12 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.
follow-up: 4 comment:3 by , 12 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 , 12 years ago
| Attachment: | T10661 MySQL InnoDB.patch added |
|---|
Explicitly create InnoDB tables in MySQL backend
comment:4 by , 12 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 , 12 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
follow-up: 8 comment:6 by , 12 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 , 12 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 |
+------------------------+-------------------+
comment:8 by , 12 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 , 11 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 , 11 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:12 by , 11 years ago
| Milestone: | unscheduled |
|---|---|
| Resolution: | → duplicate |
| Status: | new → closed |
Superseded by #10993.
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.