Hi,
I'm in the process of updating an old PGV instance to the latest trunk version.
So far so good except I've noticed a few differences in the database schema, more specifically while v10 had only 2 user tables (for user and comments) v14 has 4 (for user, comments, user_settings and gedcom_settings).
A couple of questions before running into manual mode.
1. is there any schema automatic migration tool/procedure?
2. is there any other difference between schema 10 and 14? I was apparently able to import other tables with no flaws (e.g.. news, folders) but I prefer to have a confirmation by design instead of by experience only :)
Br,
Mauro
Last edit: McGrelio 2022-02-28
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
When you launch PhpGedView with the existing database and the correct database credentials, any old database schemas are automatically updated to the latest version.
However, after having successfully launched the new version of PhpGedView, you MUST re-import the GEDCOM (letting the program erase the existing database contents) so that the database indexes are properly populated.
To be absolutely sure that you're doing things correctly, please describe in detail the procedure you are or will be following to do your upgrade to the latest-and-greatest.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm working in a test environment and I have ful backup of both filesystem and database of the production site.
I've just followed your advice:
1. old database
2. latest trunk code on filesystem
3. run the setup
- note1: old db admins are not recognized (see note 3 below) and the setup process asks to register a new admin
- note2: regarding the schema, yes the new user tables are created but the old "pgv_users" is still present
- note3: the new tables "pgv_user_comments", "pgv_user_gedcom_settings" and "pgv_user_settings" are created but they are empty meaning that no user properties are actually migrated meaning in particular that user cannot login as they are not verified by themselves and admin
4. re-import the gedcom flagging YES to "Do you want to erase the old data and replace it with this new data?"
- note 4: this actually ovewrites also the gedcom conmf/priv files reuslting in previous configuration to be done again
I also noticed that:
- note5: click counter on gedcom pages are erased after the migration as per my understanding new version uses a hit_counter database table (empty) while the old management was file based "GEDCOM_FILENAMEpgv_counters" in the index folder
- note6: files "XXX_patriarch.php" and "XXX_todays.php" are no more present in the index folder (but I don't exactly know/remeber what they were meant for)
Br,
Mauro
Last edit: McGrelio 2022-03-01
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Start with the (presumably) working original installation. If it's not working because of updates to the PHP system, no problem. However, it should have worked before the PHP update.
Using phpmyadmin or some other suitable database management system, do a full database backup (just in case).
In your server's PhpGedView install directory, delete everything except file /config.php and directories /index and /media. If you have any customized files in the /languages directory, they should be retained too. These customized language files are named something like "extra.xx.php" where "xx" is a language code.
Copy the unzipped SVN snapshot to your server's PhpGedView install directory.
Launch PhpGedView and log in as the existing admin.
Immediately access the Manage GEDCOMs page and re-import the existing GEDCOM, letting the program overwrite the database contents. If there isn't a GEDCOM to import, or if you suspect that the GEDCOM file doesn't match the database contents, export the database to a GEDCOM file and then re-import the just-exported file.
You noticed that the upgrade leaves behind an extra pgv_users table in addition to the pgv_user, pgv_user_gedcom_setting and pgv_user_setting tables. That is correct. You can delete the pgv_users table after the update to the latest PhpGedView is complete. This deletion is not done automatically because you MAY need to run the update again. Leaving this table around won't do any harm; it just wastes a little space in the database.
The pgv_user table equates the Login ID and password to a user number that's used internally. The pgv_user_setting table uses the user number as the primary index, and gives all of the details, privileges, etc for each user. The pgv_user_gedcom_setting table, primary index the user number, identifies the access rights of each user to each gedcom.
If you run into trouble, please let me know. I've done this upgrade many times. Some of the upgrades required considerable effort on my part to recover from seemingly hopeless situations.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for your patience and support Gerry, much appreciated.
This is the outcome when I follow the procedure you mentioned. Unsure if it is correct as in the end users are still not imported correctly.
Start with the (presumably) working original installation. If it's not working because of updates to the PHP system, no problem. However, it should have worked before the PHP update.
Done. Old installation was working until it had issues with PHP system updates.
In your server's PhpGedView install directory, delete everything except file /config.php and directories /index and /media.
Done. Consider that:
- I left config.php read only 444 (but I also tried writable 644)
- In my previous installation
- index files were not under ./index/ but ../data/pgvdata/
- media files were not under ./media but ../data/pgvdata/media/
- media firewall was enabled
This is not something I want to keep for the future as it forces me to disable WebServer ModSecurity (which wouldn't allow for OWASP security concerns paths starting with double dots as "../data/". But for now not to change things I left everything untouched for this test.
If you have any customized files in the /languages directory, they should be retained too. These customized language files are named something like "extra.xx.php" where "xx" is a language code.
Copy the unzipped SVN snapshot to your server's PhpGedView install directory.
Done
Launch PhpGedView and log in as the existing admin.
What is the expected result at this point? Landing to PGV homepage or entering the instal process?
In my attempt when I launch PGV the install procedure starts (install.php).
Following all the steps with no issues at #7 (create administrator) the procedure asks me to create a brand new admin, without recognizing the existing ones.
Immediately access the Manage GEDCOMs page and re-import the existing GEDCOM, letting the program overwrite the database contents. If there isn't a GEDCOM to import, or if you suspect that the GEDCOM file doesn't match the database contents, export the database to a GEDCOM file and then re-import the just-exported file.
Not sure if the previous step was correct, by the way at install step #8 I click "Administer GEDCOM files and privacy" and then import all my gedcoms with no issues.
Result now is that :
- pgv_user_settings table is still not popoulated with user settings formerly contained in pgv_users hence old admin are not recognized and no user can login as it is not verified by himself and admin as well.
- hit counts are set to zero
Br,
Mauro
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It appears as though the PGV_SCHEMA_VERSION column in the pgv_site_setting database table did not reflect the true state of your database when you started the update procedure. Also, the config.php file was either not present or did not indicate that the site was configured.
The Install procedure only starts automatically when the config.php file is not present or when its $CONFIGURED value is false. When you follow the upgrade procedure descibed above, the config.php file HAS to be present, and its $CONFIGURED value HAS to be true, otherwise the old installation would not have worked.
When you launch PhpGedView, one of the first steps is to match the PGV_SCHEMA_VERSION value in the pgv_site_setting database table against the desired value (14), and to run the scripts in the /includes/db_schema directory in sequence, beginning with the current PGV_SCHEMA_VERSION until the desired value has been attained.
You clearly did NOT start the upgrade procedure with the database and config.php files as they were when the old PhpGedView was working.
So: Restore your system (including the database) to its state as it was when PhpGedView was working with the old PHP, and then start the upgrade procedure again. DO NOT change the config.php file. You can set its permissions to 777 just while the upgrade is taking place. When everything is working, you can set it back to read-only.
If things are done correctly, PhpGedView will automatically run the database changes required to update to schema version 14, and the program will also use the protected locations of the index and media firewall directories. The /index and /media directories subordinate to the PhpGedView install directories still have to be present and still have to have 777 permissions, but they won't be used.
Here are the comment lines from the start of the db_schema_13_14.php script:
* Version 0: empty database
* Version 1: create the pgv_site_setting table
* Version 2: create the user tables, as per PhpGedView 4.2.1
* Version 3: create the genealogy tables, as per PhpGedView 4.2.1
* From Version 4 onwards, we make incremental changes, rather than
* trying to introspect/update. It's more efficient, flexible,
* and allows us to update column types, indexes, etc.
* Version 4: Performance tuning: update column definitions for pgv_dates
* Version 5: Performance tuning: update column definitions for pgv_individuals
* Version 6: Performance tuning: update column definitions for pgv_families
* Version 7: Performance tuning: update column definitions for pgv_sources
* Version 8: Performance tuning: update column definitions for pgv_media/media_mapping
* Version 9: Performance tuning: update column definitions for pgv_favorites/nextid/other/placelinks/places/remotelinks
* Version 10: Move the $DEFAULT_GEDCOM setting from gedcoms.php to pgv_site_setting
* Version 11: Add the modules and module_privacy tables for module administration
* Version 12: Move $GEDCOMS from gedcoms.php to pgv_gedcom and pgv_gedcom_setting, split pgv_users into pgv_user/pgv_user_setting/pgv_user_gedcom_setting
* Version 13: Move the hit-counters to a database table
* Version 14: Move the banned/search-engine IP addresses to a database table
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I confirm you that config.php is there with $CONFIGURED set to true and that the value of PGV_SCHEMA_VERSION row in pgv_site_setting is set to 10.
While I cannot run anymore the "old" PGV (v4.2.3) due to PDOException errors probably referred PHP System versions incompatibility I am able to run it smoothly in a local XAMP environment on my Windows machine where I meticulously followed the procedure you mentioned obaining this erro when I try the first access to PGV:
Fatal error: Uncaught exception 'PDOException' with message 'No Connection Established' in C:\xampp\htdocs\genealogy2\phpGedView\includes\classes\class_pgv_db.php:721 Stack trace: #0 C:\xampp\htdocs\genealogy2\phpGedView\includes\functions\functions_db.php(2678): PGV_DB::prepare('SELECT setting_...') #1 C:\xampp\htdocs\genealogy2\phpGedView\includes\session.php(661): get_user_setting('alberto', 'language') #2 C:\xampp\htdocs\genealogy2\phpGedView\config.php(75): require_once('C:\xampp\htdocs...') #3 C:\xampp\htdocs\genealogy2\phpGedView\index.php(35): require('C:\xampp\htdocs...') #4 {main} thrown in C:\xampp\htdocs\genealogy2\phpGedView\includes\classes\class_pgv_db.php on line 721
My understanding is that while functions_db.php(2678) is trying to read from pgv_user_setting table
PGV_DB::prepare("SELECT setting_value FROM {$TBLPREFIX}user_setting WHERE user_id=? AND setting_name=?")
the pgv_user_setting table does not exist in the schema at this stage (only pgv_users and pgv_user_comments tables are present in the schema).
I cannot understand if I'm running on an inconsistent DB schema (site was created back to 2003/2004 and last "upgraded" on 2009/2010 I guess hence I cannot rely on my memory).
Any idea/suggestion is more than welcome.
Br,
Mauro
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
"No connection established" means that PhpGedView did not connect to the database. Perhaps the database credentials (found in the /config.php file) are wrong, or the database userid doesn't have the correct permissions.
What is the value of the PGV_SCHEMA_VERSION column in the pgv_site_setting database table?
What database type and engine are you using?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I think I found the (at least an) issue regarding the migration.
In my "old"schema there is a pgv_placelocation table which apparently does not allow the db schema versions migration to complete succesfully. Removing such a table I was ableto update with the correct procedure from v4.2.3 to v4.2.4.
Tomorrow I will test the upgrade towards latest trunk directly or step by step through the intermediate versions to isolate any issue. I'll post the outcomes.
Do you know anything about pgv_placelocation table? In which version was introduced, if/when it was dismissed? I think I've starded with 3.x versionso it might be a leftover.
Br,
Mauro
By the way database is:
locally MAriaDB with MyISAM angine and utf3_general_ci
production (hosted) MySQL8 with MyISAM angine and utf3_general_ci
PGV_SCHEMA_VERSION row (not column as pgv_site_setting is a key/value table) is 10
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I see no mention of a pgv_placelocation table anywhere in the code. I even checked in version 3.3.8 of PhpGedView. Nada.
I don't see why the existence of such a table would cause the upgrade procedure to fail.
The schema upgrade is a function called at line 476 of install.php and at line 413 of /includes/session.php. It's defined at line 769 of /includes/classes/class_pgv_db.php.
The code is straightforward, although the various schema update procedures don't include much in the way of error detection.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It looks as if your database engine or SQL configuration is limiting the length of any one SQL statement.
The /includes/db_schema/db_schema_11_12.php script appears to fail on the SQL statement that starts at line 153. When submitted to the database engine, this SQL statement is 2575 characters long, assuming that the value of the global variable $TBLPREFIX is "pgv_".
Note that the error detection between lines 246 and 250 of that same script is ignoring errors.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I'm in the process of updating an old PGV instance to the latest trunk version.
So far so good except I've noticed a few differences in the database schema, more specifically while v10 had only 2 user tables (for user and comments) v14 has 4 (for user, comments, user_settings and gedcom_settings).
A couple of questions before running into manual mode.
1. is there any schema automatic migration tool/procedure?
2. is there any other difference between schema 10 and 14? I was apparently able to import other tables with no flaws (e.g.. news, folders) but I prefer to have a confirmation by design instead of by experience only :)
Br,
Mauro
Last edit: McGrelio 2022-02-28
The migration is automatic.
When you launch PhpGedView with the existing database and the correct database credentials, any old database schemas are automatically updated to the latest version.
However, after having successfully launched the new version of PhpGedView, you MUST re-import the GEDCOM (letting the program erase the existing database contents) so that the database indexes are properly populated.
To be absolutely sure that you're doing things correctly, please describe in detail the procedure you are or will be following to do your upgrade to the latest-and-greatest.
Thank you very much Gerry.
I'm working in a test environment and I have ful backup of both filesystem and database of the production site.
I've just followed your advice:
1. old database
2. latest trunk code on filesystem
3. run the setup
- note1: old db admins are not recognized (see note 3 below) and the setup process asks to register a new admin
- note2: regarding the schema, yes the new user tables are created but the old "pgv_users" is still present
- note3: the new tables "pgv_user_comments", "pgv_user_gedcom_settings" and "pgv_user_settings" are created but they are empty meaning that no user properties are actually migrated meaning in particular that user cannot login as they are not verified by themselves and admin
4. re-import the gedcom flagging YES to "Do you want to erase the old data and replace it with this new data?"
- note 4: this actually ovewrites also the gedcom conmf/priv files reuslting in previous configuration to be done again
I also noticed that:
- note5: click counter on gedcom pages are erased after the migration as per my understanding new version uses a hit_counter database table (empty) while the old management was file based "GEDCOM_FILENAMEpgv_counters" in the index folder
- note6: files "XXX_patriarch.php" and "XXX_todays.php" are no more present in the index folder (but I don't exactly know/remeber what they were meant for)
Br,
Mauro
Last edit: McGrelio 2022-03-01
Mauro:
Your "upgrade" procedure is faulty.
This is the correct way to go about this:
Start with the (presumably) working original installation. If it's not working because of updates to the PHP system, no problem. However, it should have worked before the PHP update.
Using phpmyadmin or some other suitable database management system, do a full database backup (just in case).
In your server's PhpGedView install directory, delete everything except file /config.php and directories /index and /media. If you have any customized files in the /languages directory, they should be retained too. These customized language files are named something like "extra.xx.php" where "xx" is a language code.
Download the latest PhpGedView snapshot from the SVN repository, and unzip this SVN snapshot on your local PC. The correct download can be found here:
https://sourceforge.net/p/phpgedview/svn/HEAD/tarball?path=/trunk/phpGedView
Copy the unzipped SVN snapshot to your server's PhpGedView install directory.
Launch PhpGedView and log in as the existing admin.
Immediately access the Manage GEDCOMs page and re-import the existing GEDCOM, letting the program overwrite the database contents. If there isn't a GEDCOM to import, or if you suspect that the GEDCOM file doesn't match the database contents, export the database to a GEDCOM file and then re-import the just-exported file.
You noticed that the upgrade leaves behind an extra pgv_users table in addition to the pgv_user, pgv_user_gedcom_setting and pgv_user_setting tables. That is correct. You can delete the pgv_users table after the update to the latest PhpGedView is complete. This deletion is not done automatically because you MAY need to run the update again. Leaving this table around won't do any harm; it just wastes a little space in the database.
The pgv_user table equates the Login ID and password to a user number that's used internally. The pgv_user_setting table uses the user number as the primary index, and gives all of the details, privileges, etc for each user. The pgv_user_gedcom_setting table, primary index the user number, identifies the access rights of each user to each gedcom.
If you run into trouble, please let me know. I've done this upgrade many times. Some of the upgrades required considerable effort on my part to recover from seemingly hopeless situations.
Thanks for your patience and support Gerry, much appreciated.
This is the outcome when I follow the procedure you mentioned. Unsure if it is correct as in the end users are still not imported correctly.
Done. Old installation was working until it had issues with PHP system updates.
Done. Consider that:
- I left config.php read only 444 (but I also tried writable 644)
- In my previous installation
- index files were not under ./index/ but ../data/pgvdata/
- media files were not under ./media but ../data/pgvdata/media/
- media firewall was enabled
This is not something I want to keep for the future as it forces me to disable WebServer ModSecurity (which wouldn't allow for OWASP security concerns paths starting with double dots as "../data/". But for now not to change things I left everything untouched for this test.
No customized lang files
Done
What is the expected result at this point? Landing to PGV homepage or entering the instal process?
In my attempt when I launch PGV the install procedure starts (install.php).
Following all the steps with no issues at #7 (create administrator) the procedure asks me to create a brand new admin, without recognizing the existing ones.
Not sure if the previous step was correct, by the way at install step #8 I click "Administer GEDCOM files and privacy" and then import all my gedcoms with no issues.
Result now is that :
- pgv_user_settings table is still not popoulated with user settings formerly contained in pgv_users hence old admin are not recognized and no user can login as it is not verified by himself and admin as well.
- hit counts are set to zero
Br,
Mauro
Hmmmm, strange.
It appears as though the PGV_SCHEMA_VERSION column in the pgv_site_setting database table did not reflect the true state of your database when you started the update procedure. Also, the config.php file was either not present or did not indicate that the site was configured.
The Install procedure only starts automatically when the config.php file is not present or when its $CONFIGURED value is false. When you follow the upgrade procedure descibed above, the config.php file HAS to be present, and its $CONFIGURED value HAS to be true, otherwise the old installation would not have worked.
When you launch PhpGedView, one of the first steps is to match the PGV_SCHEMA_VERSION value in the pgv_site_setting database table against the desired value (14), and to run the scripts in the /includes/db_schema directory in sequence, beginning with the current PGV_SCHEMA_VERSION until the desired value has been attained.
You clearly did NOT start the upgrade procedure with the database and config.php files as they were when the old PhpGedView was working.
So: Restore your system (including the database) to its state as it was when PhpGedView was working with the old PHP, and then start the upgrade procedure again. DO NOT change the config.php file. You can set its permissions to 777 just while the upgrade is taking place. When everything is working, you can set it back to read-only.
If things are done correctly, PhpGedView will automatically run the database changes required to update to schema version 14, and the program will also use the protected locations of the index and media firewall directories. The /index and /media directories subordinate to the PhpGedView install directories still have to be present and still have to have 777 permissions, but they won't be used.
Here are the comment lines from the start of the db_schema_13_14.php script:
* Version 0: empty database
* Version 1: create the pgv_site_setting table
* Version 2: create the user tables, as per PhpGedView 4.2.1
* Version 3: create the genealogy tables, as per PhpGedView 4.2.1
* From Version 4 onwards, we make incremental changes, rather than
* trying to introspect/update. It's more efficient, flexible,
* and allows us to update column types, indexes, etc.
* Version 4: Performance tuning: update column definitions for pgv_dates
* Version 5: Performance tuning: update column definitions for pgv_individuals
* Version 6: Performance tuning: update column definitions for pgv_families
* Version 7: Performance tuning: update column definitions for pgv_sources
* Version 8: Performance tuning: update column definitions for pgv_media/media_mapping
* Version 9: Performance tuning: update column definitions for pgv_favorites/nextid/other/placelinks/places/remotelinks
* Version 10: Move the $DEFAULT_GEDCOM setting from gedcoms.php to pgv_site_setting
* Version 11: Add the modules and module_privacy tables for module administration
* Version 12: Move $GEDCOMS from gedcoms.php to pgv_gedcom and pgv_gedcom_setting, split pgv_users into pgv_user/pgv_user_setting/pgv_user_gedcom_setting
* Version 13: Move the hit-counters to a database table
* Version 14: Move the banned/search-engine IP addresses to a database table
Hi Gerry,
I confirm you that config.php is there with $CONFIGURED set to true and that the value of PGV_SCHEMA_VERSION row in pgv_site_setting is set to 10.
While I cannot run anymore the "old" PGV (v4.2.3) due to PDOException errors probably referred PHP System versions incompatibility I am able to run it smoothly in a local XAMP environment on my Windows machine where I meticulously followed the procedure you mentioned obaining this erro when I try the first access to PGV:
Fatal error: Uncaught exception 'PDOException' with message 'No Connection Established' in C:\xampp\htdocs\genealogy2\phpGedView\includes\classes\class_pgv_db.php:721 Stack trace: #0 C:\xampp\htdocs\genealogy2\phpGedView\includes\functions\functions_db.php(2678): PGV_DB::prepare('SELECT setting_...') #1 C:\xampp\htdocs\genealogy2\phpGedView\includes\session.php(661): get_user_setting('alberto', 'language') #2 C:\xampp\htdocs\genealogy2\phpGedView\config.php(75): require_once('C:\xampp\htdocs...') #3 C:\xampp\htdocs\genealogy2\phpGedView\index.php(35): require('C:\xampp\htdocs...') #4 {main} thrown in C:\xampp\htdocs\genealogy2\phpGedView\includes\classes\class_pgv_db.php on line 721My understanding is that while functions_db.php(2678) is trying to read from pgv_user_setting table
the pgv_user_setting table does not exist in the schema at this stage (only pgv_users and pgv_user_comments tables are present in the schema).
I cannot understand if I'm running on an inconsistent DB schema (site was created back to 2003/2004 and last "upgraded" on 2009/2010 I guess hence I cannot rely on my memory).
Any idea/suggestion is more than welcome.
Br,
Mauro
"No connection established" means that PhpGedView did not connect to the database. Perhaps the database credentials (found in the /config.php file) are wrong, or the database userid doesn't have the correct permissions.
What is the value of the PGV_SCHEMA_VERSION column in the pgv_site_setting database table?
What database type and engine are you using?
I think I found the (at least an) issue regarding the migration.
In my "old"schema there is a pgv_placelocation table which apparently does not allow the db schema versions migration to complete succesfully. Removing such a table I was ableto update with the correct procedure from v4.2.3 to v4.2.4.
Tomorrow I will test the upgrade towards latest trunk directly or step by step through the intermediate versions to isolate any issue. I'll post the outcomes.
Do you know anything about pgv_placelocation table? In which version was introduced, if/when it was dismissed? I think I've starded with 3.x versionso it might be a leftover.
Br,
Mauro
By the way database is:
locally MAriaDB with MyISAM angine and utf3_general_ci
production (hosted) MySQL8 with MyISAM angine and utf3_general_ci
PGV_SCHEMA_VERSION row (not column as pgv_site_setting is a key/value table) is 10
I see no mention of a pgv_placelocation table anywhere in the code. I even checked in version 3.3.8 of PhpGedView. Nada.
I don't see why the existence of such a table would cause the upgrade procedure to fail.
The schema upgrade is a function called at line 476 of install.php and at line 413 of /includes/session.php. It's defined at line 769 of /includes/classes/class_pgv_db.php.
The code is straightforward, although the various schema update procedures don't include much in the way of error detection.
It looks as if your database engine or SQL configuration is limiting the length of any one SQL statement.
The /includes/db_schema/db_schema_11_12.php script appears to fail on the SQL statement that starts at line 153. When submitted to the database engine, this SQL statement is 2575 characters long, assuming that the value of the global variable $TBLPREFIX is "pgv_".
Note that the error detection between lines 246 and 250 of that same script is ignoring errors.