我试图导入一个旧的Vbulletin数据库,但总是得到这个错误
ERROR 1114 (HY000) at line 4734: The table 'session' is full
这个数据库备份大小是2GB,我的服务器有8GB内存。我试图添加innodb_data_file_path=ibdata1:10M:autoextend
和innodb_file_per_table
到我的。cnf,但没有解决我的问题。
my complete my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
query_cache_limit = 1M
query_cache_size = 16M
expire_logs_days = 10
max_binlog_size = 100M
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
我非常熟悉Vbulletin
数据库模式和表。我已经转换了几十个VB网站到Innodb storage engine
。
你得到这个错误的原因是因为session
表是Memory
表。当您执行要恢复的备份时,session
表一定已经非常满了。再加上导入过程中IO的开销,这就填满了RAM。但是,要使VB正常工作,实际上并不需要将此表设置为Memory
表。
为了解决这个问题,你可以将session
表转换为InnoDB
表。
我会打开。sql文件在一个文本编辑器(如果可能的话),并改变它说ENGINE = MEMORY
为您的session
表到InnoDB
,或使用字符串替换:
sed -i 's/MEMORY/INNODB/g' yourfilename.sql