Page 1 of 1

New TimeTrex Install with Existing database dump restore

Posted: Mon Oct 05, 2020 1:12 am
by umar9
Hello Dear members,

I have timetrex postgresql backup which i want to use in my new timetrex installation.

I installed new version of timetrex on a new ubuntu server then at the step during installation where it asks you to enter existing database name, user and password, i created new DB and user , password then i restored the existing sql dump file to my newly created DB via command line and continued the installation.
Connection was successful and timetrex installed successfully.

But after logging in, I dont see any of my old timetrex data. it is like if my database restoration did not work.

Kindly guide the correct way to use existing sql dump data with new timetrex installation.

thank s alot.

Re: New TimeTrex Install with Existing database dump restore

Posted: Mon Oct 05, 2020 9:29 am
by shaunw
On the surface it appears you are doing the proper steps. I would manually connect to the database using "psql" and check to make sure the data was actually restored first though. You can easily do this by running a query like:

Code: Select all

select * from users;
Then confirm your users data appears.

Re: New TimeTrex Install with Existing database dump restore

Posted: Wed Oct 07, 2020 4:33 am
by umar9
I re-performed the steps:
1.During installation I entered new DB name, user and password.
2.Restored the old DB to my newly created one "timetrex"
3.Check the connection to DB was successful in the installation stage
4.Checked the data exists in the DB "timetrex" that i restored via command line.
5. Getting this error (image attached) when i go to next step of timetrex installation. "API Exception: TimeTrex is unable to connect to its database....."
Scerenshot: https://paste.pics/71836b8d47f7d63728d544ab4221cb29

Re: New TimeTrex Install with Existing database dump restore

Posted: Wed Oct 07, 2020 12:19 pm
by shaunw
Please attach your full timetrex.log file, as it provides a more detailed error message. You can find its location in your timetrex.ini.php file.

Re: New TimeTrex Install with Existing database dump restore

Posted: Wed Oct 07, 2020 11:18 pm
by umar9
Dear shaunw,

I have attached the log file. It is not full log file but it is the logs from the database installation step procedure. I hope that should be sufficient.

Thank you.

Re: New TimeTrex Install with Existing database dump restore

Posted: Thu Oct 08, 2020 7:48 am
by shaunw
According to the log your database schema is corrupt and doesn't match the schema version that it should.

Can you please upload a copy of your compressed database dump file to our private upload folder below so we can investigate it further?
https://nextcloud.timetrex.com/s/6NePJBkdqGeLaDM

Re: New TimeTrex Install with Existing database dump restore

Posted: Fri Oct 09, 2020 5:52 am
by umar9
Dear shaunw,

I dont think our schema is corrupt as it is currently working fine in our production timetrex.
We are upgrading from our existing live timetrex 11.2.5 installation to newest timetrex 12.4.1 version.

After some investigation I believe there has been some schema changes in the newer timetrex version for example some new columns addition and data types changes.
Should there be a schema migration script we need to run? e.g. /classes/modules/install/sql/postgresql/1067A.sql ?
i think newer version use schema 1101 and older 11.2.5 use 1066 schema version.

Thank you very much!

Re: New TimeTrex Install with Existing database dump restore

Posted: Fri Oct 09, 2020 7:33 am
by shaunw
The schema is definitely corrupt, since the schema version in the database says one thing, and when TimeTrex tries to perform the schema upgrade scripts from that version forward, there are fatal errors saying some of the changes have already been made. That should never occur.

However without having a copy of your database, our hands are tied as far as further investigation is concerned.

Re: New TimeTrex Install with Existing database dump restore

Posted: Mon Oct 12, 2020 5:57 am
by umar9
Alright I have uploaded the compressed sql full db file name "timetrex.sql.bz2" into your private cloud folder. Please have a look and guide what can be done to fix this issue of restoration.
Thank you very much for your help. :)

Re: New TimeTrex Install with Existing database dump restore

Posted: Mon Oct 12, 2020 9:04 am
by shaunw
I'm not sure what happened to your database, it appears to be severely corrupted from a schema standpoint at the very least. It has schema changes applied for some newer versions of TimeTrex, but not others, which points to some kind of manual intervention and/or attempted forced upgrades that likely caused the corruption. For example some schema versions from v12.x have been applied, yet some from the earlier v11.x have not.

Do you know if this database originally started out on MySQL by chance?

Either way, unless you can shed some more light as to the history of this database and what may have happened to it, the only paths forward I see are:

1. Start from scratch with a completely fresh database if you don't place any importance on the historical data.

2. Restore from an earlier backup prior to the corruption, and attempt to upgrade from it. Likely only losing a small amount of data, assuming such backups exist.

3. Manually go through every schema version since v11.0.2 (roughly about 120 changes) and determine which ones have been applied or not and correctly apply ones that haven't. This would require intimate knowledge of the TimeTrex database and internal workings to properly carry out and we would only recommend that one of our developers perform it. Therefore there would be a non-trivial fee to have us do this for you.

Re: New TimeTrex Install with Existing database dump restore

Posted: Wed Oct 14, 2020 5:49 am
by umar9
Hi,
Thank you for your response.

I can confirm Yes we did started out with MySQL first when the older versions supported it then we moved to postgres when TimeTrex stopped support for MySQL.
Is there any solution possible?

Re: New TimeTrex Install with Existing database dump restore

Posted: Wed Oct 14, 2020 9:51 am
by shaunw
That makes more sense, as MySQL does not support DDL transactions so any failure during the upgrade process results in a corrupted schema requiring the need to restore from a backup. This is just one reason why we don't support MySQL anymore.

My guess is that the database was corrupted in MySQL, then migrated to PostgreSQL in the corrupted state. Since such corruption should never be able to occur on PostgreSQL.

The only solutions are those mentioned in my previous post.