report_schedule does not exist

Ask your questions regarding TimeTrex installation here.
Post Reply
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

report_schedule does not exist

Post by pribis »

Upgrading from mysql to postgres. The upgrade to the last mysql capable version appeared to go without a hitch (although it took a really long time - but no errors in the log).

I've done everything listed at viewtopic.php?t=7519 up to the last step: putting the data back into the postgres db.

I execute the command: psql -U <database user> -v ON_ERROR_STOP=1 -f timetrex_mysql.sql <TimeTrex_Database_Name> (With the appropriate user and db name). And I get: psql:timetrex_mysql.sql:90: ERROR: relation "report_schedule" does not exist

I'm on Ubuntu 16.04
TimeTrex: 11.2.5
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

It appears that error is being triggered by line 90 of the timetrex_mysql.sql file. Could you post the few lines above, and few lines below that here and let us know specifically which line is 90?
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

This was a TRUNCATE. I simply removed it and moved on.

Before that line was:
TRUNCATE recurring_schedule_template_control;
TRUNCATE recurring_schedule_user;
TRUNCATE regular_time_policy;
TRUNCATE remittance_destination_account;
TRUNCATE remittance_source_account;
---

But now I get:

psql:timetrex_mysql.sql:766876: ERROR: null value in column "company_id" violates not-null constraint
DETAIL: Failing row contains (4d7b3212-cf55-bed5-d2c4-000000000005, 4d7b3212-cf55-bed5-d2c4-000000000005, 10, 2000, US, Checking (USD), null, 10, 4d7b3212-cf55-bed5-d2c4-000000000001, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 1528282810, null, 1528282810, null, null, null, 0, null).
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

We will need to see the full line in the .SQL file that is causing the error in order to determine the proper course of action to fix it.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

This is what is on that line:

