Goal of creation this issue report first of all is a some place where to describe everything.
Probably we could add some notes to documentation but we will discuss this in comments.
In v1.6 DB schema has 5 columns with "text" type:
Just a note - v1.8 schema has the same 5 "text" columns, v2.0 has much more "text" columns.
If perform conversion to UFT8 (designed in upgrade 1.6->1.8) they all will get new "mediumtext" type.
See a nice article http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/
After applying 1.6->1.8 SQL patch there will remain only 4 such columns:
Then after applying 1.8->2.0 SQL patch there will remain only 3 such columns:
What a problem is here? See:
Sometimes people need to check DB schema to make sure that all is correct.
And if such inconsistencies appear then we have a question - what is that?, when and why it appeared ?
To make sure that all is fine we need to know exact reason of this inconsistency and fix it.
It's NOT very easy to figure out pure reason.
Some real scenarios:
#1 user started to use v1.6 and in V2.0 got 3 "mediumtext" columns.
#2 user skipped UTF8 conversion (during 1.6->1.8) for some reason and did fix it now and got:
- 4 columns - if convert in v1.8 a DB created in 1.6;
- 5 columns - if convert in v1.8 a DB created in 1.8;
- a lot of columns if convert in v2.0
#3 user created v1.8+ database accidentally without specifying "... character set utf8". then see scenario #2
How to fix.
for scenario #1, #2:
If you have up to 5 columns with the "mediumtext", here is ready SQLs (if currently used schema is v2.0):
for scenario #2, #3:
Need to convert all tables, repeat this SQL for all tables:
and then check and change "mediumtext" to "text" as shown above for ALL appeared "mediumtext" columns.