General upgrade process

Upgrade history

Here we document various upgrade paths we take.

1.3.18 to 1.3.40

  1. setup jessie in the sources.list
  2. apt-get update

  3. apt-get install ledgersmb

  4. remove jessie from sources.list
  5. login, which will warn you to run setup.pl, click on the link and login again

I had the error:

Cannot find Contrib script tablefunc.sql in /usr/share/postgresql/9.3/extension.
Please point your contrib_dir directive in your ledgersmb.conf to the appropriate location

I fixed the path:

index 2159841..b2c80da 100644
--- a/ledgersmb/ledgersmb.conf
+++ b/ledgersmb/ledgersmb.conf
@@ -97,7 +97,7 @@ host = localhost
 db_namespace = public
 #contrib_dir PG_CONTRIB_DIR
 #with postgresql-9.1 this stuff is in /usr/share/postgresql/9.1/extension and is injected in database with create extension pg_trgm,btree_gist...
-contrib_dir = /usr/share/postgresql/9.3/extension
+contrib_dir = /usr/share/postgresql/8.4/contrib
 # sslmode can be require, allow, prefer, or disable.  Defaults to prefer.
 sslmode = prefer

That seems to have worked, but we are still behind upstream in the debian package: http://bugs.debian.org/771822

See also 12679.

1.2.25 to 1.3.18

Work happens on a separate vserver, ledger0.koumbit.net.

We can login on https://ledger0.koumbit.net/.

Basic database setup

First, install ledgersmb from packages.

Then create a database for the data to be hosted, throught the setup.pl web interface. Enter the following information:

Create a first user, then return to setup.

Then drop the database to recreate it using the older data:

# su postgres
$ psql
postgres=# DROP DATABASE koumbit12;
postgres=# CREATE DATABASE koumbit12;
postgres=# \q

Cleanup the dump:

$ sed -i.orig 's/sql-ledger/ledgersmb/;s/ledger-smb/ledgersmb/;/avoine/d' koumbit-ls-1.2.sql

Then load a backup from 1.2:

$ psql -q koumbit12 < koumbit-ls-1.2.sql

Manual fixes to the database

Going through the web upgrade, manually fix problems not detected by ledgersmb:

Those are vendors/customers without a proper ID:

update vendor set vendornumber = id::text where vendornumber is null;
update customer SET customernumber = id::text where customernumber is null;

Same query?

update vendor set vendornumber=id where vendornumber IS NULL;
update customer set customernumber=id where customernumber IS NULL;

And those do not have proper addresses:

koumbit12=# UPDATE customer SET address1 = 'DELETEME' where (address1  ~ '[[:alnum:]_]') is not true;
UPDATE 177
koumbit12=# UPDATE vendor SET address1 = 'DELETEME' where (address1  ~ '[[:alnum:]_]') is not true;
UPDATE 71

finally, a order entry was corrupted, just kill it:

delete from oe where quonumber='578';

I also had to patch the upgrade script to skip duplicate languages:

--- 1.2-1.3-upgrade.sql 2013-12-23 15:45:27.777357759 -0500
+++ curs.sql    2013-12-23 16:26:38.741340406 -0500
@@ -552,7 +552,7 @@
        FROM lsmb12.partscustomer pv
        JOIN lsmb12.customer v ON v.id = pv.customer_id;

-INSERT INTO language SELECT * FROM lsmb12.language;
+INSERT INTO language SELECT * FROM lsmb12.language WHERE code NOT IN (SELECT code FROM language);

 INSERT INTO audittrail(trans_id, tablename, reference, formname, action,
             transdate, person_id, entry_id)

Doing the web-based upgrade

Then visit the setup page again to perform an upgrade.

First splash screen is this weird page that lists 8 apparently similar clients, with similar client numbers (voices-voix, spc, crcsit, zapmtl), I added a one ("1") after every other one. I fixed that in the original database by removing or cleaning up the duplicate clients, except for raanm which I can't figure out.

Second screen has the same problems for part E961, i added an "A". also fixed in the original database.

Next screen is invoices - lots of them. The output is not sorted. I tried adding "A"s to duplicate rows, but they're hard to find. Also, it works for some of them, but for some it doesn't work: when I save, both invoices get renamed!!! This happens when more than two invoices have the same number.

My hand-woven algorithm:

  1. look at the first screen, try to find matches
  2. for every two matches, mark one with a trailing A
  3. save page
  4. look at the first screen, if there are matches again, go to step 2, otherwise:
  5. mark every line in the first screen with a trainling A
  6. save page
  7. if items with A come back in the page, try to find matches, if there are none, I don't know what to do
  8. mark the first match with a B instead of an A, the second with a C
  9. save page, go to 7, but think of B instead of A and so on

