Проблема с конвертацией на UTF-8

[unixanet]

Хотел апгрейдить с 3.4.9 до IPS Community Suite 4.1.7 (местный мафиозный нулл).

Все хуки отключил, сделал бэкап.

Залил новые файли, потом начинаю апгрейдить. Все хорошо до того момента, как просит конвертировать БД в УТФ-8.

Запускаю конвертор, тот идет до Conversion in progress (36.84% complete) и останавливаеться. В - admin/convertutf8/index.php?controller=tools вижу такую информацию:

Info

IP.Board Character Set: utf-8
Database Character Set: utf8mb4
Original table prefix:
Converted table prefix: x_utf_
195 tables are latin1
6 tables are utf8mb4
78 tables are utf8
263 tables have incorrect collations
Can use 'dump' method: true

 

Нажимаю Check and Fix, получаю такую ошыбку - Error: Specified key was too long; max key length is 1000 bytes File: /system/Db/Db.php Line: 199

Пробовал осилить File: /system/Db/Db.php Line: 199 Но так и не понял в чем проблема.

Может стоит руками менять кодировку? Если да, то подскажите какую кодировку ставить для БД, и Collation для таблиц.

[Xuc]

эта ошибка как правило возникает когда ограницено время сессии php и время подключения к мускулу

прогоните для начала вот этим скриптом, потом попробуйте еще раз конвертером в установщике

скрипт:

<?
$dbhost = #sql_host
$dbuser = #sql_user
$dbpass = #sql_pass
$dbname = #sql_database

header('Content-type: text/plain');

$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die( mysql_error() );
$db = mysql_select_db($dbname) or die( mysql_error() );

$sql = "ALTER DATABASE `".$dbname."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$result = mysql_query($sql) or die( mysql_error() );
print "Database changed to UTF-8.\n";

$sql = 'SHOW TABLES';
$result = mysql_query($sql) or die( mysql_error() );

while ( $row = mysql_fetch_row($result) )
{
$table = mysql_real_escape_string($row[0]);
$sql = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci, 
  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
mysql_query($sql) or die( mysql_error() );
print "$table changed to UTF-8.\n";
}

mysql_close($dbconn);
?>

если БД очень большая

рекомендую конвертировать БД этой прогой на компе

 

 

[unixanet]

Спасибо за быстрый ответ!

Прогонял скриптом -

