Error in mysql replication with LOAD DATA INFILE

--90e6ba6e8c10fc7ae40497cf2536
Content-Type: text/plain; charset=ISO-8859-1

Hi guys,

i am facing a serious issue with my replication , i tried so many things but
no luck.

my replication is running with mysql 5.0.51a in master and 5.0.90 in slave.

we run LOAD DATA INFILE in master to process some csv files and load it into
a table, it runs perfectly well in master but when it comes to slave it
stops with SQL SYNTAX error

i tried running the LOAD DATA INFILE manually on the slave , but it says
different error as below

mysql> load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database
directory or be readable by all

when i chcked the file persmission it is

-rw-rw---- 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161



snippet from my error log
====================

101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in
log '.000127' at position 923914670, relay log
'/var/lib/mysql/slave-relay.000002' position: 39311
101219 0:06:32 [Note] Slave I/O thread: connected to master
'repli_user@221.131.104.66:3306',replication started in log '.000127' at
position 946657303
101219 0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near ''' at line 1' on query. Default database:
'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data'
IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY
'"' ESCAPED BY '', Error_code: 1064
101219 0:06:33 [Warning] Slave: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near ''' at line 1 Error_code: 1064
101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log '.000127' position 926912155



please help me fixing this ..

thanks in advance..

thanks
Anand

--90e6ba6e8c10fc7ae40497cf2536--

Re: Error in mysql replication with LOAD DATA INFILE

Hi Anand,

Just try 'load data local infile',it maybe work.

Eric

2010/12/20 Anand :
> Hi guys,
>
> i am facing a serious issue with my replication , i tried so many things =
but
> no luck.
>
> my replication is running with mysql 5.0.51a in master and 5.0.90 in slav=
e.
>
> we run LOAD DATA INFILE in master to process some csv files and load it i=
nto
> a table, it runs perfectly well in master but when it comes to slave it
> stops with SQL SYNTAX error
>
> i tried running the LOAD DATA INFILE manually on the slave , but it says
> different error as below
>
> mysql> load data  infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
> ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database
> directory or be readable by all
>
> when  i chcked the file persmission it is
>
> -rw-rw---- 1 mysql mysql          0 Dec 18 23:53=
 /tmp/SQL_LOAD-4-3-161
>
>
>
> snippet from my error log
> ====================
>
> 101219  0:06:32 [Note] Slave SQL thread initialized, starting replic=
ation in
> log '.000127' at position 923914670, relay log
> '/var/lib/mysql/slave-relay.000002' position: 39311
> 101219  0:06:32 [Note] Slave I/O thread: connected to master
> 'repli_user@221.131.104.66:3306',replication started in log '.000127' at
> position 946657303
> 101219  0:06:33 [ERROR] Slave SQL: Error 'You have an error in your =
SQL
> syntax; check the manual that corresponds to your MySQL server version fo=
r
> the right syntax to use near ''' at line 1' on query. Default database:
> 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.dat=
a'
> IGNORE INTO  TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLO=
SED BY
> '"' ESCAPED BY '', Error_code: 1064
> 101219  0:06:33 [Warning] Slave: You have an error in your SQL synta=
x; check
> the manual that corresponds to your MySQL server version for the right
> syntax to use near ''' at line 1 Error_code: 1064
> 101219  0:06:33 [ERROR] Error running query, slave SQL thread aborte=
d. Fix
> the problem, and restart the slave SQL thread with "SLAVE START". We stop=
ped
> at log '.000127' position 926912155
>
>
>
> please help me fixing this ..
>
> thanks in advance..
>
> thanks
> Anand
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Error in mysql replication with LOAD DATA INFILE

--90e6ba6e8a108a506d0497d286c3
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Dec 20, 2010 at 9:00 AM, Anand wrote:

