TimeTrex Time and Attendance

Have hours: employee time sheet shows Pay Period worked hours of 189. Have imported data to be sure they are in pay period

Have wage: employee has wage effective as of 2016-12-01

When generate pay stub fails saying no earnings

What am I missing?

Thanks,

Gregory

Did this just start happening or did it occur after an upgrade or some other change?

Is this happening for all employees or just this one?

Cannot generate for any employee, all have same no earnings message some also have additional deductions do not match total deductions message.

Because of the hurricane had not been able to work with system for a few months, but basically started happening recently and the system updated to ver 11 since the last time (aug) we generated paystubs successfully

What database are you using TimeTrex with?

Mysql 5.5

Do you have a backup of your database prior to the v11 upgrade by chance?

Yes and no. We backup weekly, but as your updates happen unattended there may be data between the backup date and the update date we do not capture.

Also, trying to migrate to Postgres 10.1 with posted instructions but getting this error:
Error Code: 1193. Unknown system variable 'explicit_defaults_for_timestamp'
When running the script for the Mysql database

Gregory

By the sounds of it, my guess is the v11 upgrade didn't actually work correctly with your MySQL database, therefore because of deficiencies with MySQL itself (and one reason why we discontinued support for it) you would need to restore from a backup prior to v11 upgrade, and attempt the upgrade again with the latest version of TimeTrex.

The only alternative would be to purchase an extended support package and have one of our developers attempt to manually recover the database, which could be quite time consuming and costly.

We wouldn't recommend migrating that database to PostgreSQL either, as its certainly corrupt and will just result in a corrupt PostgreSQL database. So a restore or manual recover are your only real two options.

Plan to restore, but your reply does not address some possible issues.

I have a backup of data, but the non ver 11 would be 10.7.x. Can I restore 10.7.X database and run with ver 11 of the software? I do not have backup of the software.

When was the update to ver 11 so I know how far back to go in the data?

As I will still be migrating data, I still need to get the scripts in the migrate forum post to work. I noted above the line that is aborting the script. Please advise if can delete or how to get past.

Thanks

Also, suggestion that many other softwares have. In Help About it not only shows current version, etc, but also history of updates with dates.

Finally would consider starting fresh Jan 1 in another instance of TimeTrex rather than fighting restore. See there are ways to export and import employees, etc. See way to export policies, pay stub accounts, etc. but NO way to import. It would be a lot of work to recreate all the rules, etc. Recommendations for migrating configuration data, not database or history data, from one TimeTrex instance to another to facilitate going to Postgres?

Thanks

gregory wrote: ↑Wed Dec 13, 2017 5:29 am I have a backup of data, but the non ver 11 would be 10.7.x. Can I restore 10.7.X database and run with ver 11 of the software? I do not have backup of the software.

Yes, you can restore the database, then download the latest version of TimeTrex from our website (*this is important*) and run the TimeTrex web installer to upgrade your restored database to v11.

In the future we recommend backing up the entire TimeTrex directory including all of its related storage directory to avoid losing possible data.

gregory wrote: ↑Wed Dec 13, 2017 5:29 am When was the update to ver 11 so I know how far back to go in the data?

It is recorded in the database in the "system_setting" table, under the "system_version_install_date" row, but you would have to query it manually and convert it from an epoch to human readable.

gregory wrote: ↑Wed Dec 13, 2017 5:29 am As I will still be migrating data, I still need to get the scripts in the migrate forum post to work. I noted above the line that is aborting the script. Please advise if can delete or how to get past.

Unfortunately the only automated way to migrate data is with a database dump.

gregory wrote: ↑Wed Dec 13, 2017 5:36 am Finally would consider starting fresh Jan 1 in another instance of TimeTrex rather than fighting restore. See there are ways to export and import employees, etc. See way to export policies, pay stub accounts, etc. but NO way to import. It would be a lot of work to recreate all the rules, etc. Recommendations for migrating configuration data, not database or history data, from one TimeTrex instance to another to facilitate going to Postgres?

If not using a database dump to migrate data, 95% of the data will need to be manually migrated by hand.

I am not looking for automated. I am willing to do by hand. Cannot as areas like polcies, pay stub accounts, etc. ONLY have the option to export data, NOT also to import. So once I export from original system how do I import into new system?

Export/Import would be considered automated and is only possible with very limited sets of data, which you can see under Company -> Import.

So your only two options for migrating data is with database dump (automated) or "manually" by hand, screen by screen, record by record, field by field...

