Hi
Thanks for your bug report.
In latest 3.23 and 4.0, the slave ignores any LOAD DATA INFILE
as long as one of these options is set on the slave :
replicate_do_table
replicate_wild_do_table
replicate_ignore_table
replicate_wild_ignore_table
regardless of the values of these options, which is a bug.
This bug will be fixed in the next 4.0 release and probably in the next
3.23 release (in version 3.23 we currently fix only really critical bugs, but
as this bug is very simple to fix, it may get fixed too).
If you're using a source distribution, here is a patch for 3.23,
for the sql/slave.cc file :
===== slave.cc 1.133 vs edited =====
*** /tmp/slave.cc-1.133-4477 Sat Jan 18 22:38:53 2003
--- edited/slave.cc Thu Feb 20 22:01:17 2003
***************
*** 1091,1096 ****
--- 1091,1097 ----
tables.db = thd->db;
tables.alias= tables.real_name= (char*)lev->table_name;
tables.lock_type = TL_WRITE;
+ tables.updating= 1;
// the table will be opened in mysql_load
if(table_rules_on && !tables_ok(thd, &tables))
{
Regards,
Guilhem
On Thursday 20 February 2003 20:49, Jeff Kilbride wrote:
> Heikki / Egor,
>
> I am not using the LOCAL keyword, but I am using selective replication with
> the replicate-do-table directive. Could this be causing problems? Here are
> the relevant options from my.cnf on the slave:
>
> # The MySQL server
> [mysqld]
> port = 3306
> socket = /tmp/mysql.sock
> skip-locking
> skip-name-resolve
> set-variable = max_connections=250
> set-variable = key_buffer=256M
> set-variable = max_allowed_packet=1M
> set-variable = table_cache=256
> set-variable = sort_buffer=2M
> set-variable = record_buffer=2M
> set-variable = myisam_sort_buffer_size=64M
> set-variable = thread_cache=8
>
> # Try number of CPU's*2 for thread_concurrency
> set-variable = thread_concurrency=4
>
> # Replication options
> low-priority-updates
> delay-key-write-for-all-tables
> server-id = 3
> master-host = XXX.XXX.XXX.XXX
> master-user = myuser
> master-password = mypass
> master-port = 3306
> replicate-do-table = matab.list
>
> When I issued the LOAD DATA command using the command line client, I had
> the "matab" database selected. From the slow query log excerpt in my last
> post, you can see that I was loading into the the "list" table -- which is
> the table I have specified in the replicate-do-table directive above. Would
> specifying "matab.list" make a difference?
>
> The file I was loading was approx. 5MB and none of the data made it to the
> slaves.
>
> Thanks,
> --jeff
>
> ----- Original Message -----
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, February 20, 2003 5:32 AM
> Subject: re: BUG? 3.23.55 not replicating LOAD DATA INFILE
>
> > Jeff, Egor,
> >
> > Guilhem Bichot just found a bug in replication of LOAD DATA LOCAL INFILE
>
> in
>
> > 4.0.
> >
> > The problem is the LOCAL keyword. Of a large import file, only 8 kB or 16
>
> kB
>
> > will actually be replicated in the slave. Do you use the LOCAL keyword?
> >
> > I do not know if the bug affects 3.23.
> >
> > Are you using selective replication of some tables or databases? If yes,
> > check also that your LOAD DATA INFILE is not excluded by these rules.
> >
> > Regards,
> >
> > Heikki
> > Innobase Oy
> > sql query
> >
> > .............
> > Subject: re: BUG? 3.23.55 not replicating LOAD DATA INFILE
> > From: Egor Egorov
> > Date: Thu, 20 Feb 2003 14:07:02 +0200
> >
> > On Thursday 20 February 2003 01:04, Jeff Kilbride wrote:
> > > I have one master and two slaves all running 3.23.55-max on RedHat 7.3.
> > > I've had replication up and running smoothly for several days. Today, I
> > > decided to try out LOAD DATA INFILE on the master. After successfully
> > > loading on the master, I checked both slaves and neither one had the
> > > new data. There are no errors in any of the log files and both slaves
> > > are
> >
> > still
> >
> > > replicating correctly. Only the LOAD DATA info is missing on the
> > > slaves.
>
> I
>
> > > have the slow query log running on the master and the LOAD DATA
>
> statement
>
> > > showed up there:
> > >
> > > # Time: 030219 17:09:02
> > > # User@Host: root[root] @ localhost []
> > > # Query_time: 34 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
> > > SET timestamp=1045692542;
> > > load data infile '/home/jschaffe/stro0002.txt'
> > > ignore
> > > into table list
> > > (email);
> > >
> > > Are there any settings in my.cnf that would affect this? The manual
>
> says:
> > > "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."
> > >
> > > When exactly is the "time of update propagation"? I'm assuming this is
> > > immediately, like all other replication tasks. The file still resides
> > > on the master, right now, but the slaves still haven't received the
> > > data.
>
> Is
>
> > > this a bug or am I missing something? Here are the server settings from
> > > my.cnf on the master:
> >
> > Hm .. I tested LOAD DATA INFILE with replication and it works for me ..
> > What is your slave replication options in my.cnf?
> >
> >
> >
> > --
> > For technical support contracts, goto https://order.mysql.com/?ref=ensita
> > This email is sponsored by Ensita.net http://www.ensita.net/
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Egor Egorov
> > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
> > <___/ www.mysql.com
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
>
> <[EMAIL PROTECTED]>
>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com +33 5 56 88 34 39
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
