cache.py problems with mysql

[mrbrush]

MySQL considers the word KEY a reserved word, the cache engine creates exceptions when trac is run with MySQL because 'key' is used as a column identifier in the 'cache' table.

Solution: either quote the column name 'key' with ` (backticks), or rename the column to another name which is not a reserved word in MySQL.

[mrbrush]

Patch fixing the problem by quoting every usage of 'key' as a column identifier.

[mrbrush <20090408@…>]

I don't know whether other DB engines will handle the column identifiers quoted with backticks, so the patch might not be portable to other DB engines. Please check it before committing to SVN.

[Remy Blank]

Oh, great, this had to happen. There are so many reserved words in SQL… And MySQL enforces them all.

Christian, what is the best course of action here? A quick rename of the key column to id before anybody using trunk updates? Another database upgrade? Use the backticks (is this even standard SQL)?

[mrbrush <20090408@…>]

I've tried the patched version with the SQLite database and it works. I don't know about other databases though.

[Remy Blank]

Replying to rblank:

A quick rename of the key column to id before anybody using trunk updates?

…along with a procedure for fixing the breakage for those who had already upgraded:

• Change db_version back to 21
• Drop the cache table
• Upgrade again

[Christian Boos]

The backticks don't seem to work with PostgreSQL. This is highly annoying, if we create another db23.py then we will have again merge trouble in the MultiRepos branch.

So yes, I agree with your proposal, simply change key → id, and document how to recover manually, which can be a simple sql script (so that it will also work on the MultiRepos branch):

DROP TABLE cache;
CREATE TABLE cache ( id text PRIMARY KEY, generation int );

By the way, it looks like generation was text so far… another detail to fix in db22.py at the same time?

[Remy Blank]

Replying to cboos:

So yes, I agree with your proposal, simply change key → id, and document how to recover manually,

Ok, will do ASAP.

By the way, it looks like generation was text so far… another detail to fix in db22.py at the same time?

Oops, er, yes, right…

[Remy Blank]

Ok, so after a rename, an environment that has already been upgraded generates the following error:

OperationalError: no such column: id

I hope this is enough for people searching for that error to find this ticket. Patch coming up shortly.

[Remy Blank]

Both issues are fixed in [8079] and merged to the multirepos branch in [8081].

For people who get the error above, please follow the instructions in [8079] if you are using trunk, or those in [8081] if you are using the multirepos branch.

mrbrush: Thanks for the bug report!

[mrbrush <20090408@…>]

You are very welcome.

As a final note I would like to add that the recovery script does not work for MySQL as it cannot handle primary key columns with unlimited key length, as would come for the id column with column type TEXT. The error message is:

ERROR 1170 (42000): BLOB/TEXT column 'id' used in key specification without a key length

Instead, one can use the following script to do the recovery with MySQL:

DROP TABLE IF EXISTS cache;
CREATE TABLE cache ( id text, generation int, PRIMARY KEY (id(333)) );

[Remy Blank]

Thanks for the update. The MySQL connector seems to limit the key length for a signle column to 255 characters, so if the script above doesn't work, try:

DROP TABLE IF EXISTS cache;
CREATE TABLE cache ( id text, generation int, PRIMARY KEY ( id(255)) );