Error in creating table during integrated WP install (Q2A version 1.4)

Question

Question2Answer was unable to perform the installation query below. Please check the user in the config file has CREATE and ALTER permissions:

CREATE TABLE qa_categories (categoryid INT UNSIGNED NOT NULL AUTO_INCREMENT, parentid INT UNSIGNED, title VARCHAR(80) NOT NULL, tags VARCHAR(200) NOT NULL, content VARCHAR(800) NOT NULL DEFAULT '', qcount INT UNSIGNED NOT NULL DEFAULT 0, position SMALLINT UNSIGNED NOT NULL, backpath VARCHAR(804) NOT NULL DEFAULT '', PRIMARY KEY (categoryid), UNIQUE parentid (parentid, tags), UNIQUE parentid_2 (parentid, position), KEY backpath (backpath(200))) ENGINE=InnoDB CHARSET=utf8

Error 1074: Column length too big for column 'content' (max = 255); use BLOB or TEXT instead

Comments

I have all the permissions required for creating/updating/deleting the database but I am getting the following error:

---

Additional info:

PHP 5.1.4
MySQL 4.1.22

Using phpMyAdmin, I found the qa_cookies and qa_userfields tables in my WordPress database, but no others.

Is there any other info I can provide to help with troubleshooting?

Answer 1

This is due to a bug in MySQL 4.1.22, which fails to automatically convert VARCHAR columns longer than 255 bytes into a TEXT column, as specified in the manual.

The solution for now is to modify the column definitions in the function qa_db_table_definitions(...) in qa-db-install.php, to use TEXT columns instead. For example, change this line:

'content' => 'VARCHAR('.QA_DB_MAX_CAT_CONTENT_LENGTH.') NOT NULL DEFAULT \'\'', // description of category

... to ...

'content' => 'TEXT NOT NULL DEFAULT \'\'', // description of category

Other similar problems should be treated in the same way.

Comments

Thanks for getting back to me so quickly. I can only imagine how frustrating it must be to deal with legacy MySQL. Unfortunately, I'm on a shared host.

I went through qa-db-install.php and changed all the VARCHARs that were throwing errors to TEXT, as instructed. I'm now getting a different error. The first screen I get is this:

--

Question2Answer query failed:

(SELECT '0' AS selectkey, qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type,1) AS basetype, INSTR(qa_posts.type, '_HIDDEN')>0 AS hidden, qa_posts.acount, qa_posts.selchildid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, BINARY qa_posts.title AS title, BINARY qa_posts.tags AS tags, UNIX_TIMESTAMP(qa_posts.created) AS created, BINARY qa_categories.title AS categoryname, BINARY qa_categories.backpath AS categorybackpath, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip) AS createip, qa_userpoints.points, NULL AS obasetype, NULL AS opostid, NULL AS ouserid, NULL AS ocookieid, NULL AS oip, NULL AS otime, NULL AS oflagcount, NULL AS opoints, NULL AS parentid, NULL AS qcount, NULL AS position, NULL AS content, NULL AS pageid, NULL AS flags, NULL AS nav, NULL AS widgetid, NULL AS place FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN (SELECT postid FROM qa_posts WHERE type=_utf8 'Q' ORDER BY qa_posts.created DESC LIMIT 0,50) y ON qa_posts.postid=y.postid) UNION ALL (SELECT '1', qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type,1), INSTR(qa_posts.type, '_HIDDEN')>0, qa_posts.acount, qa_posts.selchildid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, BINARY qa_posts.title, BINARY qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created), BINARY qa_categories.title, BINARY qa_categories.backpath, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip), qa_userpoints.points, 'A', aposts.postid, aposts.userid, aposts.cookieid, INET_NTOA(aposts.createip), UNIX_TIMESTAMP(aposts.created), aposts.flagcount, auserpoints.points, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN qa_posts AS aposts ON qa_posts.postid=aposts.parentid LEFT JOIN qa_userpoints AS auserpoints ON aposts.userid=auserpoints.userid JOIN (SELECT postid FROM qa_posts WHERE type=_utf8 'A' ORDER BY qa_posts.created DESC LIMIT 0,50) y ON aposts.postid=y.postid WHERE qa_posts.type!='Q_HIDDEN') UNION ALL (SELECT '2', NULL, qa_categories.categoryid, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BINARY qa_categories.title, BINARY qa_categories.tags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, qa_categories.parentid, qa_categories.qcount, qa_categories.position, NULL, NULL, NULL, NULL, NULL, NULL FROM qa_categories JOIN (SELECT NULL AS parentkey UNION SELECT grandparent.parentid FROM qa_categories JOIN qa_categories AS parent ON qa_categories.parentid=parent.categoryid JOIN qa_categories AS grandparent ON parent.parentid=grandparent.categoryid WHERE qa_categories.backpath=_utf8 '' UNION SELECT parent.parentid FROM qa_categories JOIN qa_categories AS parent ON qa_categories.parentid=parent.categoryid WHERE qa_categories.backpath=_utf8 '' UNION SELECT parentid FROM qa_categories WHERE backpath=_utf8 '' UNION SELECT categoryid FROM qa_categories WHERE backpath=_utf8 '') y ON parentid<=>parentkey ORDER BY qa_categories.position) UNION ALL (SELECT '_options', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, title, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BINARY content, NULL, NULL, NULL, NULL, NULL FROM qa_options) UNION ALL (SELECT '_time', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, UNIX_TIMESTAMP(NOW()), NULL, NULL, NULL, NULL, NULL) UNION ALL (SELECT '_navpages', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BINARY title, BINARY tags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, position, NULL, pageid, flags, nav, NULL, NULL FROM qa_pages WHERE nav IN (_utf8 'B',_utf8 'M',_utf8 'O',_utf8 'F') ORDER BY position) UNION ALL (SELECT '_widgets', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, BINARY title, tags, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, position, NULL, NULL, NULL, NULL, widgetid, place FROM qa_widgets ORDER BY position)

Error 1146: Table '<TABLE NAME DELETED>' doesn't exist

One or more tables are missing from your Question2Answer database.

--

When I select Repair Database, I get:

--

Question2Answer was unable to perform the installation query below. Please check the user in the config file has CREATE and ALTER permissions:

CREATE TABLE qa_categories (categoryid INT UNSIGNED NOT NULL AUTO_INCREMENT, parentid INT UNSIGNED, title TEXT NOT NULL DEFAULT '', tags TEXT NOT NULL DEFAULT '', content TEXT NOT NULL DEFAULT '', qcount INT UNSIGNED NOT NULL DEFAULT 0, position SMALLINT UNSIGNED NOT NULL, backpath TEXT NOT NULL DEFAULT '', PRIMARY KEY (categoryid), UNIQUE parentid (parentid, tags), UNIQUE parentid_2 (parentid, position), KEY backpath (backpath(200))) ENGINE=InnoDB CHARSET=utf8

Error 1170: BLOB/TEXT column 'tags' used in key specification without a key length

---

Yes, in the same function you also need to modify the keys on tables to specify a prefix length that is indexed. In this case you need to replace:

UNIQUE parentid (parentid, tags)

... with ...

UNIQUE parentid (parentid, tags(200))

There may be other similar cases too.

---

Success! Thank you!