Notes on Wiki tables:
In moodle 1.9
there are wiki_entries and wiki_pages table. wiki_entries stores page info including groupid, userid and pagename, wiki_pages stores the page history.
In moodle 2
we have wiki_subwikis, wiki_pages, and wiki_versions. wiki_subwikis joins wiki instance id, groupid and userid, wiki_pages cached latest page content, it uses subwikiid to join wiki id, userid in wiki_pages table the current author of it (who lately modified the wiki page), wiki_versions stores the history.

I got very confused solving this issue as everytime I re-attempted the install I would get duplicate entries as the install had partially completed earlier and I had forgotten to clear the table.

The issue is with duplicate entries already existing in the database prior to the install process. Solution? Besides preventing this from happening, it is to delete any duplicates or rename the article. Either option is better than the install failing.

Also, the fact this has happened more than once shows it is possible to add duplicate wikis to the table even though they share the same key 2, so maybe it's just safer to have this check in on the install?

Mark Nelson
added a comment - 07/Dec/10 3:55 PM I got very confused solving this issue as everytime I re-attempted the install I would get duplicate entries as the install had partially completed earlier and I had forgotten to clear the table.
The issue is with duplicate entries already existing in the database prior to the install process. Solution? Besides preventing this from happening, it is to delete any duplicates or rename the article. Either option is better than the install failing.
Also, the fact this has happened more than once shows it is possible to add duplicate wikis to the table even though they share the same key 2, so maybe it's just safer to have this check in on the install?

Dongsheng Cai
added a comment - 07/Dec/10 5:48 PM I don't have mark's db to perform full upgrade, but I create a script to test upgrade on wiki_pages wiki_entries and wiki tables.
But I cannot find the duplicated entry here (duplicated entries means same swid, pagename and userid)

To use the script
1. create a new database
2. copy your moodle 1.9 mdl_wiki_pages mdl_wiki_pages mdl_wiki_entries and mdl_wiki to the new db
3. rename them to mdl_wiki_pages_old mdl_wiki_entries_old, or you can comment out the renaming code in my script
4. move the script to moodle dirroot, then run it to see if you still got the key conflict.

Dongsheng Cai
added a comment - 07/Dec/10 6:14 PM To use the script
1. create a new database
2. copy your moodle 1.9 mdl_wiki_pages mdl_wiki_pages mdl_wiki_entries and mdl_wiki to the new db
3. rename them to mdl_wiki_pages_old mdl_wiki_entries_old, or you can comment out the renaming code in my script
4. move the script to moodle dirroot, then run it to see if you still got the key conflict.

I am not sure what is 'key 2' here, moodle created three keys on that table, primary key for id, wikiidgroupiduserid for ('subwikiid', 'title', 'userid') and a foreign key to reference subwiki.id. But there is not key to make title unique. Can you please run 'show keys from mdl_wiki_pages' in moodle 2 db?

Dongsheng Cai
added a comment - 07/Dec/10 6:20 PM I am not sure what is 'key 2' here, moodle created three keys on that table, primary key for id, wikiidgroupiduserid for ('subwikiid', 'title', 'userid') and a foreign key to reference subwiki.id. But there is not key to make title unique. Can you please run 'show keys from mdl_wiki_pages' in moodle 2 db?

Koen Roggemans
added a comment - 07/Dec/10 6:24 PM Hi Donghsheng,
If you need a life database to experiment with, just ask. Only requirement is use it carefully and confidentially.
It's 109MB zipped and contains several hundreds of wiki's.

Dongsheng Cai
added a comment - 07/Dec/10 6:29 PM Hi, Koen
Thanks, before I test on your db, can you please run 'show keys from mdl_wiki_pages' on your moodle 2 db? just to make sure which key prevent inserting new data.

Dongsheng Cai
added a comment - 08/Dec/10 9:54 AM Hi, Koen
This is the old wiki table which is renamed by moodle.
Can you please email me how to get your db? I guarantee I will use it only for testing and will remove it from my test server after testing.
BTW, is koen at roggemans dot net your email?
Thanks.

Thanks for Koen's db dump, I found out it was the bad records in 1.9 db, in group mode, old 1.9 wiki failed to bump page version. I will write a separate script to fix it, I am not sure if I should include this in moodle 2 upgrade script, because another wiki fail case was caused by invalid userid.

Dongsheng Cai
added a comment - 08/Dec/10 6:46 PM Hi all
Thanks for Koen's db dump, I found out it was the bad records in 1.9 db, in group mode, old 1.9 wiki failed to bump page version. I will write a separate script to fix it, I am not sure if I should include this in moodle 2 upgrade script, because another wiki fail case was caused by invalid userid.

With a lot of help, we managed to trace down a few problems:
1) upgrade_set_timeout() at the beginning of wiki_upgrade_migrate_versions() is not having effect. We had to move it to within the loop.
2) Detected that some records within the loop spend mins to be processed.
3) Traced the problem down to some old binary contents in DB trying to be converted
4) should wiki_ewiki_2_html() generate filtered contents? or unfiltered ones?
5) to save some cycles, wiki_ewiki_2_html() also should not using caching at all.