Database changed to UTF-8.
admin_login_logs changed to UTF-8.
admin_logs changed to UTF-8.
admin_permission_rows changed to UTF-8.
announcements changed to UTF-8.
api_log changed to UTF-8.
api_users changed to UTF-8.
attachments changed to UTF-8.
attachments_type changed to UTF-8.
backup_log changed to UTF-8.
backup_queue changed to UTF-8.
backup_vars changed to UTF-8.
badwords changed to UTF-8.
banfilters changed to UTF-8.
bbcode_mediatag changed to UTF-8.
bulk_mail changed to UTF-8.
cache_simple changed to UTF-8.
cache_store changed to UTF-8.
captcha changed to UTF-8.
ccs_attachments_map changed to UTF-8.
ccs_block_wizard changed to UTF-8.
ccs_blocks changed to UTF-8.
ccs_containers changed to UTF-8.
ccs_custom_database_1 changed to UTF-8.
ccs_custom_database_2 changed to UTF-8.
ccs_database_categories changed to UTF-8.
ccs_database_comments changed to UTF-8.
ccs_database_fields changed to UTF-8.
ccs_database_moderators changed to UTF-8.
ccs_database_modqueue changed to UTF-8.
ccs_database_ratings changed to UTF-8.
ccs_database_revisions changed to UTF-8.
ccs_databases changed to UTF-8.
ccs_folders changed to UTF-8.
ccs_menus changed to UTF-8.
ccs_page_templates changed to UTF-8.
ccs_page_wizard changed to UTF-8.
ccs_pages changed to UTF-8.
ccs_revisions changed to UTF-8.
ccs_slug_memory changed to UTF-8.
ccs_template_blocks changed to UTF-8.
ccs_template_cache changed to UTF-8.
content_cache_posts changed to UTF-8.
content_cache_sigs changed to UTF-8.
conv_apps changed to UTF-8.
conv_link changed to UTF-8.
conv_link_pms changed to UTF-8.
conv_link_posts changed to UTF-8.
conv_link_topics changed to UTF-8.
core_applications changed to UTF-8.
core_archive_log changed to UTF-8.
core_archive_restore changed to UTF-8.
core_archive_rules changed to UTF-8.
core_editor_autosave changed to UTF-8.
core_geolocation_cache changed to UTF-8.
core_hooks changed to UTF-8.
core_hooks_files changed to UTF-8.
core_incoming_email_log changed to UTF-8.
core_incoming_emails changed to UTF-8.
core_inline_messages changed to UTF-8.
core_item_markers changed to UTF-8.
core_item_markers_storage changed to UTF-8.
core_like changed to UTF-8.
core_like_cache changed to UTF-8.
core_rss_imported changed to UTF-8.
core_share_links changed to UTF-8.
core_share_links_log changed to UTF-8.
core_soft_delete_log changed to UTF-8.
core_sys_bookmarks changed to UTF-8.
core_sys_conf_settings changed to UTF-8.
core_sys_cp_sessions changed to UTF-8.
core_sys_lang changed to UTF-8.
core_sys_lang_words changed to UTF-8.
core_sys_login changed to UTF-8.
core_sys_module changed to UTF-8.
core_sys_settings_titles changed to UTF-8.
core_tags changed to UTF-8.
core_tags_cache changed to UTF-8.
core_tags_perms changed to UTF-8.
core_uagent_groups changed to UTF-8.
core_uagents changed to UTF-8.
custom_bbcode changed to UTF-8.
custom_sidebar_blocks changed to UTF-8.
dnames_change changed to UTF-8.
downloads_categories changed to UTF-8.
downloads_ccontent changed to UTF-8.
downloads_cfields changed to UTF-8.
downloads_comments changed to UTF-8.
downloads_downloads changed to UTF-8.
downloads_filebackup changed to UTF-8.
downloads_files changed to UTF-8.
downloads_files_records changed to UTF-8.
downloads_filestorage changed to UTF-8.
downloads_fileviews changed to UTF-8.
downloads_mime changed to UTF-8.
downloads_mimemask changed to UTF-8.
downloads_mods changed to UTF-8.
downloads_sessions changed to UTF-8.
downloads_temp_records changed to UTF-8.
downloads_urls changed to UTF-8.
dp3_adv_adverts changed to UTF-8.
dp3_adv_adverts_types changed to UTF-8.
dp3_adv_clicks changed to UTF-8.
dp3_adv_sections changed to UTF-8.
dp3_adv_templates changed to UTF-8.
dp3_adv_views changed to UTF-8.
emoticons changed to UTF-8.
error_logs changed to UTF-8.
faq changed to UTF-8.
forum_perms changed to UTF-8.
forums changed to UTF-8.
forums_archive_posts changed to UTF-8.
forums_recent_posts changed to UTF-8.
gallery_albums changed to UTF-8.
gallery_bandwidth changed to UTF-8.
gallery_categories changed to UTF-8.
gallery_comments changed to UTF-8.
gallery_images changed to UTF-8.
gallery_images_uploads changed to UTF-8.
gallery_moderators changed to UTF-8.
gallery_ratings changed to UTF-8.
groups changed to UTF-8.
ignored_users changed to UTF-8.
inline_notifications changed to UTF-8.
jlogica_awards changed to UTF-8.
jlogica_awards_auto_awards changed to UTF-8.
jlogica_awards_awarded changed to UTF-8.
jlogica_awards_cats changed to UTF-8.
login_methods changed to UTF-8.
mail_error_logs changed to UTF-8.
mail_queue changed to UTF-8.
member_status_actions changed to UTF-8.
member_status_replies changed to UTF-8.
member_status_updates changed to UTF-8.
members changed to UTF-8.
members_partial changed to UTF-8.
members_warn_actions changed to UTF-8.
members_warn_logs changed to UTF-8.
members_warn_reasons changed to UTF-8.
message_posts changed to UTF-8.
message_topic_user_map changed to UTF-8.
message_topics changed to UTF-8.
mobile_app_style changed to UTF-8.
mobile_device_map changed to UTF-8.
mobile_notifications changed to UTF-8.
mod_queued_items changed to UTF-8.
moderator_logs changed to UTF-8.
moderators changed to UTF-8.
nexus_ads changed to UTF-8.
nexus_alternate_contacts changed to UTF-8.
nexus_coupons changed to UTF-8.
nexus_customer_fields changed to UTF-8.
nexus_customer_history changed to UTF-8.
nexus_customers changed to UTF-8.
nexus_donate_goals changed to UTF-8.
nexus_donate_logs changed to UTF-8.
nexus_eom changed to UTF-8.
nexus_fraud_rules changed to UTF-8.
nexus_gateways changed to UTF-8.
nexus_hosting_accounts changed to UTF-8.
nexus_hosting_errors changed to UTF-8.
nexus_hosting_queues changed to UTF-8.
nexus_hosting_servers changed to UTF-8.
nexus_invoices changed to UTF-8.
nexus_licensekeys changed to UTF-8.
nexus_notes changed to UTF-8.
nexus_package_fields changed to UTF-8.
nexus_package_groups changed to UTF-8.
nexus_package_images changed to UTF-8.
nexus_packages changed to UTF-8.
nexus_packages_ads changed to UTF-8.
nexus_packages_hosting changed to UTF-8.
nexus_packages_products changed to UTF-8.
nexus_paymethods changed to UTF-8.
nexus_payouts changed to UTF-8.
nexus_product_options changed to UTF-8.
nexus_purchases changed to UTF-8.
nexus_referral_banners changed to UTF-8.
nexus_referral_rules changed to UTF-8.
nexus_referrals changed to UTF-8.
nexus_review_rates changed to UTF-8.
nexus_reviews changed to UTF-8.
nexus_ship_orders changed to UTF-8.
nexus_shipping changed to UTF-8.
nexus_subscriptions changed to UTF-8.
nexus_support_departments changed to UTF-8.
nexus_support_fields changed to UTF-8.
nexus_support_ratings changed to UTF-8.
nexus_support_replies changed to UTF-8.
nexus_support_requests changed to UTF-8.
nexus_support_severities changed to UTF-8.
nexus_support_staff changed to UTF-8.
nexus_support_statuses changed to UTF-8.
nexus_support_stock_actions changed to UTF-8.
nexus_support_tracker changed to UTF-8.
nexus_support_views changed to UTF-8.
nexus_tax changed to UTF-8.
nexus_transactions changed to UTF-8.
permission_index changed to UTF-8.
pfields_content changed to UTF-8.
pfields_data changed to UTF-8.
pfields_groups changed to UTF-8.
pma__bookmark changed to UTF-8.
pma__central_columns changed to UTF-8.
pma__column_info changed to UTF-8.
pma__favorite changed to UTF-8.
pma__history changed to UTF-8.
pma__navigationhiding changed to UTF-8.
pma__pdf_pages changed to UTF-8.
pma__recent changed to UTF-8.
pma__relation changed to UTF-8.
pma__savedsearches changed to UTF-8.
pma__table_coords changed to UTF-8.
pma__table_info changed to UTF-8.
pma__table_uiprefs changed to UTF-8.
pma__tracking changed to UTF-8.
pma__userconfig changed to UTF-8.
pma__usergroups changed to UTF-8.
pma__users changed to UTF-8.
polls changed to UTF-8.
posts changed to UTF-8.
profile_friends changed to UTF-8.
profile_friends_flood changed to UTF-8.
profile_portal changed to UTF-8.
profile_portal_views changed to UTF-8.
profile_ratings changed to UTF-8.
promenuplus_groups changed to UTF-8.
promenuplus_menus changed to UTF-8.
question_and_answer changed to UTF-8.
rc_classes changed to UTF-8.
rc_comments changed to UTF-8.
rc_modpref changed to UTF-8.
rc_reports changed to UTF-8.
rc_reports_index changed to UTF-8.
rc_status changed to UTF-8.
rc_status_sev changed to UTF-8.
reputation_cache changed to UTF-8.
reputation_index changed to UTF-8.
reputation_levels changed to UTF-8.
reputation_totals changed to UTF-8.
rss_export changed to UTF-8.
rss_import changed to UTF-8.
rss_imported changed to UTF-8.