Phew! This takes around 20 minutes to go through.

Next step is a set of form items to fill in, use:

Contrib Dir
/usr/share/postgresql/8.4/contrib
Default Country
CA
Default AR
1200
Default AP
2100

At this point, click the upgrade button should upgrade the database.

If it fails, it will report errors in /tmp/ledgersmb/dblog_stderr, and the database will look empty.

Current status

After all those changes, it seems the upgrade works. See also 12679.

The username/passwords are not properly migrated however. This seems to be completely unsupported, according to the SQL upgrade file:

-- TODO:  User/password Migration

So we'll need to recreate all users. To recreate the user:

  1. login to ledger0
  2. go to System -> Admin users -> Search users

  3. delete the user if it already exists
  4. re-create the user in System -> Admin users -> Add user

Use toggle-em-all to toggle the checkboxes.

We should also try to simply create the user in the psql database to see if that works. It should then be possible to grant him access using the regular web interface.

Translations are also not imported.

Finally, the Debian package is out of date, see http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=732997 for followup on that.

Previously found problems

We found the following error:

psql:/tmp/ledgersmb/1.2-1.3-upgrade.sql:125: ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « eca_to_contact_pkey »

This was discussed on the mailing list, in the end upstream sent me a patch in private (in ~anarcat/upgrade-anarcat.patch on ledger0) that fixes the above error.

However, now the problem is:

psql:/tmp/ledgersmb/1.2-1.3-upgrade.sql:201: ERREUR:  la nouvelle ligne viole la contrainte de vérification « location » de la relation « location_line_one_check »
CONTEXT:  instruction SQL « INSERT INTO location ( id, line_one, line_two, line_three, city, state, mail_code, country_id) VALUES (  $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8  ) »
PL/pgSQL function "location_save" line 17 at instruction SQL

This was fixed with:

UPDATE customer SET address1 = 'DELETEME' where (address1  ~ '[[:alnum:]_]') is not true;
UPDATE vendor SET address1 = 'DELETEME' where (address1  ~ '[[:alnum:]_]') is not true;

See https://sourceforge.net/mailarchive/forum.php?thread_name=8738m7e2mr.fsf%40marcos.anarc.at&forum_name=ledger-smb-users

Yet another problem were old clients without a "vendornumber", symptom:

psql:/tmp/ledgersmb/1.2-1.3-upgrade.sql:38: ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « control_code »

Homegrown fix:

koumbit12=# update vendor set vendornumber=id where vendornumber IS NULL;
UPDATE 13

yet another error:

psql:/tmp/ledgersmb/1.2-1.3-upgrade.sql:455: ERREUR:  une instruction insert ou update sur la table « invoice » viole la contrainte de clé
étrangère « invoice_trans_id_fkey »
DETAIL:  La clé (trans_id)=(12425) n'est pas présente dans la table « transactions ».

this transaction is really weird. it's not in the general ledger. yet it's an invoice, but it's not associated with a vendor or client. to find it, load any invoice in a separate window, and in the resulting URL, change the id= field to id=12425.

the fix.

koumbit12=# delete from lsmb12.invoice where trans_id = 12425;
DELETE 36
koumbit12=# delete from lsmb12.invoice where trans_id = 12427;
DELETE 6
koumbit12=# delete from lsmb12.invoice where trans_id=12431;
DELETE 6

For the record, those invoices were:

koumbit12=# select * from lsmb12.invoice where trans_id = 12427;
  id  | trans_id | parts_id |   description    |  qty  | allocated | sellprice | fxsellprice | discount | assemblyitem | unit  | project_id | deliverydate | serialnumber |                            notes
------+----------+----------+------------------+-------+-----------+-----------+-------------+----------+--------------+-------+------------+--------------+--------------+-------------------------------------------------------------
 1948 |    12427 |    10773 | Formation Drupal | -19.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |            | 2007-10-23   |              | Alternatives Drupal Training
 1949 |    12427 |    10713 | Secrétariat     |  -6.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |            | 2007-10-23   |              | Bulletin Cyberjeunes-OXFAM
 1950 |    12427 |    10728 | Consultation web |  -0.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | Intégration du logo avec fonte libre
 1951 |    12427 |    10713 | Secrétariat     |  -0.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | Droit de Cité (communications diverses)
 1952 |    12427 |    11062 | Vente            |    -2 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | Rédaction dâ\u0080\u0099un devis pour Westmount High Schoo
 1953 |    12427 |    10773 | Formation Drupal |  -2.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | mise à jour Drupal training