Koen Roggemans
added a comment - 14/Dec/10 7:34 PM With a lot of help, we managed to trace down a few problems:
1) upgrade_set_timeout() at the beginning of wiki_upgrade_migrate_versions() is not having effect. We had to move it to within the loop.
2) Detected that some records within the loop spend mins to be processed.
3) Traced the problem down to some old binary contents in DB trying to be converted
4) should wiki_ewiki_2_html() generate filtered contents? or unfiltered ones?
5) to save some cycles, wiki_ewiki_2_html() also should not using caching at all.

> 5) to save some cycles, wiki_ewiki_2_html() also should not using caching at all.
And please disable htmlpurifier if its slow to process, when I try to upgrade your db, I turn off the moodle filters and htmlpurifier to get it upgraded.

Dongsheng Cai
added a comment - 16/Dec/10 4:10 PM Koen
> 3) Traced the problem down to some old binary contents in DB trying to be converted
Can be ignored, we had the same problems when we upgrade moodle.org
> 4) should wiki_ewiki_2_html() generate filtered contents? or unfiltered ones?
Filtered contents
> 5) to save some cycles, wiki_ewiki_2_html() also should not using caching at all.
And please disable htmlpurifier if its slow to process, when I try to upgrade your db, I turn off the moodle filters and htmlpurifier to get it upgraded.

Mark Nelson
added a comment - 24/Dec/10 3:01 PM I have also received that error on update.
"Debug info: Data too long for column 'tomissingpage' at row 1
INSERT INTO mdl_wiki_links (subwikiid,frompageid,tomissingpage) VALUES(?,?,?)
[array (
0 => '40',
1 => '40',
2 => '.<personal data removed>',
)]"
Stack trace:
line 394 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 853 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 895 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
line 302 of /mod/wiki/locallib.php: call to mysqli_native_moodle_database->insert_record()
line 274 of /mod/wiki/locallib.php: call to wiki_refresh_page_links()
line 208 of /mod/wiki/db/upgrade.php: call to wiki_refresh_cachedcontent()
line 490 of /lib/upgradelib.php: call to xmldb_wiki_upgrade()
line 265 of /lib/upgradelib.php: call to upgrade_plugins_modules()
line 1352 of /lib/upgradelib.php: call to upgrade_plugins()
line 290 of /admin/index.php: call to upgrade_noncore()
I changed the datatype to 'text' rather than varchar(255) to bypass this error.

There are bad records in 1.9, for example invalid userid or version, which cause the problem of "Duplicate entry ... for key 'mdl_wikipage_subtituse_uix'" we either fix the incorrect userid/versionid manually, or ignore the bad records (what the patch does).

I can help with recover the missing data, but I will need your database the analysis what is wrong in 1.9.

Dongsheng Cai
added a comment - 11/Jan/11 11:27 AM Jody
There are bad records in 1.9, for example invalid userid or version, which cause the problem of "Duplicate entry ... for key 'mdl_wikipage_subtituse_uix'" we either fix the incorrect userid/versionid manually, or ignore the bad records (what the patch does).
I can help with recover the missing data, but I will need your database the analysis what is wrong in 1.9.

Thanks for the offer. Since our semester is starting now, I think we'll hold off on upgrading the full site to 2.0 for a while. Is there anything in particular that I could directly query the database records for that would identify the problematic records? I might be able to clean up the wikis while still running 1.9 so that it's ready for a seamless upgrade.

Jody Paul
added a comment - 14/Jan/11 11:36 AM Thanks for the offer. Since our semester is starting now, I think we'll hold off on upgrading the full site to 2.0 for a while. Is there anything in particular that I could directly query the database records for that would identify the problematic records? I might be able to clean up the wikis while still running 1.9 so that it's ready for a seamless upgrade.

Martin Dougiamas
added a comment - 17/Jan/11 11:57 AM Jody if there's any way you can send us your database tables to look at (dongsheng@moodle.com) then it would really help us work out what to test for.

The upgrade fails here too, not with a crash anymore, but with a long list of error messages.
I see several different errors: write to database error, file path error (might be artefact in test environment, but weird that there are so many), bad data found

EDIT: the file path is wrong of course, since this upgrade comes after the migration of the files to their new location

I'll copy them here (sorry for the long list ) - but I have apparently no rights to attach a file.

I spent some time investigating course 130 (both 1.9 site and upgraded site) where all the errors I can track down happen. There seem to be things wrong in the 1.9 site. Also the course is deserted (no teachers) May be someone hit the reset button and that went wrong, I don't know.

I suggest to spend no time on this issue, unless there is something to learn there for the broad picture. Especially the write to database errors seem like they shouldn't happen.

Koen Roggemans
added a comment - 18/Jan/11 10:30 PM I spent some time investigating course 130 (both 1.9 site and upgraded site) where all the errors I can track down happen. There seem to be things wrong in the 1.9 site. Also the course is deserted (no teachers) May be someone hit the reset button and that went wrong, I don't know.
I suggest to spend no time on this issue, unless there is something to learn there for the broad picture. Especially the write to database errors seem like they shouldn't happen.

The "write to database errors" error is because there is binary data in the record, it is rejected by database, we didn't display more because when running the upgrade in cli, the binary data dump could kill the terminal.

Dongsheng Cai
added a comment - 31/Jan/11 1:37 AM Koen, thanks for inspection.
The "write to database errors" error is because there is binary data in the record, it is rejected by database, we didn't display more because when running the upgrade in cli, the binary data dump could kill the terminal.