Page 1 of 1

report_schedule does not exist

Posted: Thu Jul 26, 2018 12:31 pm
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

Re: report_schedule does not exist

Posted: Thu Jul 26, 2018 1:48 pm
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?

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 5:35 am
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).

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 7:47 am
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.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 8:55 am
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.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 9:10 am
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.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 9:11 am
by pribis
Gah! Brain fart. I knew that! Thanks.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 9:50 am
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?

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 11:16 am
by pribis
I verified that standard_conforming_strings is on. So it would seem that this should not be happening. But it is.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 11:25 am
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.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 11:38 am
by pribis
I'm using mysql 5.7.22 and psql 9.5.13.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 12:37 pm
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.

Re: report_schedule does not exist

Posted: Fri Jul 27, 2018 5:18 pm
by mikeb
See item #4 in the FAQ, that should prevent those errors from happening.

Re: report_schedule does not exist

Posted: Mon Jul 30, 2018 9:38 am
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.

Re: report_schedule does not exist

Posted: Mon Jul 30, 2018 10:30 am
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

Re: report_schedule does not exist

Posted: Tue Aug 21, 2018 4:50 am
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...

Re: report_schedule does not exist

Posted: Tue Aug 21, 2018 10:36 am
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.

Re: report_schedule does not exist

Posted: Tue Aug 21, 2018 4:29 pm
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.

Re: report_schedule does not exist

Posted: Wed Aug 22, 2018 8:28 am
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.

Re: report_schedule does not exist

Posted: Wed Aug 22, 2018 9:42 am
by pribis
Set debug to 10 and attached the output.

Re: report_schedule does not exist

Posted: Wed Aug 22, 2018 10:21 am
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.

Re: report_schedule does not exist

Posted: Wed Aug 22, 2018 10:59 am
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.

Re: report_schedule does not exist

Posted: Wed Aug 22, 2018 11:07 am
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.

Re: report_schedule does not exist

Posted: Wed Aug 22, 2018 11:43 am
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.