"BIGINT value is out of range" error while upgrading from 0.11

[hagen.bauer@…]

trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`wiki`.`time` * 1000000)'")

[Ryan J Ollos]

Which version are you upgrading from, and which version are you upgrading to? Please confirm if the later is really branches/0.12-stable​.

[anonymous]

This are my steps

easy_install http://ftp.edgewall.com/pub/trac/Trac-0.12.5.tar.gz
tracd --version
tracd 0.12.5

trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`wiki`.`time` * 1000000)'")

I am not sure which version I am upgrading from. How can I check this?

[Ryan J Ollos]

Replying to anonymous:

I am not sure which version I am upgrading from. How can I check this?

If you look in your Python lib directory you might be able to find the version in the name of the egg file. On Linux, if you aren't using virtualenv, this might be something like: /usr/local/lib/python2.6/dist-packages/.

[anonymous]

this info?

 ls -l /usr/local/lib/python2.7/dist-packages 
....
drwxr-sr-x 4 root staff 4096 Sep 18 08:47 Trac-0.11.7-py2.7.egg
drwxr-sr-x 5 root staff 4096 Sep 20 09:56 Trac-0.12.5-py2.7.egg
drwxr-sr-x 5 root staff 4096 Sep 18 08:54 Trac-0.12-py2.7.egg
drwxr-sr-x 5 root staff 4096 Sep 18 08:39 Trac-1.0-py2.7.egg
.....

[Ryan J Ollos]

It looks like you could be upgrading from either Trac 0.11.7 or 0.12. Take note of Trac-1.0-py2.7.egg. What does trac-admin --version report? You probably want to try removing Trac-1.0-py2.7.egg.

[anonymous]

so I removed some versions. But no luck

ls /usr/local/lib/python2.7/dist-packages/ -l

drwxr-sr-x 4 root staff 4096 Sep 18 08:47 Trac-0.11.7-py2.7.egg

drwxr-sr-x 5 root staff 4096 Sep 20 09:56 Trac-0.12.5-py2.7.egg

trac-admin --version

trac-admin 0.12.5

trac-admin /opt/caserio/trac/caserio upgrade

OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`wiki`.`time` * 1000000)'")

[Ryan J Ollos]

I wonder if you database might have been upgraded to a newer version. db_version for 0.12.5 should be 26​. What database are you running? If you are running sqlite3,

1. Install sqlite3. On a Debian-like system, sudo apt-get install sqlite3
2. $ sqlite3 $env
3. sqlite3> SELECT * FROM system

[anonymous]

I am using mysql

select * from system

    -> ;

+--------------------------+-------+

| name                     | value |

+--------------------------+-------+

| database_version         | 24    |

| initial_database_version | 21    |

+--------------------------+-------+

[Ryan J Ollos]

Trac 0.11.7 is db_version 21​. Trac 0.12 is db_version 26​. So it seems like you were running 0.11.7 initially, and several of the upgrade steps succeeded, but db25.py​ did not.

What data is in row 1690?

[anonymous]

sorry. row 1690 of what? row in a database table? or python script?

I am confused

[Ryan J Ollos]

I'm not certain, but it looks like the 1690 shown in comment:description might be a row in the wiki table.

[anonymous]

I dont think so, the wiki contaisn only 312 rows

[Jun Omae]

The 1690 is a MySQL Error code, ​Error: 1690 SQLSTATE: 22003 (ER_DATA_OUT_OF_RANGE).

Please make sure the engine of wiki table is NOT MyISAM. Trac doesn't support MyISAM. If your env has MyISAM tables, restore the backup and convert the tables to InnoDB.

If the upgrade still fails, your wiki table has the corrupted timestamps in the time column.

select count(*) from wiki where time not between 2147483647 and -2147483648;
select time from wiki where time not between 2147483647 and -2147483648 limit 10;

[anonymous]

I converteda all table to InnoDB

⇒ no luck

