Fresh Install of 23.1, Data Migration Almost Succssful, but fails in function api_upgrade16_9_002()

[David_C_Rankin]

Ralf, Stefan, all

This post is a follow on to the additions made to the post Update database from 1.8.007 to current (21 - github). I had thought that continuing the old thread would be a good idea, however it had the old version of 21 in the title and couldn’t be changed.

In summary, I’ve used egroupware for at least 15 years. All of my data is stuck in MySQL on version 1.8.007. With a fresh install of 23.1 using composer, egroupware is working fine, but attempting to upgrade my data fails in api/setup/tables_update.inc.php on line 278 inside the following if (..) block in function api_upgrade16_9_002():

                        if ($row['contact_jpegphoto'] && ($fp = Api\Vfs::string_stream($row['contact_jpegphoto'])))

That if (..) should never be entered during the conversion of my data because there are no contact_jpegphoto entries that are not NULL, e.g. from the 1.8.007 box:

MariaDB [egroupware]> select contact_id, n_family from egw_addressbook where contact_jpegphoto != NULL;
Empty set (0.002 sec)

So something leading up to the time function api_upgrade16_9_002() is run makes setup think there is jpeg data in the contact_jpegphoto field when there is none.

The upgrade of my database begins fine but fails on the spurious fclose() error. On first run it progresses and egroupware then recognizes the database version as 14.3.907. Rerunning setup fails as egw_ea_accounts fields acc_folder_archive, acc_folder_ham, acc_spam_api and acc_further_identities were created prior to the fopen() error along with egw_addressbook field contact_files. Attempting to rerun setup then fails when the upgrade tries to reinsert the same fields.

So, dropping the fields the upgrade considers duplicates and rerunning setup proceeds well until the contact_jpegphoto error above is triggered when the upgrade attempts to open a file in the contact_jpegphoto field – which of course is NULL and then unconditionally calls fclose() on the descriptor resulting in the error, e.g.

Backup started, this might take a few minutes ...

Backup finished.

process->pass(): #1 for upgrade processing
process->upgrade(): Incoming : appname: api, version: 14.3.907, status: U
process->upgrade(): api(14.3.907 --> 23.1.004): running api_upgrade14_3_907() --> 16.1
process->upgrade(): api(16.1 --> 23.1.004): running api_upgrade16_1() --> 16.1.001
process->upgrade(): api(16.1.001 --> 23.1.004): running api_upgrade16_1_001() --> 16.1.002
process->upgrade(): api(16.1.002 --> 23.1.004): running api_upgrade16_1_002() --> 16.1.003
process->upgrade(): api(16.1.003 --> 23.1.004): running api_upgrade16_1_003() --> 16.1.004
process->upgrade(): api(16.1.004 --> 23.1.004): running api_upgrade16_1_004() --> 16.1.005
process->upgrade(): api(16.1.005 --> 23.1.004): running api_upgrade16_1_005() --> 16.9
process->upgrade(): api(16.9 --> 23.1.004): running api_upgrade16_9() --> 16.9.001
process->upgrade(): api(16.9.001 --> 23.1.004): running api_upgrade16_9_001() --> 16.9.002
process->upgrade(): api(16.9.002 --> 23.1.004): running api_upgrade16_9_002() api/setup/tables_update.inc.php (278)
An error happened!

fclose(): supplied resource is not a valid stream resource (0)

At all time the MySQL tables are left in a good state, running maridb-check egroupware returns OK for each table.

So that’s where I’m stuck. I saved a copy of the database in the current state, then dropped all tables and proceeded to checkout the rest of 23.1. I like it. I miss idots, but I can live without to get rid of having to patch and rebuild php 5.6 every time ICU changes. One quirk that 23.1 misses is the old HOME where you had the calendar for the week, and infolog - own open shown on login. That was really handy and should be something that could be added for 23.1.

So this is where I sit and where I’m stuck. Is there any kind of manual database update outline I can follow. Even a good map of data from 1.8.007 to where it would live in 23.1, I don’t mind writing the insert queries to move the data forward. The data is all there and working fine on the my old server. On the test server 23.1 is working fine, but the tables are currently empty. I need to find a way to get the old data to the new server and I don’t mind manually doing it, or working to fix the egroupware update process. It seems like it is really close to working.

