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

Question

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

Comments

Thanks - errors like this should indeed not be exposed to the user. I'll check into this for the next maintenance release.

---

I've just been looking at this. Generally MySQL truncates silently, unless it's running in strict mode. Can you check that by running this in MySQL:

show variables like 'sql_mode';

---

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

---

Thanks, so it is running in strict mode. I will try to work around this.

Answer 1

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.

Comments

Thank you - I will apply this change in our lower environment for testing and will let you know if there continue to be problems.