Удачно изменил. Потом зашел в конвертор, опять таже проблема... (((

[Xuc]

тогда пробуйте вообще сконвертировать прогой на локалхосте - как вариант

в принципе и обновить можно на компе)

и поднять потом на хостинге

еще вспомнил вариант установки на хостинге:

попробуйте после прогона скриптом - обновиться сначала до IPS4.0.13

дело в том что у нее проверяльщик кодировки кривоват и ругаться не будет - пустит на обновление, далее обновитесь до 4.1.8

[unixanet]

13 minutes ago, Xuc said:

тогда пробуйте вообще сконвертировать прогой на локалхосте - как вариант

в принципе и обновить можно на компе)

и поднять потом на хостинге

еще вспомнил вариант установки на хостинге:

попробуйте после прогона скриптом - обновиться сначала до IPS4.0.13

дело в том что у нее проверяльщик кодировки кривоват и ругаться не будет - пустит на обновление, далее обновитесь до 4.1.8

Я пробовал апгредить с 3.4.7 на 3.4.9 ритейл. Во время апгрейда, запустил конвертер на УТФ8, опять таже проблема.

Хотя с чистото листа нормально устанавливаеться 4,*.

А не вариант с 0 установить на домене чистый 4ий, потом как то хитро импортировать старую базу ?

[Xuc]

читал про такой вариант - ни разу не воспользовался

свои проверенный варианты - описал выше

[unixanet]

Если не трудно, можешь посмотреть у себя локально, у таблиц какая кодировка стоит? я имею ввиду Колейт

[Xuc]

что такой колейт?)

