infobright的问题汇总

1、安装infobright时,找不到库文件

[root@ib infobright]#./install-infobright.sh 
--datadir=/u01/mysql/data 
--cachedir=/u01/mysql/cache 
--config=/u01/mysql/my.cnf 
--port=3306 
--socket=/tmp/mysql3306.sock 
--user=mysql 
--group=mysql
 
Infobright installation script is running...
Checking system configuration...
strings: '/lib64/libc.so.6': No such file
expr: missing operand

建个soft link解决

[root@ib lib]# ln -s /lib/libc.so.6 /lib64

2、导入数据到BRIGHTHOUSE的表中,乱码

innodb表:

root@test 04:12:04>CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `char_1` varchar(30) NOT NULL,
    ->   `gmt_create` datetime NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.00 sec)
 
root@test 04:12:34>insert into test(char_1,gmt_create) values('李杰',now());
Query OK, 1 row affected (0.00 sec)
 
root@test 04:12:40>select * from test into outfile '/tmp/test.txt' fields terminated by ',';
Query OK, 1 row affected (0.00 sec)
 
[test@mac1 /tmp]
$more /tmp/test.txt
1,李杰,2011-10-09 16:12:40

brighthouse表:

mysql> CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL ,
    ->   `char_1` varchar(30) NOT NULL,
    ->   `gmt_create` datetime NOT NULL
    -> ) ENGINE=BRIGHTHOUSE  DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.01 sec)
 
mysql> load data infile '/tmp/test.txt' into table test fields terminated by ',';
Query OK, 1 row affected (0.05 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

乱码出现:

mysql> select * from test;
+----+--------+---------------------+
| id | char_1 | gmt_create          |
+----+--------+---------------------+
|  1 | ????   | 2011-10-09 16:12:40 |
+----+--------+---------------------+
1 row in set (0.00 sec)

W维西的建议:set字符集,导入成功

mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set character_set_server = 'gbk';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set character_set_database ='gbk';
Query OK, 0 rows affected (0.00 sec)
 
mysql> load data infile '/tmp/test.txt' into table test fields terminated by ',';
Query OK, 1 row affected (0.05 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> select * from test;
+----+--------+---------------------+
| id | char_1 | gmt_create          |
+----+--------+---------------------+
|  1 | 李杰   | 2011-10-09 16:12:40 |
+----+--------+---------------------+

BTW:并没有set字符集,在建表时指定latin1字符集也能导入成功

mysql> CREATE TABLE `test_latin` (
    ->   `id` int(11) NOT NULL ,
    ->   `char_1` varchar(30) NOT NULL,
    ->   `gmt_create` datetime NOT NULL
    -> ) ENGINE=BRIGHTHOUSE  DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
 
mysql> load data infile '/tmp/test.txt' into table test_latin fields terminated by ',';
Query OK, 1 row affected (0.07 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
 
mysql> select * from test_latin;
+----+--------+---------------------+
| id | char_1 | gmt_create          |
+----+--------+---------------------+
|  1 | 李杰   | 2011-10-09 16:12:40 |
+----+--------+---------------------+
1 row in set (0.00 sec)

3、导入文件中含有转义符处理

今天发现”W维西”已整理出来,大家可以参考他的blog:

http://www.mysqlsky.com/201110/infobright-escapedbytip


2 Responses to “infobright的问题汇总”

  1. Haidong Ji说道:

    不知道你有没有做大量数据输入的测试。我注意到如果数据在进入InfoBright之前需要转变的话,用命令行将之转化后输入到一个pipe文件,然后再用另一个session来做load data infile,这样速度会很快。中间可以采用pt-fifo-split来调整每次的流量,可以参考一下这篇文章:)

    http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/

Post a Comment