obviously all my timestamps are corrupted

mysql> select time from wiki where time not between 2147483647 and -2147483648  ;

+------------------+

312 rows in set (0.00 sec)

select time from wiki where time not between 2147483647 and -2147483648  and mod(time, 1000000) != 0 limit 10;

Empty set (0.00 sec)

[Ryan J Ollos]

Replying to anonymous:

obviously all my timestamps are corrupted

I don't suppose you have a backup? In either case, please make a backup now before trying anything else.

I wonder if the timestamps were multiplied by 1e6 before the upgrade step failed, and if we therefore need to divide the column by 1e6 before trying to get db25.py to successfully execute.

[anonymous]

actually i dont care for the exact timestamps. I need the information that are stored in the wiki.

Would it be possible to just insert a valid timestamp from today?

How would a timestamp of today 8am look like?

[Ryan J Ollos]

Replying to anonymous:

I converteda all table to InnoDB

I would like to try to summarize what happened here. If I was to extrapolate on the information you and Jun provided:

• You had a Trac 0.11.7 instance running with MySQL and the MyISAM engine.
• You upgraded to Trac 0.12.5.
• During the upgrade, db25.py didn't succeed, presumably because of the use of the unsupported MyISAM engine. It appears though, that the time columns, at least for the wiki table, were multiplied by 1e6 before the upgrade step failed.

I'm just wondering if it is possible to raise an error at some point when the MyISAM engine is used, or if there is anything else we can do to make Trac more robust. This is not an area where I have much knowledge. Jun, what do you think?

[Ryan J Ollos]

Replying to anonymous:

How would a timestamp of today 8am look like?

>>> from datetime import datetime
>>> from trac.util.datefmt import to_timestamp, to_utimestamp, utc
>>> now = datetime.now(utc)
>>> print to_utimestamp(now)
1379839952245343
>>> print to_timestamp(now)
1379840041

I think you'll want to use the to_timestamp value.

[Jun Omae]

Replying to rjollos:

I'm just wondering if it is possible to raise an error at some point when the MyISAM engine is used, or if there is anything else we can do to make Trac more robust. This is not an area where I have much knowledge. Jun, what do you think?

Good idea. I noticed that the idea has been filed in #10993.

[anonymous]

I am running into a loop now.

I am updating the time value which cause the error in different tables with this command

> update wiki set time =1379840041;
Query OK, 312 rows affected (0.08 sec)
Rows matched: 312  Changed: 312  Warnings: 0

Then I am running the update command which shows me a different table with the same error. After 3 or 4 iterations I am back to the wiki table

# trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`revision`.`time` * 1000000)'")
# trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`wiki`.`time` * 1000000)'")
# trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`ticket`.`time` * 1000000)'")
# trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`wiki`.`time` * 1000000)'")
# trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`revision`.`time` * 1000000)'")
# trac-admin /opt/caserio/trac/caserio upgrade
OperationalError: (1690, "BIGINT value is out of range in '(`trac`.`wiki`.`time` * 1000000)'")

[Jun Omae]

Strange. Try again with the backup of your environment which has been created before the upgrade.

1. Restore the backup
2. Change default-storage-engine to InnoDB in your /etc/my.cnf and restart mysqld

   [mysqld]
   default-storage-engine = InnoDB
   

   sudo /etc/init.d/mysql restart
   

