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 »

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: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb »

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn »

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 316 times
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb »

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn »

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 306 times
timetrex_log1.log
(177.53 KiB) Downloaded 305 times
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb »

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?
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn »

I use MariaDB 5.5.60 with default InnoDB engine.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb »

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn »

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 »

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 298 times
timetrex.log
(838.21 KiB) Downloaded 318 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 »

id not have time to edit. Probably the answer will be - my base is corrupted ...
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb »

If you can provide more information as to what you did about the previous error, that would be helpful.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
alekseyn
Posts: 9
Joined: Thu Oct 18, 2018 11:47 pm

Re: upgarde from 10.7.2 to 11.0.2

Post by alekseyn »

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 »

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: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: upgarde from 10.7.2 to 11.0.2

Post by mikeb »

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.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
Post Reply