[unixanet]

Collation - кодировки таблиц внутри БД после конвертации

[unixanet]

У кого не будь еще есть идеи???

[unixanet]

Проблема все еще не решена у меня. сайт лежит. Думал может проблема связанно с версией пхп и мускл. Обновился до нужной версии, а именно:  PHP version 5.6.18. / MySQL version 5.7.11.

Запустил апгрейд, идет до того момента, что надо конвертировать, тыкаю на конверт, выдает: - Error: include(/uploads/profile/photo-8676.jpg): failed to open stream: No such file or directory File: /home/admin/web/sitecom/public_html/conf_global.php(191) : runtime-created function Line: 1

Загуглил, такая проблема у очень многих людей, но решение так и не смог найти у буржуев. Файл профиля который спрашивает скрипт, залито на сервер, но проблема не решаеться.

Админы, друзья, как поправить ситуацию? Кстати ситуация усложняеться так же тем, что после апгрейда версии пхп и мускул, старую версию 3.5.8 не могу ставить, не корректно выдает

[unixanet]

Проблема найдена и решена.

Напишу как все исправить, если у вас идентичная проблема:

обновить версию пхп,мускул, удалить все хуки, руками конвертировать БД и его таблицы в УТФ-8, из БД удалить лишные таблицы, которые там остались с разных времен и начать апгрейд. И все

 

[podvorie]

Решил проблему иначе. В скрипте:

<?
$dbhost = #sql_host
$dbuser = #sql_user
$dbpass = #sql_pass
$dbname = #sql_database

header('Content-type: text/plain');

$dbconn = mysql_connect($dbhost, $dbuser, $dbpass) or die( mysql_error() );
$db = mysql_select_db($dbname) or die( mysql_error() );

$sql = "ALTER DATABASE `".$dbname."` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
$result = mysql_query($sql) or die( mysql_error() );
print "Database changed to UTF-8.\n";

$sql = 'SHOW TABLES';
$result = mysql_query($sql) or die( mysql_error() );

while ( $row = mysql_fetch_row($result) )
{
$table = mysql_real_escape_string($row[0]);
$sql = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci, 
  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
mysql_query($sql) or die( mysql_error() );
print "$table changed to UTF-8.\n";
}

mysql_close($dbconn);
?>

Вместо utf8_general_ci в скрипте указал utf8_unicode_ci и переконвертировал БД. После чего установка форума прошла без требования конвертации.

[Respected]

В эту сторону и нужно было думать, срипт конвертера требует от базы utf8_unicode_ci, на бОльшей части форумов установлена utf8_general_ci, поэтому скрипт не успокоится, пока не будет применён предложенный podvorie вариант 

[said]

В 13.02.2016 в 22:18, Xuc сказал:

 

для тех кто в танке.. программу скачал... какие действия дальше.. подскажите как ей пользоваться

[Respected]

Зачем вам программа? Скрипт выше справляется с конвертацией на все 100%

[said]

7 часов назад, Respected сказал:

Зачем вам программа? Скрипт выше справляется с конвертацией на все 100%

я бы был признателен если бы вы мне рассказали что с этим скриптом делать? в каком месте он находится... 

[wolnorix]

А как запустить скрипт где в нём прописывать данные для БД ?