(6 lignes)

and also

 1960 |    12431 |    10773 | Formation Drupal                      | -19.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |            | 2007-10-23   |              | Alternatives Drupal Training
 1961 |    12431 |    10713 | Secrétariat                          |  -6.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |            | 2007-10-23   |              | Bulletin Cyberjeunes-OXFAM
 1962 |    12431 |    10728 | Consultation web                      |  -0.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | Intégration du logo avec fonte libre
 1963 |    12431 |    10713 | Secrétariat                          |  -0.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | Droit de Cité (communications diverses)
 1964 |    12431 |    11062 | Vente                                 |    -2 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | Rédaction dâ\u0080\u0099un devis pour Westmount High Schoo
 1965 |    12431 |    10773 | Formation Drupal                      |  -2.5 |         0 |      21.5 |        21.5 |        0 | f            | heure |      11492 | 2007-10-23   |              | mise à jour Drupal training

12425 was similar.

Finally, a corrupt order entry:

psql:/tmp/ledgersmb/1.2-1.3-upgrade.sql:503: ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « entity_credit_account »

koumbit12=# select * from lsmb12.oe where quonumber='578';
  id   | ordnumber | transdate  | vendor_id | customer_id | amount | netamount |  reqdate   | taxincluded | shippingpoint | notes | curr | employee_id | closed | quotation | quonumber | intnotes | department_id | shipvia | language_code | ponumber | terms
-------+-----------+------------+-----------+-------------+--------+-----------+------------+-------------+---------------+-------+------+-------------+--------+-----------+-----------+----------+---------------+---------+---------------+----------+-------
 36978 |           | 2013-04-25 |         0 |           0 |    200 |       200 | 2013-05-25 | f           |               |       | CAD  |       10724 | t      | t         | 578       |          |             0 |         |               |          |     0
(1 ligne)

koumbit12=# delete from lsmb12.oe where quonumber='578';
DELETE 1

Re-running the upgrade

To try the upgrade again before we pushed the upgrade button, we can run this:

psql koumbit12 < /usr/share/ledgersmb/sql/upgrade/1.3-1.2.sql

This will restore the 1.2 database in place. Make sure you drop the failed schema otherwise you will be able to do this only once:

koumbit12=# drop schema lsmb_13fail cascade;

To completely get rid of test databases, drop the database (with DROP DATABASE), then you can use this script to run the multitude of users created by the web UI:

psql -c 'select rolname from pg_roles;' -t | grep lsmb_ | xargs -n 1 dropuser

1.2.18 to 1.2.25

I had to build from the .dsc files on sourceforge:

dget http://downloads.sourceforge.net/project/ledger-smb/ledgersmb/1.2.25/ledgersmb_1.2.25-1.dsc
dpkg-source -x ledgersmb_1.2.25-1.dsc
cd ledgersmb-1.2.25
debuild

Alternate:

 1050  tar zxf ledgersmb_1.2.25.orig.tar.gz
 1051  cd ledgersmb
 1052  svn co svn://svn.debian.org/pkg-sql-ledger/ledger-smb/tags/1.2.25-1/
 1053  mv 1.2.25-1/debian .
 1055  rm -rf 1.2.25-1/
 1056  debuild

When done, copy:

scp ../ledgersmb*deb apps.koumbit.net:

then install:

dpkg -i ledgersmb_1.2.25-1_all.deb

1.2.17 to 1.2.18

This upgrade was a bit peculiar because we went from FreeBSD to Debian, and from a local Postgres server to a remote postgres server (postgres0.koumbit.net). This was documented in 42164.

We used the .deb file found on sourceforge.

I had to install extra packages to generate bills properly:

apt-get install texlive-latex-recommended texlive-pictures

We had a problem with bill emailing, that was fixed by changing the path to sendmail. The error was:

menu.pl:88: open |/usr/bin/sendmail -t: No such file or directory at LedgerSMB/Mailer.pm line 118

The proper path to set in /etc/ledgersmb/ledgersmb.conf is /usr/sbin/sendmail.

1.2.13 to 1.2.17

  1. backup
    • tar cfz /backup/manual/ledger-1.2.13.tgz /usr/local/ledgersmb
    • su pgsql -c 'pg_dump koumbit-ls' > /backup/manual/koumbit-ls-date +%Y%m%d.sql

  2. upgrade the port (regenerate the plist, checksums and fix the version in the makefile)

  3. portupgrade the port (portupgrade ledgersmb)

