Updating and moving to a new server

Support
1

Hi

I have an older version of invoice plane and need to move it to a new domain for testing.
Then I want to rename the test domain back to the old domain name to go live when i am happy with everything. I am also moving, converting an old joomla site to wordpress at the same time in the same way. Invoice plane is in a folder of this install like mydomain.com/invoices

First I wondered where i can find out what my version of invoice plane. It does not seem to say in the back office.

Then I wanted to know the steps to update an older version of invoice plane to the latest. Do i have to step upgrade versions or can i go straight from the quite old version (about 5 years or more) to the latest. 1.6

Also when i change back the domain what files in invoice plane will i have to change when i the url of the new install from mytestdomain .com/invoices to mydomain .com/invoices

Any assistance would be greatly appreciated.

I am a self taught web person so i apologise if i am using the incorrect terms here,

2

I am a self taught web person so i apologise if i am using the incorrect terms here,

Don’t worry about that, it’s just lits of steps to answer.
To find the version, check composer.json. i Don’t know if the version was in there 5 years ago, but it’s in there for a while

Also check out https://github.com/InvoicePlane/InvoicePlane/tree/development/application/modules/setup/sql and /application/modules/setup/sql on your server. You’ll see some versions there.

1 Like
3

Thanks so much for the help. Would never had known where to look. Nothing in composer file
022_1.4.10.sql is the latest in /application/modules/setup/sql

4

If it would have been my own hobby project i would:

  • apply the sql filesto the database, 1by1
  • fresh install of 1.6
  • place that database in the fresh install
  • apply customizations that you would have made long time ago

For production project… would be interesting decision.
In your settings there should be a tab ‘updates’ which will fetch newer version of IP. Don’t know if it was there in your version.

The challenges you’re facing is not only your IP version. The PHP version from back then is … 4? 5?

That’s one of the reasons why i would ‘skip’ updating IP and go straight to version 1.6 with an updated DB

5

Forgive my ignorance but is there an offline way to update the database from 1.4.10.sql and then make a fresh install of 1.6 i can apply it to? There is no update tab in my ip version. I am a real novice at this so ways of updating a db is not my forte.

6

Make a backup before you start this endeavour.

First go to application/modules/setup/sql
See all those .sql files with version number newer than 1.4.10?
Apply those .sql files 1 by 1:

  • open the .sql file
  • copy/paste the contents in your phpmyadmin
  • execute and see that there is a good outcome

Speaking of offline:
I would do this locally on a PC, so then you’ll have your backup and you can go really slow.

A tool i use to run things locally is Docker

7

Finally got around to trying this.
got the error executing 1.4.7 sql
error
SQL query:

IP-408 - Add reference to products to items

ALTER TABLE ip_invoice_items
ADD COLUMN item_product_id INT(11) DEFAULT NULL
AFTER item_tax_rate_id

MySQL said:
#1060 - Duplicate column name ‘item_product_id’

Seems to result in the more recent invoices being deleted back to 2023 dates.

1 Like
8

will roll back and try again I guess.

9 
ALTER TABLE ip_invoice_items
ADD COLUMN item_product_id INT(11) DEFAULT NULL
AFTER item_tax_rate_id

Do you know which version file that sql statement is from?
Check /application/modules/setup/sql/(filename).sql

After the file gets executed it will end up in the ip_versions table in your database.

Your field is in the table, but that version file is not in your versions table.
After you’ve added that version file in your table, make a new database backup and keep going.

10

It’s this one: application/modules/setup/sql/019_1.4.7.sql: https://github.com/InvoicePlane/InvoicePlane/application/modules/setup/sql/019_1.4.7.sql#L26

There’s a lot of new fields and changes in there, so check of they’re all in your database

11

Ok just did the whole process and got the same error on 1.4.7.sql
powered on and now clicking on most things in the dashboard gives the error A
(just adding notes as I go)

Database Error Occurred
Error Number: 1054

Unknown column ‘ip_client_custom.client_custom_fieldid’ in ‘ON’

