change varchar to longtext

Question

hi

first of all thank you for your awesome Q2A system and sorry for my weak english writing skill(i'm persian)

i want to change 8000 content character limitation.i try that by change db/maxima.php definitions.but not work(just remove "8000 character limitation error message" and cut the content automatically)

next i try to change database qa_post:content but if i increase 8000 to more than 20000 it change automatic VARCHAR to "mediumtext" type.

i need more than 20000.how can get that?

i change Varchar to Longtext and content send complete.is it problem with change varchar to longtext?(in present and future(if i want install plugins and update core and etc)

what is diffrence between varchar and text(exactly longtext)?

thank you VERY much

Answer 1

After all, you are what characters (not bytes) required for contents? The maximum number of characters that can be stored in VARCHAR is 21845 UTF characters (not bytes). If 20,000 characters is enough, you should change content to VARCHAR(20000). If you need any more, you should use MEDIUMTEXT (LONG VARCHAR).

Basically, even if you have changed field type and field size, failure will not occur. However, surely the performance will be degraded. The extent of degradation of performance will be dependent on your hardware. If high spec CPU is equipped on your server, degradation would be a little.

In addition, if you changed program OR database schema, there is a need to be careful to upgrading core and  adding plugins. For example, if you add specific plugin which is necessary more than 8000 characters in content field, that plugin will change content field with "ALTER" query. In other words, the changes you make will be overwritten by the plugin. This change is carried out in a place and timing where it is not visible to your eyes. So, you may not notice that. This is one example, but there is such risk.

Comments

thank you
my site is in persian language.(my collation in database is "utf8_general_ci")
i change content type to mediumtext.
if i check database "qa_posts:content" after installing each plugins and updating q4a versions for be sure no changes occured,everythings is ok?
additional qeustions:
i see two table in database "qa_postmeta " and "qa_postmetas"
1.what are "qa_postmeta " and "qa_postmetas"?
"qa_postmetas" has a row name "content" and it's type is "varchar(8000)"
2.i must change that to mediumtext too?
thank you VERY much

---

If you're worried that problem occurs, I recommend that you test on the local server. qa_postmetas is table for storing extra (custom) fields of question.
Refer to "Admin" > "Posting" > "Custom field for extra information on ask form:"
And my plugin (http://www.question2answer.org/qa/23346).
If you want to allow more than 8000 characters in the extended field, you also need to change it. However, in many case it would be unnecessary.

Answer 2

You'll need to alter the table and modify the QA_DB_MAX_CONTENT_LENGTH constant:

ALTER TABLE `qa_posts` 
CHANGE COLUMN `content` `content` VARCHAR(32000) NULL DEFAULT NULL;

@define('QA_DB_MAX_CONTENT_LENGTH', 32000);

Regarding the automatic switching from VARCHAR to MEDIUMTEXT, note that a MySQL row can be only 65535 bytes (not counting BLOB/TEXT columns). If you try to change a column that would result a the total row size to exceed that limit, MySQL automatically prevents that situation by turning it into a MEDIUMTEXT. You can play with the values and find out which is the limit but I would leave it as a MEDIUMTEXT.

There is a related question here: http://www.question2answer.org/qa/39622

Also note that changing the value of QA_DB_MAX_CONTENT_LENGTH after install will only affect the checkings of questions, answers and comments length. If you modify this value before setting up the database it will change the length of these fields too ^options.content, ^posts.content (this one is the one you're looking for), ^usernotices.content and ^messages.content.

Finally, the obvious comment: backup everything before you change your DB structure :)

Comments

thank you but ican't raise moe than 20000
my questions are
1."what happen if i change that to longtext?"
2."what diffrence with varchar and longtext?"
3"which things mus i notice in futuer for upgrading q2a version and installing plugins
thank you very much

---

0. "thank you but ican't raise moe than 20000" => If you read the answer again you'll notice I have explained why this happens
1. and 2. If you read the linked related question in my answer you'll see I answer this questions too
3. Not sure what you mean but of course you might have issues in the future if some plugin assumes this is a varchar. I hardly believe you could have any issue, though, as the value has been increased and not decreased

---

sorry.your answer is very good.problem is me.
i read your answere and link first time(and now again).but i can't understand my certain question answere.(i'm newbie).i'm afraid of change that and bad things happen for my site
i just want a certain answere
"can i change varchar to longtext without any problem(now and in future)?"
1.what happen if a plugin assumes this is varchar(but be longtext)
2.what must to do before install each plugin(check which files or change which datas)
and this question again for q2a core(what happen when i change that(any problem) and what must to do before updating q2a version each time)
---------
sorry.I'm not english..i try hard ( :-(  )to explain my question.hope you understand my question(and HOPE i can undrestand your answere)

---

0. You can NEVER be 100% sure that you won't have any problem after changing that. Anyway, it is VERY unlikely that something bad will happen.
1. Depends 100% on each plugin.
2. Unless you're capable of reading and understanding the code changes between different Q2A versions and the plugins' code themselves, then you'll have to ask here in the forum and trust the answers you get. No plugin SHOULD break anything, as I mentioned in item 0, but the most likely thing that could happen is that the core increases this value, for example, to 10000. This would mean for you that the value will actually be decreased so you would have to apply these changes back. The thing is that if this value is decreased to 10000, then you will lose the data over the 10000 characters.

So, in short, anything COULD happen so you can not be 100% sure that nothing will break if you change the core. The good thing is it is up to you if you want to take the risk.