replication failure while importing data.

Hi all,

I am getting a replication failure on the slave when I run a dump import
of my database on the master. Both servers are running mysql 4.0.12
production release. The error displayed on the slave error log file is
as follows:

ERROR: 1005 Can't create table './cms/category.frm' (errno: 150)030327
11:36:01 Slave: error 'Can't create table './cms/category.frm' (errno:
150)' on query 'CREATE TABLE category ( CATEGORYID int(10) unsigned NOT
NULL default '0',
PARENTCATEGORYID int(10) unsigned default NULL,
NAME varchar(255) binary NOT NULL default '',
FULLNAME blob NOT NULL,
ORDERNUMBER int(10) unsigned NOT NULL default '0',
CATLEVEL tinyint(4) NOT NULL default '0',
ISACTIVE tinyint(1) NOT NULL default '0',
CREATEDATE datetime NOT NULL default '0000-00-00 00:00:00',
CREATEDBYID int(10) unsigned NOT NULL default '0',
UPDATEDATE datetime NOT NULL default '0000-00-00 00:00:00',
UPDATEDBYID int(10) unsigned NOT NULL default '0',
UNSPSCCODE varchar(20) default NULL,
CATEGORYCODE varchar(50) binary NOT NULL default '',
DBUPDATEDATE datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (CATEGORYID),
KEY PARENTCATEGORYID (PARENTCATEGORYID),
KEY CREATEDBYID (CREATEDBYID),
KEY UPDATEDBYID (UPDATEDBYID),
FOREIGN KEY (`PARENTCATEGORYID`) REFERENCES `category` (`CATEGORYID`),
FOREIGN K', error_code=1005
030327 11:36:01 Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'sunx86-bin.003' position 233 11:43:51 Slave I/O thread exiting,
read up to log 'sunx86-bin.003', position 3465070

The table creation of this is as under:

create table category (
categoryid int unsigned not null,
parentcategoryid int unsigned null,
name varchar(255) binary not null,
fullname blob not null,
ordernumber int unsigned not null,
catlevel tinyint not null,
isactive bool not null,
createdate datetime not null,
createdbyid int unsigned not null,
updatedate datetime not null,
updatedbyid int unsigned not null,
unspsccode char(20) null,
categorycode varchar(50) binary not null,
dbupdatedate datetime not null,
constraint pk_cat primary key (categoryid),
index(parentcategoryid),index(createdbyid),index(updatedbyid ),
constraint fk_catcat foreign key (parentcategoryid)
references category (categoryid),
constraint fk_catdsa1 foreign key (createdbyid)
references datasourceauthor
(datasourceauthorid),
constraint fk_catdsa2 foreign key (updatedbyid)
references datasourceauthor
(datasourceauthorid)
) type=innodb;



Has anybody got an idea of what's going wrong here, is this a bug in the
mysql distribution or something wrong with the table creation.

Sohail


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: replication failure while importing data.

I am also using this before the import statement:

set foreign_key_checks=0;
set autocommit=0;

Sincerely,

Sohail

Sohail Hasan wrote:

> Hi all,
>
> I am getting a replication failure on the slave when I run a dump
> import of my database on the master. Both servers are running mysql
> 4.0.12 production release. The error displayed on the slave error log
> file is as follows:
>
> ERROR: 1005 Can't create table './cms/category.frm' (errno:
> 150)030327 11:36:01 Slave: error 'Can't create table
> './cms/category.frm' (errno: 150)' on query 'CREATE TABLE category (
> CATEGORYID int(10) unsigned NOT NULL default '0',
> PARENTCATEGORYID int(10) unsigned default NULL,
> NAME varchar(255) binary NOT NULL default '',
> FULLNAME blob NOT NULL,
> ORDERNUMBER int(10) unsigned NOT NULL default '0',
> CATLEVEL tinyint(4) NOT NULL default '0',
> ISACTIVE tinyint(1) NOT NULL default '0',
> CREATEDATE datetime NOT NULL default '0000-00-00 00:00:00',
> CREATEDBYID int(10) unsigned NOT NULL default '0',
> UPDATEDATE datetime NOT NULL default '0000-00-00 00:00:00',
> UPDATEDBYID int(10) unsigned NOT NULL default '0',
> UNSPSCCODE varchar(20) default NULL,
> CATEGORYCODE varchar(50) binary NOT NULL default '',
> DBUPDATEDATE datetime NOT NULL default '0000-00-00 00:00:00',
> PRIMARY KEY (CATEGORYID),
> KEY PARENTCATEGORYID (PARENTCATEGORYID),
> KEY CREATEDBYID (CREATEDBYID),
> KEY UPDATEDBYID (UPDATEDBYID),
> FOREIGN KEY (`PARENTCATEGORYID`) REFERENCES `category` (`CATEGORYID`),
> FOREIGN K', error_code=1005
> 030327 11:36:01 Error running query, slave SQL thread aborted. Fix
> the problem, and restart the slave SQL thread with "SLAVE START". We
> stopped at log 'sunx86-bin.003' position 233 11:43:51 Slave I/O
> thread exiting, read up to log 'sunx86-bin.003', position 3465070
>
> The table creation of this is as under:
>
> create table category (
> categoryid int unsigned not null,
> parentcategoryid int unsigned null,
> name varchar(255) binary not null,
> fullname blob not null,
> ordernumber int unsigned not null,
> catlevel tinyint not null,
> isactive bool not null,
> createdate datetime not null,
> createdbyid int unsigned not null,
> updatedate datetime not null,
> updatedbyid int unsigned not null,
> unspsccode char(20) null,
> categorycode varchar(50) binary not null,
> dbupdatedate datetime not null,
> constraint pk_cat primary key (categoryid),
> index(parentcategoryid),index(createdbyid),index(updatedbyid ),
> constraint fk_catcat foreign key (parentcategoryid)
> references category (categoryid),
> constraint fk_catdsa1 foreign key (createdbyid)
> references datasourceauthor
> (datasourceauthorid),
> constraint fk_catdsa2 foreign key (updatedbyid)
> references datasourceauthor
> (datasourceauthorid)
> ) type=innodb;
>
>
>
> Has anybody got an idea of what's going wrong here, is this a bug in
> the mysql distribution or something wrong with the table creation.
>
> Sohail
>



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org