0

I received a Last_Errno: 1118,

Last_Error: Error 'Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.' on query.

I then compared my master and slave and have found the innodb_file_format and innodb_file_format_max on my master are barracuda but antelope on my slaves.

I think the barracuda increase noted here, https://dev.mysql.com/doc/refman/5.6/en/innodb-file-format.html, explains this:

index key prefixes up to 3072 bytes

explains the issue so now how can I safely upgrade the file format of my slaves?

Master:

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

Slave:

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

Slave2:

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

Table definition:

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 |

Post slave update:

+-------------------------------------------+------------------------+
| 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)
Improve this question
8
  • What versions are involved? It sounds like you have a newer Master and an older Slave. Also, please provide SHOW CREATE TABLE for the table involved.
    – Rick James
    Mar 12, 2019 at 15:30
  • @RickJames I've added the output of mysql --version and the last line from the SHOW CREATE TABLE statement. If there is an additional line(s) needed please let me know. There are 85 columns and 8 indexes in place so I thought that'd be too much to add. 1 PRIMARY KEY, 2 KEY, and 5 FULLTEXT KEY
    – user3783243
    Mar 12, 2019 at 15:55
  • Looking at all innodb variables I notice the following differ, innodb_buffer_pool_size, innodb_empty_free_list_algorithm, innodb_file_format (as previously noted), innodb_file_format_max (PN), innodb_ft_cache_size, innodb_ft_min_token_size, and innodb_status_output.
    – user3783243
    Mar 12, 2019 at 15:59
  • Distrib 5.6.41-84.1 refers to the command line mysql. Need to know the server: SELECT @@version;
    – Rick James
    Mar 12, 2019 at 16:53
  • I think we need all of SHOW CREATE TABLE. Notice how the error message suggests changing some datatypes. If you have a lot of VARCHAR(255), perhaps shrinking some of them to reasonable sizes would solve the problem.
    – Rick James
    Mar 12, 2019 at 16:56

2 Answers 2

Reset to default
1

Simply changing to Barracuda is not sufficient to achieve much.

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)

More discussion on the 767 limit, plus workarounds: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Notice how the error message suggests changing some datatypes. If you have a lot of VARCHAR(255), perhaps shrinking some of them to reasonable sizes would solve the problem.

Have you changed innodb_page_size? (You probably should not.)

Use SELECT @@version; to get the version of the server. (mysql --version gets the version of that client.)

innodb_buffer_pool_size should be about 70% of RAM on each server -- if you have over 4GB of RAM. If you have less, well, how tiny is your RAM?

The SETs above can be done temporarily (as shown) or permanently my.cnf. In general, 1 == ON and 0 == OFF for any 2-value settings.

Hmmm... Only the PK and a 3-column KEY come under the issue of 3072 (or 767), and neither of them seems to be more than 767. So, I don't understand the Slave's complaint. Fulltext does not have 767/3072 issues.

You have about 18 'large' text/blob columns. With ROW_FORMAT=COMPACT, each would take up to 768+20 bytes before overlowing. That's about 14KB, which is more than the 8KB limit. Dynamic and Compressed take only 20 bytes each, so there is plenty of room.

An alternative to changing the row_format is to use 'vertical partitioning'. That is, make a parallel table (same PK) and move some of the text columns into it. (With a single extra table, you probably need to move 9 of them.)

Short Answer: The Master and Slave had different ROW_FORMATs.

Improve this answer
8
  • My current settings are for innodb_large_prefix = OFF and innodb_file_per_table = ON; both values should be 1 in my.cnf? Also the innodb_buffer_pool_size is 2147483648 on master but only 134217728 on the slave. Is that an issue?
    – user3783243
    Mar 12, 2019 at 17:11
  • I haven't touched innodb_page_size. My master has 2 more MBs than my slaves. Slave 1 and 2: 31370 MB, Master: 31372 MB
    – user3783243
    Mar 12, 2019 at 17:31
  • 1
    I added a few paragraphs to my Answer. As for 31GB of RAM -- you could (should) increase the buffer_pool_size to, say, 20G on all machines. I don't think it is part of the issue, however Compressed needs to keep both compressed and uncompressed blocks in the buffer_pool.
    – Rick James
    Mar 12, 2019 at 17:39
  • I've made the changes on slave 2 but the behavior persists. I've added all variables like innodb to the question. I used show variables like '%innodb%';.
    – user3783243
    Mar 12, 2019 at 17:47
  • 1
    @user3783243 - Please provide SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name like '%test_table';
    – Rick James
    Mar 12, 2019 at 20:12
0

After setting innodb_file_format on the slave, ALTER TABLE {db}.{tbl} FORCE for all innodb tables will re-create these as baracuda. Then START SLAVE again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.