Strange MySQL replication update error (Magento) -


mysql replication little bit new me, looks i've got working till errors don't understand.

some background: i'm replicating databases (over ssl) mysql 5.6.22 server mariadb 10.0.15 server, isn't doing else being dedicated slave master. executes queries runs updating problem magento database. if skip query, run similar query causes same error.

this error slave status gives me:

could not execute update_rows event on table magento_db.log_visitor; column 'visitor_id' cannot null, error_code: 1048; can't find record in 'log_visitor', error_code: 1032; column 'visitor_id' cannot null, error_code: 1048; handler error ha_err_key_not_found; event's master log mysql-bin.000121, end_log_pos 7656

exec_master_log_pos 7215, assume that's irrelevant , error in next query (/transaction block).

here piece of (verbose) mysqlbinlog:

commit/*!*/;  # @ 7215  #150330  2:19:45 server id 1  end_log_pos 7292 crc32 0xf975481b 	query	thread_id=25	exec_time=0	error_code=0  set timestamp=1427674785/*!*/;  begin  /*!*/;  # @ 7292  # @ 7358  #150330  2:19:45 server id 1  end_log_pos 7358 crc32 0x0312921e 	table_map: `magento_db`.`log_url_info` mapped number 2528  #150330  2:19:45 server id 1  end_log_pos 7497 crc32 0xe3704a8b 	write_rows: table id 2528 flags: stmt_end_f  ### insert `magento_db`.`log_url_info`  ### set  ###   @1=12534083 /* longint meta=0 nullable=0 is_null=0 */  ###   @2='http://www.myshop.com/catalog/category/view/id/29?cat=31&color=22&dir=desc&order=position&price=9-' /* varstring(765) meta=765 nullable=1 is_null=0 */  ###   @3=null /* varstring(765) meta=765 nullable=1 is_null=1 */  # @ 7497  # @ 7565  #150330  2:19:45 server id 1  end_log_pos 7565 crc32 0x340012cd 	table_map: `magento_db`.`log_visitor` mapped number 2513  #150330  2:19:45 server id 1  end_log_pos 7656 crc32 0xd3d2e26f 	update_rows: table id 2513 flags: stmt_end_f  ### update `magento_db`.`log_visitor`  ###  ###   @1=3036630 /* longint meta=0 nullable=0 is_null=0 */  ### set  ###   @2='deq65v4ks7tgahp2lvih8s74j1' /* varstring(192) meta=192 nullable=0 is_null=0 */  ###   @3=1427667585 /* timestamp(0) meta=0 nullable=1 is_null=0 */  ###   @4=1427667585 /* timestamp(0) meta=0 nullable=0 is_null=0 */  ###   @5=12534083 /* longint meta=0 nullable=0 is_null=0 */  ###   @6=1 /* shortint meta=0 nullable=0 is_null=0 */  # @ 7656  # @ 7714  #150330  2:19:45 server id 1  end_log_pos 7714 crc32 0xc1eee09b 	table_map: `magento_db`.`log_url` mapped number 2529  #150330  2:19:45 server id 1  end_log_pos 7770 crc32 0xf7bcccad 	write_rows: table id 2529 flags: stmt_end_f  ### insert `magento_db`.`log_url`  ### set  ###   @1=12534083 /* longint meta=0 nullable=0 is_null=0 */  ###   @2=3036630 /* longint meta=0 nullable=1 is_null=0 */  ###   @3=1427667585 /* timestamp(0) meta=0 nullable=0 is_null=0 */  # @ 7770  #150330  2:19:45 server id 1  end_log_pos 7801 crc32 0x51775dd4 	xid = 29537  commit/*!*/;  # @ 7801  #150330  2:19:53 server id 1  end_log_pos 7886 crc32 0xd6d724c7 	query	thread_id=26	exec_time=0	error_code=0  set timestamp=1427674793/*!*/;

visitor_id first column shows in phpmyadmin, when perform show columns log_visitor;, i'm guessing column maps '@1' (couldn't find how verify this). when search record visitor_id 3036630, finds one. note not due external queries, when perform start slave; again, hangs on same error. also, tried running mysql_upgrade on slave, other warning, solved nothing.

bottom line is: don't know how interpret error, maybe looking @ wrong query? feels me there shouldn't error, maybe incompatibility?

any suggestions welcome!

edit: requested, show create table, seem same on both servers after doing diff, except increment index:

create table `log_visitor` (   `visitor_id` bigint(20) unsigned not null auto_increment comment 'visitor id',   `session_id` varchar(64) not null comment 'session id',   `first_visit_at` timestamp null default null comment 'first visit time',   `last_visit_at` timestamp not null default '0000-00-00 00:00:00' comment 'last visit time',   `last_url_id` bigint(20) unsigned not null default '0' comment 'last url id',   `store_id` smallint(5) unsigned not null comment 'store id',   primary key (`visitor_id`)  ) engine=innodb auto_increment=3036631 default charset=utf8 comment='log visitors table'

the slave created dropping databases (i've had multiple attempts), doing mysqldump databases after acquiring read lock on master, importing slave , starting slave @ right position. queries , when look, got update error 1 described here.

it seems found solution. once copied/adjusted nice my.cnf master, included mysql 5.6 setting "binlog_row_image = minimal". causes binlog skip columns in set-clause of update, in where-clause , unchanged. mariadb not seem have implemented setting , default binlog row format requires value fields.


Comments

Popular posts from this blog

cakephp - simple blog with croogo -

How to group boxplot outliers in gnuplot -

bash - Performing variable substitution in a string -