mariadb/mysql表文件太大导致的数据库启动不了
我们帮助客户部署的一个采集器程序，突然罢工了。采集后台打开不了，经过检查发现是使用的mariadb启动不了了。 任凭我们怎么修改MariaDB的参数，都是启动不了。
170911 19:01:33 mysqld_safe Starting mysqld daemon with databases from /data/mariadb/mysql
170911 19:01:33 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 29710 ...
170911 19:01:33 InnoDB: The InnoDB memory heap is disabled
170911 19:01:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170911 19:01:33 InnoDB: Compressed tables use zlib 1.2.7
170911 19:01:33 InnoDB: Using Linux native AIO
170911 19:01:33 InnoDB: Initializing buffer pool, size = 2.0G
170911 19:01:33 InnoDB: Completed initialization of buffer pool
170911 19:01:33 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 420788850640
170911 19:01:33 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 420788866252
170911 19:01:33 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
170911 19:01:34 InnoDB: Waiting for the background threads to start
InnoDB: Dump of the tablespace extent descriptor: len 40; hex 0000000000000002001d800014ee001c000011f600000004aaaaaaffffffffffffffffffffffffff; asc ;
InnoDB: Serious error! InnoDB is trying to free page 1985087
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free space info is corrupt.
InnoDB: You may need to dump your InnoDB tables and recreate the whole
InnoDB: database!
InnoDB: Please refer to
InnoDB: /doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
170911 19:01:34 InnoDB: Assertion failure in thread 140466100107008 in file fsp0fsp.c line 3326
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to .
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: /doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
170911 19:01:34 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see /en/reporting-bugs
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 5.5.52-MariaDB
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=0
max_threads=153
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 466712 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x48000
/usr/libexec/mysqld(my_print_stacktrace+0x3d)[0x7fc17b57f2dd]
/usr/libexec/mysqld(handle_fatal_signal+0x515)[0x7fc17b1948d5]
/lib64/libpthread.so.0(+0xf370)[0x7fc17a8c5370]
/lib64/libc.so.6(gsignal+0x37)[0x7fc1790751d7]
/lib64/libc.so.6(abort+0x148)[0x7fc1790768c8]
/usr/libexec/mysqld(+0x6d88e1)[0x7fc17b3cc8e1]
/usr/libexec/mysqld(+0x6e1020)[0x7fc17b3d5020]
/usr/libexec/mysqld(+0x673fe4)[0x7fc17b367fe4]
/usr/libexec/mysqld(+0x746317)[0x7fc17b43a317]
/usr/libexec/mysqld(+0x746918)[0x7fc17b43a918]
/usr/libexec/mysqld(+0x73b157)[0x7fc17b42f157]
/usr/libexec/mysqld(+0x645d5d)[0x7fc17b339d5d]
/usr/libexec/mysqld(+0x639bd6)[0x7fc17b32dbd6]
/lib64/libpthread.so.0(+0x7dc5)[0x7fc17a8bddc5]
/lib64/libc.so.6(clone+0x6d)[0x7fc17913773d]
The manual page at /doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
170911 19:01:34 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
在网上搜索了很多文章，有用的很少，不过其中有一篇，说是最新的mysql5.7已经修正了这个问题。也没有解决问题。
后来我检查采集器所用的数据库表，查看里面的数据文件。发现cache_feeds_http.ibd 这个表32G竟然有大小，可能超过了mariadb的系统限制。没有想到采集数据量太大，导致feeds模块的缓存表，过大。幸好是缓存表，我直接将本地的这个文件，比较小的，上传上去，将32G这个文件替换掉。修改这个文件的所有者，重新改为mysql。启动，可以了。
chown mysql:mysql /../cache_feeds_http.ibd
解决以后，采集器重新正常运行。后来清除缓存，发现，总是报：cache_feeds_http这个表找不到的系统错误。
这才发现，原来我手动删除Mariadb的数据库表，是有遗留问题的。我决定将这个表清理干净，然后再重新创建一遍。
CREATE TABLE `cache_feeds_http` (
`cid` varchar(255) NOT NULL DEFAULT '' COMMENT 'Primary Key: Unique cache ID.',
`data` longblob COMMENT 'A collection of data to cache.',
`expire` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry should expire, or 0 for never.',
`created` int(11) NOT NULL DEFAULT '0' COMMENT 'A Unix timestamp indicating when the cache entry was created.',
`serialized` smallint(6) NOT NULL DEFAULT '0' COMMENT 'A flag to indicate whether content is serialized (1) or not (0).',
PRIMARY KEY (`cid`),
KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cache table for Feeds downloads.';
使用这样的SQL，总是不成功。当我去创建表的时候，提示表已经存在，但是当我执行删除表命令的时候，又提示表不存在。
使用Drupal代码的形式，采用下面的代码，尝试创建cache_feeds_http：
$schema['cache_feeds_http'] = drupal_get_schema_unprocessed('system', 'cache');
$schema['cache_feeds_http']['description'] = 'Cache table for Feeds downloads.';
db_create_table('cache_feeds_http', $schema);
还是不行，尝试了很多次以后，我决定，将表名，从cache_feeds_http改为cache_feeds_https，通过命令行，执行前面的sql，改了名字以后的，这次很顺利。
我将生成好的：cache_feeds_https.frm，cache_feeds_https.ibd下载到本地，改名为：
cache_feeds_http.frm，cache_feeds_http.ibd，上传上去。
这个时候，再去执行删除表命令，创建表命令，竟然成功了。最后删除cache_feeds_https表。