OK. Thank you for clarification. Just out of curiosity intended use of the export feature for paystubs, policies, etc?

Based on the amount of work will migrate the company that appeared to upgrade to ver 11 OK. Still need to resolve the issue described above on the MYSQL script Error Code: 1193. Unknown system variable 'explicit_defaults_for_timestamp' or know if it is OK to delete that line from the script TimeTrex provided for migration. Running Mysql 5.5

Will migrate screen by screen the company with the apparent database issue.

Thanks again, Gregory

You can try removing the SQL query that causes the error and see if things work, but that error is due to your MySQL version being older than when that feature was available.

Migrated database from MYSQL 5.5 to 5.7 which allowed the MYSQL script part of the migrate instructions to run without errors. Performed rest of steps (Postgresql 10.1), but psql -U <timetrexuser> -v ON_ERROR_STOP=1 -f timetrex_mysql.sql <timetrex_database>

died with message of 'relation "absence_policy" does not exist'

Please advise.

Gregory

Is there any more context or output you can provide us, as that error message doesn't make a lot of sense, since it seems to hint that the "absence_policy" table doesn't exist when its trying to insert data into it.

That table should definitely be there, if its not then something is severely wrong your database.

Issue is my misunderstanding the instructions:
5. Install a fresh copy of TimeTrex on PostgreSQL, make sure it matches the version currently installed and running on MySQL.

6. Run the following command, be sure to replace <database user>, <TimeTrex_Database_Name> with your corresponding information: psql -U <database user> -v ON_ERROR_STOP=1 -f timetrex_mysql.sql <TimeTrex_Database_Name>

If you see any errors appear on the screen, not all your data was migrated, therefore please post the errors to the forums and we will be happy to assist you in resolving them.

7. Copy the "salt" line from the old MySQL timetrex.ini.php to the new PostgreSQL timetrex.ini.php file.
8. Run the TimeTrex web-based installer using the PostgreSQL to complete the migration.

What am I supposed to do on 5. I copied the TimeTrex application to webserver and created a Postgresql database and user, but there are NO tables. Thus step 6 which does alter commands fails. Should step 5 read like step 8 and I should run the TimeTrex web-based installer so that it creates the table? They are worded very differently so would appear different actions should be taken, but not clear otherwise how to create table structure in Postgresql.

Thanks,

Gregory

gregory wrote: ↑Mon Dec 18, 2017 4:34 pm Issue is my misunderstanding the instructions:
5. Install a fresh copy of TimeTrex on PostgreSQL, make sure it matches the version currently installed and running on MySQL.

...

What am I supposed to do on 5. I copied the TimeTrex application to webserver and created a Postgresql database and user, but there are NO tables. Thus step 6 which does alter commands fails. Should step 5 read like step 8 and I should run the TimeTrex web-based installer so that it creates the table? They are worded very differently so would appear different actions should be taken, but not clear otherwise how to create table structure in Postgresql.

On step #5, you need to do a full install of TimeTrex, go through the web installer, create a company, administrator user, etc... That data will all be deleted anyways, but the key to ensure all the database tables are created.

Would be helpful for future readers to word step 5 similar to step 8 if user is supposed to perform same action of running web installer.

Did tables are there, but get error now ERROR: relation "system_log_id_seq" does not exist. There are system_log and system_log_detail tables, but no _id_seq. Should I delete from sql script or manually add table (would need structure)

thanks,

Gregory

gregory wrote: ↑Mon Dec 18, 2017 5:54 pm Would be helpful for future readers to word step 5 similar to step 8 if user is supposed to perform same action of running web installer.

This has already been done.

gregory wrote: ↑Mon Dec 18, 2017 5:54 pm Did tables are there, but get error now ERROR: relation "system_log_id_seq" does not exist. There are system_log and system_log_detail tables, but no _id_seq. Should I delete from sql script or manually add table (would need structure)

"*_id_seq" tables are sequence tables that contain a single row and single column. They should not exist in TimeTrex v11.0.0 or newer at all. Do you have any other tables in MySQL that end in "_id_seq" or is that the only one?

That was only id_seq table in Mysql database so deleted and reexported. Had to do additional clean up as had timestamp fields with 0 that Postgresql did not like and I deleted, a systemlog entry with id = 1 that I deleted and some other similar lines.

Import was successful and now running 11.0.2 in Postgresql for 1 company, the other reentering all data screen by screen in Postgresql based TimeTrex

thanks for help.