2

Error in mysqld.log:

2020-02-02T01:17:32.133974Z 3069 [ERROR] /usr/sbin/mysqld: The table 'IS_DUT_IFID' is full

None of the many threads I've found about this error have helped me to resolve this issue.

Here's what I've checked or tried so far:

  • innodb_data_file_path - autoextend
  • innodb_temp_data_file_path - autoextend
  • innodb_file_per_table - ON (and in effect)
  • /var/lib/mysql and /tmp disk space - 47GB available
  • innodb_buffer_pool_* increased
    • 1 instance of 128 MB (default) increased to 4 instances of 256MB for a total of 1GB
  • no extra large blobs being used to my knowledge but did this just in case as it was the only other suggestion I could find:
    • innodb_log_file_size increased from 50MB (default) to 128 MB
    • innodb_log_buffer_size increased from 16MB (default) to 32 MB

I did notice IS_DUT_IFID.ibd recently passed the 4GB mark. The file is on an ext4 volume managed by Red Hat 6.8. It is 32-bit, but the file being over 4GB doesn't appear to be causing the OS any problems and shouldn't be hitting any OS limits according to this: https://access.redhat.com/articles/rhel-limits

Server has 8GB RAM, but I guess only 4GB would be available to any given process due to it being 32-bit. I was under the impression MySQL could intelligently process chunks at a time and would only load what it needed to so this shouldn't be an issue. Maybe I'm wrong?

My only thought is to upgrade the OS to 64-bit so I can run MySQL 64-bit, but I'd much prefer to leave this server as is if at all possible. Any advice greatly appreciated!

.

MySQL info:

mysql> show table status like 'IS_DUT_IFID';
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| IS_DUT_IFID | InnoDB |      10 | Dynamic    | 35362861 |             32 |  1160773632 |               0 |   2279604224 |   6291456 |           NULL | 2020-01-30 15:11:10 | 2020-02-02 11:00:38 | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb%';
+------------------------------------------+------------------------+
| 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_parts         | 8                      |
| innodb_adaptive_max_sleep_delay          | 150000                 |
| 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_chunk_size            | 268435456              |
| innodb_buffer_pool_dump_at_shutdown      | ON                     |
| innodb_buffer_pool_dump_now              | OFF                    |
| innodb_buffer_pool_dump_pct              | 25                     |
| innodb_buffer_pool_filename              | ib_buffer_pool         |
| innodb_buffer_pool_instances             | 4                      |
| innodb_buffer_pool_load_abort            | OFF                    |
| innodb_buffer_pool_load_at_startup       | ON                     |
| innodb_buffer_pool_load_now              | OFF                    |
| innodb_buffer_pool_size                  | 1073741824             |
| innodb_change_buffer_max_size            | 25                     |
| innodb_change_buffering                  | all                    |
| innodb_checksum_algorithm                | crc32                  |
| innodb_checksums                         | ON                     |
| innodb_cmp_per_index_enabled             | OFF                    |
| innodb_commit_concurrency                | 0                      |
| innodb_compression_failure_threshold_pct | 5                      |
| innodb_compression_level                 | 6                      |
| innodb_compression_pad_pct_max           | 50                     |
| innodb_concurrency_tickets               | 5000                   |
| innodb_data_file_path                    | ibdata1:12M:autoextend |
| innodb_data_home_dir                     |                        |
| innodb_deadlock_detect                   | ON                     |
| innodb_default_row_format                | dynamic                |
| innodb_disable_sort_file_cache           | OFF                    |
| innodb_doublewrite                       | ON                     |
| innodb_fast_shutdown                     | 1                      |
| innodb_file_format                       | Barracuda              |
| innodb_file_format_check                 | ON                     |
| innodb_file_format_max                   | Barracuda              |
| innodb_file_per_table                    | ON                     |
| innodb_fill_factor                       | 100                    |
| innodb_flush_log_at_timeout              | 1                      |
| innodb_flush_log_at_trx_commit           | 1                      |
| innodb_flush_method                      |                        |
| innodb_flush_neighbors                   | 1                      |
| innodb_flush_sync                        | ON                     |
| innodb_flushing_avg_loops                | 30                     |
| innodb_force_load_corrupted              | OFF                    |
| innodb_force_recovery                    | 0                      |
| 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_large_prefix                      | ON                     |
| innodb_lock_wait_timeout                 | 50                     |
| innodb_locks_unsafe_for_binlog           | OFF                    |
| innodb_log_buffer_size                   | 33554432               |
| innodb_log_checksums                     | ON                     |
| innodb_log_compressed_pages              | ON                     |
| innodb_log_file_size                     | 134217728              |
| innodb_log_files_in_group                | 2                      |
| innodb_log_group_home_dir                | ./                     |
| innodb_log_write_ahead_size              | 8192                   |
| innodb_lru_scan_depth                    | 1024                   |
| innodb_max_dirty_pages_pct               | 75.000000              |
| innodb_max_dirty_pages_pct_lwm           | 0.000000               |
| innodb_max_purge_lag                     | 0                      |
| innodb_max_purge_lag_delay               | 0                      |
| innodb_max_undo_log_size                 | 1073741824             |
| 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_cleaners                     | 4                      |
| innodb_page_size                         | 16384                  |
| innodb_print_all_deadlocks               | OFF                    |
| innodb_purge_batch_size                  | 300                    |
| innodb_purge_rseg_truncate_frequency     | 128                    |
| innodb_purge_threads                     | 4                      |
| 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_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                       | ON                     |
| innodb_support_xa                        | ON                     |
| innodb_sync_array_size                   | 1                      |
| innodb_sync_spin_loops                   | 30                     |
| innodb_table_locks                       | ON                     |
| innodb_temp_data_file_path               | ibtmp1:12M:autoextend  |
| innodb_thread_concurrency                | 0                      |
| innodb_thread_sleep_delay                | 10000                  |
| innodb_tmpdir                            |                        |
| innodb_undo_directory                    | ./                     |
| innodb_undo_log_truncate                 | OFF                    |
| innodb_undo_logs                         | 128                    |
| innodb_undo_tablespaces                  | 0                      |
| innodb_use_native_aio                    | ON                     |
| innodb_version                           | 5.7.29                 |
| innodb_write_io_threads                  | 4                      |
+------------------------------------------+------------------------+
134 rows in set (0.01 sec)

