Announcement

Announcement Module
Collapse
No announcement yet.

when load data with mysqldump backup, got err.log: WSREP: NULL key skipped

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • when load data with mysqldump backup, got err.log: WSREP: NULL key skipped

    121108 10:10:14 [Note] WSREP: NULL key skipped: INSERT INTO `user_other_info` VALUES (81219282,0x78696E78696E3033303340736F68752E636F6D ,'',0x30,0 x3133383130353039383830,0x31,'','','','',0x78696E7 8696E30333 0335F4E4557,0x32,0x33303031,0x33,0x313130303030,0x 3131303130 30,0x312C38,'','','
    121108 10:10:15 [Note] WSREP: NULL key skipped: INSERT INTO `user_other_info` VALUES (81219282,0x78696E78696E3033303340736F68752E636F6D ,'',0x30,0 x3133383130353039383830,0x31,'','','','',0x78696E7 8696E30333 0335F4E4557,0x32,0x33303031,0x33,0x313130303030,0x 3131303130 30,0x312C38,'','','

    it took me 7 days to load data with mysqldump backup (300G)

    TEST

    mysql> create table testnull1 (id int,userid varchar(20) null,primary key (id))engine innodb;
    Query OK, 0 rows affected (0.00 sec)

    mysql> create table testnull2 (id int,userid varchar(20) null,primary key (id),unique key uni_userid(userid))engine innodb;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into testnull1 values(1,'u1');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into testnull1 values(2,null);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into testnull2 values(1,'u2');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into testnull2 values(2,null);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into testnull2 values(3,null);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into testnull1 values(3,null);
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from testnull1;
    +----+--------+
    | id | userid |
    +----+--------+
    | 1 | u1 |
    | 2 | NULL |
    | 3 | NULL |
    +----+--------+
    3 rows in set (0.00 sec)

    mysql> select * from testnull2;
    +----+--------+
    | id | userid |
    +----+--------+
    | 2 | NULL |
    | 3 | NULL |
    | 1 | u2 |
    +----+--------+
    3 rows in set (0.00 sec)


    LOG:

    121108 17:05:21 [Note] WSREP: TO BEGIN: 4, 0 : create table testnull1 (id int,userid varchar(20) null,primary key (id))engine innodb
    121108 17:05:21 [Note] WSREP: TO BEGIN: 5, 2
    121108 17:05:21 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1
    121108 17:05:21 [Note] WSREP: TO END: 5, 2 : create table testnull1 (id int,userid varchar(20) null,primary key (id))engine innodb
    121108 17:05:21 [Note] WSREP: TO END: 5
    121108 17:05:52 [Note] WSREP: TO BEGIN: 5, 0 : create table testnull2 (id int,userid varchar(20) null,primary key (id),unique key uni_userid(userid))engine innodb
    121108 17:05:52 [Note] WSREP: TO BEGIN: 6, 2
    121108 17:05:52 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1
    121108 17:05:52 [Note] WSREP: TO END: 6, 2 : create table testnull2 (id int,userid varchar(20) null,primary key (id),unique key uni_userid(userid))engine innodb
    121108 17:05:52 [Note] WSREP: TO END: 6
    121108 17:06:38 [Note] WSREP: NULL key skipped: insert into testnull2 values(2,null)
    121108 17:06:52 [Note] WSREP: NULL key skipped: insert into testnull2 values(3,null)

  • #2
    I also loaded data with this dump file into Percona-Server-server-55-5.5.27-rel29.0.315, it took me 1 day.

    Comment

    Working...
    X