Upgrade hangs at 97%

Ask your questions regarding TimeTrex installation here.
Locked
mcgillicutty
Posts: 4
Joined: Fri Jan 18, 2013 1:23 pm

Upgrade hangs at 97%

Post by mcgillicutty »

Hello,

I've encountered an install error while trying to upgrade. The upgrade is from version 5.0.8 to 5.2.3. I have also tried upgrading to 5.1.0 while troubleshooting but still receive the same error. This error is crucial, as the current version of timetrex we are on does not have the updated tax tables. We cannot pay our employees until this data is up to date. :( Current setup is:

- Ubuntu Desktop/Server 10.04 LTS (desktop for test environment, server for production)
- Apache v2.2.14
- MySQL v5.1.66
- PHP 5.3.2

Steps to upgrade:
1) Download most recent zip from sourceforge
2) Copy timetrex.ini.php
3) Replace active timetrex directory with updated source files
4) Replace timetrex.ini.php
5) Reset permissions to timetrex directory
6) Run install/upgrade from browser

Error log below:

ALTER TABLE schedule rename to schedule_tmp;
DROP INDEX schedule_id ON schedule_tmp;
DROP INDEX schedule_recurring_schedule_template_control_id ON schedule_tmp;
DROP INDEX schedule_start_time ON schedule_tmp;
DROP INDEX schedule_user_date_id ON schedule_tmp;

CREATE TABLE schedule (
id integer NOT NULL,
company_id integer DEFAULT 0 NOT NULL,
user_date_id integer NOT NULL,
status_id integer NOT NULL,
replaced_id integer DEFAULT 0 NOT NULL,
recurring_schedule_template_control_id integer DEFAULT 0 NOT NULL,
start_time timestamp NOT NULL,
end_time timestamp NOT NULL,
schedule_policy_id integer,
absence_policy_id integer,
branch_id integer,
department_id integer,
job_id integer,
job_item_id integer,
total_time integer,
note text,
created_date integer,
created_by integer,
updated_date integer,
updated_by integer,
deleted_date integer,
deleted_by integer,
deleted smallint DEFAULT 0 NOT NULL
);
INSERT INTO schedule (id,company_id,user_date_id,status_id,start_time,end_time,schedule_policy_id,absence_policy_id,branch_id,department_id,job_id,job_item_id,total_time,note,created_date,created_by,u
pdated_date,updated_by,deleted_date,deleted_by,deleted) ( select a.id,CASE WHEN c.company_id is NOT NULL THEN c.company_id ELSE 0 END,a.user_date_id,a.status_id,a.start_time,a.end_time,a.schedule_poli
cy_id,a.absence_policy_id,a.branch_id,a.department_id,a.job_id,a.job_item_id,a.total_time,a.note,a.created_date,a.created_by,a.updated_date,a.updated_by,a.deleted_date,a.deleted_by,a.deleted FROM sch
edule_tmp as a LEFT JOIN user_date as b ON a.user_date_id = b.id LEFT JOIN users as c ON b.user_id = c.id ORDER BY a.user_date_id );
DROP TABLE schedule_tmp;

CREATE UNIQUE INDEX schedule_id ON schedule(id);
CREATE INDEX schedule_recurring_schedule_template_control_id ON schedule(recurring_schedule_template_control_id);
CREATE INDEX schedule_start_time_end_time ON schedule(start_time,end_time);
CREATE INDEX schedule_user_date_id ON schedule(user_date_id);
CREATE INDEX schedule_company_id ON schedule(company_id);

UPDATE schedule_id_seq set ID = ( select max(id) from schedule )+1;

DELETE FROM user_wage where user_id = 0;

UPDATE company_generic_map_id_seq set ID = ( select max(id) from company_generic_map )+1;
"

</pre><br>
DEBUG [L0114] [222ms]: <b>InstallSchema_Base::_InstallSchema()</b>: Schema SQL has data, executing commands!<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: PHP ERROR - FATAL(1): Uncaught exception 'ADODB_Exception' with message 'mysqli error: [1146: Table 'timetrex.schedule_id_seq' doesn't exist] in adodb_throw(<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: <br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: UPDATE schedule_id_seq set ID = ( select max(id) from schedule )+1, )<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: ' in /var/www/timetrex/classes/adodb/adodb-exceptions.inc.php:78<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: Stack trace:<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: #0 /var/www/timetrex/classes/adodb/adodb.inc.php(227): adodb_throw('mysqli', 'adodb_throw', 1146, 'Table 'timetrex...', '??UPDATE schedu...', false, Object(ADODB_mysqli))<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: #1 /var/www/timetrex/classes/adodb/adodb.inc.php(1050): ADODB_TransMonitor('mysqli', 'EXECUTE', 1146, 'Table 'timetrex...', '??UPDATE schedu...', false, Object(ADODB_mysqli))<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: #2 /var/www/timetrex/classes/adodb/adodb.inc.php(1017): ADOConnection->_Execute('??UPDATE schedu...', false)<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: #3 /var/www/timetrex/classes/modules/install/InstallSchema_Base.class.php(125): ADOConnection->Execute('??UPDATE schedu...')<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: #4 /var/www/timetrex/classes/modules/install/InstallSchema_Base.class.php(169): InstallSchema_Base->_InstallSchema()<br>
DEBUG [L0078] [402ms]: <b>Debug::Shutdown()</b>: #5 [internal function]: InstallSchema_Base File: /var/www/timetrex/classes/adodb/adodb-exceptions.inc.php Line: 78<br>
DEBUG [L0184] [402ms]: <b>Debug::Text()</b>: Detected PHP errors (1), emailing log...<br>
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Upgrade hangs at 97%