766876:INSERT INTO remittance_source_account (id, legal_entity_id, status_id, type_id, country, name, description, data_format_id, currency_id, last_transaction_number, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25, value26, value27, value28, value29, value30, created_date, created_by, updated_date, updated_by, deleted_date, deleted_by, deleted) VALUES ('4d7b3212-cf55-bed5-d2c4-000000000005','4d7b3212-cf55-bed5-d2c4-000000000005',10,2000,'US','Checking (USD)',NULL,10,'4d7b3212-cf55-bed5-d2c4-000000000001','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1528282810,NULL,1528282810,NULL,NULL,NULL,0);

But I don't see company_id here.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

It appears that you are trying to do the MySQL -> PostgreSQL conversion with TimeTrex v11.2.5, which is incorrect.

You must do the conversion with v11.0.2 first as described in the FAQ, only once you have fully migrated to PostgreSQL and everything is running on v11.0.2 properly, can you proceed to upgrade to a newer version like v11.2.5.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

Gah! Brain fart. I knew that! Thanks.
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

So close:

psql:timetrex_mysql.sql:1089984: invalid command \'t

brian@bxp(bxp):12:41:/var/www/time2/db/timetrex $ sed -n 1089984p timetrex_mysql.sql
INSERT INTO message (id, parent_id, object_type_id, object_id, priority_id, status_id, status_date, subject, body, require_ack, ack, ack_date, ack_by, created_date, created_by, updated_date, updated_by, deleted_date, deleted_by, deleted) VALUES ('4d7b3212-cf55-bed5-d2c4-000000000010','4d7b3212-cf55-bed5-d2c4-000000000008',50,'4d7b3212-cf55-bed5-d2c4-000000000008',50,20,1271702500,'Re:','No, cause the 11th was a Sunday and I don\'t remember logging in that day...the Excel spread sheet that was sent to me says the 11th that I was logged in for 10.73 but I\'m pretty sure that it was the 12th not the 11th.',0,NULL,NULL,NULL,1271425408,'4d7b3212-cf55-bed5-d2c4-000000000003',1271702500,'4d7b3212-cf55-bed5-d2c4-000000000002',NULL,NULL,0);

What did I miss this time?
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

I verified that standard_conforming_strings is on. So it would seem that this should not be happening. But it is.
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

I just grepped the sql file and found: SET standard_conforming_strings = 'off';SET backslash_quote = 'on';SET escape_string_warning = 'off';

So technically this should work. But again...it ain't.
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

I'm using mysql 5.7.22 and psql 9.5.13.
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

Well, I ran sed over the file and replaced the \' with ''. That got me pretty far. But now \r\n is causing problems.

I'll wait to hear back. Maybe you have a more convenient solution than what I am doing.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

See item #4 in the FAQ, that should prevent those errors from happening.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

I did do #4 correctly. It still didn't work.

So, I started completely from scratch, following each step EXACTLY as written.

Once again, I got: psql:timetrex_mysql.sql:90: ERROR: relation "report_schedule" does not exist. I listed all the tables in the postgres db and there is no report_schedule table. Neither does user_group_tree and user_tax. Can you confirm that 11.0.2 install for postgres in fact creates these tables?

I got around this last time by removing the TRUNCATE for this table (At first I had thought I had accidentally installed the latest postgres install instead of the 11.0.2, but that is not the case after all). But my guess is I'll eventually error out because the mysql version has these tables.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

Those tables wouldn't be in the PostgreSQL database, they were in your MySQL database prior to running:

Code: Select all

php tools/convert_mysql_to_postgresql.php truncate > delete_all_data.sql
That script simply goes through every table in your database and adds a "TRUNCATE" line to ensure that when restoring the .SQL file there isn't conflicting data from a partial install.

I'm not sure why you have those tables in your MySQL database to begin with, since they shouldn't exist because they have been removed many versions ago, but when we see this its often due to a previous incomplete upgrades and the fact that MySQL can't rollback transactions that affect schema changes. So there is no harm in deleting the following tables from your source MySQL database then trying everything from scratch again: report_schedule, user_group_tree, user_tax
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

Looks like user_tax is indeed needed:

DBError::__construct(): Code: DBError(1146) Message: mysqli error: [1146: Table 'timetrex.user_tax' doesn't exist] in adodb_throw...
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

But then the import to postgres of the converted data fails because of the user_tax table. So it looks like it needs to be deleted after, and only after the upgrade of the mysql version to 11.0.2.

But after all that I fail with psql:timetrex_mysql.sql:156327: ERROR: date/time field value out of range: "0000-00-00 00:00:00"
LINE 1: ...000-000000000000',NULL,0,NULL,NULL,NULL,NULL,NULL,'0000-00-0...
.

Seems like I got the conversion to work once and then it fails over and over again when trying to replicate the previous upgrade process. Even with lots of notes. So I'll start over again (For like the fifth time) and see what I get. For the record, this upgrade process sucks. I wish timetrex never had an option for mysql in the first place.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

We haven't had too many problems converting MySQL to PostgreSQL, but something seems strange with your database or the order you are doing things in. I'm having a difficult time following which commands you are having problems with and what order you are running them in though.

If you can show us each command that you run and its full output from start to finish, we may have a better chance of assisting you.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

Somehow I missed step 2. Not sure how. But I've done this so many times over the past month I'm bound to miss something I guess

I followed everything very closely this time. When I try to install the 11.0.2 version of TimeTrex on postgres the installer gets through building the db and then it asks for company information. When I fill it out and click next I get:

ERROR: Detected UNCOMMITTED transaction: Count: 1 Off: 3 OK: 0, emailing log

There is no other error. I've set debugging to 5 (not sure how high I can go) but nothing changes. I've reinstalled several times now with the same results.
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

Set debug to 10 and attached the output.
Attachments
timetrex.log.zip
(4.54 KiB) Downloaded 489 times
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

According to that log its trying to apply SQL schema changes from versions newer than v11.0.2, which of course are failing.

That tells me that you have likely unzipped a version of TimeTrex newer than v11.0.2 over top of v11.0.2, then tried to downgrade, by unzipping a older version over top of the newer version. That doesn't work since it doesn't remove files from the newer version, and leaves you with files from both the old and new versions.

If you ever need to downgrade versions of TimeTrex you need to start with a completely fresh directory and unzip the older version into that directory, then copy across your timetrex.ini.php file. Older versions of TimeTrex don't know what to do with files it finds from newer versions of TimeTrex, and thus completely breaks things.

Newer versions of TimeTrex can be unzipped on top of older versions without a problem though.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

What? Wait...no....that log is from a fresh install of 11.0.2 of the postgres version of TT. It is brand new.
pribis
Posts: 86
Joined: Mon Apr 19, 2010 10:33 am

Re: report_schedule does not exist

Post by pribis »

I deleted the 11.0.2 I had and copied it back over from the original zip (I've done this multiple times already) and ran the installer again. This time it worked. No clue as to why.
mikeb
Posts: 709
Joined: Thu Jul 27, 2006 11:58 am

Re: report_schedule does not exist

Post by mikeb »

pribis wrote: Wed Aug 22, 2018 10:59 am What? Wait...no....that log is from a fresh install of 11.0.2 of the postgres version of TT. It is brand new.
It definitely wasn't, it contained SQL schema files from newer versions of TimeTrex.
TimeTrex Community Edition is developed and supported by volunteers.
Help motivate us to continue by showing your appreciation!
Post Reply