http://www.freebsd.org/cgi/query-pr.cgi?pr=129262

1.2.11 to 1.2.13

  1. backup
    • tar cfz /backup/manual/ledger-1.2.10.tgz /usr/local/ledgersmb
    • pg_dump koumbit-ls > koumbit-ls-date +%Y%m%d.sql

  2. upgrade the port (regenerate the plist, checksums and fix the version in the makefile)

  3. portupgrade the port (portupgrade ledgersmb)

1.2.10 to 1.2.11

To correct a tax issue.

  1. backup
    • tar cfz /backup/manual/ledger-1.2.10.tgz /usr/local/ledgersmb
      pg_dump koumbit-ls > koumbit-ls-`date +%Y%m%d`.sql
  2. upgrade the port (regenerate the plist, checksums and fix the version in the makefile)
  3. portupgrade the port (portupgrade ledgersmb)

1.2.9 to 1.2.10

Not documented

1.1.12 to 1.2.9

Notes

Make sure to read the release notes (doc/release_notes) and the UPGRADE files before and while upgrading. A few times.

The upgrade is non-trivial. Amongst other things, users are moved to the database (from the users/ directory. We want to have users in a dataset seperate from our existing datasets (koumbit-ls, etc).

More importantly, the database schemes change. We need to load the sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql, which is buggy in 1.2.7.

Changes in 1.2 that might affect us:

  1. All core tables now have defined primary keys.
  2. all user information has been moved into the database and the password algorythm has been changed from crypt to md5.

Steps taken

  1. Backup
    • tar cfz /backups/manual/ledger-pre-1.2.9.tgz /usr/local/ledger-smb
    • backup through the web interface
    • lethe# su pgsql -c 'pg_dumpall -o' > pgsql-pre-ledger-1.2.9.sql

  2. Upgrade the port (follow the auto plist instructions, mainly)

  3. Read the release notes and the UPGRADE files *again*
  4. Install the port
  5. Run the database upgrades as detailed by the UPGRADE notes
  6. Install a user database:
    • $ psql -U ledger-smb koumbit-ls
      psql> create database "ledger_smb_users" WITH ENCODING = 'LATIN1';
      psql> \c ledger_smb_users
      psql> \i /usr/local/ledgersmb/sql/Pg-central.sql
  7. Create the admin user:
    • $ psql -U ledger-smb ledger_smb_users
      psql> update users_conf set password = md5('pass');
  8. Create ledgersmb.conf, based on the .default file. The db credentials are now stored there
  9. Import the users (for this we need to copy the users/ directory in) <!> this actually failed because pgsql doesn't listen on localhost

  10. Upgrade the templates (after copying them over too)

Problems

  1. Duplicate keys in recurring* tables. Fix:
    • 54c54
      < ALTER TABLE recurringemail ADD PRIMARY KEY (id);
      ---
      > ALTER TABLE recurringemail ADD PRIMARY KEY (id, formname);
      58c58
      < ALTER TABLE recurringprint ADD PRIMARY KEY (id);
      ---
      > ALTER TABLE recurringprint ADD PRIMARY KEY (id, formname);
  2. Duplicate keys in vendor table. Erroneous rows:
    • koumbit-ls=# select id, count(id) from vendor group by id having count(id) > 1;
       id | count
      ----+-------
       30 |     2
       29 |     2
      (2 rows)
      #29 is a real duplicate. The two rows are exactly the same. #30 is a real problem. The two rows designate two entirely different vendors.
    • Fix for #29
      • DELETE FROM vendor WHERE id = 29 AND ctid = '(1,2)'::tid;
      Fix for #30
      • begin;
        select id,oid FROM vendor where id=30;
        update vendor set id=nextval('id') where id=30 and oid=70789;
        New ID is now 12424.
  3. Similar problem with vendortax:
    • select vendor_id, count(vendor_id) from vendortax group by vendor_id,chart_id having count(vendor_id) > 1;
       vendor_id | count
      -----------+-------
              30 |     2
              30 |     2
    • Fix
      koumbit-ls=# update vendortax set vendor_id = 12424 where vendor_id = 30 and (oid = 70848 or oid = 70849);
      UPDATE 2

Previous upgrades

See SqlLedgerUpgrade and TransferGnuCashSqlLedger.

LedgerSmbUpgrade (last edited 2015-04-07 10:40:27 by TheAnarcat)