Problem with German Umlauts after Upgrade to 2.1.3

[HelAu007]

Hello
I just upgraded from 2.0.17 to 2.1.3
The upgrade didnt finished completey it stopped during last step (db migration to UTF8)
I have done the UTF8 migration with phpmyadmin and all collations and data are changed to utf8mb3_collation_ci.
When I look at old threads I can see that lowcase Umlauts are fine, but Uppercase are not displayed.
Even when I type a new messge its showing fine during typing but when I press preview the uppercase Characters get scrambled. When I gress submit a database error occurs :
Incorrect string value: '\xC3\xEF\xBF\xBD\xC3\xEF...' for column 'body' at row 1
Datei: .../Sources/Subs-Post.php
Zeile: 1897
I have added my php-info settings, any hints where to look at ?
Kind regards
Helmut

[Sesquipedalian]

I suspect that there is a mismatch somewhere between between your database tables and your SMF settings.

The best way to fix this would be to restore the 2.0.17 version your forum from a backup, run the Convert to UTF-8 function in the administration centre, and then upgrade to SMF 2.1.3 again.

If that isn't possible, you'll need to check the configuration of SMF and your database yourself.

Regarding the SMF settings, please check the following:

• In the smf_settings table in the database, the global_character_set setting should have a value of UTF-8. If it isn't, fix it. (Note: the "smf_" prefix on the table name might have been replaced with something else.)
• In your language files, open the index.{language}.php file and check whether $txt['lang_character_set'] is set to UTF-8. If not, don't change it. Instead, use SMF's administration centre to uninstall the entire language package and then install the latest version of it.
• In your Settings.php file, make sure that $db_character_set is set to UTF-8. If it isn't, fix it.

Regarding your database tables, you need to check that the character encoding and collation of the database as a whole, every table, and every column of every table are all set to the appropriate values. It is possible to have different encodings in use in different columns and tables within the same database. Because you changed the tables yourself, it is possible that you missed something.

[HelAu007]

Hello,

Thanks for your hints !
Converting the version 2.0.17 to UTF is failing with:

Code Select Expand

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod mod varbinary(32) NOT NULL default '',
CHANGE COLUMN subject subject ' at line 4
File: /var/www/vhosts/hosting123155.a2fc7.netcup.net/httpdocs_qdtest/Sources/ManageMaintenance.php
Line: 664

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.7. The above error might possibly go away if you execute the latest version of upgrade.php.
If i do the command line upgrade anyway this one fails with

Code Select Expand

Database Error: Data too long for column 'username' at row 2404 
                                        ALTER TABLE smf_alerts
                                                                CHANGE COLUMN `message` `message` varbinary(100) NOT NULL default '',
                                                                CHANGE COLUMN `avatar` `avatar` longblob NOT NULL,
                                                                CHANGE COLUMN `mod` `mod` varbinary(32) NOT NULL default '',
                                                                CHANGE COLUMN `subject` `subject` varbinary(256) NOT NULL default '',
                                                                CHANGE COLUMN `username` `username` varbinary(30) NOT NULL default ''
Is there a way to convert 2.0.17 to UTF-8 manually from commandline ?

[Sesquipedalian]

The error you encountered while trying to convert to UTF-8 suggests that there is something malformed about one of your tables. Specifically, it is a table that has a column named "mod" in it. Unfortunately, the MySQL error doesn't tell us which table caused the problem, so we will have to figure that out for ourselves.

There are no tables in a standard SMF 2.0 installation that contain a "mod" column, so this table was probably added by a modification that you installed at some point. To figure out which table is causing the problem, you will need to look through your database and find any tables that are not standard SMF tables, and then check each of those tables for a "mod" column. Once we know which table is causing the problem, we can work on fixing it.

For reference, here is the list of standard SMF 2.0 tables. Again, the "smf_" prefix on the table names may be different in your particular database.

Code Select Expand

smf_admin_info_files
smf_approval_queue
smf_attachments
smf_ban_groups
smf_ban_items
smf_board_permissions
smf_boards
smf_calendar
smf_calendar_holidays
smf_categories
smf_collapsed_categories
smf_custom_fields
smf_group_moderators
smf_log_actions
smf_log_activity
smf_log_banned
smf_log_boards
smf_log_comments
smf_log_digest
smf_log_errors
smf_log_floodcontrol
smf_log_group_requests
smf_log_karma
smf_log_mark_read
smf_log_member_notices
smf_log_notify
smf_log_online
smf_log_packages
smf_log_polls
smf_log_reported
smf_log_reported_comments
smf_log_scheduled_tasks
smf_log_search_messages
smf_log_search_results
smf_log_search_subjects
smf_log_search_topics
smf_log_spider_hits
smf_log_spider_stats
smf_log_subscribed
smf_log_topics
smf_mail_queue
smf_membergroups
smf_members
smf_message_icons
smf_messages
smf_moderators
smf_openid_assoc
smf_package_servers
smf_permission_profiles
smf_permissions
smf_personal_messages
smf_pm_recipients
smf_pm_rules
smf_poll_choices
smf_polls
smf_scheduled_tasks
smf_sessions
smf_settings
smf_smileys
smf_spiders
smf_subscriptions
smf_themes
smf_topics
Once you have found the table that contains the "mod" column, there are two possible ways forward:

• We try to fix whatever is wrong with that table. For this, we'll need more information about the table's structure. You should be able to get the table structure information via phpMyAdmin (or whatever MySQL tools you use).
• Simply uninstall whatever modification created that table, and select the option to delete the modification's data during the uninstallation process. That should delete the problematic table.

The second option is certainly the easiest solution. The downside is that you will lose whatever data that table held. However, if that modification is not supported on SMF 2.1, it won't matter because you will no longer be able to use it anyway. So, once you know which modification added that table, check whether it is supported in SMF 2.1. If not, definitely choose option 2.