Page 1 of 1
upgarde from 10.7.2 to 11.0.2
Posted: Fri Oct 19, 2018 12:10 am
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?
Re: upgarde from 10.7.2 to 11.0.2
Posted: Fri Oct 19, 2018 7:55 am
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.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Sun Oct 21, 2018 10:41 pm
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.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Mon Oct 22, 2018 8:07 am
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.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Mon Oct 22, 2018 10:40 pm
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
Re: upgarde from 10.7.2 to 11.0.2
Posted: Tue Oct 23, 2018 8:55 am
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?
Re: upgarde from 10.7.2 to 11.0.2
Posted: Tue Oct 23, 2018 9:38 pm
by alekseyn
I use MariaDB 5.5.60 with default InnoDB engine.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Wed Oct 24, 2018 7:53 am
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.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Wed Oct 24, 2018 10:06 pm
by alekseyn
thank. I'll try to figure it.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Fri Oct 26, 2018 6:36 am
by alekseyn
Hi!
I overcame the error with the date. There is a new one when updating the database.
log in attachment.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Fri Oct 26, 2018 7:09 am
by alekseyn
id not have time to edit. Probably the answer will be - my base is corrupted ...
Re: upgarde from 10.7.2 to 11.0.2
Posted: Fri Oct 26, 2018 7:58 am
by mikeb
If you can provide more information as to what you did about the previous error, that would be helpful.
Re: upgarde from 10.7.2 to 11.0.2
Posted: Sun Oct 28, 2018 11:00 pm
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).
Re: upgarde from 10.7.2 to 11.0.2
Posted: Mon Oct 29, 2018 7:42 am
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?
Re: upgarde from 10.7.2 to 11.0.2
Posted: Mon Oct 29, 2018 8:16 am
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.