SELECT SQL_CALC_FOUND_ROWS ip_client_custom., ip_custom_fields. FROM ip_client_custom INNER JOIN ip_custom_fields ON ip_client_custom.client_custom_fieldid = ip_custom_fields.custom_field_id WHERE client_id = ‘466’ ORDER BY custom_field_table ASC, custom_field_order ASC, custom_field_label ASC

Filename: core/MY_Model.php

Line Number: 160

16

image
image966×454 57.7 KB

After importing the .sql files up to 1.4.6 the ip_versions file shows 1.4.10 Am I doing it wrong? Have tried importing the sql and also tried copy paste and go under sql in myphpadmin.

(Sorry for my dumb questions. I don’t even know if they are dumb)

17

I mean up to 1.4.10. So are they pre-populated. Totally confused now.

18

They are not dumb, you have a complicated situation.
You have a database that is half-updated and half-broken.

A .sql file ends up in your ip_versions table after you have run /setup with SETUP_COMPLETE=true in your ipconfig.php.

Any error with a missing field in any SQL statement means the .sql file isn’t executed, even though it shows un ip_versions

19

Find in which file that field was added.
2 options:

  • Delete the line from ip_versions and run /setup (not recommended yet)
  • manually execute the SQL from the file
20

So I have returned to the original un updated database 1.4.10.sql in my duplicate old test server (mainly because new invoices have been added to the old active server).
It is the 1.4.7sql that causes the error “IP-408 - Add reference to products to items”

So should I update up to 1.4.6 then try something before the error of 1.4.7sql

The error
"Database Error Occurred
Error Number: 1054
Unknown column ‘ip_client_custom.client_custom_fieldid’ in ‘ON’… etc
Is from importing the databese to the 1.62 new install so should I be trying to fix the update from the old server first?

1 Like
23

I think the invoices dated back may have been because i didn’t update to the latest live database correctly (or at all) and that is from when I made the copy site to play with.

1 Like
24

I honestly have no idea.

I honestly have no idea.

Maybe your 1.6.2 + updating everything that’s missing will fix your problem, but with every error i would have reminded myself: "oh yep, back in 2025 i had a half database that was manually updated.

Maybe 1.4.6 is your “base”.

Grab your broken 1.6.2 database, structure only and maybe a real, default 1.6.2 structure only and compare them.

I usually use “Meld” or “WinMerge”

That field got added in one of the 38 update .sql files

25

Just reading my confusing posts. Here is the Where i am back to and things i know…
I am back to square one with the live old site 1.4.10.sql A copy of the old site 1.4.10.sql to play with that i use to update the sql that will throw the following error when executing 1.4.7.sql


ALTER TABLE `ip_invoice_items`
ADD COLUMN `item_product_id` INT(11) DEFAULT NULL
AFTER `item_tax_rate_id`

MySQL said:
#1060 - Duplicate column name ‘item_product_id

and a new 6.2 installation (of course with the broken updated database because i didn’t back up the fresh blank 6.2 database).

When I execute the 1.4.7 on my copy of the old site I will get the error

Database Error Occurred
Error Number: 1054

Unknown column ‘ip_client_custom.client_custom_fieldid’ in ‘ON’

SELECT SQL_CALC_FOUND_ROWS ip_client_custom.*, ip_custom_fields.* FROM `ip_client_custom` INNER JOIN `ip_custom_fields` ON `ip_client_custom`.`client_custom_fieldid` = `ip_custom_fields`.`custom_field_id` WHERE `client_id` = ‘466’ ORDER BY `custom_field_table` ASC, `custom_field_order` ASC, `custom_field_label` ASC

Filename: core/MY_Model.php

Line Number: 160"

Updating the sql after that throws no more errors going to the latest database SQL

So the problem seems to be only the 1.4.7sql execution. (others seem to have the same error on google but no solution i can find) Database error after upgrade to 1.4.7 - #7

And yeah manually updating the DB in 2025 would give me sleepless nights HA!

Of note the original 1.4.10 site has never been updated from install.

1 Like