Post by shaunw »

Check your MySQL error log, there is likely a failed query being reported in there, we would need to know what it is.
mcgillicutty
Posts: 4
Joined: Fri Jan 18, 2013 1:23 pm

Re: Upgrade hangs at 97%

Post by mcgillicutty »

Thanks for your quick reply, Shaun. Here is a related error I found in /var/log/mysql/error.log:

Code: Select all

Version: '5.1.66-0ubuntu0.10.04.3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
130118  6:31:15 [ERROR] /usr/sbin/mysqld: Table './timetrex/system_log_id_seq' is marked as crashed and should be repaired
130118  6:31:15 [Warning] Checking table:   './timetrex/system_log_id_seq'
130118  6:31:15 [ERROR] /usr/sbin/mysqld: Table './timetrex/system_setting_id_seq' is marked as crashed and should be repaired
130118  6:31:15 [Warning] Checking table:   './timetrex/system_setting_id_seq'
130118  6:31:15 [ERROR] /usr/sbin/mysqld: Table './timetrex/user_preference_id_seq' is marked as crashed and should be repaired
130118  6:31:15 [Warning] Checking table:   './timetrex/user_preference_id_seq'
130118  7:44:53 [Note] /usr/sbin/mysqld: Normal shutdown
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Upgrade hangs at 97%

Post by shaunw »

30118 6:31:15 [ERROR] /usr/sbin/mysqld: Table './timetrex/system_log_id_seq' is marked as crashed and should be repaired
Unfortunately you're a poster child for the exact reason why there is a big warning message in TimeTrex asking people to avoid MySQL like the plague. Your database is corrupt and MySQL will just silently keep chugging along as if nothing is the problem...

I would recommend restoring from your latest backup and trying the upgrade again. While repairing the tables may work, it doesn't guarantee that the data in them is actually correct, and there is a really good chance of running into silent corruption issues or strange crashes later on. Not to mention the upgrade has already failed, so there is no way to rollback the changes, yet another reason why MySQL is not recommended.
mcgillicutty
Posts: 4
Joined: Fri Jan 18, 2013 1:23 pm

Re: Upgrade hangs at 97%

Post by mcgillicutty »

Yikes. Fortunately, I did make a backup just before upgrading. Unfortunately, I have tried the upgrade multiple times with multiple versions with no luck. Table check of the sql backup file shows the status of those tables is okay. I tried a repair just in case...same result.

As I was peeking around the source files earlier, I noticed a MySQL to Postgre conversion script. I require MySQL for another piece of software I run, but is it possible to convert my current TimeTrex database over to Postgre and run them both? Will the corruption carry over?

I tried to call the support line last night, but the office was closed. Is there any way I can get expedited support over the weekend, or will I have to wait until Monday? I'm hoping to have this resolved quickly so I can get paychecks out.
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Upgrade hangs at 97%

Post by shaunw »

MySQL to PostgreSQL conversion isn't an easy process nor is it fully automated, I wouldn't recommend this route if you have payroll to get out. If you have a backup in theory you should be able to restore from it, run a database check then try the upgrade again. You do realize with MySQL if the upgrade fails even once you can't just try it again, you have to restore from backup before you will have any hope of attempting the upgrade again. This is another shortcoming of MySQL, as it lacks DDL transactions.

After hours emergency support is available, however its double the regular cost per hour. If you are interested in this you will need to email your contact information to support@timetrex.com and someone will call you back.
mcgillicutty
Posts: 4
Joined: Fri Jan 18, 2013 1:23 pm

Re: Upgrade hangs at 97%

Post by mcgillicutty »

I have been dropping and restoring the database between upgrade attempts. I also tried individual table repairs, but not an entire database check. At this point, I'd like to leave this in the hands of someone more familiar with the process. I will be contacting after hours support now. Thanks for your input, Shaun.
halflife
Posts: 5
Joined: Sat Feb 09, 2013 9:31 pm

Re: Upgrade hangs at 97%

Post by halflife »

Maybe a little late to the game but two things:
1) quite often the database can be resurrected from that condition by deleting the ib_logfile* files. The request that caused the crash is likely still sitting there waiting to be deleted therefore the endless loop.

2) If you really need mysql, I would suggest xtradb by Percona instead. It is a drop in replacement for mysql but comes with many improvements like ready to and very easy to cluster right out of the box ( I mean a few simple commands and it takes over and provisions a slave) and live periodic backup.

Don't take what I am saying as a plug for mysql over postgres, postgres is the tool for this task for sure. BTW it is very straight forward to run both on the same machine. If you are using Innodb without the file per table option though, performance will get very bad over time and you will start seeing mysql crashes like you just went through. Hmm....

You might want to compare the size of a complete mysql dump of all of your databases with the actual size of ibdata1. A big difference means that situation is already on you.
Locked