mysql> show variables like '%tmp%';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| default_tmp_storage_engine       | InnoDB   |
| innodb_tmpdir                    |          |
| internal_tmp_disk_storage_engine | InnoDB   |
| max_tmp_tables                   | 32       |
| slave_load_tmpdir                | /tmp     |
| tmp_table_size                   | 16777216 |
| tmpdir                           | /tmp     |
+----------------------------------+----------+
7 rows in set (0.00 sec)

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

Additional info:

Originating INSERT error from Perl script:

The table 'IS_DUT_IFID' is full [for Statement "INSERT INTO IS_DUT_IFID ( ddv_id, dut_ifid_id, is_id) VALUES ( '84600', 3172, 1594897 )"]
  • If the OS is 32-bit, it cannot use your 8GB of RAM! Only half of that! – Rick James Feb 12 at 5:12
  • @RickJames Depends on what you mean by "it", I suppose. The OS can use more with PAE. I think the MySQL server process would be limited to 4GB. I limited the buffer pool allocation and memory usage didn't appear to be growing anywhere near 4GB, but it's possible that was somehow related so that's why I moved to 64 bit. – user1111991 Mar 5 at 22:05
  • A 32-bit OS cannot touch anything address above 4 billion. Nor can any program running on that server. The CPU simply has no physical way to do it. The other 4GB is sitting there collecting dust. If you have 64-bit OS, but 32-bit MySQL, then MySQL cannot, for example, use a buffer_pool > 4G. The OS can see all 8GB, but MySQL cannot see more than 4GB. – Rick James Mar 5 at 23:04
  • OK, I'm reading about PAE.... – Rick James Mar 5 at 23:05
  • OK, I guess I snoozed through the invention of PAE. But it still sounds like MySQL must be 64-bit in order to access more than 4GB. – Rick James Mar 5 at 23:18
2

That sounds like the answer: Upgrade the OS to 64-bit to run the 64-bit version of MySQL.

Interestingly, there is a limit to the size of an InnoDB table in Windows 32

Note Bulletpoint 10 of MySQL Documentation on "InnoDB Limits"

Tablespace files cannot exceed 4GB on Windows 32-bit systems (Bug #80149).

Note the excerpt from that bug report (submitted 2020-01-26 08:10 AM)

When the temporary table reaches 4GB in size, the testcase fails like this:

mysql> insert into t select * from t; Query OK, 98304 rows affected (6 min 54.49 sec) Records: 98304 Duplicates: 0 Warnings: 0

mysql> insert into t select * from t; ERROR 1114 (HY000): The table 'c:\tmp#sql2cc04_17_10' is full

o) Works on Linux 64-bit and Windows 64-bit works okay. o) tmpdir and datadir has plenty of free space.

That looks like your situation as well.

Perhaps the same limit exists for the 32-bit version of MySQL as well.

| improve this answer | |
  • Thanks for the reply... and all of the other replies you've made over the years. I've learned a lot from your posts! – user1111991 Feb 11 at 14:33
  • It's taking some time to migrate to the 64-bit OS and it'll take more time to test the changes. Hopefully I'll be able to post a successful update next week. – user1111991 Feb 11 at 14:41
  • You should bump up innodb_buffer_pool_size to 6G. You can run SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 6;. Then, add innodb_buffer_pool_size = 6G to my.cnf. – RolandoMySQLDBA Feb 11 at 15:18
  • Migrating to 64-bit MySQL did indeed resolve the issue. Now that that issue is resolved, I can look into upping the buffer pool size. Thanks! – user1111991 Feb 19 at 15:50
0

"Table is full" almost always indicates a disk space issue. It could be in your main disk partition or in the "tmp" partition.

The buffer_pool does cache stuff. You have innodb_buffer_pool_size = 1G. So, regardless of 32-bit vs 64-bit, and regardless of the size of the table, that size will work -- maybe slower for a bigger table, but it will still work. (1G would be too big if your available RAM were under 2GB. But, again, you would not see "table is full".)

/var/lib/mysql and /tmp disk space - 47GB available

In both? Or in each? InnoDB is smart enough to have a table span two disk partitions. So, how much is available in each?

SHOW VARIABLES LIKE '%dir%';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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