Updating and moving to a new server

UnderDog · March 28, 2025, 11:11am

Luckily you’re doing everything on your local / test environment.

MySQL said:
#1060 - Duplicate column name ‘item_product_id

First instinct would be:

drop the column so it can be re-added
(^^ don’t do that)

If you check your … what is it … ip_invoice_items table it’s probably filled with lots of invoice_items

So … the .sql file is wrong.
And it’s probably in ip_versions, but still gets executed.
That’s weird, but ok.

Move that file to somewhere temporary.
You don’t know if the rest either did or did not get executed.

any fresh 1.6.2 can be done by just downloading the .zip and installating it.
That will give you the fresh database.

Unknown column ‘ip_client_custom.client_custom_fieldid

Just add that column.
Find out in which .sql file it got added and write that down.

Now you’re stuck between update 19 and update 38.

Still, i would compare the broken 1.6.2 structure and the fresh 1.6.2 structure (export from phpmyadmin)

Then you’ll probably find out what’s missing as well

jimcor · March 29, 2025, 12:49pm

I’m kind of broken here. I just don’t know enough to understand the instructions. I almost broke our live site cause of interruptions.

jimcor · March 29, 2025, 12:57pm

I appreciate your help so much but I think you might assume I know more than I do. The help has been exceptional. So much appreciation for taking the time UnderDog.

UnderDog · March 29, 2025, 3:48pm

copy/paste the instructions that you didn’t understand.

The problem is that client_custom_fieldid is missing, it’s not added to the database.

That means you’ll have to find how it was added.
Most likely it’s here:
https://github.com/InvoicePlane/InvoicePlane/application/modules/setup/models/Mdl_setup.php#L273

But that also means that after a file in /application/modules/setup runs, something can still occur inside the application.

That happens in that link i gave you.

Long story short:
Better stick to going to /setup with your setting for DISABLE_SETUP=true
and
SETUP_COMPLETED=true

It should detect: héy, i’m at a really old version, let’s update.
If it finds that column that’s already there, find a way to not execute that file.

jimcor · April 14, 2025, 2:13am

Ok so I am pasting the compared broken updated DB on the left with a clean 6.2 install on the right (disregarding the users and etc) Hope this is heading in the right direction? (in winmerge)

got a couple of differences with - Dumping data for table ip_email_templates etc
then…

  --
-- Dumping data for table `ip_families`
--
INSERT INTO `ip_families` (`family_id`, `family_name`) VALUES etc

and…

--
-- Dumping data for table `ip_invoices`
--

