`
Donald_Draper
  • 浏览: 980941 次
社区版块
存档分类
最新评论

The table is full问题解决过程

阅读更多
The table‘xxxx’is full 设置临时表大小 :http://blog.csdn.net/a351945755/article/details/23454671
MySQL 出现 The table is full 的解决方法 :https://my.oschina.net/longjianghu/blog/162960
     今天在执行一个数据插入操作时,程序抛出 Cause: java.sql.SQLException: The table 'tb_logs' is full异常,看了一下相关文章,说是MySQL临时空间和堆空间的时间,就尝试可不可以删除一些数据,truncate table tb_logs,抛出table doesn‘t exist,执行任何命令没有反应,于是就根据相关文章中的提示修改配置
tmp_table_size = 32M
max_heap_table_size = 32M

重启mysql没有反应,查看mysql错误日志,
Donad_Draper:/home2/mysql # view -f mysqld.log 
2017-05-05 18:11:21 28045 [Note] Server socket created on IP: '::'.
2017-05-05 18:11:21 28045 [ERROR] /usr/sbin/mysqld: Error writing file '/mysqldata/Donad_Draper.pid' (Errcode: 28 - No space left on device)
2017-05-05 18:11:21 28045 [ERROR] Can't start server: can't create PID file: No space left on device
170505 18:13:38 mysqld_safe Starting mysqld daemon with databases from /mysqldata
2017-05-05 18:13:38 f74316d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. 
This option may be removed in future releases, together with the option innodb_use_sys_malloc 
and with the InnoDB's internal memory allocator.

从日志来看是磁盘空间不够的原因,查看磁盘空间,

Donad_Draper:/mysqldata # df  -lh 
Filesystem                             Size  Used Avail Use% Mounted on
/dev/disksafe/sda3                      32G   17G   14G  56% /
udev                                   3.9G  132K  3.9G   1% /dev
tmpfs                                   12G   76K   12G   1% /dev/shm
/dev/disksafe/sda1                     189M  127M   53M  71% /boot
/dev/mapper/vg_mysqldata-lv_mysqldata   30G   30G     0 100% /mysqldata


果然是磁盘空间满的原因,由于系统部的人不在,敝人不会扩磁盘空间,又想赶快把问题解决,不想留在下周,所以用了的个最笨的方法,移除一些不要的数据库物理文件,
查看mysql数据目录:
Donad_Draper:/mysqldata # ls
auto.cnf      area_data3       ibdata1          area_data1           mysql-bin.index     area_data5
dls_databack  ib_logfile0  Donad_Draper.pid  mysql             performance_schema  area_data4
area_data2      ib_logfile1  lost+found       mysql-bin.000001  center_data

Donad_Draper:/mysqldata #du -sh *  
4.0K    auto.cnf
22G     dls_databack
16M     area_data2
1.1G    area_data3
49M     ib_logfile0
49M     ib_logfile1
13M     ibdata1
4.0K    Donad_Draper.pid
4.0K    lost+found
16M     area_data1
2.2M    mysql
4.0K    mysql-bin.000001
4.0K    mysql-bin.index
636K    performance_schema
5.0M    area_data4
16M     area_data5
6.3G    center_data



由于center_data库,已是陈旧的数据库,所以就把这个物理文件移动别的磁盘

Donad_Draper:/mysqldata # mv center_data /home2/bak/
Donad_Draper:/mysqldata # 

Donad_Draper:/mysqldata # df -lh
Filesystem                             Size  Used Avail Use% Mounted on
/dev/disksafe/sda3                      32G   19G   12G  62% /
udev                                   3.9G  132K  3.9G   1% /dev
tmpfs                                   12G   76K   12G   1% /dev/shm
/dev/disksafe/sda1                     189M  127M   53M  71% /boot
/dev/mapper/vg_mysqldata-lv_mysqldata   30G   24G  4.8G  84% /mysqldata
Donad_Draper:/mysqldata #  


在重新启动数据库,可以了使用。
所以在下次出现这个The table 'tb_logs' is full这个问题时,首先检查是不是磁盘空间满的问题,查看服务器状态:
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 0           |
| Binlog_cache_disk_use                         | 0           |
| Binlog_cache_use                              | 0           |
| Binlog_stmt_cache_disk_use                    | 0           |
| Binlog_stmt_cache_use                         | 0           |
...
 Ssl_cipher                                    |             |
| Ssl_cipher_list                               |             |
| Ssl_client_connects                           | 0           |
| Ssl_connect_renegotiates                      | 0           |
| Ssl_ctx_verify_depth                          | 0           |
| Ssl_ctx_verify_mode                           | 0           |
| Ssl_default_timeout                           | 0           |
| Ssl_finished_accepts                          | 0           |
| Ssl_finished_connects                         | 0           |
| Ssl_server_not_after                          |             |
| Ssl_server_not_before                         |             |
| Ssl_session_cache_hits                        | 0           |
| Ssl_session_cache_misses                      | 0           |
| Ssl_session_cache_mode                        | NONE        |
| Ssl_session_cache_overflows                   | 0           |
| Ssl_session_cache_size                        | 0           |
| Ssl_session_cache_timeouts                    | 0           |
| Ssl_sessions_reused                           | 0           |
| Ssl_used_session_cache_entries                | 0           |
| Ssl_verify_depth                              | 0           |
| Ssl_verify_mode                               | 0           |
| Ssl_version                                   |             |
| Table_locks_immediate                         | 98          |
| Table_locks_waited                            | 0           |
| Table_open_cache_hits                         | 1           |
| Table_open_cache_misses                       | 41          |
| Table_open_cache_overflows                    | 40          |
| Tc_log_max_pages_used                         | 0           |
| Tc_log_page_size                              | 0           |
| Tc_log_page_waits                             | 0           |
| Threads_cached                                | 0           |
| Threads_connected                             | 7           |
| Threads_created                               | 7           |
| Threads_running                               | 2           |
| Uptime                                        | 510         |
| Uptime_since_flush_status                     | 510         |
+-----------------------------------------------+-------------+
341 rows in set

服务器状态没有什么问题,在查看表状态:
mysql> show table status where name='tb_logs';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------+
| 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    |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------+
| tb_logs | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |               0 |        16384 |         0 |          32328 | 2017-05-05 19:02:13 | NULL        | NULL       | utf8_general_ci | NULL     |                | 操作日志表 |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+------------+
1 row in set

mysql> 

如果是rows过多的原因,则修改表MAX_ROWS:
ALTER TABLE tb_logs MAX_ROWS=1000000000; 

这个不建议做。


否则查看mysql临时空间和堆空间大小
mysql> SHOW VARIABLES WHERE Variable_name LIKE '%table_size%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
2 rows in set


如果是空间问题,修改相关配置:

Donad_Draper:/home/fsjrfw # vim /etc/my.cnf
# The MySQL server
[mysqld]
tmp_table_size = 32M
max_heap_table_size = 32M
:wq

重启:

mysql> SHOW VARIABLES WHERE Variable_name LIKE '%table_size%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 33554432 |
| tmp_table_size      | 33554432 |
+---------------------+----------+
2 rows in set
mysql> 











分享到:
评论

相关推荐

    Table is full

    如何解决MySql的Table is full问题 最近使用Mysql的Memory引擎时,出现了'Table’ is full的异常。解决办法是修改max_heap_table_size参数,改大以足够容纳表的大小,比如512M。

    SSD7 选择题。Multiple-Choice

    The degree of a table is the number of _____ in the table. (a) keys (b) columns (c) rows (d) foreign keys Correct answer is (b) Your score on this question is: 10.00 Feedback: (b) ...

    通信与网络中的MySQL数据库单一表突破4G限制的实现方法

    为解决此问题,我翻阅了很多资料,本文将以我此次问题的解决过程,介绍问题发生的原因及对策。  根据经验,The table is full提示往往出现在以下两种情况:  1. 表中设置了MAX_ROWS值,简单的说,若MAX_ROWS...

    EasyTable.v16.00.for Delphi 10.4 by [CS].rar (首先在互联网上. 仅在CSDN中.)

    The great advantage of EasyTable compared to other database systems is that it allows to integrate a database file into the application executable file. EasyTable contains TEasySession component ...

    Devart UniDAC v5.3.10 Full Source

    -The EmptyTable method is added to TUniTable -The SQL property is added to TUniConnection -Support for the Upper and Lower statements in TDADataSet.Filter is added -Support for the ftOraTimeStamp type...

    微软内部资料-SQL性能优化5

    The only source of any storage location information is the sysindexes table, which keeps track of the address of the root page for every index, and the first IAM page for the index or table....

    Mastering Full-Stack React Web Development

    Full-stack web development is being redefined by the impact of ReactJS. If MEAN demonstrated just how effective combining JavaScript frameworks and tools could be for the modern web developer, by ...

    F28335 AD采集可行吗

    The other two should be defined as 0.#define POST_SHIFT 0 // Shift results after the entire sample table is full#define INLINE_SHIFT 1 // Shift results as the data is taken from the results regsiter#...

    a project model for the FreeBSD Project.7z

    This, combined with the vast amount of dependencies in the kernel and that it is not easy to see all the consequences of a kernel change, demands developers with a relative full understanding of the ...

    微软内部资料-SQL性能优化3

    Another type of table lock is a schema stability lock (Sch-S) and is compatible with all table locks except the schema modification lock (Sch-M). The schema modification lock (Sch-M) is incompatible ...

    IBO v5.3.3 Build 1955(January 2, 2014) Full Source

    functional application that shows you how to replicate a portion of a table into a separate database and how to put a full text search index against the replicated data. The service app runs in order ...

    Spectral Core Full Convert Enterprise v6.11.0.1683

    While this is not a complete feature list, it will certainly give you a look into the quality and robustness of Full Convert. It auto-adapts to your data and database engines used and selects the best...

    The VLSI Handbook, Second Edition.pdf

    Each section is divided into chapters, each of which is written by a leading expert in the field to enlighten and refresh knowledge of the mature engineer, and to educate the novice. Each section ...

    二级减速器课程设计说明书reducer design specification.doc

    According to the recommended reasonable transmission ratio range in table 2-2, the transmission ratio range of the expanded two-stage gear reducer ia=8 ~ 40, the transmission ratio range of v-belt ...

    云模型在文本挖掘应用中的关键问题研究

    The information table in knowledge representation system is introduced to text representation. On this basis, text system is expressed as text information table based on VSM model. 2) Text ...

    acpi控制笔记本风扇转速

    now validated when the table is loaded. Added two new warnings during FADT verification - 1) if the FADT is larger than the largest known FADT version, and 2) if there is a mismatch between a 32-bit ...

    WMS_TableV1.0_表结构_wms_

    4. 入库/出库单表(Receipt/Dispatch Table):记录货物的入库和出库信息,包括单据编号、商品ID、数量、操作时间等,这些数据直接影响库存的增减和财务核算。 5. 库存状态表(Inventory Status Table):实时反映...

    Universal-USB-Installer

    running the Program is not restricted, and the output from the Program is covered only if its contents constitute a work based on the Program (independent of having been made by running the Program). ...

    Senfore_DragDrop_v4.1

    A batch file, convert_forms_to delphi_4_format.bat, is supplied in the demo directory which automates the conversion process. The C++ Builder demo forms are distributed in binary format. 7) If ...

    EhLib 8.0 Build 8.0.023 Pro Edition FullSource for D7-XE8

    EhLib 8.0 Build 8.0.023 Pro Edition FullSource for D7-XE8 亲测可用,含全部源码及 Demo The Library contains components and classes for Borland Delphi versions 7, 9, Developer Studio 2006, Delphi 2007...

Global site tag (gtag.js) - Google Analytics