Mysql
將羚羊升級為奴隸梭子魚
我收到了一個
Last_Errno: 1118，Last_Error：錯誤’行大小太大（> 8126）。將某些列更改為 TEXT 或 BLOB 或使用 ROW_FORMAT=DYNAMIC 或 ROW_FORMAT=COMPRESSED 可能會有所幫助。在目前行格式中，768 字節的 BLOB 前綴被內聯儲存。查詢。
然後我比較了我的主人和奴隸，發現我的主人和我
innodb_file_format的奴隸只是我的奴隸。innodb_file_format_max``barracuda``antelope我認為這裡提到的梭魚增加，https ://dev.mysql.com/doc/refman/5.6/en/innodb-file-format.html解釋了這一點：
最多 3072 字節的索引鍵前綴
解釋了這個問題，所以現在我怎樣才能安全地升級我的奴隸的文件格式？
掌握：
mysql Ver 14.14 Distrib 5.6.41-84.1, for Linux (x86_64) using 6.2 ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 5.6.41-84.1-log奴隸：
mysql Ver 14.14 Distrib 5.6.41-84.1, for Linux (x86_64) using 6.2 ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 5.6.41-84.1奴隸2：
mysql Ver 14.14 Distrib 5.6.41-84.1, for Linux (x86_64) using 6.2 ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED 5.6.41-84.1表定義：
CREATE TABLE `test_table` ( `ID` int(11) NOT NULL, `Name` varchar(1000) DEFAULT NULL, `Tags` varchar(1000) NOT NULL, `Details` varchar(2000) DEFAULT NULL, `Active` tinyint(1) DEFAULT NULL, `UserID` int(11) DEFAULT NULL, `DateAdded` datetime DEFAULT NULL, `Excerpt` text, `Disclosures` varchar(10000) DEFAULT NULL, `Discussion` text, `Acknowledgements` varchar(2000) DEFAULT NULL, `TextContent` mediumtext, `Materials` text, `HTMLTopContent` text, `StreamID` int(11) DEFAULT NULL, `DatePublished` datetime DEFAULT NULL, `Access` int(11) DEFAULT NULL, `DurationMonths` int(11) DEFAULT NULL, `DurationDays` int(11) DEFAULT NULL, `AuthorProduced` tinyint(1) DEFAULT NULL, `CollaborationType` int(11) DEFAULT NULL, `Indexed` tinyint(1) DEFAULT NULL, `Revision` int(11) DEFAULT NULL, `Results` text, `Stage` int(11) DEFAULT NULL, `SectionID` int(11) DEFAULT NULL, `xmlcache` text, `MaterialMigration` text, `keycat` int(11) DEFAULT NULL, `doi` varchar(25) DEFAULT NULL, `pmid` int(11) DEFAULT NULL, `Issue` int(11) DEFAULT NULL, `Introduction` text, `Citations` text, `SID` int(11) NOT NULL DEFAULT '0', `Title` varchar(200) NOT NULL DEFAULT '', `AbbreviatedTitle` varchar(300) DEFAULT NULL, `Description` varchar(200) NOT NULL DEFAULT '', `Article` tinyint(1) NOT NULL DEFAULT '0', `IsInPress` tinyint(4) NOT NULL DEFAULT '0', `FinalizedDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ProgressStep` int(2) DEFAULT NULL COMMENT, `DraftPublished` datetime DEFAULT NULL, PRIMARY KEY (`ID`), KEY `Articles_StreamID_Active_DatePublished` (`StreamID`,`Active`,`DatePublished`), FULLTEXT KEY `AbstractTest` (`Details`,`Name`), FULLTEXT KEY `xmlcache` (`xmlcache`), FULLTEXT KEY `articles_fulltext` (`Name`,`Tags`,`Details`,`Excerpt`,`Disclosures`,`Introduction`,`Acknowledgements`,`TextContent`,`Discussion`,`HTMLTopContent`,`Rep_Results`,`Citations`,`xmlcache`,`Materials`), FULLTEXT KEY `xmlcache_2` (`xmlcache`), FULLTEXT KEY `Materials` (`Materials`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED |發布奴隸更新：
+-------------------------------------------+------------------------+ | Variable_name | Value | +-------------------------------------------+------------------------+ | ignore_builtin_innodb | OFF | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_partitions | 1 | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_additional_mem_pool_size | 8388608 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_populate | OFF | | innodb_buffer_pool_size | 2147483648 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | innodb | | innodb_checksums | ON | | innodb_cleaner_lsn_age_factor | high_checkpoint | | innodb_cmp_per_index_enabled | OFF | | innodb_commit_concurrency | 0 | | innodb_compressed_columns_threshold | 96 | | innodb_compressed_columns_zip_level | 6 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_concurrency_tickets | 5000 | | innodb_corrupt_table_action | assert | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite | ON | | innodb_empty_free_list_algorithm | backoff | | innodb_fake_changes | OFF | | innodb_fast_shutdown | 1 | | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | | innodb_file_per_table | ON | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_flush_neighbors | 1 | | innodb_flushing_avg_loops | 30 | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_foreground_preflush | exponential_backoff | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_kill_idle_transaction | 0 | | innodb_large_prefix | ON | | innodb_lock_wait_timeout | 50 | | innodb_locking_fake_changes | ON | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | ./ | | innodb_log_arch_expire_sec | 0 | | innodb_log_archive | OFF | | innodb_log_block_size | 512 | | innodb_log_buffer_size | 8388608 | | innodb_log_checksum_algorithm | innodb | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_lru_scan_depth | 1024 | | innodb_max_bitmap_file_size | 104857600 | | innodb_max_changed_pages | 1000000 | | innodb_max_dirty_pages_pct | 75 | | innodb_max_dirty_pages_pct_lwm | 0 | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_mirrored_log_groups | 1 | | innodb_monitor_disable | | | innodb_monitor_enable | | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_numa_interleave | OFF | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 2000 | | innodb_optimize_fulltext_only | OFF | | innodb_page_size | 16384 | | innodb_print_all_deadlocks | OFF | | innodb_print_lock_wait_timeout_info | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_only | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_sched_priority_cleaner | 19 | | innodb_show_locks_held | 10 | | innodb_show_verbose_locks | 0 | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 6 | | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_tmpdir | | | innodb_track_changed_pages | OFF | | innodb_undo_directory | . | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | | innodb_use_atomic_writes | OFF | | innodb_use_global_flush_log_at_trx_commit | ON | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 5.6.41-84.1 | | innodb_write_io_threads | 4 | +-------------------------------------------+------------------------+ 148 rows in set (0.00 sec)
簡單地更改為 Barracuda 並不足以取得很大的成就。
SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_large_prefix=1; logout & login (to get the global values); ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- (or COMPRESSED)關於 767 限制的更多討論，以及解決方法：http: //mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
請注意錯誤消息如何建議更改某些數據類型。如果您有很多 VARCHAR(255)，也許將其中一些縮小到合理的大小可以解決問題。
你變了
innodb_page_size嗎？（你可能不應該。）用於
SELECT @@version;獲取伺服器的版本。（mysql --version獲取該客戶端的版本。）
innodb_buffer_pool_size應該是每台伺服器上大約 70% 的 RAM——如果你有超過 4GB 的 RAM。如果你的記憶體更少，那麼你的 RAM 有多小？以上
SETs可以臨時（如圖所示）或永久完成my.cnf。通常，1==ON和0==OFF用於任何 2 值設置。嗯…… 3072（或767）的問題下只有PK和3列
KEY，而且似乎都沒有超過767。所以，我不明白奴隸的抱怨。全文沒有 767/3072 問題。您有大約 18 個“大”文本/blob 列。使用
ROW_FORMAT=COMPACT時，每個將佔用 768+20 個字節，然後才會溢出。這大約是 14KB，超過了 8KB 的限制。動態和壓縮每個只佔用 20 個字節，因此有足夠的空間。更改 row_format 的替代方法是使用“垂直分區”。也就是說，製作一個平行表（相同的 PK）並將一些文本列移動到其中。（如果有一個額外的桌子，您可能需要移動其中的 9 個。）
簡答：主從不同
ROW_FORMATs。