upgarde from 10.7.2 to 11.0.2

Ask your questions regarding TimeTrex installation here.
Post Reply
alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Fri Oct 19, 2018 12:10 am

Good day.
Now I have a mysql version of the database.
I want to upgrade to version 11.0.2, then make postgres version of the database.
Is it possible to upgrade from version 10.7.2 to version 11.0.2?
The upgrade ends with an error (even clean mysql database, created in 10.7.2).
Is it possible to convert the base to postgres under version 10.7.2?

mikeb
Posts: 413
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb » Fri Oct 19, 2018 7:55 am

Yes, you can definitely upgrade from any version older than v11.0.2 to v11.0.2. Our FAQ explains the exact procedure:
viewtopic.php?f=6&t=7519

If you getting error messages, please copy and paste the full error message and any other relevant information here.

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Sun Oct 21, 2018 10:41 pm

Hello!
I read viewtopic.php?f=6&t=7519 and make updates on this instruction.
Check on the necessary paths / requirements.
Go to upgrade databases...
Than I have error: Timetrex is unable to connect to its database...
Error log in attachments.
Thanks.
Attachments
timetrex.log
log on update
(173.4 KiB) Downloaded 9 times

mikeb
Posts: 413
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb » Mon Oct 22, 2018 8:07 am

It appears that there was a problem upgrading your MySQL database, therefore you will need to restore from your latest backup and attempt the upgrade again.

Unfortunately due to a limitation with MySQL, if any errors occur during the database schema upgrade process you must restore from backup before trying again. After doing that, if it fails again, we will need a copy of the log file from that first failure to diagnose the issue further.

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Mon Oct 22, 2018 10:40 pm

OK.
I restored the database from the dump and tried to upgrade. Same.
What is not clear - I made a new empty database, launched the installer under 10.7.2 - everything worked fine. But the upgrade to 11.0.2 again failed! In the attachment 2 logs (1 - the base of the dump, 2 - empty).
Thanks.
PS. I did not specify - my system is centos 7.5
Attachments
timetrex_log2.log
(1.49 MiB) Downloaded 9 times
timetrex_log1.log
(177.53 KiB) Downloaded 8 times

mikeb
Posts: 413
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb » Tue Oct 23, 2018 8:55 am

It appears log1 is from upgrading the restored database, if thats the case, then your backup is corrupted, since there are already tables created in it that are from v11.0.2 of TimeTrex, so you would need to go to a earlier backup prior to any attempt of upgrading to v11.0.2.

In log2, that appears to be the fresh install, which has a different error... What version of MySQL are you using?

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Tue Oct 23, 2018 9:38 pm

I use MariaDB 5.5.60 with default InnoDB engine.

mikeb
Posts: 413
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb » Wed Oct 24, 2018 7:53 am

The query that is failing is this:

Code: Select all

SET SQL_MODE='ALLOW_INVALID_DATES';
ALTER TABLE `recurring_schedule` CHANGE `start_time` `start_time` timestamp DEFAULT '1970-01-01 00:00:01', CHANGE `end_time` `end_time` timestamp DEFAULT '1970-01-01 00:00:01';
With the error being:
mysqli error: [1067: Invalid default value for 'start_time']
That is working fine on our test systems running MySQL v5.5.62 here though. Its possible you may have some configuration mode set that is breaking it, but I'm not sure what that would be.

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Wed Oct 24, 2018 10:06 pm

thank. I'll try to figure it.

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Fri Oct 26, 2018 6:36 am

Hi!
I overcame the error with the date. There is a new one when updating the database.
log in attachment.
Attachments
tt_schema_failure.state.log
(22 Bytes) Downloaded 9 times
timetrex.log
(838.21 KiB) Downloaded 8 times

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Fri Oct 26, 2018 7:09 am

id not have time to edit. Probably the answer will be - my base is corrupted ...

mikeb
Posts: 413
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb » Fri Oct 26, 2018 7:58 am

If you can provide more information as to what you did about the previous error, that would be helpful.

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Sun Oct 28, 2018 11:00 pm

I assume, that when calculating or establishing some old dates of database, Mariadb has a bug. You can make manually a sql query with dates with dates 1971 ... 1980, and some will be installed, some not. no_zero_in_dates parameter did not help. The solution was to set the server date to UTC, and the date was set correctly (or the invalid_date parameter was executed correctly).

alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn » Mon Oct 29, 2018 7:42 am

Hello!
When updating mtsql database to postgres, I get an error:

psql:timetrex_mysql.sql:45938: ERROR: duplicate key value violates unique constraint "punch_punch_control_status_id"
DETAIL: Key (punch_control_id, status_id)=(16b7f494-c185-f362-80d3-000000015400, 20) already exists.

what to do and whether to pay attention?

mikeb
Posts: 413
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb » Mon Oct 29, 2018 8:16 am

Again your MySQL database isn't doing you any favors, so you will need to manually correct it.

As always, when the upgrade fails with MySQL, you have to restore from most recent backup, make any corrections to the data, then attempt the upgrade again. To speed this process up, you will probably want to make a backup prior to each TimeTrex upgrade attempt to avoid having to re-do all the manual queries each time.

After a fresh restore, you will need to find the duplicate "punch" records and manually fix them somehow, likely by deleting them. You can do that with queries like this: (Note, that pre-upgrade the IDs are all integers, so you just use the last 12 digits of the UUID and remove the leading 0's from it)

Find the duplicate punch records that have duplicate status_id values by running something like this:

Code: Select all

select * from punch where punch_control_id = 15400
If any of the records have deleted = 1 set, then those are the best ones to delete (since they are technically already deleted). If they don't, you will need to look at each record and see which one you want to keep and which one you want to delete. Remember its just records with the same status_id that you need to delete.

Once done, attempt the upgrade again and hopefully there aren't too many of those that need fixing.

Post Reply