Fw: Load data infile & replication - 3.23.56

Previously posted in the main list. One further comment, I am using
replicate-do-table for the affected table in my slave config.

Thanks,
--jeff

----- Original Message -----
From: "Jeff Kilbride"
To: "MySQL"
Sent: Tuesday, March 25, 2003 11:05 PM
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:
>
> ---------------------------
> MASTER
> mysql> select count(*) from list where sourceID=0;
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.01 sec)
>
> mysql> load data infile '/home/stuff/new50001.txt'
> -> ignore
> -> into table list
> -> (info);
> Query OK, 189662 rows affected (1 min 18.43 sec)
> Records: 190660 Deleted: 0 Skipped: 998 Warnings: 0
>
> mysql> select count(*) from list where sourceID=0;
> +----------+
> | count(*) |
> +----------+
> | 189662 |
> +----------+
> 1 row in set (0.24 sec)
>
> SLAVE
> mysql> select count(*) from list where sourceID=0;
> +----------+
> | count(*) |
> +----------+
> | 190660 |
> +----------+
> 1 row in set (0.21 sec)
> ---------------------------
>
> Notice that the slave doesn't skip the records that the master does, so
the
> 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?
>
> Thanks,
> --jeff
>
>


--
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: Fw: Load data infile & replication - 3.23.56

Jeff Kilbride writes:
> Previously posted in the main list. One further comment, I am using
> replicate-do-table for the affected table in my slave config.
>
> Thanks,
> --jeff

// for the filter

With 3.23, file that is loaded has to be present on the slave machine
too.

This is well documented in our manual.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
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: Fw: Load data infile & replication - 3.23.56

Hi Sinisa,

Can you send me a link to where this is documented? The only thing I can
find is in section 4.10.4, which says:

a.. In 3.23, LOAD DATA INFILE will be handled properly as long as the file
still resides on the master server at the time of update propagation. LOAD
LOCAL DATA INFILE will be skipped. In 4.0, this limitation is not present -
all forms of LOAD DATA INFILE are properly replicated.

This says the file must still reside on the master, but nothing about the
file having to reside on the slave.

Thanks,
--jeff

// for the filter

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Wednesday, March 26, 2003 12:37 PM
Subject: Re: Fw: Load data infile & replication - 3.23.56


> Jeff Kilbride writes:
> > Previously posted in the main list. One further comment, I am using
> > replicate-do-table for the affected table in my slave config.
> >
> > Thanks,
> > --jeff
>
> // for the filter
>
> With 3.23, file that is loaded has to be present on the slave machine
> too.
>
> This is well documented in our manual.
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
>


--
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: Fw: Load data infile & replication - 3.23.56

I tried this again -- this time with the file to be loaded in the same
location on both the master and the slave machines. Same result. The slave
loaded all the data, even though the master skipped several records.

Thanks,
--jeff

----- Original Message -----
From: "Jeff Kilbride"
To:
Cc:
Sent: Wednesday, March 26, 2003 2:32 PM
Subject: Re: Fw: Load data infile & replication - 3.23.56


> Hi Sinisa,
>
> Can you send me a link to where this is documented? The only thing I can
> find is in section 4.10.4, which says:
>
> a.. In 3.23, LOAD DATA INFILE will be handled properly as long as the file
> still resides on the master server at the time of update propagation. LOAD
> LOCAL DATA INFILE will be skipped. In 4.0, this limitation is not
present -
> all forms of LOAD DATA INFILE are properly replicated.
>
> This says the file must still reside on the master, but nothing about the
> file having to reside on the slave.
>
> Thanks,
> --jeff
>
> // for the filter
>
> ----- Original Message -----
> From: "Sinisa Milivojevic"
> To:
> Cc:
> Sent: Wednesday, March 26, 2003 12:37 PM
> Subject: Re: Fw: Load data infile & replication - 3.23.56
>
>
> > Jeff Kilbride writes:
> > > Previously posted in the main list. One further comment, I am using
> > > replicate-do-table for the affected table in my slave config.
> > >
> > > Thanks,
> > > --jeff
> >
> > // for the filter
> >
> > With 3.23, file that is loaded has to be present on the slave machine
> > too.
> >
> > This is well documented in our manual.
> >
> > --
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> > <___/ www.mysql.com
> >
> >
>