INSERT INTO `ip_invoices` (`invoice_id`, `user_id`, `client_id`, `invoice_group_id`, `invoice_status_id`, `is_read_only`, `invoice_password`, `invoice_date_created`, `invoice_time_created`, `invoice_date_modified`, `invoice_date_due`, `invoice_number`, `invoice_discount_amount`, `invoice_discount_percent`, `invoice_terms`, `invoice_url_key`, `payment_method`, `creditinvoice_parent_id`) VALUES
(1, 1, 1, 3, 4, 1, '', '2016-06-28', '23:18:55'  etc...
then 
--
-- Dumping data for table `ip_invoice_amounts`
-- etc

then -

-- Dumping data for table `ip_invoice_items`
--

INSERT INTO `ip_invoice_items` (`item_id`, `invoice_id`, `item_tax_rate_id`, `item_product_id`, `item_date_added`, `item_task_id`, `item_name`, `item_description`, `item_quantity`, `item_price`, `item_discount_amount`, `item_order`, `item_is_recurring`, `item_product_unit`, `item_product_unit_id`, `item_date`) VALUES
(1, 1, 0, NULL, '2016-06-28', NULL, 'test', etc..

then some differences in ip_settings but that all seems to be settings related…??? Post below

jimcor · April 15, 2025, 3:38am

Is this WinMerge the correct thing to do to compare the databases>

jimcor · April 15, 2025, 4:09am

This is what I don’t understand at the moment!

At what stage do I go to /setup? I mean do I do this before executing the failed 1.4.7sql execution in myphpadmin? And does > " Better stick to going to /setup with your setting for DISABLE_SETUP=true

and
SETUP_COMPLETED=true"

mean i edit a setup file in the database that has been upgraded to 1.4.6

“Maybe 1.4.6 is your “base”.”

(that’s what you meant about base) then find the “settings” directory or file and find if the settings are at SETUP_COMPLETED=False" Or and edit that to “true”? Is that what you are suggesting?

UnderDog · April 15, 2025, 6:20am

Only compare the structure of the databases, not the data

UnderDog · April 15, 2025, 6:27am

I’ll get back to you on that one, i have to figure out what you mean.

Compare the structures of both fresh 1.6.2 and the old database first.
Can you compare the broken one with 1.6.2 and the (probably) 1.4.6 wit 1.6.2?

I’ll get back to you on that one, i have to figure out what you mean.

There is no “settings file”.
There is an ipconfig.php file.
SETUP_COMPLETED= does NOT need to be changed. Keep it at “true”

I’ll explain the rest once i’ve figured out what you mean

jimcor · April 16, 2025, 4:59am

LIke this?

jimcor · April 16, 2025, 5:30am

Oh, I just compared above with the Original updated to 1.6.2 after pushing through the error at 1.4.7. (just figuring out winmerge) I will go back and push the old database to 1.4.6.

jimcor · April 16, 2025, 6:33am

current old server on the left…

Lots of tables missing after this ending as follows…

Am I getting this right?

jimcor · April 16, 2025, 6:58am

This is 1.4.6 left and 1.6.2

And again a bunch of tables leading to…

UnderDog · April 16, 2025, 7:48am

So i only focused on those custom* tables, because that was your error message:

That comparison is almost awesome, besides that you have data on the left side, which doesn’t need to be there in the comparison.

For when you ever get to solve this:

It’s the task of /setup/upgrade_tables (something like that) to add those new fields to the database.

It scrolls through /application/modules/setup/sql and it tries to apply all the files in there.

As soon as it has applied the file it adds it to the ip_versions table, so it doesn’t get applied again.

When you compare the structure of “1.4.6” with the structure of “1.6.2” you’ll see all those texts marked in dark orange, right?

Those are all the fields that need to be added to get to 1.6.2

Hope it makes sense

jimcor · April 16, 2025, 9:41am

Makes sense-ish yes. I am at home now so If l have a look tomorrow to see if I can fugure out how to add those tables to the 1.4.6 structure then try applying the version updates after that???

Do you mean in myphpadmin? And to all tables or just try to add the ’

To the 1.4.6 database? Then continue with upgrading the sql versions to 1.6.2?

UnderDog · April 16, 2025, 10:52am

That comes from InvoicePlane. Don’t touch that upgrading of the database tables through InvoicePlane until you have a few backups you can fall back to.

Without backups you fall all the way back to the database of version 1.4.6 and it basically undoes all the work you will be doing.

1.4.6 is your base.
1.6.2 is tour target.

Everything in between is in those dark orange lines (of that makes sense)

Do as much as you can in phpMyAdmin.

InvoicePlane is for “using” the database.
phpMyAdmin is for “repairing” your ancient database.

You’re repairing it with those dark orange lines.

If structure of 1.4.6 + repairs is equal to structure of 1.6.2 you should be almost happy

after that:
If contents of ip_versions of 1.4.6 + repairs is equal to contents of ip_versions in 1.6.2 you should be very close to being happy.

alternative

Insert into 1.6.2 table select (field, field, field) from 1.4.6 table

But that gets really complicated really quickly.
It’s table by table.
Every table that has differences (dark orange lines as far as fields are concerned), you’ll have to make up for the fields in 1.6.2 that aren’t in 1.4.6

Very, very complicated

second alternative

Make an export of the structure of 1.4.6 (use phpMyAdmin, i think you already have this)
Make an export of the structure of 1.6.2
(use phpMyAdmin, i think you already have this)

Hello ChatGPT,
Upgrade my old database
(copy structure 1.4.6)
to my new database
(copy structure 1.6.2)

Tell me per table exactly which steps to take in my phpMyAdmin (i can paste SQL, if that will help)

context:
The old database is more than 6 years old and hasn’t been upgraded since.

All updated to the database would have ended up in the `ip_versions` table, so that the scripts wouldn't have run twice

jimcor · April 16, 2025, 1:48pm

I suppose I could try the ChatGPT route and then do a winmerge of the ChatGPT updated 1.4.6 and compare with the clean database of 1.6.2 and then (still have no idea if it worked most likely:) compare tables?..

I’m a little dyslexic, so reading code melts my brain a little so manual repair would be difficult, perhaps not impossible).

Export of the structure is just an export of the database I assume, the database being the structure? Then yes, I have this saved.

Really appreciate your time on this UnderDog!

jimcor · April 16, 2025, 1:56pm

I’ll go over your guide again tomorrow and see. I just realised you gave 3 suggestions. I though one was very complicated, and then there is a ChatGPT way.

UnderDog · April 16, 2025, 2:03pm

Almost.
When you press “Export” in phpMyAdmin you’ll have all kinds of options.

When exporting you can choose:

data
structure <<<=== that is what you want
data and structure (which is standard)

Your choice for both 1.4.6 and 1.6.2 will be “Structure” and not “Structure and Data”

When everything works you can make normal backups again. Normally that’s “Structure and Data” in 1 file.

UnderDog · April 16, 2025, 2:04pm

If you doubt what chatgpt is giving you, just tdll them that. They’re just like robots. Really fun.