Related issues #9247 and maybe also #9251

#9251 could be related because some of the database columns affected by what I describe below belog to the extensions table.

Priority = urgent because has to be solved before 3.5.0 release.

Steps to reproduce the issue

1. Update a Joomla! 3.4.8 to 3.5.0 Beta 3.
2. After successful update go to "Extensions -> Manage -> Database"
3. You will see a database problem reported that the conversion to "UTF-8 Multibyte (utf8mb4)" or "UTF-8" (which of these 2 depends on if your database server and client api support utf8mb4 or not) has not been done yet.
4. Click the "Fix" button to solve the problem.

Expected result

All database problems are solved.

Actual result

A new problem occurs:

Table '#__user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014)

(replace "#__" by your database prefix)

System information (as much as possible)

Not relevant, happens on all kinds of systems when updating a 3.4.8 (or previous) to 3.5.0 Beta (1 or 2 or 3) with any method (Joomla! Update component or Extensions Installer - Upload and install package).

Additional comments

The reason for this behavior is that the statemens

ALTER TABLE #__table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

result on a Joomla! where data is still in utf8 (without mb4) in columns of data type TEXT being changed to MEDIUMTEXT and columns of data type MEDIUMTEXT being changed to LONGTEXT by MySQL, see MySQL bug report https://bugs.mysql.com/bug.php?id=31291 where is clarified that it's not a bug but a feature.

This is a general problem affecting many table columns (see list below).

The reason why only the column mentioned above is reported up to now is that this is the only one for which is some update sql with a data type change.

But as soon as in future other columns will be changed in update sqls, those may have the problem, too.

My hopefully complete analysis has given me a long list of Joomla! core table columns being affected (see list below).

There is no easy way out without data loss, because the joomla.sql for new installation defines these data types for the columns as before but in the for both kinds of databases with or without utf8mb4 support.

If we make these bigger in the joomla.sql and in some update sql file, too, then this results also in bigger data types when being run on a non-utf8mb4 database, where then the max allowed lengths for some column will really be used.

If then later the database is migrated to a server supporting utf8mb4, the bigger data types will then be again changed to the next bigger one. And so again the problem occurs and we end up in fure all being of type "VERY BIG LONG ENDLESS MEGA TEXT" (if this data type exists in mySQL

One solution could be to have different joomla.sql for both kinds of databases, we could make the one for utf8mb4 using the bigger types but the one for non-utf8mb4 suign the smaller ones, and handle updating with the utf8(mb4) conversion procedure. This would be one way out but maybe not accepted by PLT and users beause having 2 installation sqls.

The other solution is easier but causes data loss:

At the end of the conversion, we change the data types back to what they were. But if somone has used max lenghts for some column's data all the time before, this causes data being truncated.

It may differ from column to column which it the best way, e.g. for params column where we know they were by far big enough all the time so we not lose anything when truncating back, we could say we just change back to the old type, as menioned the 2nd way.

But for columns like metadata or metadesc this may not be acceptable, so for those we would need the 1st way to handle them.

Both of these solutions are not nice to maintain because when in future new tables or table columns are added or existing ones are changed in some update sql, the statement to handle the unwanted type change on utf8mb4 conversion has to be added to the administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion-02.sql at the end.

Or another easy solution could be that we make the data types bigger in joomla.sql and a new update sql in general and make the installer database model (or was it the database driver?) which replaces "utf8mb4" by "utf8" in SQL statements before execution be clever enough to change the data types in case of no utf8mb4 support to the smaller one, which is then the one used before 3.5. But this is not easy, we should not just replace "MEDIUMTEXT" by "TEXT" and "LONGTEXT" by "MEDIUMTEXT" wherever it occurs in case of no utf8mb4 support, or could we?

This replacement should be of course done only on DDL statements and not on insert or update or so, we should implement a way to exclude that.

If that would be acceptable it would be not complicated.

To make the decision by PLT or experts easier here now the list on affected tables.colums:

1. Those which automatically changed from TEXT to MEDIUMTEXT at character set conversion

   #__banners.description
   #__banners.metakey
   #__banners.params
   #__banner_clients.extrainfo
   #__banner_clients.metakey
   #__categories.params
   #__contact_details.address
   #__contact_details.params
   #__contact_details.metakey
   #__contact_details.metadesc
   #__contact_details.metadata
   #__content.images
   #__content.urls
   #__content.metakey
   #__content.metadesc
   #__content.metadata
   #__content_types.rules
   #__content_types.field_mappings
   #__extensions.manifest_cache
   #__extensions.params
   #__extensions.custom_data
   #__extensions.system_data
   #__finder_filters.data
   #__languages.metakey
   #__languages.metadesc
   #__menu.params
   #__messages.message
   #__modules.content
   #__modules.params
   #__newsfeeds.params
   #__newsfeeds.metakey
   #__newsfeeds.metadesc
   #__newsfeeds.metadata
   #__newsfeeds.description
   #__newsfeeds.images
   #__overrider.string
   #__tags.params
   #__tags.images
   #__tags.urls
   #__template_styles.params
   #__ucm_content.params
   #__ucm_content.images
   #__ucm_content.urls
   #__ucm_content.metakey
   #__ucm_content.metadesc
   #__updates.description
   #__updates.data
   #__updates.detailsurl
   #__updates.infourl
   #__update_sites.location
   #__users.params
   #__user_notes.body
   #__user_profiles.profile_value

2. Those which automatically changed from MEDIUMTEXT to LONGTEXT at character set conversion

   #__categories.description
   #__contact_details.misc
   #__content.introtext
   #__content.fulltext
   #__finder_filters.params
   #__session.data
   #__tags.description
   #__ucm_content.body
   #__ucm_history.version_data