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>