Re: Sort results by order in list
Hi, It comes from external source together with Relevance value (float), where the first ID has highest relevance and subsequent IDs are in decreasing order. For example: 109k7 1.79 s3x6 1.34 sxmns 1.21 wt57 0.93 I could use these numbers in the query as well if it helps. PaPa On 10/30/07, Peter Brawley [EMAIL PROTECTED] wrote: I.e. the ideal output would be: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | s3x6 | 2007-10-07 08:58:20 | | wt57 | 2007-10-07 15:57:37 | | sxmns | 2007-10-06 02:17:30 | +---+-+ What rule generates the order 109k7, s3x6, wt57, sxmns? PB Papalagi Pakeha wrote: Hello, I have a query like: SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57'); which gives me: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | sxmns | 2007-10-06 02:17:30 | | wt57 | 2007-10-07 15:57:37 | | s3x6 | 2007-10-07 08:58:20 | +---+-+ How can I get the results sorted by the order in which they appear in the ID list? Indeed I could do it in the application but prefer to get the results in the right order from MySQL as the ID list may be long and I may need only first few entries cropped by LIMIT clause. Ideally it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I need some sort of stored function for that? I.e. the ideal output would be: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | s3x6 | 2007-10-07 08:58:20 | | wt57 | 2007-10-07 15:57:37 | | sxmns | 2007-10-06 02:17:30 | +---+-+ Thanks PaPa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort results by order in list
Papalagi Pakeha schrieb: Hello, I have a query like: SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57'); which gives me: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | sxmns | 2007-10-06 02:17:30 | | wt57 | 2007-10-07 15:57:37 | | s3x6 | 2007-10-07 08:58:20 | +---+-+ How can I get the results sorted by the order in which they appear in the ID list? i do not fully understand ... ORDER BY `id` ... ??? what ID List? Indeed I could do it in the application but prefer to get the results in the right order from MySQL as the ID list may be long and I may need only first few entries cropped by LIMIT clause. Ideally it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I need some sort of stored function for that? ORDER BY DATE(`start_date`) DESC, `id` I.e. the ideal output would be: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | s3x6 | 2007-10-07 08:58:20 | | wt57 | 2007-10-07 15:57:37 | | sxmns | 2007-10-06 02:17:30 | +---+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort results by order in list
On 10/30/07, Sebastian Mendel [EMAIL PROTECTED] wrote: Papalagi Pakeha schrieb: Hello, I have a query like: SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57'); which gives me: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | sxmns | 2007-10-06 02:17:30 | | wt57 | 2007-10-07 15:57:37 | | s3x6 | 2007-10-07 08:58:20 | +---+-+ How can I get the results sorted by the order in which they appear in the ID list? i do not fully understand ... ORDER BY `id` ... ??? what ID List? This ID list: ('109k7','s3x6','sxmns','wt57') I.e. I want to get the results in the same order in which they appear in the above list. 109k7 first, s3x6 second, etc. Simple WHERE id IN (...) gives me random order. PaPa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort results by order in list
Hi, what you probably want is SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57') ORDER BY FIELD(id,'109k7','s3x6','sxmns','wt57') /Johan Papalagi Pakeha skrev: On 10/30/07, Sebastian Mendel [EMAIL PROTECTED] wrote: Papalagi Pakeha schrieb: Hello, I have a query like: SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57'); which gives me: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | sxmns | 2007-10-06 02:17:30 | | wt57 | 2007-10-07 15:57:37 | | s3x6 | 2007-10-07 08:58:20 | +---+-+ How can I get the results sorted by the order in which they appear in the ID list? i do not fully understand ... ORDER BY `id` ... ??? what ID List? This ID list: ('109k7','s3x6','sxmns','wt57') I.e. I want to get the results in the same order in which they appear in the above list. 109k7 first, s3x6 second, etc. Simple WHERE id IN (...) gives me random order. PaPa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on November 1
Hi, as some of you may have noticed we've been running educational sessions on MySQL internals for quite a while. That program is called MySQL University; see http://forge.mysql.com/wiki/MySQL_University. Tomorrow Sergey Petrunia will give a MySQL University session on: How MySQL Handles ORDER BY, GROUP BY, and DISTINCT Please register for this session by filling in your name on the session Wiki page that you can find here: http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions Registering is not required but appreciated. Thanks! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication and ibdata file size
Hi all, on a replication architecture, with the same server, the same Mysql version (4.1.21) and the same configuration, the same database. I have a difference between two ibdata file size innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend on the master : -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:36 ibdata6 -rw-rw1 mysqlmysql 22G Oct 30 11:40 ibdata7 on the slave -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata6 -rw-rw1 mysqlmysql 15G Oct 30 11:40 ibdata7 The difference is over 7Go !!! Is there anybody who has got any explanation about this ??? Thanks all
Re: Sort results by order in list
On 10/30/07, Johan Höök [EMAIL PROTECTED] wrote: Hi, what you probably want is SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57') ORDER BY FIELD(id,'109k7','s3x6','sxmns','wt57') That's exactly it! Thanks a lot :-) Just for the record, here's the FIELD() function description: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field PaPa
RE: Fulltext Relevancy not returning anticipated results?
By default, MySQL ignores any word with less than four characters when doing full text searches. You can change this in my.conf, the setting should be obvious. I had to do this so that customers could find inventory items that were red. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 5:39 PM To: mysql@lists.mysql.com Subject: Fulltext Relevancy not returning anticipated results? I have a database of products, doing a search on them trying to achieve a modicum of relevancy, but am getting a strange result on some returned rows: QUERY: select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name) against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against ('vic*' IN BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE) as score from products where active='y' and site like '%,1,%' and match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE) order by score desc I get returned 6 rows from the product database. The strange thing is, there is a product (at least one that is known about), with the name: Salchichon de Vic that is NOT being returned as a result. When I break out the scores, I get a match on the first three products returned for the titles: 6-Pack Vichy Catalan Sparkling Spring Water 2-Pack Vichy Catalan Mineral Water San Vicente - Tempranillo 2001 And the other three products have a match in the large_desc. What is confusing me is why I am not getting any match on the titles for the Salchichon de Vic - which by my thought process should be the most relevant of all returns? Any thoughts on this? The term vic used in this case is the search string submitted by the user. You can see the search in action at http://www.tienda.com/support/search.html -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
Jerry: Sorry - I should have mentioned in my previous email that we have changed that min. word length to 2 in the config already, as we have MANY 3 letter searches... ;) Any other suggestions? On 10/30/07 9:12 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: By default, MySQL ignores any word with less than four characters when doing full text searches. You can change this in my.conf, the setting should be obvious. I had to do this so that customers could find inventory items that were red. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 5:39 PM To: mysql@lists.mysql.com Subject: Fulltext Relevancy not returning anticipated results? I have a database of products, doing a search on them trying to achieve a modicum of relevancy, but am getting a strange result on some returned rows: QUERY: select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name) against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against ('vic*' IN BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE) as score from products where active='y' and site like '%,1,%' and match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE) order by score desc I get returned 6 rows from the product database. The strange thing is, there is a product (at least one that is known about), with the name: Salchichon de Vic that is NOT being returned as a result. When I break out the scores, I get a match on the first three products returned for the titles: 6-Pack Vichy Catalan Sparkling Spring Water 2-Pack Vichy Catalan Mineral Water San Vicente - Tempranillo 2001 And the other three products have a match in the large_desc. What is confusing me is why I am not getting any match on the titles for the Salchichon de Vic - which by my thought process should be the most relevant of all returns? Any thoughts on this? The term vic used in this case is the search string submitted by the user. You can see the search in action at http://www.tienda.com/support/search.html -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication and ibdata file size
One possible explanation (possibly not the only one): if you do a massive update on the master, that transaction would need to create many blocks of versioned data. If you roll that transaction back, those blocks will be freed to be reused, but the datafiles won't shrink. Since that transaction wasn't commited, it won't be written to the binary log, so it won't be executed and rolled back on the slave (that's only true when all tables involved on a transaction are transaction-safe tables). -- Augusto Bott On 10/30/07, Thomas Raso [EMAIL PROTECTED] wrote: Hi all, on a replication architecture, with the same server, the same Mysql version (4.1.21) and the same configuration, the same database. I have a difference between two ibdata file size innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend on the master : -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:36 ibdata6 -rw-rw1 mysqlmysql 22G Oct 30 11:40 ibdata7 on the slave -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata6 -rw-rw1 mysqlmysql 15G Oct 30 11:40 ibdata7 The difference is over 7Go !!! Is there anybody who has got any explanation about this ??? Thanks all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Relevancy not returning anticipated results?
Sorry, no. I hope someone else will be able to help. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 9:56 AM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Fulltext Relevancy not returning anticipated results? Jerry: Sorry - I should have mentioned in my previous email that we have changed that min. word length to 2 in the config already, as we have MANY 3 letter searches... ;) Any other suggestions? On 10/30/07 9:12 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: By default, MySQL ignores any word with less than four characters when doing full text searches. You can change this in my.conf, the setting should be obvious. I had to do this so that customers could find inventory items that were red. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 5:39 PM To: mysql@lists.mysql.com Subject: Fulltext Relevancy not returning anticipated results? I have a database of products, doing a search on them trying to achieve a modicum of relevancy, but am getting a strange result on some returned rows: QUERY: select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name) against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against ('vic*' IN BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE) as score from products where active='y' and site like '%,1,%' and match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE) order by score desc I get returned 6 rows from the product database. The strange thing is, there is a product (at least one that is known about), with the name: Salchichon de Vic that is NOT being returned as a result. When I break out the scores, I get a match on the first three products returned for the titles: 6-Pack Vichy Catalan Sparkling Spring Water 2-Pack Vichy Catalan Mineral Water San Vicente - Tempranillo 2001 And the other three products have a match in the large_desc. What is confusing me is why I am not getting any match on the titles for the Salchichon de Vic - which by my thought process should be the most relevant of all returns? Any thoughts on this? The term vic used in this case is the search string submitted by the user. You can see the search in action at http://www.tienda.com/support/search.html -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication and ibdata file size
Thanks a lot for this explanation, yeah we are doing many massive update and insert in our databases. -- Thomas Raso 2007/10/30, Augusto Bott [EMAIL PROTECTED]: One possible explanation (possibly not the only one): if you do a massive update on the master, that transaction would need to create many blocks of versioned data. If you roll that transaction back, those blocks will be freed to be reused, but the datafiles won't shrink. Since that transaction wasn't commited, it won't be written to the binary log, so it won't be executed and rolled back on the slave (that's only true when all tables involved on a transaction are transaction-safe tables). -- Augusto Bott On 10/30/07, Thomas Raso [EMAIL PROTECTED] wrote: Hi all, on a replication architecture, with the same server, the same Mysql version (4.1.21) and the same configuration, the same database. I have a difference between two ibdata file size innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:500M:autoextend on the master : -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:39 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:36 ibdata6 -rw-rw1 mysqlmysql 22G Oct 30 11:40 ibdata7 on the slave -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata1 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata2 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata3 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata4 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata5 -rw-rw1 mysqlmysql2.0G Oct 30 11:40 ibdata6 -rw-rw1 mysqlmysql 15G Oct 30 11:40 ibdata7 The difference is over 7Go !!! Is there anybody who has got any explanation about this ??? Thanks all
Re: Sort results by order in list
PaPa, It comes from external source together with Relevance value (float), Then you need to ORDER BY that func. PB - Papalagi Pakeha wrote: Hi, It comes from external source together with Relevance value (float), where the first ID has highest relevance and subsequent IDs are in decreasing order. For example: 109k7 1.79 s3x6 1.34 sxmns 1.21 wt57 0.93 I could use these numbers in the query as well if it helps. PaPa On 10/30/07, Peter Brawley [EMAIL PROTECTED] wrote: I.e. the ideal output would be: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | s3x6 | 2007-10-07 08:58:20 | | wt57 | 2007-10-07 15:57:37 | | sxmns | 2007-10-06 02:17:30 | +---+-+ What rule generates the order 109k7, s3x6, wt57, sxmns? PB Papalagi Pakeha wrote: Hello, I have a query like: SELECT id, start_date FROM iddt WHERE id IN ('109k7','s3x6','sxmns','wt57'); which gives me: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | sxmns | 2007-10-06 02:17:30 | | wt57 | 2007-10-07 15:57:37 | | s3x6 | 2007-10-07 08:58:20 | +---+-+ How can I get the results sorted by the order in which they appear in the ID list? Indeed I could do it in the application but prefer to get the results in the right order from MySQL as the ID list may be long and I may need only first few entries cropped by LIMIT clause. Ideally it should ORDER BY DATE(start_date) DESC and then by the ID list. Do I need some sort of stored function for that? I.e. the ideal output would be: +---+-+ | id| start_date | +---+-+ | 109k7 | 2007-10-07 12:06:58 | | s3x6 | 2007-10-07 08:58:20 | | wt57 | 2007-10-07 15:57:37 | | sxmns | 2007-10-06 02:17:30 | +---+-+ Thanks PaPa
Inserting a value in an autoincrement list?
Hello, I'm programming with PHP+Mysql a CMS system to easily update my site. I want to have a list of titles to which I can add new ones either at the end of the list or in a chosen position so I can choose what order they show up in. The first solution that I thought of was to add one to all of the position values heigher than the position I wish to insert the new one to. But this would envolve alot of queries and therefore resources. So now I'm wandering if it is possible to do this directly with mysql. Here is an example of what I want to do I will have a table called titles like this : table : title --- POSTITION| TITLE 1| title joejjeo 2| title ejuejej 3| title ekkke 4| title eueoueo 5|title eehiehiehop And I wish to insert : 3|title inserted So that titles in positions 3, 4 and 5 become titles 4, 5 and 6 So I would have : table : title --- POSTITION| TITLE 1| title 1 2| title 2 3|title inserted 4| title 3 5| title 4 6|title 5 Is there an easy way to do this with mysql ? and if not what would be the best way to achieve this? Thanks :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ibbackup failure
Hi all, When i try to backup the database through ibbackup i am getting the following error :- Details are here. - InnoDB Backup Utility v1.3.0; Copyright 2003-2005 Innobase Oy This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackup prints innobackup completed OK!. innobackup: Using mysql Ver 14.12 Distrib 5.0.38, for unknown-linux-gnu (x86_64) using readline 5.0 innobackup: Using mysql server version 5.0.38-enterprise-gpl-log innobackup: Using InnoDB Hot Backup version 3.0.0 - ibbackup: You had specified the option --suspend-at-end 071030 10:27:59 ibbackup: Suspending the backup procedure to wait ibbackup: until you delete the marker file /mysql/bk01/2007-10-30_06-12-46/ibbackup_suspended innobackup: Continuing after ibbackup has suspended innobackup: Connecting to mysql with options found in file /mysql/bk01/2007-10-30_06-12-46/connection_info innobackup: Connected to database with mysql child process (pid=28047) Is this a bug or any patch __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Record Counting
Neil Tompkins wrote: I have a table of records all of which have a timestamp against them like 2007-10-25 10:10:19, and category like red, blue etc and a unique key. Using a SELECT statement is it possible to retrieve the count and number of records for each day e.g 2007-10-25 for all red, and all blue etc Hi neil. I would use SELECT count(colour_field_name) AS Counter, DATE_FORMAT(datetimecol,'%y-%m-%d') as Date, colour_field_name FROM table_name GROUP BY Date, colour_field_name; Regards, Ranjeet Walunj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
I knew I’ve seen this error before ☺ Thanks a lot. -andrey From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 1:55 AM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 Try lose the space after group_concat. PB Andrey Dmitriev wrote: Thanks.. It doesn't seem to work though.. I did verify I am on 5.0 mysql select service_names.name as 'Service', - group_concat (hosts.name) - from monarch.hosts as hosts, monarch.services as services, monarch.service_names as service_names - where - hosts.host_id=services.host_id - and service_names.servicename_id=services.servicename_id - group by service_name.name - - - ; ERROR 1305 (42000): FUNCTION mysql.group_concat does not exist -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 4:00 PM To: Andrey Dmitriev Cc: mysql@lists.mysql.com Subject: Re: query question Hi, Andrey Dmitriev wrote: This is kind of achievable in Oracle in either sqlplus mode, or with the use of analytical functions. Or in the worst case by writing a function. But basically I have a few tables Services, Hosts, service_names And I can have a query something like select service_names.name as 'Service', hosts.name as 'Host' from hosts, services, service_names where hosts.host_id=services.host_id and service_names.servicename_id=services.servicename_id order by service_names.name Which outputs something like | SSH | mt-ns4 | | SSH | tsn-adm-core | | SSH | tsn-juno | | SSH | tsn-tsn2 However, the desired output is one line per service name, so something like | SSH | mt-ns4, tsn-adm-core, tsn-juno, tsn-tsn2 | Can this be done w/o writing procedural code in mysql? Yes. Have a look at GROUP_CONCAT(). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group by time range.
I have the following query... SELECT CreateDate, count( * ) FROM `userprofile` GROUP BY CreateDate It isn't exactly what I want. Records are added to this table in 2 main ways. First people use the web site interface to create records. In this case, records are only added by one or 2 people and with a significant time between record inserts. The second way is through an import that reads data from a text file. In the second case the date on the records will all be close together with about 60 records added per second. What I want to do is find all the groups where the inserts all happened with in say 10 seconds. So my group by would be more like.. GROUP BY CreateDate +- 10 SECOND Is there a way to do this? -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
OK - I am at a total loss here :) We have added an addition fulltext field with the highest rating: match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16 And verified that in that field, there is indeed a keyword vic - but still - that result is not returned, SO... That leads me to believe, that despite what my eyes see in the my.cnf: ft_min_word_len = 2 Is indeed, not true. So: 1. How do I confirm that when MYSQL starts up, it is indeed paying attention the the file that I assume it is, /etc/my.cnf 2. How can I confirm whether this setting is confirmed as min word length of 2, rather than the default 3? 3. Am I losing my mind that this search is not returning the result set that I expect? Am I missing something in my query, is the search being performed trying to match something I am not expecting? I guess I am expecting the match to work similar to the like query, except that it is looking for a string of vic somewhere within the field... Perhaps it is something to do with the * appended on there? I thought that as a wildcard operator it would match vic , vichon, vickey, abcvicdef equally, but am I mistaken in that? If so, how can I replicate a like type search with '%vic%' using fulltext, which is our intention? The reason that I am using fulltext is: 1. I understand that is may be faster than a simple like search 2. The number of fields we are using for searching 3. The relevancy ranking that we are doing in this case Am I wrong? I am really pushing the envelope of my MYSQL knowledge here, so assistance is appreciated ;) I am not adverse to reading documentation, just sometimes confused by what I am reading :) TIA! On 10/30/07 10:12 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: Sorry, no. I hope someone else will be able to help. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 9:56 AM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Fulltext Relevancy not returning anticipated results? Jerry: Sorry - I should have mentioned in my previous email that we have changed that min. word length to 2 in the config already, as we have MANY 3 letter searches... ;) Any other suggestions? On 10/30/07 9:12 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: By default, MySQL ignores any word with less than four characters when doing full text searches. You can change this in my.conf, the setting should be obvious. I had to do this so that customers could find inventory items that were red. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Mike Morton [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 5:39 PM To: mysql@lists.mysql.com Subject: Fulltext Relevancy not returning anticipated results? I have a database of products, doing a search on them trying to achieve a modicum of relevancy, but am getting a strange result on some returned rows: QUERY: select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name) against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against ('vic*' IN BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE) as score from products where active='y' and site like '%,1,%' and match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE) order by score desc I get returned 6 rows from the product database. The strange thing is, there is a product (at least one that is known about), with the name: Salchichon de Vic that is NOT being returned as a result. When I break out the scores, I get a match on the first three products returned for the titles: 6-Pack Vichy Catalan Sparkling Spring Water 2-Pack Vichy Catalan Mineral Water San Vicente - Tempranillo 2001 And the other three products have a match in the large_desc. What is confusing me is why I am not getting any match on the titles for the Salchichon de Vic - which by my thought process should be the most relevant of all returns? Any thoughts on this? The term vic used in this case is the search string submitted by the user. You can see the search in action at http://www.tienda.com/support/search.html -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- Cheers Mike
Re: Group by time range.
Hi, Chris W wrote: I have the following query... SELECT CreateDate, count( * ) FROM `userprofile` GROUP BY CreateDate It isn't exactly what I want. Records are added to this table in 2 main ways. First people use the web site interface to create records. In this case, records are only added by one or 2 people and with a significant time between record inserts. The second way is through an import that reads data from a text file. In the second case the date on the records will all be close together with about 60 records added per second. What I want to do is find all the groups where the inserts all happened with in say 10 seconds. So my group by would be more like.. GROUP BY CreateDate +- 10 SECOND Convert the date to a number of seconds, then round to the nearest 20 seconds and convert it back to a date. You can use FROM_UNIXTIME() and UNIX_TIMESTAMP() for the conversion. You can round to the nearest 20 seconds by dividing by 20, rounding to the nearest whole number, and multiplying by 20 again. Group by the resulting expression. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
Mike, Mike Morton wrote: OK - I am at a total loss here :) We have added an addition fulltext field with the highest rating: match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16 And verified that in that field, there is indeed a keyword vic - but still - that result is not returned, SO... That leads me to believe, that despite what my eyes see in the my.cnf: ft_min_word_len = 2 SHOW VARIABLES LIKE 'ft%'; Should show you the variables you care about. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
VIOLA! As it turns out, the cnf file that I was shown was the cnf file on the OLD server that we used when we had DB/Webserver combined on one server - that conf was not updated for the new separate DB server. Thanks muchly all, that change made the search work as expected!!! Thanks for the starting point for finding that mistake Baron! :) *knocks head against wall repeatedly* On 10/30/07 5:09 PM, Baron Schwartz [EMAIL PROTECTED] wrote: Mike, Mike Morton wrote: OK - I am at a total loss here :) We have added an addition fulltext field with the highest rating: match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16 And verified that in that field, there is indeed a keyword vic - but still - that result is not returned, SO... That leads me to believe, that despite what my eyes see in the my.cnf: ft_min_word_len = 2 SHOW VARIABLES LIKE 'ft%'; Should show you the variables you care about. -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group by time range.
Chris, What I want to do is find all the groups where the inserts all happened with in say 10 seconds. So my group by would be more like.. Perhaps the easiest solution is to make a temp table of datetime ranges from the resultset, then join from and group by those rowIDs. PB - Chris W wrote: I have the following query... SELECT CreateDate, count( * ) FROM `userprofile` GROUP BY CreateDate It isn't exactly what I want. Records are added to this table in 2 main ways. First people use the web site interface to create records. In this case, records are only added by one or 2 people and with a significant time between record inserts. The second way is through an import that reads data from a text file. In the second case the date on the records will all be close together with about 60 records added per second. What I want to do is find all the groups where the inserts all happened with in say 10 seconds. So my group by would be more like.. GROUP BY CreateDate +- 10 SECOND Is there a way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP+MySQL: localhost or 127.0.0.1?
Hello all, Using PHP+MySQL, I've noted that: If I use 'localhost', I'm using socket to do the connection. If the socket was wrong, the connections was failed. If I use '127.0.0.1', I'm not using socket and I'll be connected if I have permission. Now the question: Which one have better performance/ speedy? Thanks -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't find file: './mysql/(database_name).frm'
using Distrib 5.0.45 on ubuntu 7.04, mysql was installed via adept manager. I'm getting many error messages on the type: Can't find file: './mysql/(database_name).frm' for numerous databases. The mysql database was installed automatically during installation, and I'm getting them for ./mysql/time_zone_name.frm. Also: I'm getting these type of messages from databases that were installed manually via mysq and a script file. And: I'm getting them from databases installed by directly copying to /var/lib/mysql/(database name) from other machines. In each case, I find that the ownership is set at mysql:mysql and permissions at 660. I'd like to resolve this problem ASAP as it is driving me crazy :-), so any help would be appreciated. thanks tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
Tim Johnson wrote: Can't find file: './mysql/(database_name).frm' for numerous databases. That's the file the actual table data is stored in. Unless you're on a shared machine and are trying to run a private copy of MySQL, you probably don't mean to put store table data in a subdirectory of the current directory (./mysql). Typically this stuff goes in /var/lib/mysql If that's what you want, you can either override the defaults by setting up a custom my.cnf or get a build of MySQL that has the defaults set sanely for your needs. Perhaps the Ubuntu 6.06 LTS binaries you can download from mysql.com will work on your newer system. If so, you can be sure they'll be configured sanely. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
On Tuesday 30 October 2007, Warren Young wrote: Tim Johnson wrote: Can't find file: './mysql/(database_name).frm' for numerous databases. That's the file the actual table data is stored in. Unless you're on a shared machine and are trying to run a private copy of MySQL, you probably don't mean to put store table data in a subdirectory of the current directory (./mysql). Typically this stuff goes in /var/lib/mysql If that's what you want, you can either override the defaults by setting up a custom my.cnf or get a build of MySQL that has the defaults set sanely for your needs. Perhaps the Ubuntu 6.06 LTS binaries you can download from mysql.com will work on your newer system. If so, you can be sure they'll be configured sanely. Oops! Wrongly worded subject and problem. The file pattern is *not* as I expressed it above. It should be ./(database_name)/(table_name).frm IOWS: If it is a problem with the mysql database and the time_zone_name table the error references './mysql/time_zone_name.frm' If it is a problem with database 'project' and table 'customer' then the error references './project/customer.frm' I hope that is clearer. thanks tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
On Tuesday 30 October 2007, Warren Young wrote: Tim Johnson wrote: The file pattern is *not* as I expressed it above. Yeah, I figured that out, and ignored the error in my reply. The answer remains the same: unless you're purposefully doing something weird, there's a configuration error in that MySQL build. It's not that the configuration cannot possibly be right, just that it's unlikely to be correct. Is there a possible repair routine to run? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
Tim Johnson wrote: The file pattern is *not* as I expressed it above. Yeah, I figured that out, and ignored the error in my reply. The answer remains the same: unless you're purposefully doing something weird, there's a configuration error in that MySQL build. It's not that the configuration cannot possibly be right, just that it's unlikely to be correct. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL connection failed: Can not connect to MySQL server
Hi,I have a mail system on FreeBSD 6.2 + Exim 4.62 + MySQL 5.0. Exim was compiled with MySQL support, the MySQL database is external to the mail server. Although the system works, I have a lot of daily messages in exim's log with the following error: MYSQL connection failed: Can not connect to MySQL server on'192 .168.5.1 '(1) What causes some messages are rejected. To try to reproduce the problem I have tried doing Telnet connections to the server and MySQL, sometimes I get the following error: Telnet 192.168.5.1 3306 Trying 192.168.5.1 ... Telnet: connect to address 192.168.5.1: Operation not permitted Telnet: Unable to connect to remote host Someone could help me with this ?, thanks in advance Best regards _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx
Re: Can't find file: './mysql/(database_name).frm'
Tim Johnson wrote: Is there a possible repair routine to run? It isn't broken, per se. Someone chose to build it with this configuration, and presumably that person made a choice that is sensible for their needs. If it doesn't work for you, you can either build MySQL from source with the configuration choices you like, or switch to another build that has defaults you like. That's why I suggested downloading the official binaries from mysql.com: they work for most people. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
On Tuesday 30 October 2007, Warren Young wrote: Tim Johnson wrote: Is there a possible repair routine to run? It isn't broken, per se. When you say that it isn't broken, could you please elaborate? And thanks in advance, because, from where I'm sitting, time is money and I know that it takes time to answers these emails. Someone chose to build it with this configuration, and presumably that person made a choice that is sensible for their needs. If it doesn't work for you, you can either build MySQL from source with the configuration choices you like, or switch to another build that has defaults you like. That's why I suggested downloading the official binaries from mysql.com: they work for most people. Do I understand that you think that this problem comes from the way that the ubuntu development team built mysql? I find this very strange, considering that I have installed mysql on on ubuntu before without this problem. What I did do different on this install was copy some (but not all) directly to /var/lib/mysql/(database_name). I'm also very nervous about mixing the install of of binaries with a fairly succesful update schema - that is - the debian based package management system. Regards Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
Hi, If you are using binaries from mysql.com and extract the files and when u try to start the database it says in the error frm/* not found...then u might try out this option as Try to run the ./configure file which will come with binaries, It will auto create the frm and MY* files under the datadirectory of mysql (/usr/local/mysql/data).This might help u out. under /usr/local/mysql u find a file configure Try to run it.and check out. - Original Message From: Warren Young [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Wednesday, October 31, 2007 8:09:30 AM Subject: Re: Can't find file: './mysql/(database_name).frm' Tim Johnson wrote: Is there a possible repair routine to run? It isn't broken, per se. Someone chose to build it with this configuration, and presumably that person made a choice that is sensible for their needs. If it doesn't work for you, you can either build MySQL from source with the configuration choices you like, or switch to another build that has defaults you like. That's why I suggested downloading the official binaries from mysql.com: they work for most people. -- MySQL General Mailing List For list archives: http://lists..mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Can't find file: './mysql/(database_name).frm'
Tim Johnson wrote: It isn't broken, per se. When you say that it isn't broken, could you please elaborate? Once again: It seems to me that you're seeing a purposeful choice of configuration. It could very well be that the configuration makes sense in some one's use. The fact that it breaks for you doesn't mean the configuration makes no sense. I don't know for a fact that this is what's going on. I don't use Ubuntu. I'm just trying to find an explanation for why it is the way it is. And thanks in advance, because, from where I'm sitting, time is money and I know that it takes time to answers these emails. I'm glad you see that, but why then are you continuing to argue back and forth instead of going to mysql.com and getting a different set of binaries to try? You could have done that about five times in the time it's took to have this exchange. Even if it didn't work, we'd be farther along towards a solution by now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL connection failed: Can not connect to MySQL server
In the last episode (Oct 31), Jose Romero said: Hi,I have a mail system on FreeBSD 6.2 + Exim 4.62 + MySQL 5.0. Exim was compiled with MySQL support, the MySQL database is external to the mail server. Although the system works, I have a lot of daily messages in exim's log with the following error: MYSQL connection failed: Can not connect to MySQL server on'192 .168.5.1 '(1) What causes some messages are rejected. To try to reproduce the problem I have tried doing Telnet connections to the server and MySQL, sometimes I get the following error: Telnet 192.168.5.1 3306 Trying 192.168.5.1 ... Telnet: connect to address 192.168.5.1: Operation not permitted Telnet: Unable to connect to remote host Someone could help me with this ?, thanks in advance Best regards Do you maybe have an ipfw rule that would be blocking that? That's the only thing I can think of that will cause an EPERM error on a socket connect call. A limit rule with a too-low connection count could cause what you're seeing. Adding an explicit allow rule before your limit rule, allowing traffic to port 3306 of the other server, should fix your problem. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: './mysql/(database_name).frm'
On Tuesday 30 October 2007, Warren Young wrote: Tim Johnson wrote: It isn't broken, per se. When you say that it isn't broken, could you please elaborate? Once again: It seems to me that you're seeing a purposeful choice of configuration. It could very well be that the configuration makes sense in some one's use. The fact that it breaks for you doesn't mean the configuration makes no sense. I don't know for a fact that this is what's going on. I don't use Ubuntu. I'm just trying to find an explanation for why it is the way it is. And thanks in advance, because, from where I'm sitting, time is money and I know that it takes time to answers these emails. I'm glad you see that, but why then are you continuing to argue back and forth instead of going to mysql.com and getting a different set of binaries to try? You could have done that about five times in the time it's took to have this exchange. Even if it didn't work, we'd be farther along towards a solution by now. I am not arguing. I am being cautious. If I were on a slack or redhat system, which I'm more familiar with, I would have reinstalled already. I'm going to refer this to a a debian or ubuntu list and see what responses I get before I do anything further. Thanks for your help. Don't make an argument where none exists :-). tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
adding event in 2nd table??
Dear Friends, I am having 2 tables as follow: Table 1- customer_id | contacts | etc this table has unique customer_id while Table 2 may have more than 2 records with the same customer_id Table 2- customer_id | visit_no | details of visit I want to know an example of updating the Table 2 with autoincrementing the visit_no respective to the customer_id e.g. Table 1 customer_id | contacts | etc 1001 tel. nos.etc. 1002 tel. nos.etc. 1003 tel. nos.etc. Table 2 customer_id | visit_no | details of visit 1001 1 details 1001 2 details 1001 3 details 1002 1 details 1002 2 details 1003 1 details 1003 2 details 1003 3 details 1003 4 details Can anyone suggest an example for this type of table? or someother way of having the same results. At present I can update the records in 2nd table by manually entering visit_no value, but one has to remember the previous visit. Any help is appreciated. Thanks Love and regards Prasad