Re: Fw: Load data infile & replication - 3.23.56

[Sohail Hasan]

Dear Alexander,

I am running mysql 4.0.12 on both the master and slave servers. I am 
facing the same problem with a slight variation. When I am trying to 
import my database dump on the master the replication fails and slave 
thread exits with the following error in the hostname.err file on the slave:

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,
...
...
...
 FOREIGN KEY (`PARAMETERID`) REFERENCES `cms.parameter` 
(`PARAMETERID`)) TYPE=InnoDB', error_code=1005030325 18:12:54  Error 
running query, slave SQL thread aborted. Fix the problem, and restart 
the slave SQL thread with "SLAVE START". We stopped at log 
'qfarooq-bin.004' position 289

For you reference I have uploaded my table descriptions and database on 
the secret folder. Please have a look at these files:

mysqlCMS.sql
cms3.dmp.gz
mycms.cnf
Sincerely,

Sohail

Alexander Keremidarski wrote:

Jeff,

Jeff Kilbride wrote:
<cut>

Subject: BUG: Load data infile & replication - 3.23.56



Replication doesn't seem to be replicating LOAD DATA INFILE 
correctly in
3.23.56. Starting with a master and slave that were in sync, I 
imported a
file:

<cut>

Most probably slave is out of sync with master BEFORE you issue LOAD 
DATA. This can explain why both servers skip different number of rows.

LOAD DATA should skip rows in case there are are duplicate Primary Key 
values.
You use IGNORE keyword to instruct it to continue in this case.

Notice that the slave doesn't skip the records that the master does, so

<cut>

Remember that Replication does not replicate your Data. What it does 
is to
execute same queries on Slave after they are executed on Master - so 
called
"Logical Replication".

There is no easy way to check if data are in sync and every non-select 
query
issued on Slave renders Slave out of sync. MySQL has no way to prevent 
it. Obviously if your queries are executed on tables with different 
content result will be different.

It is your applicaton responcibility to make sure no queries on Slave 
touch the data.

As in your case Master skips number of rows, but Slave accepts them 
this might mean some of the following:

1. Table on Slave has no Primary Key or it is different than on Master.
  use SHOW CREATE TABLE on both Master and Slave and compare them
2. Those skipped rows used to exist on Slave, but were deleted there 
(DELETE issued on Slave only).

3. There are rows Inserted on Master which for unknown reason were not 
Replicated
  3.1. Rows were inserted before Replication was started or Reset
  3.2. Some queries were skipped with SQL_SLAVE_SKIP_COUNTER=N
  3.3. Replication failed for some queries, but silently continued

Only in case of 3.3. there is chance it is Bug

Try to repeat it with newly created table making sure nothing changes 
this table durin your test. If you can repeat it send us detailed 
instructions on how to repeat it.

If possible upload your table and data file at 
ftp://support.mysql.com/pub/mysql/secret/

master and slave are out of sync after the LOAD DATA INFILE executes. 
I am
running the 3.23.56 binaries, RedHat 7.3, on both machines. Also, is 
there
any way to find out what records were skipped and why?


You can't see anything more than number of Warrnings before version 4.1
We have new feature implemented in 4.1
SHOW WARNINGS;
Best regards



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]