--
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: Fw: Load data infile & replication - 3.23.56

Hi Jeff,

could you please ftp a file that causes the problem when LOADed into this
`list` table
(instructions below) ?

Thanks!

# If you can't produce a test case in a few rows, or if the test table is too
big to be mailed to the mailing list (more than 10 rows), you should dump
your tables using mysqldump and create a `README' file that describes your
problem. Create a compressed archive of your files using tar and gzip or zip,
and use ftp to transfer the archive to
ftp://support.mysql.com/pub/mysql/secret/.

Guilhem

On Thursday 27 March 2003 07:17, Jeff Kilbride wrote:
> I tried this again -- this time with the file to be loaded in the same
> location on both the master and the slave machines. Same result. The slave
> loaded all the data, even though the master skipped several records.
>
> Thanks,
> --jeff
>
> ----- Original Message -----
> From: "Jeff Kilbride"
> To:
> Cc:
> Sent: Wednesday, March 26, 2003 2:32 PM
> Subject: Re: Fw: Load data infile & replication - 3.23.56
>
>
> > Hi Sinisa,
> >
> > Can you send me a link to where this is documented? The only thing I can
> > find is in section 4.10.4, which says:
> >
> > a.. In 3.23, LOAD DATA INFILE will be handled properly as long as the file
> > still resides on the master server at the time of update propagation. LOAD
> > LOCAL DATA INFILE will be skipped. In 4.0, this limitation is not
> present -
> > all forms of LOAD DATA INFILE are properly replicated.
> >
> > This says the file must still reside on the master, but nothing about the
> > file having to reside on the slave.
> >
> > Thanks,
> > --jeff
> >
> > // for the filter
> >
> > ----- Original Message -----
> > From: "Sinisa Milivojevic"
> > To:
> > Cc:
> > Sent: Wednesday, March 26, 2003 12:37 PM
> > Subject: Re: Fw: Load data infile & replication - 3.23.56
> >
> >
> > > Jeff Kilbride writes:
> > > > Previously posted in the main list. One further comment, I am using
> > > > replicate-do-table for the affected table in my slave config.
> > > >
> > > > Thanks,
> > > > --jeff
> > >
> > > // for the filter
> > >
> > > With 3.23, file that is loaded has to be present on the slave machine
> > > too.
> > >
> > > This is well documented in our manual.
> > >
> > > --
> > > __ ___ ___ ____ __
> > > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> > > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
> > > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> > > <___/ www.mysql.com
> > >
> > >
> >
>
>

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com

--
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: Fw: Load data infile & replication - 3.23.56

Jeff,

Jeff Kilbride wrote:



> 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:


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



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 Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
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: Fw: Load data infile & replication - 3.23.56

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:
>
>
>
>> 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:
>>
>
>
> 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
>>
>
>
>
> 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/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Fw: Load data infile & replication - 3.23.56

Jeff Kilbride writes:
> I tried this again -- this time with the file to be loaded in the same
> location on both the master and the slave machines. Same result. The slave
> loaded all the data, even though the master skipped several records.
>
> Thanks,
> --jeff
>

This is not a bug.

This is caused by a difference in table contents on the slave versus
those on the master.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



--
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: Re: Fw: Load data infile & replication - 3.23.56

On Thursday 27 March 2003 14:22, Sohail Hasan wrote:

> 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,

See: the error 150 is:

[egor@ezekiel egor]$ perror 150
150 = Foreign key constraint is incorrectly formed






--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com




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