> Hi guys,
>
> i am facing a serious issue with my replication , i tried so many things
> but no luck.
>
> my replication is running with mysql 5.0.51a in master and 5.0.90 in slave.
>
> we run LOAD DATA INFILE in master to process some csv files and load it
> into a table, it runs perfectly well in master but when it comes to slave it
> stops with SQL SYNTAX error
>
> i tried running the LOAD DATA INFILE manually on the slave , but it says
> different error as below
>
> mysql> load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
> ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database
> directory or be readable by all
>
> when i chcked the file persmission it is
>
> -rw-rw---- 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161
>
>
>
> snippet from my error log
> ====================
>
> 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication
> in log '.000127' at position 923914670, relay log
> '/var/lib/mysql/slave-relay.000002' position: 39311
> 101219 0:06:32 [Note] Slave I/O thread: connected to master
> 'repli_user@221.131.104.66:3306',replication started in log '.000127' at
> position 946657303
> 101219 0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version for
> the right syntax to use near ''' at line 1' on query. Default database:
> 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data'
> IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY
> '"' ESCAPED BY '', Error_code: 1064
> 101219 0:06:33 [Warning] Slave: You have an error in your SQL syntax;
> check the manual that corresponds to your MySQL server version for the right
> syntax to use near ''' at line 1 Error_code: 1064
> 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix
> the problem, and restart the slave SQL thread with "SLAVE START". We stopped
> at log '.000127' position 926912155
>
>
>
> please help me fixing this ..
>
> thanks in advance..
>
> thanks
> Anand
>

--90e6ba6e8a108a506d0497d286c3--

Re: Error in mysql replication with LOAD DATA INFILE

--90e6ba6e8f024ab2820497d9e00f
Content-Type: text/plain; charset=ISO-8859-1

The application is designed to work such a way that it will process the csv
files daily as part of the aggregate process to calculate some metrics.

it runs fine on the master, when it come to slave through replicaiton it
fails with the error.

i even tried upgrading the slave to latest version mysql 5.1.53 after i see
some post on the internet saying we have some issues in the older version ,
but it keeps giving the same error.


thanks
Anand


On Mon, Dec 20, 2010 at 7:42 PM, who.cat wrote:

> i wanna know you have done LOAD DATA INFILE in master ,why are you tring
> to do it in the slave ?The master didn't replication the data to the master
> ?
>
> All you best
> ------------------------
> What we are struggling for ?
> The life or the life ?
>
>
>
>
> On Mon, Dec 20, 2010 at 3:32 PM, Anand Kumar wrote:
>
>> On Mon, Dec 20, 2010 at 9:00 AM, Anand wrote:
>>
>> > Hi guys,
>> >
>> > i am facing a serious issue with my replication , i tried so many things
>> > but no luck.
>> >
>> > my replication is running with mysql 5.0.51a in master and 5.0.90 in
>> slave.
>> >
>> > we run LOAD DATA INFILE in master to process some csv files and load it
>> > into a table, it runs perfectly well in master but when it comes to
>> slave it
>> > stops with SQL SYNTAX error
>> >
>> > i tried running the LOAD DATA INFILE manually on the slave , but it says
>> > different error as below
>> >
>> > mysql> load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
>> > ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the
>> database
>> > directory or be readable by all
>> >
>> > when i chcked the file persmission it is
>> >
>> > -rw-rw---- 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161
>> >
>> >
>> >
>> > snippet from my error log
>> > ====================
>> >
>> > 101219 0:06:32 [Note] Slave SQL thread initialized, starting
>> replication
>> > in log '.000127' at position 923914670, relay log
>> > '/var/lib/mysql/slave-relay.000002' position: 39311
>> > 101219 0:06:32 [Note] Slave I/O thread: connected to master
>> > 'repli_user@221.131.104.66:3306',replication started in log '.000127'
>> at
>> > position 946657303
>> > 101219 0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
>> > syntax; check the manual that corresponds to your MySQL server version
>> for
>> > the right syntax to use near ''' at line 1' on query. Default database:
>> > 'caratlane_diamonds'. Query: 'LOAD DATA INFILE
>> '/tmp/SQL_LOAD-4-3-161.data'
>> > IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED
>> BY
>> > '"' ESCAPED BY '', Error_code: 1064
>> > 101219 0:06:33 [Warning] Slave: You have an error in your SQL syntax;
>> > check the manual that corresponds to your MySQL server version for the
>> right
>> > syntax to use near ''' at line 1 Error_code: 1064
>> > 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted.
>> Fix
>> > the problem, and restart the slave SQL thread with "SLAVE START". We
>> stopped
>> > at log '.000127' position 926912155
>> >
>> >
>> >
>> > please help me fixing this ..
>> >
>> > thanks in advance..
>> >
>> > thanks
>> > Anand
>> >
>>
>
>

--90e6ba6e8f024ab2820497d9e00f--