Any help would be greatly appreciated, and good job on 23.1!

[StefanU]

Hi David.

First of all: In principle, updates from 1.8 (and even older) to a current EGroupware are possible and often work immediately.

However, problems can always occur the older the data. A lot can simply have happened over the years.

We offer migration support for such cases through our support. With simple steps you can get an updated database:

• Upload the old backup to the EGw test system (in our data centre).
• We carry out the database update
• You test in the test system
• You make a backup, download and install the backup.

If there is a problem with the database, we repair it directly.

Usually this is cheaper/faster than trying it yourself for a long time.

If this is an alternative for you:

Contact us - Send a message to the EGroupware team

In addition, I would strongly recommend that you use a standard installation of EGroupware.
We also offer support for this.

I have given a lecture on this. It is in German, but I have translated the slides:

🇩🇪 (🇬🇧) Folien/Vortrag - slides/lecture Kielux 2021: Docker für EGroupware

---

Otherwise @RalfBecker will have to look here to see if he can help you/find the time…

Stefan

[David_C_Rankin]

Thank you Stefan,

If money was no object, I’d just send it to you, but I’ve always been a “chop my own wood” type person and I’ve become reasonably dangerous with PHP and SQL over the years, so I figure we can muddle through this somehow. My database is small, I’ve only got 653 contacts and probably 20 active calendar/infolog entries in the database I’m trying to move. (the big one will be my office database – which burned on June 11 and the demo cost on the structure alone is bank breaking). So learning how to make the update process work for this first install was to ensure I could upgrade my office database later.

While I understand the enterprise benefit to containerization, I’ve never had the hardware to justify separate servers for the applications I use. I host a number of apps and I have found the webapp standard provides the excellent data isolation while allowing all to run in a single LAMP stack. Your composer install is just perfect for that. It is an asset for egroupware. With webapp each install like egroupware runs as a dedicated user rather than under the web-server user and data is on the server is accessed as that user preventing the potential of cross-application data access under the web-server user. It’s much lighter on my hardware than trying to run 5 separate docker installs, one for each application. It’s a much harder install and setup than just loading and starting a docker container, but it’s a trade-off.