3. Convert all tables to InnoDB

   ALTER TABLE `system` ENGINE=InnoDB;
   ALTER TABLE `permission` ENGINE=InnoDB;
   ALTER TABLE `auth_cookie` ENGINE=InnoDB;
   ALTER TABLE `session` ENGINE=InnoDB;
   ALTER TABLE `session_attribute` ENGINE=InnoDB;
   ALTER TABLE `attachment` ENGINE=InnoDB;
   ALTER TABLE `wiki` ENGINE=InnoDB;
   ALTER TABLE `revision` ENGINE=InnoDB;
   ALTER TABLE `node_change` ENGINE=InnoDB;
   ALTER TABLE `ticket` ENGINE=InnoDB;
   ALTER TABLE `ticket_change` ENGINE=InnoDB;
   ALTER TABLE `ticket_custom` ENGINE=InnoDB;
   ALTER TABLE `enum` ENGINE=InnoDB;
   ALTER TABLE `component` ENGINE=InnoDB;
   ALTER TABLE `milestone` ENGINE=InnoDB;
   ALTER TABLE `version` ENGINE=InnoDB;
   ALTER TABLE `report` ENGINE=InnoDB;
   

4. Run trac-admin /path/to/env upgrade

[Peter Suter]

Replying to jomae:

Replying to rjollos:

I'm just wondering if it is possible to raise an error at some point when the MyISAM engine is used, or if there is anything else we can do to make Trac more robust. This is not an area where I have much knowledge. Jun, what do you think?

Good idea. I noticed that the idea has been filed in #10993.

In #10661 we also discussed this a bit. (And #8089 also mentions this and other MySQL requirement checks.)

[Jun Omae]

Good idea. I noticed that the idea has been filed in #10993.

In #10661 we also discussed this a bit. (And #8089 also mentions this and other MySQL requirement checks.)

I've created proposed changes in log:jomae.git:ticket10993, see comment:2:ticket:10993.

[Ryan J Ollos]

Replying to jomae:

Good idea. I noticed that the idea has been filed in #10993.

So I guess we can close this as a duplicate of #10993, but we are just waiting to see if the issue is resolved by the reporter.

hagen.bauer: Have you had any success resolving your issue?

[anonymous]

hi, no since I dont have a recent backup my data seem to be lost :-(

[Ryan J Ollos]

I just tested upgrading from 0.11.7 to 0.12.5 in an environment with SQLite, though as far as I know it should work the same with MySQL. A backup of the database was created in $env/db: sqlite.21.1382733182.bak. Do you have a database backup file if your environment's db directory?

[Ryan J Ollos]

If you don't find the backup mentioned in comment:27, do you at least have a backup from right after the upgrade failed? I'm afraid this could get very complicated if you've been hacking at the database trying to fix it, so the most straightforward thing we can do at this point is to start with a backup from whatever the database looked like right after the upgrade failed.

If we were to start right after the upgrade failed, looking at db25.py​ we can see there are 7 tables that have columns that get multiplied by 1e6, a total of 9 columns. The first thing I would want to know is whether db25.py was able to alter all of the columns. We can determine this by running queries like Jun showed in comment:13.

If all of the columns were altered, then we can set system.db_version to 25, and run the upgrade again. If not all of the columns were altered, then we could remove the entries from the tables list in db_25.py that have been altered, and run the upgrade again.

Please don't do anything else though without making a backup. It can only get worse from here if you don't!

$ trac-admin <env> help hotcopy
hotcopy <backupdir> [--no-database]

    Make a hot backup copy of an environment

    The database is backed up to the 'db' directory of the destination, unless
    the --no-database option is specified.

[Remy Blank]

IIRC we implemented the backup on upgrade for MySQL and PostgreSQL some time in 0.12.x, so no, he probably doesn't have a backup. But the upgrade instructions did mention that you had to backup by hand for these DB backends, so we're not the only ones to blame.

But I do hope that you made a backup before starting to fiddle with timestamps? If so, you should restore that one, then analyze each column containing timestamps and check if the timestamps are in seconds or in microseconds. In the latter case, you should divide them by 1e6. Only when you have reverted all timestamps to seconds should you run the upgrade again (or alternatively, change all timestamps to microseconds and set the DB version to 25).

[Ryan J Ollos]

Duplicate of #10993.

[oyvindsk]

Ops, that sql is wrong!!

The order of the timestamps is reversed. It should be something like:

select count(*) from wiki where time not between  -2147483648 AND 2147483647;