Import Troubles

[Kaelon]

Uh oh. SQL Error during Wiki import. Any thoughts?

Database error in vBulletin 3.8.0 Beta 1:

Invalid SQL:

				INSERT INTO nuwiki_revision
					(threadid, userid, username, reason, dateadded, pagetext, ipaddress, deleted, minorchange)
				VALUES
					(85966, 1, 'Kaelon', '', 1223823149, '[template]Navbox[/template]\n[box=left][IMG]http://www.playbypost.com/gallery/files/1/wikibook_911551.jpg[/IMG][/box] [B][SIZE=3]Welcome to Aelyria Wiki.[/SIZE][/B] \nAelyria Wiki is the collective knowledge repository for the World of Aelyria. At the Wiki, citizens and adventurers  ... (continues on for hundreds of entries)...

MySQL Error   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Rinishad', '', 1223011591, '[COLOR=#d7cea4][FONT=Verdana]An item used primarily ' at line 6
Error Number  : 1064
Request Date  : Sunday, October 12th 2008 @ 03:53:45 PM
Error Date    : Sunday, October 12th 2008 @ 03:53:48 PM
Script        : hxxp://www.playbypost.com/forums/admincp/vaultwiki_import.php?do=import
Referrer      : hxxp://www.playbypost.com/forums/admincp/vaultwiki_import.php
IP Address    : 71.232.39.188
Username      : Kaelon
Classname     : vB_Database
MySQL Version : 5.0.51a-community-log

[Kaelon]

This appeared to happen only during the Wiki Revision Import, not the actual Wiki Articles import. Nevertheless, none of my NuWiki articles were imported in the process.

[Kaelon]

And, unfortunately, trying to run the Article Postcache doesn't seem to work. I get another SQL Error:

Database error in vBulletin 3.8.0 Beta 1:

Invalid SQL:

				DELETE FROM postcache
				WHERE postid IN (1263407,1263463,1263669,1264651,1264652,1270733,1272905,1273073,1274343,1274926,1274929,1274931,1275204,1275795,1276121,1276427,1276588,1283140,1285702,1286900,1288124,1288137,1288142,1288410,1290237,1291597,1291621,1292361,1292364,1293197,1294558,1294559,1294877,1306160,1317497,1317618,1321042,1323630,1330331,1330332,1330333,1342996,1343214,1343970,1352918,1354865,1354948,1355079,1355283);

MySQL Error   : Table 'playbypo_aelyria.postcache' doesn't exist
Error Number  : 1146
Request Date  : Sunday, October 12th 2008 @ 04:09:06 PM
Error Date    : Sunday, October 12th 2008 @ 04:09:07 PM
Script        : http://www.playbypost.com/forums/admincp/vaultwiki.php?do=uncache
Referrer      : http://www.playbypost.com/forums/admincp/vaultwiki.php?do=chooser
IP Address    : 71.232.39.188
Username      : Kaelon
Classname     : vB_Database
MySQL Version : 5.0.51a-community-log

[pegasus]

I'm guessing Rinishad has an apostrophe in his username. Okay well this is easy enough to fix because actually at that point in the import no data had been copied yet. It was still preparing info.

In admincp/vaultwiki_import.php, find:

$post[nuwiki_lastmodifiedbyname] 

Replace with:

" . $db->escape_string($post[nuwiki_lastmodifiedbyname]) . " 

[Kaelon]

I performed the changes, but once again, there seemed to be another SQL Error during Wiki Import.

Database error in vBulletin 3.8.0 Beta 1:

Invalid SQL:

			REPLACE INTO vault_revision
				(threadid, userid, dateline, pagetext, minor, reason)
			VALUES
			
		(
			75074,
			1,
			1198016596,
			[h1] Wiki vBCode ... (hundreds of lines removed)

MySQL Error   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[h1] Wiki vBCode: Creating Links in Aelyria Wiki[/h1]
This reference guide will' at line 9
Error Number  : 1064
Request Date  : Sunday, October 12th 2008 @ 04:17:26 PM
Error Date    : Sunday, October 12th 2008 @ 04:17:26 PM
Script        : http://www.playbypost.com/forums/admincp/vaultwiki_import.php?do=import
Referrer      : http://www.playbypost.com/forums/admincp/vaultwiki_import.php
IP Address    : 71.232.39.188
Username      : Kaelon
Classname     : vB_Database
MySQL Version : 5.0.51a-community-log

Much of my forum makes use of apostrophes as a part of the type of user we have; many of them have special characters in their usernames, posts, revisions, etc.

[Kaelon]

Updated with correct SQL Error from the latest import attempt.

[pegasus]

Okay, still fine. I'm not so sure why these parts of the importer weren't escaped.

In admincp/vaultwiki_import.php, find:

$values[] = "
        (
            " . $revision['threadid'] . ",
            " . $revision['userid'] . ",
            " . $revision['dateadded'] . ",
            " . $revision['pagetext'] . ",
            " . $revision['minorchange'] . ",
            " . $revision['reason'] . "
        )"; 

Replace with:

$values[$revision['threadid'] . '-' . $revision['dateadded']] = "
        (
            " . $revision['threadid'] . ",
            " . $revision['userid'] . ",
            " . $revision['dateadded'] . ",
            '" . $db->escape_string($revision['pagetext']) . "',
            " . $revision['minorchange'] . ",
            '" . $db->escape_string($revision['reason']) . "'
        )"; 

Also in admincp/vaultwiki.php, find:

DELETE FROM " . TABLE_PREFIX . "postcache 

Replace with:

DELETE FROM " . TABLE_PREFIX . "postparsed 

[Kaelon]

Applied those changes, and another error once again...

Database error in vBulletin 3.8.0 Beta 1:

Invalid SQL:

			REPLACE INTO vault_revision
				(threadid, userid, dateline, pagetext, minor, reason)
			VALUES
			 
        ( 
            75074, 
            1, 
            1198016596, 
            [h1] Wiki vBCode: Creating Links in Aelyria Wiki[/h1]\r\nThis reference guide will give all you need to know in order to link, manage, and format your own Aelyria Wiki articles. The better formatting you can achieve, the easier for others to read, and for you to edit later on. Please note that all (but the old table vBcode) existing vBcodes will function in the wiki articles.\r\n\r\n[h2] WIKI: Creating A Wiki Link[/h2]\r\nYou can create links to any of the Aelyria Wiki articles with the WIKI code. The following example will create a link to an article named Aelyria Wiki FAQ.\r\n[co de][noparse][WIKI]Aelyria Wiki FAQ[/WIKI][/noparse][/cod e]\r\nResult:\r\n\r\n[WIKI]Aelyria Wiki FAQ[/WIKI]\r\n\r\nIf the article to which the link points does not exist, the text will be in [COLOR=\"Red\"]red[/COLOR].\r\n\r\nIn addition, there is also another way to create a link in the Aelyria Wiki:\r\n[ code][noparse][[Aelyria Wiki FAQ]][/noparse][/co de]\r\n\r\n\r\n[h2] Adding Headlines[/h2]You can add headlines (headers) to a wiki article by using the following Wiki codes:\r\n[co de][noparse][h1]headline text[/h1]\r\n[h2]headline text[/h2]\r\n[h3]headline text[/h3]\r\n[h4]headline text[/h4]\r\n[h5]headline text[/h5]\r\n[h6]headline text[/h6]\r\n[h7]headline text[/h7][/noparse][/co de]\r\n\r\nAlternatively, you can enclose headline text with \"=\" sign for headlines; incrementing amount of the sings will give you sub-headline:\r\n[co de][noparse]=headline=\r\n==subheadline==\r\n===more subheadline===[/noparse][/co de]\r\n\r\n\r\n[h2] Table of Contents[/h2]If an article has 4 or more headlines, a table of contents with links to the different sections is added at the top of the article.\r\n\r\n\r\n[h2] Controlling the Table of Contents location and visibility[/h2]Aelyria Wiki will add a Table of Contents to an article whenever there are at least 4 headlines present. However, there are a couple of \'magic words\' that can alter this default behaviour:\r\n\r\nThe following magic word will hide the Table of Content regardless of the number of headlines present in the article.\r\n\r\n[IMG]http://www.nuhit.com/nh/images/nuwiki/NuWiki%20-%20NOTOC.png[/IMG]\r\n\r\nThe following magic word will instruct Aelyria Wiki to show the Table of Content if at least there is one headline in the article.\r\n\r\n[IMG]http://www.nuhit.com/nh/images/nuwiki/NuWiki%20-%20FORCETOC.png[/IMG]\r\n\r\nIf you want to change the default location of the Table of Content, use the following magic word to indicate its new location.\r\n\r\n[IMG]http://www.nuhit.com/nh/images/nuwiki/NuWiki%20-%20TOC.png[/IMG]\r\n\r\nPlease note that the NOPARSE vBCode does not function with these magic words.\r\n\r\n\r\n[h2] BOOK[/h2]The BOOK vBCode creates a link to a Aelyria Wiki Book. If the book doesn\'t exist, a link to the \'All Books\' page is created.\r\n\r\nThe syntax for BOOK is as follows:\r\n[COD E][noparse][book]book title[/book][/noparse][/CO DE]\r\n\r\nBooks are useful ways of organising multiple articles which have a common theme.  Simply click on the add to book link at the bottom of the page to add the article to a book.  Some advantages of the book system\r\n1) A book can contain both protected and unprotected pages\r\n2) You can edit the book to re-organise the chapters\r\n3) the table of contents for a book will automatically include all the other chapters as well.\r\n\r\nNote for point 3: Because vbulletin caches recently crated pages for speed, it means that if an article is added to a book the full table of contents will not be generated (until the page is a week old).  You can speed this proces up by editing a page, and simply adding or deleting a blank space or line.  THis will cause the page to be re-parsed.  Note the same goes for all the other chapters in a book, a new table of contents will not be generated for each chapter until it expires from the cache.\r\n\r\n[h2] SHOWTHREADS[/h2]Using the SHOWTHREADS vBCode will add a list of the latest thread from a forum into a Aelyria Wiki Article.\r\n\r\nThe syntax for SHOWTHREADS is as follows:\r\n[ CODE][noparse][showthreads=count]forumid[/showthreads][/noparse] [/ CODE]\r\nWhere:\r\n[LIST]\r\n[*][B]\r\n[*]count[/B] is the number of threads to list, if not specified, 10 threads are shown.\r\n[*][B]forumid[/B] is the forum id from where to list threads, if not specified, the latest thread in the forum are shown.\r\n[/LIST]\r\n\r\nHere is an example:\r\n[COD E][noparse][showthreads=5]6[/showthreads][/noparse][/COD E]\r\nWill produce:\r\n[showthreads=5]6[/showthreads]\r\n\r\n\r\n[h2] TEMPLATE[/h2]Aelyria Wiki Templates use the following syntax:\r\n[CO DE][template]Template Name[/template][/ CODE]\r\n\r\n\r\n[h2] TABLE[/h2]The TABLE vBCode enables the creation of style rich tables in your Aelyria Wiki Articles.\r\n\r\nThe syntax is as follows:\r\n[CO DE][noparse][table]\r\n[tr]\r\n[td]first cell[/td]\r\n[td]second cell[/td]\r\n[/tr]\r\n[/table][/noparse][/C ODE]\r\nEach of the opening elements can have a combination of standard Aelyria Wiki CSS Styles.\r\n\r\nFor instance\r\n[C ODE][noparse][table=class:tborder]\r\n[tr]\r\n[td=class:alt2;color:red;]first cell[/td]\r\n[td=class:alt1]second cell[/td]\r\n[/tr]\r\n[/table][/noparse][/ CODE]\r\nWill produce:\r\n[table=class:tborder]\r\n[tr]\r\n[td=class:alt2;color:red;]first cell[/td]\r\n[td=class:alt1]second cell[/td]\r\n[/tr]\r\n[/table]\r\n\r\n\r\n[h2] DIV[/h2]The DIV vBCode allows greater flexibility in editing Aelyria Wiki Articles.\r\n\r\nThe syntax is as follows:\r\n[ CODE][div=css styles]content[/div] [/CO DE]\r\nWhere[B] css styles[/B] is a semi-colon ( ; ) separated list of CSS Styles.\r\n\r\nFor instance:\r\n\r\n[COD E][noparse][div=color: red; font-weight: bold; border: 2px dashed blue; background: #FFFFC0; float: right; width:50%; text-align:center]This is some text[/div][/noparse][/CODE ]\r\n\r\nWill produce:\r\n\r\n[div=color: red; font-weight: bold; border: 2px dashed blue; background: #FFFFC0; float: right; width:50%; text-align:center]This is some text[/div]\r\n\r\n\r\n[h2] SPAN[/h2]The SPAN vBCode complements the DIV vBcode for inline elements.\r\n\r\nThe syntax is as follows:\r\n[ CODE][span=css styles]content[/span] [/CODE ]\r\nWhere [b][i]css styles[/i][/b] is a semi-colon ( ; ) separated list of CSS Styles.\r\n\r\nFor example:\r\n\r\n[ CODE][noparse][span=color: red; font-weight: bold; border: 2px dashed blue; background: #FFFFC0; text-align:center]This is some text[/span][/noparse][/CODE ]\r\n\r\nWill produce:\r\n\r\n[span=color: red; font-weight: bold; border: 2px dashed blue; background: #FFFFC0; text-align:center]This is some text[/span]\r\n\r\n\r\n[h2] FOOTNOTE[/h2]The FOOTNOTE vBCode is used to add footnotes to a Aelyria Wiki Article. Whenever a FOOTNOTE vBCode is found, it is replaced by a footnote index enclosed in square brackets (e.g. [3]), which links to the corresponding footnote that is listed at the end of the article.\r\n\r\nThe Syntax for the FOOTNOTE BBCode is:\r\n[CODE ][noparse][footnote]footnote text[/footnote][/noparse][/CODE ]\r\n\r\n\r\n[h2] KEYWORDS[/h2]\r\nThe KEYWORDS vBCode can be used to specify extra keywords related to a Aelyria Wiki Article. These keywords are displayed in a smaller font, and somewhat greyed out.\r\n[code ][noparse][keywords]keyword list[/keywords][/noparse][/c ode]\r\n\r\n\r\n[h2] [noparse]#REDIRECT[/noparse][/h2]Aelyria Wiki supports the concept of Article Redirects. Having an article which contains a #REDIRECT followed with a wiki link, will instruct Aelyria Wiki to redirect to the viewer to the new article.\r\n\r\nThe syntax is as follows:\r\n[CODE ][noparse]#REDIRECT [wiki]article title[/wiki][/noparse] [/CODE ]\r\n\r\n\r\n[h2] FIELD[/h2]The FIELD vBCode allows the use of custom thread fields within the text of a Aelyria Wiki Article.\r\n\r\nThe syntax for the FIELD BBCode is:\r\n[ CODE][noparse][field]field friendly name[/field][/noparse][ /CODE], 
            0, 
             
        ), 

... (several hundred lines) ...

MySQL Error   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[h1] Wiki vBCode: Creating Links in Aelyria Wiki[/h1]\r\nThis reference guide wi' at line 9
Error Number  : 1064
Request Date  : Sunday, October 12th 2008 @ 04:27:35 PM
Error Date    : Sunday, October 12th 2008 @ 04:27:37 PM
Script        : [url]http://www.playbypost.com/forums/admincp/vaultwiki_import.php?do=import[/url]
Referrer      : [url]http://www.playbypost.com/forums/admincp/vaultwiki_import.php[/url]
IP Address    : 71.232.39.188
Username      : Kaelon
Classname     : vB_Database
MySQL Version : 5.0.51a-community-log

Thoughts?

[pegasus]

Haha, typo. I updated the first find/replace code in my last post.

[Kaelon]

We finally made it to Importing Book Data, but... guess what?

Database error in vBulletin 3.8.0 Beta 1:

Invalid SQL:

		INSERT INTO vault_revision
			(threadid, userid, reason, dateline, pagetext, minor, section, rollback, pending)
		VALUES
		(
			0,
			1,
			'',
			1223843779,
			'',
			0,
			'',
			0,
			0
		);

MySQL Error   : Duplicate entry '0-1223843779' for key 2
Error Number  : 1062
Request Date  : Sunday, October 12th 2008 @ 04:36:19 PM
Error Date    : Sunday, October 12th 2008 @ 04:36:22 PM
Script        : http://www.playbypost.com/forums/admincp/vaultwiki_import.php?do=import
Referrer      : http://www.playbypost.com/forums/admincp/vaultwiki_import.php
IP Address    : 71.232.39.188
Username      : Kaelon
Classname     : vB_Database
MySQL Version : 5.0.51a-community-log

[Kaelon]

Should I empty out some tables, or uninstall the product, and reinstall and try again, or...?

[Kaelon]

Another interesting bug (vbSEO-related), is that on the forum display where the NameSpace for Aelyria wiki is showing, the "Last Post" links to a non-existent URL: http://www.playbypost.com/.php?=&goto=newpost

Thoughts?

[pegasus]

So far you don't have to worry about that. I just hope there aren't any more issues with the importer. We recently added backwards compatibility with MySQL 4 and that's how most of these issues cropped up.

And I don't think that other bug is vbSEO related. It may be because you are still importing the wiki and the article cache is currently corrupt.

[Kaelon]

So far you don't have to worry about that. I just hope there aren't any more issues with the importer. We recently added backwards compatibility with MySQL 4 and that's how most of these issues cropped up.

Thanks. Did you see my latest error? It looks like it craps out during the Book Import due to co-existing values.

Database error in vBulletin 3.8.0 Beta 1:

Invalid SQL:

		INSERT INTO vault_revision
			(threadid, userid, reason, dateline, pagetext, minor, section, rollback, pending)
		VALUES
		(
			0,
			1,
			'',
			1223843779,
			'',
			0,
			'',
			0,
			0
		);

MySQL Error   : Duplicate entry '0-1223843779' for key 2
Error Number  : 1062
Request Date  : Sunday, October 12th 2008 @ 04:36:19 PM
Error Date    : Sunday, October 12th 2008 @ 04:36:22 PM
Script        : http://www.playbypost.com/forums/admincp/vaultwiki_import.php?do=import
Referrer      : http://www.playbypost.com/forums/admincp/vaultwiki_import.php
IP Address    : 71.232.39.188
Username      : Kaelon
Classname     : vB_Database
MySQL Version : 5.0.51a-community-log

[pegasus]

Delete any threads that have been added to your Book namespace.

In vault/special_plugins.php, find (in function special_article_creator):

    vault_article_modify($threadinfo['threadid'], $vbulletin->userinfo['userid'], TIMENOW);

    vault_create_revision(
        $threadinfo['threadid'],
        $vbulletin->userinfo['userid'],
        '',
        TIMENOW,
        $newpost['message']
    ); 

Replace with:

    vault_article_modify($newpost['threadid'], $vbulletin->userinfo['userid'], TIMENOW);

    vault_create_revision(
        $newpost['threadid'],
        $vbulletin->userinfo['userid'],
        '',
        TIMENOW,
        $newpost['message']
    );