Edgewall Software
Modify

Opened 6 years ago

Closed 6 years ago

Last modified 5 years ago

#11307 closed defect (duplicate)

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

Reported by: hagen.bauer@… Owned by:
Priority: high Milestone:
Component: admin/console Version: 0.12.5
Severity: critical Keywords: upgrade mysql needinfo
Cc: Ryan J Ollos Branch:
Release Notes:
API Changes:

Description (last modified by Ryan J Ollos)

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

Attachments (0)

Change History (32)

comment:1 by Ryan J Ollos, 6 years ago

Description: modified (diff)

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

comment:2 by anonymous, 6 years ago

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?

Last edited 6 years ago by Ryan J Ollos (previous) (diff)

in reply to:  2 comment:3 by Ryan J Ollos, 6 years ago

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

comment:4 by anonymous, 6 years ago

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
.....
Last edited 6 years ago by Ryan J Ollos (previous) (diff)

comment:5 by Ryan J Ollos, 6 years ago

Cc: Ryan J Ollos added
Version: 0.12-stable0.12.5

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.

comment:6 by anonymous, 6 years ago

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)'")
Last edited 6 years ago by Ryan J Ollos (previous) (diff)

comment:7 by Ryan J Ollos, 6 years ago

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

comment:8 by anonymous, 6 years ago

I am using mysql

select * from system

    -> ;

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

| name                     | value |

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

| database_version         | 24    |

| initial_database_version | 21    |

+--------------------------+-------+
Last edited 6 years ago by Ryan J Ollos (previous) (diff)

comment:9 by Ryan J Ollos, 6 years ago

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?

comment:10 by anonymous, 6 years ago

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

I am confused

comment:11 by Ryan J Ollos, 6 years ago

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

comment:12 by anonymous, 6 years ago

I dont think so, the wiki contaisn only 312 rows

comment:13 by Jun Omae, 6 years ago

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;
Last edited 6 years ago by Jun Omae (previous) (diff)

comment:14 by anonymous, 6 years ago

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)
Last edited 6 years ago by Ryan J Ollos (previous) (diff)

in reply to:  14 comment:15 by Ryan J Ollos, 6 years ago

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.

comment:16 by anonymous, 6 years ago

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?

in reply to:  14 ; comment:17 by Ryan J Ollos, 6 years ago

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?

Last edited 6 years ago by Ryan J Ollos (previous) (diff)

in reply to:  16 comment:18 by Ryan J Ollos, 6 years ago

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.

in reply to:  17 ; comment:19 by Jun Omae, 6 years ago

Keywords: mysql added

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.

comment:20 by anonymous, 6 years ago

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)'")
Last edited 6 years ago by Jun Omae (previous) (diff)

comment:21 by Jun Omae, 6 years ago

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

in reply to:  19 ; comment:22 by Peter Suter, 6 years ago

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.)

Last edited 6 years ago by Peter Suter (previous) (diff)

in reply to:  22 comment:23 by Jun Omae, 6 years ago

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.

comment:24 by Jun Omae, 6 years ago

Keywords: needinfo added
Summary: Upgrade Error"BIGINT value is out of range" error while upgrading from 0.11

in reply to:  19 comment:25 by Ryan J Ollos, 6 years ago

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?

comment:26 by anonymous, 6 years ago

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

comment:27 by Ryan J Ollos, 6 years ago

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?

Last edited 6 years ago by Ryan J Ollos (previous) (diff)

comment:28 by Ryan J Ollos, 6 years ago

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.
Last edited 6 years ago by Ryan J Ollos (previous) (diff)

comment:29 by Remy Blank, 6 years ago

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).

comment:30 by Ryan J Ollos, 6 years ago

Milestone: 1.0.2

comment:31 by Ryan J Ollos, 6 years ago

Resolution: duplicate
Status: newclosed

Duplicate of #10993.

comment:32 by oyvindsk, 5 years ago

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;

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted.
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.