Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

Data too long for column 'word' error on long URL in question

+4 votes
728 views
asked Jun 20, 2012 in Q2A Core by ScottCher

I've got a user that posted a very long URL as text into a question body.  When Question2Answer parses the message, it inserts every word from the message into the qa_words table but this very long URL exceeds the length constraints for the word field in the table so that process pops an error (see below for the error).

My preference would be to (in no particular order):

  • Not pop this rather detailed error message to the user but instead swallow it and perhaps log it (maybe send an email to an admin) instead.
  • Have the word parsing routine test the length of each word before attempting to add it to avoid the error all-together - I'd rather lose some indexing capability on obvisously non-sensical words than lose all indexing for a particular question.
  • Have the word parsing routine recognize URL's and skip them during this indexing process.

I've alread recommended to the user that he use TinyUrl (or some other algorithm) or post the URL as a embedded URL on some label text in the message instead but I'd rather avoid the problem no matter what the user does.

By the way, when I tried to add the URL as a link on some label text, I couldn't - the form wouldn't let me hit the OK button on the link dialog after I'd pasted in the long URL.

Pops a very nice, detailed error message (great for an admin, probably not so great for exposing  the underbelly of the machine - yes, I get its open source for everyone to see already.):

Question2Answer query failed:

INSERT INTO qa_words (word) VALUES <values removed for brevity, including the long URL>

Error 1406: Data too long for column 'word' at row 66

Q2A version: 1.5.2
commented Aug 24, 2012 by ScottCher
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
commented Aug 25, 2012 by gidgreen
Thanks, so it is running in strict mode. I will try to work around this.

1 Answer

+1 vote
answered Sep 12, 2012 by gidgreen
selected Apr 17, 2013 by ScottCher
 
Best answer

OK, the solution to this is pretty straightforward. In qa-db-post-create.php, change the following line:

qa_db_query_sub('INSERT INTO ^words (word) VALUES $', $rowstoadd);

... to ...

qa_db_query_sub('INSERT IGNORE INTO ^words (word) VALUES $', $rowstoadd);

This fix will be rolled into Q2A 1.5.3.

commented Sep 12, 2012 by ScottCher
Thank you - I will apply this change in our lower environment for testing and will let you know if there continue to be problems.
...