test - ignore
ignore
pt-table-checksum: --ignore-tables-regex does not work properly?
Hi All. I use: percona-toolkit-2.2.4-1.noarch Percona-Server-server-55-5.5.28-rel29.1.335.rhel6.x86_64 Percona-Server-shared-compat-5.5.28-rel29.1.335.rhel6.x86_64 Percona-Server-client-55-5.5.28-rel29.1.335.rhel6.x86_64 Percona-Server-shared-55-5.5.28-rel29.1.335.rhel6.x86_64 on Centos 6.3 2.6.32-279.14.1.el6.x86_64 I have created checksum table and tried to use --ignore-tables-regex to remove some tables from checking. pt-table-checksum --chunk-size-limit= --nocheck-plan --replicate-check --ignore-tables-regex=^test.s_.*_tmp$ --ignore-tables=test.catalogsearch_fulltext,test.catalogsearch_result,test.report_event,test.report_viewed_product_index,test.z_crawler_log,test.z_logger_debug_ajax,test.z_crawler_queue,test.catalog_category_anc_categs_index_tmp,test.catalog_category_anc_products_index_tmp,test.catalog_category_product_index_enbl_tmp,test.catalog_category_product_index_tmp,test.catalog_product_index_eav_decimal_tmp,test.catalog_product_index_eav_tmp,test.catalog_product_index_price_bundle_opt_tmp,test.catalog_product_index_price_bundle_sel_tmp,test.catalog_product_index_price_bundle_tmp,test.catalog_product_index_price_cfg_opt_agr_tmp,test.catalog_product_index_price_cfg_opt_tmp,test.catalog_product_index_price_downlod_tmp,test.catalog_product_index_price_final_tmp,test.catalog_product_index_price_opt_agr_tmp,test.catalog_product_index_price_opt_tmp,test.catalog_product_index_price_tmp,test.cataloginventory_stock_status_tmp,test.z_I04_data_for_crawler,test.z_import_prices_mdk,test.z_import_prices_sku,test.z_import_translations,test.z_import_translations_model --recursion-method dsn=h=localhost,D=percona,t=dsns --user=percona --password=percona --nocheck-replication-filters --databases=test,mysql localhost But it does not work, for example table test.s_xxx_tmp gives error message: 09-18T03:10:47 Skipping table test.s_xxx_tmp because it has problems on these replicas: Table test.s_xxx_tmp does not exist on replica server.local This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 09-18T03:10:47 Error checksumming table test.s_xxx_tmp: DBD::mysql::db selectrow_hashref failed: Tab le 'test.s_xxx_tmp' doesn't exist [for Statement EXPLAIN SELECT * FROM `test`.`s_xxx_tmp` WHERE 1=1] at /usr/bin/pt-table-checksum line 6528. I've tried --ignore-tables-regex= multiple times: ^test.s_.*_tmp$ '^test.s_.*_tmp$' ^test.s_.\*_tmp$ ^test.s_\.\*_tmp$ but without good result. What is the correct syntax in this case? Best regards, Rafal Radecki.
re: ignore-db-dir
Hi! Noel == Noel Butler noel.but...@ausics.net writes: Noel Shaun, Noel Is this option planned for backport into 5.5.x ? Another option is to provide a patch and suggest to have it included in MariaDB 5.5. You can of course also consider to sponsor this so that we can do this for you... Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
ignore-db-dir
Shaun, Is this option planned for backport into 5.5.x ? Cheers signature.asc Description: This is a digitally signed message part
mysqldump --ignore-table
Dear all, I am currently trying to figure-out how I could ignore multiple tables in mysql using a simple a regex. For example I have multiple tables which have the following structure: mytable1, mytable2, ..,mytable100. And I would like these tables to be ignore when doing mysqldump by doing something like this: mysqldump --ignore-table = mydb.table* I am wondering if there is any way do something like this in mysql! Thank you kindly for the help, regards, daniel
Re: mysqldump --ignore-table
Hi Daniel,
you can use a workaround from the shell,
cd /path/to/your/database (e.g.: cd /var/lib/mysql/mydb)
ls -al *table** | awk '{print $8}' | awk -F. '{print --ignore-table=*mydb
*.$1}' | xargs mysqldump -u*root* -p*toor* *--your-flags **mydb*
It's not that beautiful but it should work.
Claudio
2011/6/8 zia mohaddes zia.si...@gmail.com
Dear all,
I am currently trying to figure-out how I could ignore multiple tables in
mysql using a simple a regex. For example I have multiple tables which have
the following structure: mytable1, mytable2, ..,mytable100. And I
would like these tables to be ignore when doing mysqldump by doing
something
like this:
mysqldump --ignore-table = mydb.table*
I am wondering if there is any way do something like this in mysql!
Thank you kindly for the help,
regards,
daniel
--
Claudio
Replication: ignore specified columns?
I know that you can ignore certain databases and tables in mysql replication, but is it possible to replicate all but a certain column or two from a table? This is 5.1.48 on linux. Thanks, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication: ignore specified columns?
No. If it is for security reasons: did you think about a view on slave db? (removing rights from source table) In case you dont want those columns to reach the slave you could use triggers to reflect changes from source table (or a view) on master to a table(with missing columns) that will be replicated. Just a couple of ideas Claudio 2010/7/23 Bryan Cantwell bcantw...@firescope.com I know that you can ignore certain databases and tables in mysql replication, but is it possible to replicate all but a certain column or two from a table? This is 5.1.48 on linux. Thanks, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: test - please ignore
i said: ignore! But did it work? ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
test - please ignore
i said: ignore! -- hartmut -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Getting mySQL to ignore case sensitivity of field names
Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. Many thanks Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. On Aug 27, 2009, at 2:57 PM, Adrian Aitken wrote: Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
Hi Scott, it's not the values I have a problem with, it's the fieldnames themselves. As an example the mysql.user table has 'Host' but when I do an update setting 'host' to a value it fails to update. I have to enter 'Host'. The mysql.com link seems to only talk about field values. Regards Adrian - Original Message - From: Scott Haneda To: Adrian Aitken Cc: mysql@lists.mysql.com Sent: Thursday, August 27, 2009 11:04 PM Subject: Re: Getting mySQL to ignore case sensitivity of field names http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
Oops, sorry about that. My understanding is this is OS dependent: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html You may be able to set lowercase tables names, but would always have to use lowercase. I would just stick to entering in the correct case, as the other methods seem prone to trouble. On Aug 27, 2009, at 3:04 PM, Scott Haneda wrote: http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. On Aug 27, 2009, at 2:57 PM, Adrian Aitken wrote: Hi, Google isn't my friend :-( How can I tell mySQL 5.0 to ignore the case of field names i.e. FullName should also be able to be referenced as fullname,fullNAME etc ? I'm running it on a linux box at home but my copy at work (running on Windows 2000 server) has this by default - I certainly set any options. When I Google all I seem to get are hits about ignoring case in select query values not field names. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
On Aug 27, 2009, at 5:11 PM, Adrian Aitken wrote: Hi Scott, it's not the values I have a problem with, it's the fieldnames themselves. As an example the mysql.user table has 'Host' but when I do an update setting 'host' to a value it fails to update. I have to enter 'Host'. The mysql.com link seems to only talk about field values. That should not happen. Column names are not case sensitive in MySQL. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html: Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Can we see your query? Regards Adrian - Original Message - From: Scott Haneda To: Adrian Aitken Cc: mysql@lists.mysql.com Sent: Thursday, August 27, 2009 11:04 PM Subject: Re: Getting mySQL to ignore case sensitivity of field names http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ignore accents in order by
PJ wrote: Let me put it this way, I am not having the problem. The problem seems to be withthe way that character encoding is set up on the internet - as confused and inconsistent as most everything else. You can put whatever charset you want in the header, in the collations in your database, your htmls... you see already that the options start to multiply rapidly... You're making it more complicated than it is. Just stick to UTF8 and you'll be fine. without even considering the browsers. So, I have tried about all combinations possible and there is no one way to implement display and use of accents. Sure there is. UTF-8. Period. UTF-8 does not handle them very well at all; iso-8895-1 doesn't either; you can set the coding on your browser to whatever you want - when you update or reload the file the little black diamond devils come back or turn into little blank squares on IE8... I think we've gone OT here, but honestly I have no problem with accents nor any other special characters anywhere - database, browser, whereever. And yes, I work with 4-5 different languages on a daily basis. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ignore accents in order by
I agree with Per, I use utf8 and it works fine for me, even with Chinese characters On Fri, Jun 12, 2009 at 8:40 AM, Per Jessen p...@computer.org wrote: PJ wrote: Let me put it this way, I am not having the problem. The problem seems to be withthe way that character encoding is set up on the internet - as confused and inconsistent as most everything else. You can put whatever charset you want in the header, in the collations in your database, your htmls... you see already that the options start to multiply rapidly... You're making it more complicated than it is. Just stick to UTF8 and you'll be fine. without even considering the browsers. So, I have tried about all combinations possible and there is no one way to implement display and use of accents. Sure there is. UTF-8. Period. UTF-8 does not handle them very well at all; iso-8895-1 doesn't either; you can set the coding on your browser to whatever you want - when you update or reload the file the little black diamond devils come back or turn into little blank squares on IE8... I think we've gone OT here, but honestly I have no problem with accents nor any other special characters anywhere - database, browser, whereever. And yes, I work with 4-5 different languages on a daily basis. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com
ignore accents in order by
Is there a way to order lists while ignoring the accents? So far, I have found nothing simple; and I need to keep the accents for output. The language is French (and québécois) :-) TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ignore accents in order by
Hi, I'm not having any problem on my local computer mysql select text,text2 from table1 order by text2 desc; +--+---+ | text | text2 | +--+---+ | a| 1 | | �| 0 | +--+---+ mysqlselect text,text2 from table1 order by text2 desc; +--+---+ | text | text2 | +--+---+ | �| 1 | | a| 0 | +--+---+ What Collation are you using? can you send us the table schema? thx! Isart On Thu, Jun 11, 2009 at 4:48 PM, PJ af.gour...@videotron.ca wrote: Is there a way to order lists while ignoring the accents? So far, I have found nothing simple; and I need to keep the accents for output. The language is French (and québécois) :-) TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com
Re: ignore accents in order by
Isart Montane wrote: Hi, I'm not having any problem on my local computer mysql select text,text2 from table1 order by text2 desc; +--+---+ | text | text2 | +--+---+ | a   |    1 | | �   |    0 | +--+---+ mysqlselect text,text2 from table1 order by text2 desc; +--+---+ | text | text2 | +--+---+ | �   |    1 | | a   |    0 | +--+---+ What Collation are you using? can you send us the table schema? thx! Isart On Thu, Jun 11, 2009 at 4:48 PM, PJ af.gour...@videotron.ca mailto:af.gour...@videotron.ca wrote: Is there a way to order lists while ignoring the accents? So far, I have found nothing simple; and I need to keep the accents for output. The language is French (and québécois) :-) TIA -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com mailto:p...@ptahhotep.com  http://www.ptahhotep.com  http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:   http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com Let me put it this way, I am not having the problem. The problem seems to be withthe way that character encoding is set up on the internet - as confused and inconsistent as most everything else. You can put whatever charset you want in the header, in the collations in your database, your htmls... you see already that the options start to multiply rapidly... without even considering the browsers. So, I have tried about all combinations possible and there is no one way to implement display and use of accents. UTF-8 does not handle them very well at all; iso-8895-1 doesn't either; you can set the coding on your browser to whatever you want - when you update or reload the file the little black diamond devils come back or turn into little blank squares on IE8... so, do it any way you like... anarchy prevails! ;-) -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Create function ignore deps
I want to check all my functions and procs into my svn as individual sql files. When I use these to create my db, the person doing this may not realize the correct order to run these files and not have dependency challenges... How can I have procs that depend on functions, or vice versa, get successfully created without regard to correct order? Thanks Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Create function ignore deps
On Wed, May 27, 2009 at 11:24 AM, Cantwell, Bryan bcantw...@firescope.com wrote: I want to check all my functions and procs into my svn as individual sql files. When I use these to create my db, the person doing this may not realize the correct order to run these files and not have dependency challenges... How can I have procs that depend on functions, or vice versa, get successfully created without regard to correct order? Thanks Bryancan I prefixed the filenames of the various discrete files with numeric prefixes like so 10-create-customer.sql 20-finalize-transaction.sql ... and then wrote a shell script to fire them off in sort-order. I deliberately used the numbering convention from line-numbered basic to allow me to inject intervening files without having to renumber the set. -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Retrieve insert_id on an insert ignore?
I have a table like this:
unique_serial - Auto Increment
field_1
field_2
field_3
The Primary key is a combination of field_1, field_2, and field_3.
I want to do:
INSERT IGNORE INTO table_name (field_1,field_2,field_3) VALUES
('xx','xx','xx')
Sometimes this will be an existing record, sometimes it will create a
new record. In either case I want to return the value of
unique_serial. Is there any way to get my INSERT IGNORE to return this
value, saving me the need to perform a second query?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rules for binlog-ignore-db
I'm looking for some clarification with the documentation for the rules regarding the binary log. In the Binary Log portion of the docs ( http://dev.mysql.com/doc/refman/5.0/en/binary-log.html ) , in the part where it's showing the evaluation steps used to determine what gets written to the binary log, it states: 2. There are some rules (--binlog-do-db, --binlog-ignore-db, or both). Is there a default database (has any database been selected by USE?)? * No: Do not write the statement, and exit. * Yes: Go to the next step. I'm interpreting this to mean that if there is no default database it will not get written if you have a single binlog-ignore-db statement. I tested this out, and it seemed to work (contradicting the doumentation) Is there something I'm missing here, or is the documentation wrong perhaps? Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order By and Ignore Punctuation
Try something like this. If there are multiple punctuation values you
want to ignore you can nest multiple REPLACE functions.
mysql create table names (name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql insert into names values
('Osbourn'),(O'shea),(O'Malley),('Olathe'),('Ottawa');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql SELECT name
FROM names
ORDER BY REPLACE(name,','');
+--+
| name |
+--+
| Olathe |
| O'Malley |
| Osbourn |
| O'shea |
| Ottawa |
+--+
5 rows in set (0.00 sec)
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 03, 2007 7:48 PM
To: Bill Guion
Cc: mysql@lists.mysql.com
Subject: RE: Order By and Ignore Punctuation
I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check out the documentation on the MATCH()AGAINST() systax as well
as fulltext searches in general.
For example:
SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST () WHERE ... ORDER
BY Score;
Hope to help,
-Andy
-Original Message-
From: Bill Guion [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 03, 2007 8:33 PM
To: mysql@lists.mysql.com
Subject: Order By and Ignore Punctuation
I would like to perform a query of a personnel database with an ORDER BY
clause that ignores punctuation. For example, O'shea would sort after
Osbourne, not to the beginning of the Os.
Is this doable in the query?
-= Bill =-
--
You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order By and Ignore Punctuation
I would like to perform a query of a personnel database with an ORDER BY clause that ignores punctuation. For example, O'shea would sort after Osbourne, not to the beginning of the Os. Is this doable in the query? -= Bill =- -- You can tell a lot about a man by the way he handles these three things: a rainy day, lost luggage, and tangled Christmas tree lights. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order By and Ignore Punctuation
Hi, Bill Guion wrote: I would like to perform a query of a personnel database with an ORDER BY clause that ignores punctuation. For example, O'shea would sort after Osbourne, not to the beginning of the Os. Is this doable in the query? If you only have a limited number of punctuation characters to remove, you could do something like ORDER BY REPLACE(last_name, ', ) You can nest REPLACE() as many times as needed. This is admittedly ugly and will defeat indexes, but it's the only thing I can think of. If the sorting must be efficient, you might consider maintaining another column on the table, which has the name without punctuation. You could then index this column and ORDER BY it without using any string manipulation. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order By and Ignore Punctuation
I would suggest you order by something that includes a fulltext index on the specific column. Maybe check out the documentation on the MATCH()AGAINST() systax as well as fulltext searches in general. For example: SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST () WHERE ... ORDER BY Score; Hope to help, -Andy -Original Message- From: Bill Guion [mailto:[EMAIL PROTECTED] Sent: Thursday, May 03, 2007 8:33 PM To: mysql@lists.mysql.com Subject: Order By and Ignore Punctuation I would like to perform a query of a personnel database with an ORDER BY clause that ignores punctuation. For example, O'shea would sort after Osbourne, not to the beginning of the Os. Is this doable in the query? -= Bill =- -- You can tell a lot about a man by the way he handles these three things: a rainy day, lost luggage, and tangled Christmas tree lights. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test - please ignore
Re: INSERT IGNORE BUG?
Hi Ed, Can you please reply with a repeatable test case? On 2/1/07, Ed Pauley II [EMAIL PROTECTED] wrote: I am importing a file into a table in which I want it to ignore duplicate rows. When I specify --ignore (this also happens if I do a SELECT IGNORE INTO from the client also) I get a duplicate key error. If I run the command again it skips the first such instance of a duplicate key and gives me an error (and exits) for the next instance of a duplicate key. If I run the command over and over it finally goes through the whole file. Then the fun starts over. The primary key is on 9 columns but the index shown in the error only has 6 of the columns listed. Is this a bug? I am running ver. 5.0.27 on Linux. -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT IGNORE BUG?
I am importing a file into a table in which I want it to ignore duplicate rows. When I specify --ignore (this also happens if I do a SELECT IGNORE INTO from the client also) I get a duplicate key error. If I run the command again it skips the first such instance of a duplicate key and gives me an error (and exits) for the next instance of a duplicate key. If I run the command over and over it finally goes through the whole file. Then the fun starts over. The primary key is on 9 columns but the index shown in the error only has 6 of the columns listed. Is this a bug? I am running ver. 5.0.27 on Linux. -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doh! Ignore last post...
I've just found convert()... -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I used to think I was indecisive, but now I'm not so sure. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA, Ignore in SET?
I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. Thanks in advance. _ Hitta rätt på nätet med MSN Search http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by ignore it you mean skip the input line and do not load it, you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
From: Paul DuBois [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA, Ignore in SET? Date: Sat, 28 Jan 2006 17:59:23 -0600 At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by ignore it you mean skip the input line and do not load it, you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Actually i dont care how it's done i just don't want it in my database. Ignore, Skip, instantly delete... whatever :) Is it impossible? _ Nyhet! MSN Messenger i Mobiltelefonen! http://mobile.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA, Ignore in SET?
At 0:07 + 1/29/06, Jessica Svensson wrote: From: Paul DuBois [EMAIL PROTECTED] To: Jessica Svensson [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA, Ignore in SET? Date: Sat, 28 Jan 2006 17:59:23 -0600 At 23:42 + 1/28/06, Jessica Svensson wrote: I'm doing load data a few times a day via cron and using this: LOAD DATA LOCAL INFILE '/file.txt' INTO TABLE input [...] (@partnumb, description, price) SET product_id=(SELECT product_id FROM products WHERE [EMAIL PROTECTED]) [...] Now if the partnumber does NOT exists in the products table the product_id gets the value 0 (zero). I would like to have it ignore if there is not match, so i don't need to run a seperate query to delete everything with product_id = 0. If by ignore it you mean skip the input line and do not load it, you can't do that. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Actually i dont care how it's done i just don't want it in my database. Ignore, Skip, instantly delete... whatever :) Is it impossible? LOAD DATA attempts to load every line. The only way it won't happen will be that some error occurs or you're using IGNORE and a duplicate-key error occurs. If you want to selectively ignore lines based on some other criterion, LOAD DATA is probably the wrong approach. At least if you're loading the data directly into the target table. You might consider another approach: Load the data into a temporary table, delete from it those records that have no product_id match (use the multiple-table DELETE syntax that enables you to delete records based on join conditions), and then load the remaining records into your target table (INSERT ... SELECT). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump: getting it to dump INSERT IGNORE
Hi All, I have read thehttp://dev.mysql.com/doc/refman/5.0/en/ mysqldump.html and can find nothing regarding getting dump to INSERT IGNORE instead of simply INSERT INTO. Is there any way to get INSERT IGNORE to be dumped? Otherwise, I fear I may be forced to parse the dump file and do a few replacements. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: getting it to dump INSERT IGNORE
Hi. Use the --insert-ignore option of mysqldump. You can type 'mysqldump --help' on command line to see all options available. Richard AB. - Original Message - From: Michael Williams [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, December 11, 2005 6:19 PM Subject: mysqldump: getting it to dump INSERT IGNORE Hi All, I have read thehttp://dev.mysql.com/doc/refman/5.0/en/ mysqldump.html and can find nothing regarding getting dump to INSERT IGNORE instead of simply INSERT INTO. Is there any way to get INSERT IGNORE to be dumped? Otherwise, I fear I may be forced to parse the dump file and do a few replacements. Regards, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 09/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions on INSERT IGNORE
I want to be sure I understand INSERT IGNORE... correctly before I start depending on it. Up until now, I have not been using any kind of key or unique index, since many of my tables are created automatically and, until now, it has been difficult for me to create a way to distinguish between the tables that would have multiple matching records and the tables that need to have only unique values. So now that I can start using keys, I have a few questions: 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10 minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead, and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? 3) While this has been stated on the mysql.com, it is not what I was originally taught by a friend and some books, so I want to be clear I understand this correctly before I depend on it. As I understand it, INSERT IGNORE... compares the data being inserted with the keys of all records in the table and will not insert it if it duplicates an existing multi-column key. The IGNORE basically tells MyQL to not generate an error message if the data I'm inserting is a duplicate, so I can use INSERT IGNORE... in a Perl program to be sure I'm not duplicating records and not getting error messages on it if the data is a duplicate. Is this correct? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions on INSERT IGNORE
Comments embedded. See below Hal Vaughan [EMAIL PROTECTED] wrote on 11/01/2005 02:50:13 PM: I want to be sure I understand INSERT IGNORE... correctly before I start depending on it. Up until now, I have not been using any kind of key or unique index, since many of my tables are created automatically and, until now, it has been difficult for me to create a way to distinguish between the tables that would have multiple matching records and the tables that need to have only unique values. ... Which tables can have duplicate records in them should be something decided BEFORE you begin to populate the tables. 99.999% of the time, each row of any one table should be different from every other row on the same table. That difference may exist as a combination of values or as a single value but it is NEARLY ALWAYS in your best interest to have no two rows identical. It's not too late to make this decision. However, by putting it off for so long, implementing your uniqueness rules may be much more difficult. ... So now that I can start using keys, I have a few questions: Keys make finding records much faster. You should probably have created a few long before now. 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. No, the same routine will not be used. A hash of the values of the columns that participate in each PRIMARY KEY or UNIQUE KEY will be computed for each row (and stored as part of the key's index structure) and also for each new row as it is being INSERTed. If the new row's hash matches the hash of any UNIQUE or PRIMARY KEY an error will be thrown by the server. An INSERT with the IGNORE modifier will ignore that particular error (basically skipping that row) and continue inserting records. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead,... none. The speed-up will come from the combination of INSERT IGNORE and the UNIQUE or PRIMARY key(s) defined on the target table. It will not come from simply changing INSERT to INSERT IGNORE. In fact, defining ANY keys on that table will cut your processing time considerably. You should be able to insert several hundred records every second (1000s per minute). Your total lack of all indexes has absolutely killed your performance. ... and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? The DELAYED modifier asks the server to buffer your INSERTs so that they can be interleaved with any active read requests allowing your client to believe it has finished inserting records much sooner than if it had waited on all of those inserts to actually happen. It should not be necessary to use DELAYED under most circumstances. You will need to benchmark both techniques to determine which one works best for your situation. 3) While this has been stated on the mysql.com, it is not what I was originally taught by a friend and some books, so I want to be clear I understand this correctly before I depend on it. As I understand it, INSERT IGNORE... compares the data being inserted with the keys of all records in the table and will not insert it if it duplicates an existing multi-column key. ... Then you understand incorrectly. Only two types of keys have any affect on INSERT IGNORE: UNIQUE and PRIMARY. Regular indexes have no effect on uniqueness. If you have a PRIMARY KEY or a UNIQUE INDEX (KEY is interchangeable with INDEX) defined on a table, then all rows within that table must be different from all other rows in the same table for those columns or combination of columns you used to define the key. UNIQUE and PRIMARY KEYs are just like regular indexes except they have a little extra muscle: they define for the database what to look for in order to reject duplicate records (that is known as a uniqueness constraint). Normal (regular
Re: Questions on INSERT IGNORE
On Tuesday 01 November 2005 03:35 pm, [EMAIL PROTECTED] wrote: Which tables can have duplicate records in them should be something decided BEFORE you begin to populate the tables. 99.999% of the time, each row of any one table should be different from every other row on the same table. That difference may exist as a combination of values or as a single value but it is NEARLY ALWAYS in your best interest to have no two rows identical. It's not too late to make this decision. However, by putting it off for so long, implementing your uniqueness rules may be much more difficult. That's a problem with being self-taught -- there are many things like that one can miss. I was not aware any indexing sped things up. As for the design -- I always knew which tables required unique values, but it was a matter of what I had time to do and when I could do it. The priority was to get the system working, and make sure all the smaller programs did their job and played nicely together. Now that everything works, I can spare time to write code that will go through and easily distinguish which tables, when they are created, will need indexing. ... So now that I can start using keys, I have a few questions: Keys make finding records much faster. You should probably have created a few long before now. 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. No, the same routine will not be used. A hash of the values of the columns that participate in each PRIMARY KEY or UNIQUE KEY will be computed for each row (and stored as part of the key's index structure) and also for each new row as it is being INSERTed. If the new row's hash matches the hash of any UNIQUE or PRIMARY KEY an error will be thrown by the server. An INSERT with the IGNORE modifier will ignore that particular error (basically skipping that row) and continue inserting records. Okay -- great. That also answers my last question -- I needed to be sure IGNORE did what I thought it did. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead,... none. The speed-up will come from the combination of INSERT IGNORE and the UNIQUE or PRIMARY key(s) defined on the target table. It will not come from simply changing INSERT to INSERT IGNORE. In fact, defining ANY keys on that table will cut your processing time considerably. You should be able to insert several hundred records every second (1000s per minute). Your total lack of all indexes has absolutely killed your performance. So, unless I misunderstand, adding both keys and IGNORE will speed things up by a factor of 100 to 1000 or more. That is a huge relief. (Again, the first step was developing the system and making sure it works, so now I'm speeding it up and adding other useful features, like keys. ... and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? The DELAYED modifier asks the server to buffer your INSERTs so that they can be interleaved with any active read requests allowing your client to believe it has finished inserting records much sooner than if it had waited on all of those inserts to actually happen. It should not be necessary to use DELAYED under most circumstances. You will need to benchmark both techniques to determine which one works best for your situation. Which leads to another question: If my program things the data is inserted, and it is delayed, is the queue of DELAYed transactions kept anywhere -- so if MySQL or the system crashes, none of the DELAYed transactions are lost? 3) While this has been stated on the mysql.com, it is not what I was originally taught by a friend and some books, so I want to be clear I understand this correctly before I depend on it. As I understand it, INSERT IGNORE... compares the data being inserted with the keys
Re: Questions on INSERT IGNORE
Follow up at bottom: On Tuesday 01 November 2005 04:15 pm, Hal Vaughan wrote: On Tuesday 01 November 2005 03:35 pm, [EMAIL PROTECTED] wrote: Which tables can have duplicate records in them should be something decided BEFORE you begin to populate the tables. 99.999% of the time, each row of any one table should be different from every other row on the same table. That difference may exist as a combination of values or as a single value but it is NEARLY ALWAYS in your best interest to have no two rows identical. It's not too late to make this decision. However, by putting it off for so long, implementing your uniqueness rules may be much more difficult. That's a problem with being self-taught -- there are many things like that one can miss. I was not aware any indexing sped things up. As for the design -- I always knew which tables required unique values, but it was a matter of what I had time to do and when I could do it. The priority was to get the system working, and make sure all the smaller programs did their job and played nicely together. Now that everything works, I can spare time to write code that will go through and easily distinguish which tables, when they are created, will need indexing. ... So now that I can start using keys, I have a few questions: Keys make finding records much faster. You should probably have created a few long before now. 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. No, the same routine will not be used. A hash of the values of the columns that participate in each PRIMARY KEY or UNIQUE KEY will be computed for each row (and stored as part of the key's index structure) and also for each new row as it is being INSERTed. If the new row's hash matches the hash of any UNIQUE or PRIMARY KEY an error will be thrown by the server. An INSERT with the IGNORE modifier will ignore that particular error (basically skipping that row) and continue inserting records. Okay -- great. That also answers my last question -- I needed to be sure IGNORE did what I thought it did. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead,... none. The speed-up will come from the combination of INSERT IGNORE and the UNIQUE or PRIMARY key(s) defined on the target table. It will not come from simply changing INSERT to INSERT IGNORE. In fact, defining ANY keys on that table will cut your processing time considerably. You should be able to insert several hundred records every second (1000s per minute). Your total lack of all indexes has absolutely killed your performance. So, unless I misunderstand, adding both keys and IGNORE will speed things up by a factor of 100 to 1000 or more. That is a huge relief. (Again, the first step was developing the system and making sure it works, so now I'm speeding it up and adding other useful features, like keys. ... and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? The DELAYED modifier asks the server to buffer your INSERTs so that they can be interleaved with any active read requests allowing your client to believe it has finished inserting records much sooner than if it had waited on all of those inserts to actually happen. It should not be necessary to use DELAYED under most circumstances. You will need to benchmark both techniques to determine which one works best for your situation. Which leads to another question: If my program things the data is inserted, and it is delayed, is the queue of DELAYed transactions kept anywhere -- so if MySQL or the system crashes, none of the DELAYed transactions are lost? 3) While this has been stated on the mysql.com, it is not what I was originally taught
Re: INSERT IGNORE Doesn't Seem To Work
Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is How can I do a job without using the tool designed for the job?. If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
On Thursday 25 August 2005 04:44 am, [EMAIL PROTECTED] wrote: Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is How can I do a job without using the tool designed for the job?. If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec I have some routines for entering large amounts of data into different tables. *IF* INSERT IGNORE worked, it was easy for me to simply add IGNORE to a query string (this is all in Perl) for tables where I did not want dupes. I also have a number of tables where there are reasons for allowing multiple entries. There are also some tables where items from one source must not be duplicated, where entries from another source should be, since they are counted later. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
Hal, *IF* INSERT IGNORE worked ... INSERT IGNORE _does_ work exactly as documented in the manual: "If you specify the IGNORE keyword in an INSERT statement, errors that occur while executing the statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the error is ignored and the row is not inserted. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest value values and inserted; warnings are produced but the statement does not abort." (http://dev.mysql.com/doc/mysql/en/insert.html) , it was easy for me to simply add "IGNORE " to a query string (this is all in Perl) for tables where I did not want dupes. In relational databases, the usual method of preventing duplicate values is via PRIMARY or UNIQUE indexes. Absent such indexes, you need application code to prevent dupes. PB - Hal Vaughan wrote: On Thursday 25 August 2005 04:44 am, [EMAIL PROTECTED] wrote: Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is "How can I do a job without using the tool designed for the job?". If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec I have some routines for entering large amounts of data into different tables. *IF* INSERT IGNORE worked, it was easy for me to simply add "IGNORE " to a query string (this is all in Perl) for tables where I did not want dupes. I also have a number of tables where there are reasons for allowing multiple entries. There are also some tables where items from one source must not be duplicated, where entries from another source should be, since they are counted later. Hal No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/81 - Release Date: 8/24/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT IGNORE Doesn't Seem To Work
I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
Hi Hal, in order to get INSERT IGNORE to work as you want it you must violate a unique index somehow, i.e. you must have a unique index on Name,Value or both and then you would get a quiet ignore of that violation. The IGNORE keyword doesn't make the INSERT as such different, it just affects the errorhandling of a UNIQUE KEY violation. Regards, /Johan Hal Vaughan wrote: I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
The insert will only be bounced where you specify the columns as unique. Thus you need either separate UNIQUE indexes on Name and Value, if you want them to be individually unique, or a single joint UNIQUE index if you want them to be jointly unique but separately duplicable. The INSERT command only checks columns that is instructed are to be unique. The purpose of the IGNORE modifier is simply to ignore the error produced when a duplicate occurs. Alec Hal Vaughan [EMAIL PROTECTED] 24/08/2005 07:47 Please respond to [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject INSERT IGNORE Doesn't Seem To Work I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
On Wednesday 24 August 2005 02:47 am, Hal Vaughan wrote: I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
Hal Vaughan [EMAIL PROTECTED] wrote on 08/24/2005 12:41:36 PM: On Wednesday 24 August 2005 02:47 am, Hal Vaughan wrote: I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? Hal Not really. You have to define the table in such a way that some kind of duplicated data is wrong before the SQL engine can guard against them. Exactly what form of duplication you don't want is entirely up to you and your needs. You tell the SQL engine what kind of duplication to reject by either defining your PRIMARY KEY or a UNIQUE key or some combination of PRIMARY and UNIQUE keys in such a way to dissalow the duplication you want to avoid. Otherwise you will need to search for duplicates in your application BEFORE you build your INSERT statement so that you just do not execute any INSERT statements that would create duplicated information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
IGNORE: test only
IGNORE: test only since I did not get my last posting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test - please ignore
Just a test - thanks There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it any faster to use IGNORE with Load Data Infile?
I'm loading 100 million rows into a MyISAM table and I'm wondering what overhead is there when using the Load Data Infile REPLACE over Load Data Infile Ignore syntax. For example, does the REPLACE do a lookup prior to inserting the row? Would it be faster to use Ignore? There is no possibility of having duplicate unique keys so I'm wondering if one syntax has a speed benefit over the other. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignore
Please ignore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignore
Please ignore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignore a single query in replication
Hey, Is there a way to tell the slave to not execute a query without ignoring tables or databases? There are a bunch of queries that happen on the master for statistical purposes that don't use temp tables and generate large amounts of data. These queries don't need to run on the slaves and in fact slow it down quite a bit. I've tried hunting around the online docs, but I can't seem to find anything. For some reason I thought there was some sort of comment that I could put infront of my query to accomplish this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
Gary Richardson wrote: There are a bunch of queries that happen on the master for statistical purposes that don't use temp tables and generate large amounts of data. These queries don't need to run on the slaves and in fact slow it down quite a bit. If the queries modify tables that are being replicated, then how would the slave remain in sync with the master if it didn't replicate them? -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
So I gather you are creating a table, and doing some work in it, but even though it isn't declared 'temporary' it really is and you don't want it replicated? If this is the case you can create the table in a separate database, and in your mysql configuration tell the binary logging to exclude that database. Then anything in that specific database won't get replicated, I believe you can only do this exclusion on the database level, not per table. But you can perform all your queries across databases just fine. On Thu, 2004-10-21 at 11:24 -0700, Gary Richardson wrote: Hey, Is there a way to tell the slave to not execute a query without ignoring tables or databases? There are a bunch of queries that happen on the master for statistical purposes that don't use temp tables and generate large amounts of data. These queries don't need to run on the slaves and in fact slow it down quite a bit. I've tried hunting around the online docs, but I can't seem to find anything. For some reason I thought there was some sort of comment that I could put infront of my query to accomplish this. Thanks. -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ignore a single query in replication
Try SET SQL_LOG_BIN=0 before you run your queires on master. This will be valid for that connection only. -Original Message- From: Gary Richardson [mailto:[EMAIL PROTECTED] Sent: Thu 10/21/2004 11:24 AM To: Mysql General (E-mail) Subject: Ignore a single query in replication Hey, Is there a way to tell the slave to not execute a query without ignoring tables or databases? There are a bunch of queries that happen on the master for statistical purposes that don't use temp tables and generate large amounts of data. These queries don't need to run on the slaves and in fact slow it down quite a bit. I've tried hunting around the online docs, but I can't seem to find anything. For some reason I thought there was some sort of comment that I could put infront of my query to accomplish this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
If the queries modify tables that are being replicated, then how would the slave remain in sync with the master if it didn't replicate them? These are essentially temporary tables that aren't defined as such -- they typically take a long time to derive (30 minutes to an hour) and are used for multiple queries afterwards before being dropped. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
If this is the case you can create the table in a separate database, and in your mysql configuration tell the binary logging to exclude that database. Then anything in that specific database won't get replicated, I believe you can only do this exclusion on the database level, not per table. But you can perform all your queries across databases just fine. Yeah, I thought about that. We're currently creating the tables in the test db. I'm just worried about someone trying to update/insert/delete data back in the main database based on a query against the temporary data. In that case the source data wouldn't exist on the replica. I suppose create a database called test_not_replicated or something similar to help avoid people doing that. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
This was exactly what I was looking for :) Too bad you need to be SUPER to do it :( Thanks. On Thu, 21 Oct 2004 12:44:11 -0700, Sanjeev Sagar [EMAIL PROTECTED] wrote: Try SET SQL_LOG_BIN=0 before you run your queires on master. This will be valid for that connection only. -Original Message- From: Gary Richardson [mailto:[EMAIL PROTECTED] Sent: Thu 10/21/2004 11:24 AM To: Mysql General (E-mail) Subject: Ignore a single query in replication Hey, Is there a way to tell the slave to not execute a query without ignoring tables or databases? There are a bunch of queries that happen on the master for statistical purposes that don't use temp tables and generate large amounts of data. These queries don't need to run on the slaves and in fact slow it down quite a bit. I've tried hunting around the online docs, but I can't seem to find anything. For some reason I thought there was some sort of comment that I could put infront of my query to accomplish this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
Gary Richardson wrote: These are essentially temporary tables that aren't defined as such -- they typically take a long time to derive (30 minutes to an hour) and are used for multiple queries afterwards before being dropped. In that case, why not just ignore those tables for replication? I realize that you excluded that as a possible solution in your initial message, but that would be the normal way to do it. Without knowing why that doesn't work for you it's hard to give an answer that might. Do you not have control over the server configuration? -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DELETE IGNORE fails for foreign key references with InnoDb
Hi there, I'm finding that DELETE IGNORE doesn't actually ignore all errors when using InnoDb and trying to remove rows that would result in a foreign key error. I've checked the docs and think that what I'm doing should work, but doesn't - I'm using server 4.1.4-gamma: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 820 to server version: 4.1.4-gamma-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql DROP TABLE IF EXISTS b,a; Query OK, 0 rows affected (0.02 sec) mysql CREATE TABLE a - ( - id INTEGER NOT NULL AUTO_INCREMENT, - PRIMARY KEY(id) - ) - ENGINE=InnoDb; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE b - ( - aid INTEGER NOT NULL, - PRIMARY KEY(aid), - FOREIGN KEY (aid) REFERENCES a(id) - ) - ENGINE=InnoDb; Query OK, 0 rows affected (0.02 sec) mysql INSERT INTO a() VALUES(); Query OK, 1 row affected (0.29 sec) mysql INSERT INTO b SELECT * FROM a; Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql INSERT INTO a(id) VALUES(1024); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM a; +--+ | id | +--+ |1 | | 1024 | +--+ 2 rows in set (0.00 sec) mysql SELECT * FROM b; +-+ | aid | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql DELETE IGNORE FROM a; ERROR 1105 (HY000): Unknown error mysql I'm actually being lazy and just trying to mop up rows that are not referenced anymore. I would use ON DELETE CASCADE, except that the constraint is pointing the wrong way around in my application (I may have to fix this). Anyone know if DELETE IGNORE should work? Will it work in later releases of 4.1, although I don't see reference to this problem in the 4.1.5-gamma changelog? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
Yes. I also tried that. Are there any other suggestions? What's about the different sections in my.cnf? Should these statements beeing written under [mysqld_safe], too? Victor Pendleton wrote: In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- [werk01] - Mike Lohmann Software Engineering | System Administration Herforder Strasse 24 33602 Bielefeld Telefon:+49 52 1 - 96 74 26 1 Fax:+49 52 1 - 96 74 36 4 Mobil: +49 17 1 - 27 04 15 6 http://www.werk01.de http://mike.werk01.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
Hi, I have had similar problems with do-table, but never succeded finalizing my bug-report. I have added (on master) ... SET SQL_LOG_BIN = 0 as start of each action where not to replicate data SET SQL_LOG_BIN = 1 as start of each action where to replicate data Further I have had some issues where data seams to be lost during replication, mainly index, hardly data. For the last couple of days it seams OK after having added FLUSH LOGS (save log-files) after each action where data must be replicated. This migh give a number of smal replication-files, but actually they seams to be replicated faster than one big file. So right now, my master and two slaves seams to be in fine condition. Further I have made ans ASP/XML program where to read table-status from master and each slave, and comparing datasize, indexsize and so, for having a complete database-status view . Best regards Peter - Original Message - From: Mike Lohmann [EMAIL PROTECTED] To: Victor Pendleton [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 13, 2004 11:49 AM Subject: Re: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table Yes. I also tried that. Are there any other suggestions? What's about the different sections in my.cnf? Should these statements beeing written under [mysqld_safe], too? Victor Pendleton wrote: In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- [werk01] - Mike Lohmann Software Engineering | System Administration Herforder Strasse 24 33602 Bielefeld Telefon:+49 52 1 - 96 74 26 1 Fax:+49 52 1 - 96 74 36 4 Mobil: +49 17 1 - 27 04 15 6 http://www.werk01.de http://mike.werk01.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table
Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT IGNORE like feature for rows failing foreign key constraints?
I never got a reply for this, and I'm still trying to figure out the best way to handle it. Anyone? John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 2:17 PM To: [EMAIL PROTECTED] Subject: INSERT IGNORE like feature for rows failing foreign key constraints? I have a logging table where I insert a large number of rows every 5 minutes. For performance reasons this occurs in bulk inserts of about 5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...)) One of the fields in the table is an id that connects it to another table. It is possible that by the time the insert occurs (they queue up in memory briefly before I create the bulk insert) a separate process has deleted the entry in the parent table and the id is invalid. When this happens right now the entire insert of 5000 rows fails because one single row is bad. I want the behavior to be that the one fails silently and the other 4999 insert successfully. Any ideas how I can do this? It seems like INSERT IGNORE would make sense but that appears to only ignore duplicates not foreign key failures. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT IGNORE like feature for rows failing foreign key constraints?
I have a logging table where I insert a large number of rows every 5 minutes. For performance reasons this occurs in bulk inserts of about 5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...)) One of the fields in the table is an id that connects it to another table. It is possible that by the time the insert occurs (they queue up in memory briefly before I create the bulk insert) a separate process has deleted the entry in the parent table and the id is invalid. When this happens right now the entire insert of 5000 rows fails because one single row is bad. I want the behavior to be that the one fails silently and the other 4999 insert successfully. Any ideas how I can do this? It seems like INSERT IGNORE would make sense but that appears to only ignore duplicates not foreign key failures. John A. McCaskey Software Development Engineer IP Sciences, Inc. [EMAIL PROTECTED] 206.902.2027
Test - please ignore
Testing the MySQL mailing list. Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another test - please ignore
Testing another time the mailing list. Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test please ignore
had no mysql list mail for a while just testing please ignore mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tests please ignore
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| - --- .- Este mensaje está digitalmente firmado para garantizar ~ su origen .- El intercambio de llaves públicas se realiza a petición ~ de las partes interesadas via e-mail - --- .- This message has been digitally signed .- Public Key (PGP or GPG) available upon request -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFArK3Q8WJSBCrOXJ4RApvwAJ9F1KP/8wBfDorSv9I04Z2DNlvO9gCeO0kU iTiOcrZ1bXUdPK7YCRljGkE= =B1R4 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
please ignore
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 please ignore - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAfnQ38WJSBCrOXJ4RAoLdAKCzq8I7IyYWdeOZhAearCOtdiy5SACff13C rXyNw+3aprFYN2v5pd+PELs= =ppFP -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ignore
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, i been having problems with my mail server, just ignore this message. - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAfoF68WJSBCrOXJ4RAvAHAJ45Row9Tx9YX4dljRSfKLuyBvATRQCeKt2/ xpEhRCE3zNsfJnyHjgfTQrw= =U2t2 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: please ignore
That's a lot of stuff to ignore but I managed to do so successfully -Ursprüngliche Nachricht- Von: Victor Medina [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 15. April 2004 13:39 An: [EMAIL PROTECTED] Mysql. Com Betreff: please ignore -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 please ignore - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAfnQ38WJSBCrOXJ4RAoLdAKCzq8I7IyYWdeOZhAearCOtdiy5SACff13C rXyNw+3aprFYN2v5pd+PELs= =ppFP -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get MySQL to ignore the backslashes?
My apologies for missing the previous discussion and thanks for the info. A follow up
question: is there a performance penalty associated with using PreparedStatement's
with queries that will never be reused? Clearly, there's a performance benefit for
statements executed multiple times, but how does using a PreparedStatement compare
with using a Statement (with a hard coded SQL string) for single use queries?
Thanks in advance,
Alex
==
Hi Alex. This question was discussed last week. Search the archives
for 'backslash'. The short answer is that there is no global option to
do what you want. You'll have to do it in code.
On Mon, 2004-04-12 at 17:10, [EMAIL PROTECTED] wrote:
Is there a 'global' way to tell MySQL to not interpret the backslashes ('\'s) in the
submitted SQL as escape characters? In other words, I'd like for them to always be
treated as if they themselves were already escaped with a backslash (i.e. '\\'). I'm
using 3.23.52, accessing it with an older (2.x?) MM JDBC driver. I know of a couple
of
code-level solutions that will require a number of changes in the existing code, but
was
hoping to find a more 'global' solution, via either a global call or a configuration
setting. Any ideas are much appreciated.
Thanks in advance,
Alex Zeltser
__
Introducing the New Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
__
Introducing the New Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get MySQL to ignore the backslashes?
Is there a 'global' way to tell MySQL to not interpret the backslashes ('\'s) in the
submitted SQL as escape characters? In other words, I'd like for them to always be
treated as if they themselves were already escaped with a backslash (i.e. '\\'). I'm
using 3.23.52, accessing it with an older (2.x?) MM JDBC driver. I know of a couple
of code-level solutions that will require a number of changes in the existing code,
but was hoping to find a more 'global' solution, via either a global call or a
configuration setting. Any ideas are much appreciated.
Thanks in advance,
Alex Zeltser
__
Introducing the New Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get MySQL to ignore the backslashes?
Hi Alex. This question was discussed last week. Search the archives
for 'backslash'. The short answer is that there is no global option to
do what you want. You'll have to do it in code.
On Mon, 2004-04-12 at 17:10, [EMAIL PROTECTED] wrote:
Is there a 'global' way to tell MySQL to not interpret the backslashes ('\'s) in the
submitted SQL as escape characters? In other words, I'd like for them to always be
treated as if they themselves were already escaped with a backslash (i.e. '\\').
I'm using 3.23.52, accessing it with an older (2.x?) MM JDBC driver. I know of a
couple of code-level solutions that will require a number of changes in the existing
code, but was hoping to find a more 'global' solution, via either a global call or a
configuration setting. Any ideas are much appreciated.
Thanks in advance,
Alex Zeltser
__
Introducing the New Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Netscape. Just the Net You Need.
New! Netscape Toolbar for Internet Explorer
Search from anywhere on the Web and block those annoying pop-ups.
Download now at http://channels.netscape.com/ns/search/install.jsp
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
TEST - please ignore!
-- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
retrieve ignored records from LOAD DATA INFILE IGNORE
Is there anyway to get mySQL to generate a warning or other info when it ignores a row via LOAD DATA INFILE IGNORE? I'm happy having the duplicates ignored but ideally would like to log which records were dupes in a place I can find them again. -- thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test mail (please ignore)
test -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test!!!Please ignore this message
Title: Test!!!Please ignore this message Hi fellows, ...just a test.Please ignore this message. _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPER Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore Replication Temp Tables
On Tue, 20 Jan 2004, Todd Burke wrote: Is there any way to disable replication of all temp tables using replicate-ignore-table or some other means? The names of the temp tables are generated randomly by a script. Thanks If you could have all temporary tables starting with tmp or something like that (since the script generates the names it should be easy just to tag something at the start or end that makes them stand out), then you can use: (from manual: http://www.mysql.com/doc/en/Replication_Options.html) --replicate-wild-ignore-table=db_name.table_name Tells the slave thread to not replicate a query where any table matches the given wildcard pattern. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates. Please read the notes that follow this option list. Example: --replicate-wild-ignore-table=foo%.bar% will not do updates to tables in databases that start with foo and whose table names start with bar. Note that if you do --replicate-wild-ignore-table=foo%.% then the rule will be propagated to CREATE DATABASE and DROP DATABASE, that is, these two statements will not be replicated if the database name matches the database pattern (foo% here) (this magic is triggered by % being the table pattern). Escaping wildcard characters _ and %: see notes in the description of replicate-wild-do-table just above. cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignore Replication Temp Tables
Is there any way to disable replication of all temp tables using replicate-ignore-table or some other means? The names of the temp tables are generated randomly by a script. Thanks Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problems: Ignore table
The replication between two servers works. But I tried to disable replication on one table. According to the MYSQL Documentation. I did the following: #1 stop Slave #2 Modified my.cnf on slave [mysqld] server-id=3 master-host=192.168.1.129 master-user=aert12 master-password=password replicate-ignore-table=info.notes # Added this Line #3 Restarted slave server /etc/init.d/mysql restart #4 start slave show status slave shows that replication is running , but actually it doesnt. I even can type start slave many times with no warning. Previously I got an error message if I try to start slave that is running. What I did wrong? _ Working moms: Find helpful tips here on managing kids, home, work and yourself. http://special.msn.com/msnbc/workingmom.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ignor table in replication: replicate-wild-ignore-table
I want to stop replication for one table in the database. According to the mysql documentation: I need to add the parameter (replicate-wild-ignore-table ) to the my.cnf file. I have some question. #1 In what database I should made the change to my.cnf? Slave or Master? #2 Than I should restart the database. I use Linux. So I just need to restart mysqld? Thank you _ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignor table in replication: replicate-wild-ignore-table
At 22:37 + 12/19/03, Mike S wrote: I want to stop replication for one table in the database. According to the mysql documentation: I need to add the parameter (replicate-wild-ignore-table ) to the my.cnf file. I have some question. #1 In what database I should made the change to my.cnf? Slave or Master? #2 Than I should restart the database. I use Linux. So I just need to restart mysqld? http://www.mysql.com/doc/en/Replication_Options.html says: --replicate-wild-do-table=db_name.table_name Tells the slave thread to restrict replication to queries where any of the updated tables match the specified wildcard pattern. So you would use this option on the slave host. Restart the slave server after adding the option to its my.cnf file. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replicate-ignore(d)-table continues to replicate
Hi, We are using the binary distribution of mysql for solaris 9 64bit on 2 machines that are replicating to each other for redundancy: mysql-standard-4.0.14-sun-solaris2.9-sparc-64bit I am successfully able use replicate-ignore-db=blah to stop replication on a database, however when I specify individual tables to ignore the slave thread continues to replicate the tables completely oblivious to the ignore statement. I have so far tried to get the slave to ignore nearly every table in the target database to no effect. Only replicate-do-db replicate-ignore-db work Here is a sample of my.cnf [mysqld] port= 3306 socket = /tmp/mysql.sock log = /var/log/mysql/mysqld.log log-error = /var/log/mysql/mysqld-error.log log-slow-queries = /var/log/mysql/mysqld-slow.log datadir = /var/lib/mysql skip-locking key_buffer = 256M max_allowed_packet = 100M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M net_buffer_length = 8K myisam_sort_buffer_size = 64M thread_cache = 8 thread_concurrency = 4 query_cache_size = 16M log-bin server-id = 2 master-host = somehost.somedomain master-user = replicator log-bin log-warnings log-slave-updates replicate-ignore-db=mysql replicate-ignore-table=radius.Accouting replicate-ignore-table=radius.RADAUTHLOG replicate-ignore-table=radius.RADSTATSLOGns1 replicate-ignore-table=radius.RADSTATSLOGns2 replicate-do-table=radius.raduser replicate-do-table=radius.RADONLINE replicate-do-table=radius.RADPOOL Cheers Lee Webb -- Systems Administrator DOT Communications [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IGNORE THIS MESSAGE
didn't ignore, huh? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Just testing please ignore
Testing ..Please ignore this email
re: Making MySQL ignore non-database directories in datadir
On Thursday 20 March 2003 22:42, John Hardin wrote: It would be really nice if there were some way (a variable) to make MySQL totally ignore certain directories in the datadir - for example, lost+found, RCS, and suchlike. The listed dirs should not show up in SHOW DATABASES and you should not be able to CREATE a database with a name that's on the list. (Sorry if this has come up before and been hashed to death. Google couldn't find much on this subject.) First of all it's not a good idea to put non-database directory to the datadir. Second, if the user doesn't have SHOW DATABASE privilege or doesn't have privileges on the database at all, he can't see database in the SHOW DATABASE output. Third, you can't forbid creating databases with certain names on the MySQL server level. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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
Making MySQL ignore non-database directories in datadir
All: It would be really nice if there were some way (a variable) to make MySQL totally ignore certain directories in the datadir - for example, lost+found, RCS, and suchlike. The listed dirs should not show up in SHOW DATABASES and you should not be able to CREATE a database with a name that's on the list. (Sorry if this has come up before and been hashed to death. Google couldn't find much on this subject.) -- John Hardin KA7OHZ [EMAIL PROTECTED] Internal Systems Administratorvoice: (425) 672-1304 Apropos Retail Management Systems, Inc. fax: (425) 672-0192 --- To disable the Internet to save EMI and Disney is the moral equivalent of burning down the library of Alexandria to ensure the livelihood of monastic scribes. -- John Ippolito of the Guggenheim --- 63 days until The Matrix Reloaded - 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
Test Please Ignore :)
Test -- Karthikeyan Balasubramanian [EMAIL PROTECTED] -- http://www.fastmail.fm - Access your email from home and the web - 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
re: binlog-ignore-db replicate-ignore-db problem
On Tuesday 11 March 2003 18:57, vlady wrote: I am trying to set up a replication ignoring a given database, but unfortunatly it doesn't works (for me). In my.cnf of my master I have at the end : log-bin server-id= 1 binlog-ignore-db=access The line: binlog-ignore-db=access is supposed to make the master not to log updates for db access, but but it doesn't, and I can see it examinig my bin log with mysqlbinlog Because you explicitly specify database in your query. Check the manual: http://www.mysql.com/doc/en/Binary_log.html binlog-ignore-db=database_name - Tells the master that updates where the current (i.e. selected) database is 'database_name' should not be stored in the binary log. Note that if you use this you should ensure that you only do updates in the current database. In the other hand on my repication in my.cnf I have: replicate-ignore-db=access which is supposed to make the repication to not replicate for db access, but again it doesn't do this. The same. If you specify replicate-ignore-db=access, slave ignores all queries in the binary logs that listed after use access: http://www.mysql.com/doc/en/Replication_Options.html I know that it should works, so I suppose there is some misudrestanding at my side, so please help me with that. There are a couple of my update queries in my bin-log: update access.lastCheck set id = '7147671' where id = '123'; update access.lastCheck set id = '7147670' where id = '345'; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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
binlog-ignore-db replicate-ignore-db problem
Hi All, I am trying to set up a replication ignoring a given database, but unfortunatly it doesn't works (for me). In my.cnf of my master I have at the end : log-bin server-id= 1 binlog-ignore-db=access The line: binlog-ignore-db=access is supposed to make the master not to log updates for db access, but but it doesn't, and I can see it examinig my bin log with mysqlbinlog In the other hand on my repication in my.cnf I have: replicate-ignore-db=access which is supposed to make the repication to not replicate for db access, but again it doesn't do this. I know that it should works, so I suppose there is some misudrestanding at my side, so please help me with that. There are a couple of my update queries in my bin-log: update access.lastCheck set id = '7147671' where id = '123'; update access.lastCheck set id = '7147670' where id = '345'; . Best regards Vlady - 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
Please IGNORE my previous bug report. Script errors.
Description: Please IGNORE my previous bug report. I had another script which was deleting the data. Stupid user error. How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:Mudit Wahal Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.1-alpha-max (Official MySQL-max binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha-max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 3 days 36 min 19 sec Threads: 1 Questions: 1847948 Slow queries: 0 Opens: 252379 Flush tables: 20 Open tables: 42 Queries per second avg: 7.070 Environment: System: Linux bp6 2.4.20 #8 Wed Jan 29 09:50:06 PST 2003 i686 unknown Architecture: i686 Some paths: /home/wmudit/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Jan 24 18:13 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1282588 Sep 4 2001 /lib/libc-2.2.4.so -rw-r--r--1 root root 27304836 Sep 4 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 4 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL-max binary' --with-extra-charsets=complex --with-server-suffix=-max --enable-thread-safe-client --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared --with-berkeley-db --with-innodb - 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
replication ignore delete statement
Hi, is it possible to ignore all sql delete statement while doing mysql replication? - 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
RE: replication ignore delete statement
You could do something like have a process periodically run the non-delete queries in the logs against another database and replicate the second database. Wouldn't be automatic, but you'd have as much control as you like over what goes in the other db. Depending on what you're trying to do, it may be enough that the logs (including the delete statements) end up on the slave. If your queries against the no-deletes version are trivial, you may be able to just pipe the log output through grep. - James Moore - 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
