Re: LOAD DATA in replication
Hello Neubyr, On 1/29/2014 7:16 PM, neubyr wrote: I am trying to understand MySQL statement based replication with LOAD DATA LOCAL INFILE statement'. According to manual - https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html - LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I am seeing it replicated as 'LOAD DATA INFILE'. I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements. Appreciate any help on this. Master is using MySQL 5.0 and slave is using MySQL 5.6. -thanks, N The slave is not receiving the file from your local disk. When that file arrives at the master (due to your LOAD DATA LOCAL ..) it is stored in the binary log and copied (via replication) to the slave where the slave performs a server-side LOAD DATA... . This is how STATEMENT-based replication operates. Does that make better sense? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: LOAD DATA in replication
Thanks for the details Shawn. So row based replication would avoid server side LOAD DATA on slave. Unfortunately, the Master is using MySQL ver 5.0, so I don't think it can use row based replication. - thanks, N On Thu, Jan 30, 2014 at 7:48 AM, shawn l.green shawn.l.gr...@oracle.comwrote: Hello Neubyr, On 1/29/2014 7:16 PM, neubyr wrote: I am trying to understand MySQL statement based replication with LOAD DATA LOCAL INFILE statement'. According to manual - https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html - LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I am seeing it replicated as 'LOAD DATA INFILE'. I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements. Appreciate any help on this. Master is using MySQL 5.0 and slave is using MySQL 5.6. -thanks, N The slave is not receiving the file from your local disk. When that file arrives at the master (due to your LOAD DATA LOCAL ..) it is stored in the binary log and copied (via replication) to the slave where the slave performs a server-side LOAD DATA... . This is how STATEMENT-based replication operates. Does that make better sense? -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
LOAD DATA in replication
I am trying to understand MySQL statement based replication with LOAD DATA LOCAL INFILE statement'. According to manual - https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html - LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I am seeing it replicated as 'LOAD DATA INFILE'. I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements. Appreciate any help on this. Master is using MySQL 5.0 and slave is using MySQL 5.6. -thanks, N
Re: LOAD DATA in replication
If I don't mistake, there are some parameters to make that you are saying. Check statement-based-replication and row-based-replication. I think that this could help you. Regards, Antonio.
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: [EMAIL PROTECTED] 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ 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/[EMAIL PROTECTED]
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: 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]
Re: BUG: Load data infile replication - 3.23.56
On Tue, Mar 25, 2003 at 11:05:06PM -0800, Jeff Kilbride wrote: 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: [snip] 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. Interesting. I have a theory, but I'd need to toy with it a bit before embarassing myself with a guess... Might you also put this up on bugs.mysql.com? Also, is there any way to find out what records were skipped and why? Nope. Not yet. I think that's coming in 4.1? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 50 days, processed 1,736,124,981 queries (394/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: BUG: Load data infile replication - 3.23.56
Jeremy, Also, is there any way to find out what records were skipped and why? Nope. Not yet. I think that's coming in 4.1? Yes and no: http://www.mysql.com/doc/en/SHOW_WARNINGS.html The MySQL server sends back the total number of warnings and errors you got for the last command. My interpretation of that is you won't be able to identify individual records that were skipped, just the total number of those. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
