Fresh Install of 23.1
Well, I tried to nuke this post because it has 21.1 in the title and start a new thread, but I can’t find a new thread button, so I’ll reppost here, but this is related to 23.1.
Hello Stefan, Ralf, all,
I’ve not had time to mess with migration in a while. So I am looking at it again. I did a composer install of 23.1, it’s quite happy, but moving my data forward is probably futile. I did a fresh install of egw from git and added the legacy apps back git clone app ... Then I moved my database from my old server to new test box and made sure MariaDB was happy (it was).
So I pulled my old header.inc.php and pointed my browser to egroupware/setup. Presto, the admin login box appeared, logged in and chose the Upgrade option. (it correctly showed my tables as 1.8.007. I pressed go, it worked for a while and then got an error:
AlterColumnSQL('egw_ea_accounts','acc_folder_archive',Array ( [type] => varchar [precision] => 128 [comment] => archive folder ) ) sql=Array ( [0] => ALTER TABLE `egw_ea_accounts` ADD `acc_folder_archive` VARCHAR(128) )
Duplicate column name 'acc_folder_archive'
AlterColumnSQL('egw_ea_accounts','acc_folder_ham',Array ( [type] => varchar [precision] => 128 [comment] => ham folder ) ) sql=Array ( [0] => ALTER TABLE `egw_ea_accounts` ADD `acc_folder_ham` VARCHAR(128) )
Duplicate column name 'acc_folder_ham'
AlterColumnSQL('egw_ea_accounts','acc_spam_api',Array ( [type] => varchar [precision] => 128 [comment] => SpamTitan API URL ) ) sql=Array ( [0] => ALTER TABLE `egw_ea_accounts` ADD `acc_spam_api` VARCHAR(128) )
Duplicate column name 'acc_spam_api'
AlterColumnSQL('egw_addressbook','contact_files',Array ( [type] => int [precision] => 1 [default] => 0 [comment] => &1: photo, &2: pgp, &4: smime ) ) sql=Array ( [0] => ALTER TABLE `egw_addressbook` ADD `contact_files` TINYINT DEFAULT 0 )
Duplicate column name 'contact_files'
api/setup/tables_update.inc.php (278)
An error happened!
So back to setup and run it again with debug enable, it now recognizes my tables as 14.3.907, but it still runs into duplicate column issues:
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()
AlterColumnSQL('egw_ea_accounts','acc_folder_archive',Array ( [type] => varchar [precision] => 128 [comment] => archive folder ) ) sql=Array ( [0] => ALTER TABLE `egw_ea_accounts` ADD `acc_folder_archive` VARCHAR(128) )
Duplicate column name 'acc_folder_archive'
--> 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()
AlterColumnSQL('egw_ea_accounts','acc_folder_ham',Array ( [type] => varchar [precision] => 128 [comment] => ham folder ) ) sql=Array ( [0] => ALTER TABLE `egw_ea_accounts` ADD `acc_folder_ham` VARCHAR(128) )
Duplicate column name 'acc_folder_ham'
AlterColumnSQL('egw_ea_accounts','acc_spam_api',Array ( [type] => varchar [precision] => 128 [comment] => SpamTitan API URL ) ) sql=Array ( [0] => ALTER TABLE `egw_ea_accounts` ADD `acc_spam_api` VARCHAR(128) )
Duplicate column name 'acc_spam_api'
--> 16.9.002
process->upgrade(): api(16.9.002 --> 23.1.004): running api_upgrade16_9_002()
AlterColumnSQL('egw_addressbook','contact_files',Array ( [type] => int [precision] => 1 [default] => 0 [comment] => &1: photo, &2: pgp, &4: smime ) ) sql=Array ( [0] => ALTER TABLE `egw_addressbook` ADD `contact_files` TINYINT DEFAULT 0 )
Duplicate column name 'contact_files'
api/setup/tables_update.inc.php (278)
An error happened!
fclose(): supplied resource is not a valid stream resource (0)
That doesn’t look that bad or dire. If I can fix this, GREAT!, If not, I’ll just nuke it, drop the database on this machine and start with a new install.
Is there any hope of finishing this database migration?
Oh, I forgot, MariaDB is quite happy with the current state of the database, it passes all checks, e.g.
$ mariadb-check -c egroupware
egroupware.egw_access_log OK
egroupware.egw_accounts OK
egroupware.egw_acl OK
egroupware.egw_addressbook OK
egroupware.egw_addressbook2list OK
egroupware.egw_addressbook_extra OK
egroupware.egw_addressbook_lists OK
egroupware.egw_admin_queue OK
egroupware.egw_admin_remote OK
egroupware.egw_applications OK
egroupware.egw_async OK
egroupware.egw_bookmarks OK
egroupware.egw_cal OK
egroupware.egw_cal_dates OK
egroupware.egw_cal_extra OK
egroupware.egw_cal_holidays OK
egroupware.egw_cal_repeats OK
egroupware.egw_cal_timezones OK
egroupware.egw_cal_user OK
egroupware.egw_categories OK
egroupware.egw_config OK
egroupware.egw_contentmap OK
egroupware.egw_customfields OK
egroupware.egw_ea_accounts OK
egroupware.egw_ea_credentials OK
egroupware.egw_ea_identities OK
egroupware.egw_ea_notifications OK
egroupware.egw_ea_valid OK
egroupware.egw_etemplate OK
egroupware.egw_history_log OK
egroupware.egw_importexport_definitions OK
egroupware.egw_infolog OK
egroupware.egw_infolog_extra OK
egroupware.egw_kb_articles OK
egroupware.egw_kb_comment OK
egroupware.egw_kb_questions OK
egroupware.egw_kb_ratings OK
egroupware.egw_kb_related_art OK
egroupware.egw_kb_search OK
egroupware.egw_kb_urls OK
egroupware.egw_lang OK
egroupware.egw_languages OK
egroupware.egw_links OK
egroupware.egw_locks OK
egroupware.egw_mailaccounts OK
egroupware.egw_news OK
egroupware.egw_news_export OK
egroupware.egw_notificationpopup OK
egroupware.egw_pm_constraints OK
egroupware.egw_pm_elements OK
egroupware.egw_pm_extra OK
egroupware.egw_pm_members OK
egroupware.egw_pm_milestones OK
egroupware.egw_pm_pricelist OK
egroupware.egw_pm_prices OK
egroupware.egw_pm_projects OK
egroupware.egw_pm_roles OK
egroupware.egw_polls OK
egroupware.egw_polls_answers OK
egroupware.egw_polls_votes OK
egroupware.egw_preferences OK
egroupware.egw_reg_accounts OK
egroupware.egw_reg_fields OK
egroupware.egw_resources OK
egroupware.egw_resources_extra OK
egroupware.egw_sharing OK
egroupware.egw_sitemgr_active_modules OK
egroupware.egw_sitemgr_blocks OK
egroupware.egw_sitemgr_blocks_lang OK
egroupware.egw_sitemgr_categories_lang OK
egroupware.egw_sitemgr_categories_state OK
egroupware.egw_sitemgr_content OK
egroupware.egw_sitemgr_content_lang OK
egroupware.egw_sitemgr_modules OK
egroupware.egw_sitemgr_notifications OK
egroupware.egw_sitemgr_notify_messages OK
egroupware.egw_sitemgr_pages OK
egroupware.egw_sitemgr_pages_lang OK
egroupware.egw_sitemgr_properties OK
egroupware.egw_sitemgr_sites OK
egroupware.egw_sqlfs OK
egroupware.egw_sqlfs_props OK
egroupware.egw_syncmldeviceowner OK
egroupware.egw_syncmldevinfo OK
egroupware.egw_syncmlsummary OK
egroupware.egw_timesheet OK
egroupware.egw_timesheet_extra OK
egroupware.egw_tracker OK
egroupware.egw_tracker_assignee OK
egroupware.egw_tracker_bounties OK
egroupware.egw_tracker_escalated OK
egroupware.egw_tracker_escalations OK
egroupware.egw_tracker_extra OK
egroupware.egw_tracker_replies OK
egroupware.egw_tracker_votes OK
egroupware.egw_wiki_interwiki OK
egroupware.egw_wiki_links OK
egroupware.egw_wiki_pages OK
egroupware.egw_wiki_rate OK
egroupware.egw_wiki_remote_pages OK
egroupware.egw_wiki_sisterwiki OK
egroupware.phpgw_mydms_ACLs OK
egroupware.phpgw_mydms_DocumentContent OK
egroupware.phpgw_mydms_DocumentLinks OK
egroupware.phpgw_mydms_Documents OK
egroupware.phpgw_mydms_Folders OK
egroupware.phpgw_mydms_GroupMembers OK
egroupware.phpgw_mydms_Groups OK
egroupware.phpgw_mydms_KeywordCategories OK
egroupware.phpgw_mydms_Keywords OK
egroupware.phpgw_mydms_Notify OK
egroupware.phpgw_mydms_Sessions OK
egroupware.phpgw_mydms_UserImages OK
egroupware.phpgw_mydms_Users OK
Let me know, and let me know if I can send anything else. If you can point me in the right direction, I don’t even mind trying to fix the code where this is occurring. If we get it working, then you have a good tool for 1.8 -> 23 migration.
Follow-up - all of the columns it complains are duplicates – are indeed added with the correct data type in the tables, e.g.
MariaDB [egroupware]> describe egw_ea_accounts;
...
| acc_folder_archive | varchar(128) | YES | | NULL | |
| acc_folder_ham | varchar(128) | YES | | NULL | |
| acc_spam_api | varchar(128) | YES | | NULL | |
| acc_further_identities | tinyint(4) | NO | | 1 | |
and
MariaDB [egroupware]> describe egw_addressbook;
...
| contact_files | tinyint(4) | YES | | 0 | |
One thought I had was just dropping the columns it thinks are duplicates and trying again?? What do you think of that approach?
I tried it, and I think it worked, but the fclose() error seems to be a problem. After removing the duplicate columns and re-running the database update with debug output enable, the following was all I received:
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)
The fclose() seems to be throwing an error. Any idea how to fix that as well.
This does indeed look like a bug in the egw conversion routine. I dug into it further and the erroneous fclose() is triggered 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)
The first condition $row['contact_jpegphoto'] should never be true and that block should never be entered, but somewhere in the earlier conversion from 1.8.007 -> 16.9.002 something must be added in the contact_jpegphoto field that is causing it to evaluate true?? That causes the $fp = Api\Vfs::string_stream($row['contact_jpegphoto']) to execute and then fclose($fp); is called unconditionally – even though $fp would be invalid due to trying to string_stream(NULL) or similar. I’ll have to install the backup of the database saved in that state and see if there is anything add in the contact_jpegphoto in the upgrade process to that point. This seems like an easy fix. Hell, I could just comment out the if (..) block for my data and see what happens.
Saved backup and database in this state and then dropped all apps and completed /setup. Kudos, the interface looks good, though I really miss idots… 
The dark-mode background is a bit to glossy black and provides a harsh contrast with the text. In my old age, I’ve gotten to where I prefer a softer dark background like #18191d or similar (which is still really dark, but eliminate the harsh black/white contrast)
Data is king though. There is no way I can live without 20 years of egroupware data in my 1.8.007 install. Do you have any SQL map that I could manually use to map the 1.8.007 data into the new 23.1 schema?
Also, the composer install worked fine. It has a few quirks and left the vendor/npm-asset/... permissions a bit wonky and a few others needed manual intervention, but on balance, this works great (and reasonably fast on a dual-core Celeron box with 4G of RAM with egw and nextcloud both running! No containers! And egroupware is running circles around nextcloud. Rather than throwing the Composer install out, it is a great asset for egroupware and work keeping. Just needs a little TLC.
Worst case, I’ll just loop over the tables and dump both the 1.8.007 and 23.1 table descriptions from bash and then build a set of insert queries to go from one to the other. I’m really oping you have a map of some kind so I don’t end up reinventing the wheel (which I unfortunately do from time to time)
Let me know on the rest, and good work on 23.1!