To try and migrate this install, I don’t mind pouring over the code and troubleshooting, but was really hoping the contact_jpegphoto and fclose() error was something that had come up before - just to avoid starting from scratch. It is very encouraging to hear that upgrades from 1.8.007 do succeed. It looks like it came very close to doing that. I have backups of everything and the entire .xz backup of the 1.8.007 database is only 760K, so we are not talking about a large amount of data (this is from a secondary personal install of egroupware that started around 0.8x or 0.9x.

With my new install and the old data I can dump the table definitions and mapping common columns isn’t a problem, but where I have a knowledge gap is whether the old data has to be mapped to any new fields or tables added between versions or whether table/column constraints will take care of a majority of it?

I’ll keep plugging away, but if you have any pointers they would be much appreciated. It has been great to see egroupware grow and the success you have had over the years. Keep up the great work. (and we will see about getting a “home” summary of calendar week and infolog own/open on login

== Update ==

I’ve dumped the description of each table in 1.8.007 and 23.1. I’ve created a diff of the table structure for each table. Most of the difference are simple Type or Default column changes with a few column deletions, a few additions and a few new tables created.

Question: If I create a copy of my 1.8.007 and write the alter and or create statements necessary to manually update the old to new table structure – is anything else required as far as the new table or column data or will the table Default values allow the updated database to work as is?

OR

Would it be better to simply load both databases (the 1.8.007 and empty 23.1) databases under different database names and simply use select/insert queries to move old to new?

I have an .xz compressed file of the differences if you are interested. It simply shows the changes ('-') old 1.8.007 column changes/deletions and ('+') new 23.1 changes/additions. I can’t upload it here as .xz files are not allowed and it’s not worth posting 100k of text. Let me know if interested and I can post it either on paste.opensuse.org or send it to you directly.

Answer - 2

Definitely just load old and new and migrate data - slow, but steady, Aside, migrating egw_accounts, I found ralf still has an active account on the box from the 2008/9 timeframe - we are getting old. Migration is shaping up as follows. egwmigrate db holds the copy of the 1.8.007 data that the structure is modified to allow simple insert into the egw231 (23.1) database

egw_access_log:

MariaDB [egw231]> alter table egwmigrate.egw_access_log add user_agent varchar(255) default NULL;
MariaDB [egw231]> insert into egw_access_log select * from egwmigrate.egw_access_log;

egw_accounts:

MariaDB [egw231]> alter table egwmigrate.egw_accounts add account_description varchar(255) default NULL;
MariaDB [egw231]> alter table egwmigrate.egw_accounts drop column account_challenge;
MariaDB [egw231]> alter table egwmigrate.egw_accounts drop column account_response;
MariaDB [egw231]> insert ignore into egw_accounts select * from egwmigrate.egw_accounts;

egw_acl: (no change)
...

[David_C_Rankin]

Hell yes! Success!

Not all apps were used in my 1.8.007 install, and others like bookmarks hadn’t been used in years, so I skipped updating those. But addressbook, calendar, infolog, knowledge base, global categories, phpbrain (still not sure how to display in 23.1), etc… all migrated fine manually. The global categories ave an issue in 23.1 not showing some of the old, but that’s for another question.

(sorry Ralf, looks like your account on the server expired on 2008/08/30)

For anyone who has the setup/ upgrade fail, here is a summary of how I manually migrated 1.8.007 database to the current 23.1,

egw231     - copy of 23.1 database to insert into
egwmigrate - copy of 1.8.007 database to select from

egw_access_log:

  MariaDB [egw231]> alter table egwmigrate.egw_access_log add user_agent varchar(255) default NULL;
  MariaDB [egw231]> insert into egw_access_log select * from egwmigrate.egw_access_log;

egw_accounts:

  MariaDB [egw231]> alter table egwmigrate.egw_accounts add account_description varchar(255) default NULL;
  MariaDB [egw231]> alter table egwmigrate.egw_accounts drop column account_challenge;
  MariaDB [egw231]> alter table egwmigrate.egw_accounts drop column account_response;
  
  insert ignore into egw_accouts select * from egwmigrate.egw_accounts;
  
  MariaDB [egw231]> insert ignore into egw_accounts select * from egwmigrate.egw_accounts;

egw_acl: (no change - no records)

egw_addressbook:

  MariaDB [egw231]> alter table egwmigrate.egw_addressbook drop column contact_jpegphoto;
  MariaDB [egw231]> alter table egwmigrate.egw_addressbook add contact_files tinyint default 0;
  MariaDB [egw231]> insert ignore into egw_addressbook select * from egwmigrate.egw_addressbook;

egw_addressbook2list:

  MariaDB [egw231]> insert into egw_addressbook2list select * from egwmigrate.egw_addressbook2list;

egw_addressbook_extra: (no change - no records)

egw_addressbook_lists:

  MariaDB [egw231]> insert into egw_addressbook_lists (list_id, list_name, list_owner, list_created, list_creator) select list_id, list_name, list_owner, list_created, list_creator from egwmigrate.egw_addressbook_lists;

egw_admin_queue: (no change - no records)

egw_admin_remote: (no change - no records)

egw_applications: (no change - apps handled per-install fine)

egw_async: (no change, no update, a few alarms likely in past by time of data migration)

egw_bookmarks: (not transfered - way out of date, most are 404)

egw_cal: 

  MariaDB [egw231]> alter table egwmigrate.egw_cal add range_start bigint default NULL;
  (table definition should be default 0)
  MariaDB [egw231]> alter table egwmigrate.egw_cal add range_end bigint default NULL;
  MariaDB [egw231]> update egwmigrate.egw_cal set range_start=1;
  MariaDB [egw231]> insert into egw_cal select * from egwmigrate.egw_cal;

egw_cal_dates: 

MariaDB [egw231]> alter table egwmigrate.egw_cal_dates add recur_exception tinyint default 0;
MariaDB [egw231]> insert into egw_cal_dates select * from egwmigrate.egw_cal_dates;

egw_cal_extra: (no change - no records)

egw_cal_holidays: (no change - no records)

egw_cal_repeats: (no change - all enddates in the past)

egw_cal_timezones: (no change)

egw_cal_user:

  MariaDB [egw231]> insert into egw_cal_user (cal_id, cal_recur_date, cal_user_type, cal_user_id, cal_status, cal_quantity, cal_role, cal_user_modified) select cal_id, cal_recur_date, cal_user_type, cal_user_id, cal_status, cal_quantity, cal_role, cal_user_modified from egwmigrate.egw_cal_user;

egw_cat2entry: (no change - table dropped)

egw_categories:

  MariaDB [egw231]> insert ignore into egw_categories select * from egwmigrate.egw_categories;
  
  Need to Manually Add in 23.1:
  
    |      6 |        6 |          0 |         0 | Feature Requests     |
    |      7 |        7 |          0 |         0 | Bugs                 |

egw_config: (no change - config for current version)

egw_contentmap: (no change)

egw_emailadmin: (table dropped - now egw_ea_...)

egw_customfields: (new table)

egw_ea_accounts: (new table)

egw_ea_credentials: (new table)

egw_ea_identities: (new table)

egw_ea_notifications: (new table)

egw_ea_valid: (new table)

egw_etemplate: (not used, last update Tue 12 Jun 2012 04:48:04 PM CDT)

egw_felamimail_accounts: (table dropped)

egw_felamimail_displayfilter: (table dropped)

egw_felamimail_signatures: (table dropped)

egw_history_log:

  MariaDB [egw231]> insert into egw_history_log (history_record_id, history_appname, history_owner, history_status, history_new_value, history_timestamp, history_old_value) select history_record_id, history_appname, history_owner, history_status, history_new_value, history_timestamp, history_old_value from egwmigrate.egw_history_log;

egw_importexport_definitions: (no need to change)

egw_index: (table dropped)

egw_index_keywords: (table dropped)

egw_infolog_xxx: (all tables skipped - no current open on home server)

egw_kb_articles:

  MariaDB [egw231]> insert into egw_kb_articles select * from egwmigrate.egw_kb_articles;

egw_kb_comment: (nothing to migrate)

egw_kb_questions: (nothing to migrate)

egw_kb_ratings: (nothing to migrate)

egw_kb_related_art: (nothing to migrate)

egw_kb_search:

  MariaDB [egw231]> insert into egw_kb_search select * from egwmigrate.egw_kb_search;

egw_kb_urls: (skipped - 404)

egw_lang: (skipped - version tables fine)

egw_languages: (skipped - version tables fine)

egw_locks: (nothing to migrate)

egw_mailaccounts: (nothing to migrate)

egw_notificationpopup: (nothing to migrate)

egw_openid_xxx: (no change new tables)

egw_pm_xxx: (no change, no open pm on home server)

egw_preferences: (skipped - version tables fine)

egw_reg_accounts: (nothing to migrate)

egw_resources_xxx: (nothing to migrate)

egw_sitemgr_xxx: (nothing to migrate)

egw_sqlfs_xxx: (nothing to migrate)

egw_timesheet: (nothing to migrate)

egw_timesheet_extra: (nothing to migrate)

egw_tracker_xxx: (nothing to migrate)

egw_wiki_xxx: (nothing to migrate)

done

If you just use column specific insert and select statements you can dispense with altering the old table to add/drop columns. However the insert into new_table select * from olddb.old_table; is worth a quick add column or two for the simplicity. Also, don’t overlook the insert ignore ... use where existing records were preserved by skipping on insert from the old table.

Now if I can just make idots work :)

P.S. - In 1.8.007 global categories had cat_owener of 5. Updating all in the database to cat_owner of 0 and then adding 1 new category in 23.1 - then all migrated categories reappeared - like magic.