PhpGedView

Gah! What is the point of having a preview facility on the forum if it looks nothing like the text that eventually appears.

The initial less-than greater-than should be me quoting your original post as follows...

I can readily create/drop/etc. tables in the PGV database via MySql Administrator using the PGV user account credentials.

Can you give me the SQL command to create the pgvsitesetting table? It will take me a week to track down all of the variable values to substitute, and the chances of me getting it correct are small.

In the file dbschema0_1.php, temporaily replace "self::exec" with "die".

When you try to load PGV, this will show you exactly what SQL it is trying to execute.

Used the dbschema0_1 mod to obtain the SQL command. Create table attempt failed initially. Next, backed up the pgv database via MySQL Administrator (to SQL Script), dropped the database and restored it from the backup. Create command worked that time. Dropped all tables, restored again from the backup and tried the pgv upgrade. Got past the site settings create table step but eventually failed with -

Fatal error: Maximum execution time of 30 seconds exceeded in C:\Inetpub\wwwroot\PhpGedView\includes\classes\classpgvdb.php on line 698

Examined database and found new pgvlink, pgvname and pgvsitesetting Innodb tables (all of the 4.1.5 tables are MyISAM). First two are empty but site setting table has 1 row, "PGVSCHEMAVERSION 3". Repeating results in the same error and database state. MySQL server process grinds at 50% of CPU until the timeout occurs.

I note that the new tables are InnoDB, even though my server configuration uses MyISAM as default storage engine. The 4.1.5 tables are MyISAM. Is mixing OK?

PGV doesn't specify database engines. If you have a mix of MyISAM and InnoDB, then this because those were the defaults when the tables were created. A mix of engines isn't a problem.

The update scripts are designed to fail safe, so that if they time out, you can resume by refreshing the page. Have you confirmed that refreshing (and hitting your timeout) doesn't advance the version number?

If you at schema version 3, then the next step (3 to 4) simply modifies some column definitions in the pgv_dates table.

30 seconds should be plenty for this. Do you have a very large DB or a very slow server?

If you have an up-to-date copy of your gedcom file (i.e. you followed the instructions about syncing your file/db before the backup), then you can truncate this table. The contents will be recreated when you import, and the structure modifications should work a lot faster.

Following the timeout, re-executed several times with same timeout error on schema 3. Examined the dates table and it had 167180 rows. Deleter all rows and re-executed with success.

The timeouts were occurring with my desktop unloaded and about 50% of the cpu used by the MySQL server process. The system is not unexpectedly slow. After getting past this issue, import of a ~10Mb GEDCOM takes maybe 120 seconds, and is very comparable to the v4.1.5 performance.

Still have a handfull of upgrade glitches, but none appear to be DB related. Thanks for the help.

BTW. I am toying with the idea of switching to SQLite but want to retain the current database state (i.e. user accounts/last login time/etc.) Is there a straightforward way? I have SQLite tools, and can write SQLite DB code if necessary.

Thanks again.

Tom

<BTW. I am toying with the idea of switching to SQLite >
You can't be serious? Why would you wish to take a performance hit like that?
-Stephen

BTW. I am toying with the idea of switching to SQLite 

Tom, think very hard before you do that. Its OK as an alternative if you have (and intend to stay with) a small GEDCOM. But for any reasonable size, stick to MySQL.