Re: How to import data from Dbase3?
Do you want to do a one time import ? Create an ODBC source that maps to the dBase datasource and write an application to read it from there and import into mysql. - Navneet BG Mahesh wrote: hi How do I import data from Dbase3 into MySQL? -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import data from Dbase3?
Ehrin, If you have dBase III or later, Clipper or FoxPro, you can export the data comma-separated, then use a free csv-to-mysql utility eg http://sourceforge.net/projects/csv-mysql/ to get it into mysql. If you don't have an xbase executable, it's a little harder, but not much provided there's no memo file. A dbase data file is an ASCII file with a binary header. You can read the header in any file viewer, observe the header size, and find the field names lengths. The data is entirely ASCII, padded to exact col lengths, thus simple to parse with a perl or php script. If there's a memo file, you need an xbase executable. PB - Ehrwin Mina wrote: BG Mahesh wrote: hi How do I import data from Dbase3 into MySQL? -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ BG, There are tools you can use to migrate it to mysql, i use it before that i get it in the forum. You can search some forum indicating the migration tools for DB3, Foxpro, access. thanks, -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql threads and debian woody
I have mysql-server 3.23.49-8.9 in a debian woody, chosen for its stability. It looks like the server stops accepting connections when it has created more than 1024 threads. The kernel is 2.4.29. failed: Can't create a new thread (errno 11). If you are not out of available memory. I have searched a lot for OS problem : I changed kernel thread max: more /proc/sys/kernel/threads-max 14336 I also changed ulimit max open files in the init.d mysql script: ulimit -n 6 -u unlimited I recompiled the kernel with a modified version of limits.h I still can't go beyond 1000 threads. I know this because I type show processlist in mysql. What else can I try ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql exclusion query with JOIN (request for help)
Anyone knows who to make exclusion query when table is referenced via JOIN: SELECT * FROM ae_articles AS art LEFT JOIN ae_articlesections AS sec ON art.ID=sec.articleID LEFT JOIN ae_articlesections AS sec2 ON art.ID=sec2.articleID LEFT JOIN ae_articlesections AS sec3 ON art.ID=sec3.articleID WHERE 1 AND (art.title LIKE '%bush%') AND sec.sectionID='1' AND sec2.sectionID='2' AND sec3.sectionID'3' GROUP BY art.ID This returns all articles, even those that are in section ID 3. I only want to retrieve those that are in section 1 AND 2, but NOT 3! How to do this? I am absolutely stuck not knowing what to do, since MySQL does not provide any examples! Thank you, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import data from Dbase3?
Actually I prefer to dump the dbase file on a Linux machine and directly import it into MySQL. - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Ehrwin Mina [EMAIL PROTECTED] Subject: Re: How to import data from Dbase3? Date: Wed, 23 Mar 2005 02:21:48 -0600 Ehrin, If you have dBase III or later, Clipper or FoxPro, you can export the data comma-separated, then use a free csv-to-mysql utility eg http://sourceforge.net/projects/csv-mysql/ to get it into mysql. If you don't have an xbase executable, it's a little harder, but not much provided there's no memo file. A dbase data file is an ASCII file with a binary header. You can read the header in any file viewer, observe the header size, and find the field names lengths. The data is entirely ASCII, padded to exact col lengths, thus simple to parse with a perl or php script. If there's a memo file, you need an xbase executable. PB - Ehrwin Mina wrote: BG Mahesh wrote: hi How do I import data from Dbase3 into MySQL? -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ BG, There are tools you can use to migrate it to mysql, i use it before that i get it in the forum. You can search some forum indicating the migration tools for DB3, Foxpro, access. thanks, -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005 -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ -- __ IndiaInfo Mail - the free e-mail service with a difference! www.indiainfo.com Check out our value-added Premium features, such as an extra 20MB for mail storage, POP3, e-mail forwarding, and ads-free mailboxes! Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
search through one/several tables
Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. Thanks for any help, Melanie _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql exclusion query with JOIN (request for help)
Your query can be made a lot simpler if I understand your description: SELECT * FROM ae_articles AS art JOIN ae_articlesections AS sec ON art.ID=sec.articleID WHERE art.title LIKE '%bush%' AND sec.sectionID IN (1,2) This has nothing to do with MySQL, but is a matter of knowing SQL syntax. You can find info on joins at: http://dev.mysql.com/doc/mysql/en/join.html Regards, Jigal. - Original Message - From: Dan Duris [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 23, 2005 10:35 AM Subject: mysql exclusion query with JOIN (request for help) Anyone knows who to make exclusion query when table is referenced via JOIN: SELECT * FROM ae_articles AS art LEFT JOIN ae_articlesections AS sec ON art.ID=sec.articleID LEFT JOIN ae_articlesections AS sec2 ON art.ID=sec2.articleID LEFT JOIN ae_articlesections AS sec3 ON art.ID=sec3.articleID WHERE 1 AND (art.title LIKE '%bush%') AND sec.sectionID='1' AND sec2.sectionID='2' AND sec3.sectionID'3' GROUP BY art.ID This returns all articles, even those that are in section ID 3. I only want to retrieve those that are in section 1 AND 2, but NOT 3! How to do this? I am absolutely stuck not knowing what to do, since MySQL does not provide any examples! Thank you, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] !DSPAM:4241386c147615731984052! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
I am not quite sure I understand your question, then: you would have to make your table structure a little clearer. Generally, however, puristic database design would say that you do not have the same class of data in different tables. Instead, you have one master table with all the similar data in it, tagged by a unique ID, and all the other tables refer to the master copy by that ID. You then reconstruct the original table at query time using a JOIN. Then, of course, it is trivial to search the master table. I will say that I think fulltext will not help you, if I understand your problem. Fulltext divides a column into separate words, based on the spaces (and non alphanumerics) in the string. Since, as I understand it, your DNA sequences have no natural breaks and the words, such as they are, can start at any base, fulltext will not help you. mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 11:00:08: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql threads and debian woody
Hello. I have mysql-server 3.23.49-8.9 in a debian woody, chosen for its stability. It looks like the server stops accepting connections when it has created more than 1024 threads. I recommend you to switch to the official binaries. Does the problem remain on the latest release (4.1.10a now)? Francesc Guasch [EMAIL PROTECTED] wrote: I have mysql-server 3.23.49-8.9 in a debian woody, chosen for its stability. It looks like the server stops accepting connections when it has created more than 1024 threads. The kernel is 2.4.29. failed: Can't create a new thread (errno 11). If you are not out of available memory. I have searched a lot for OS problem : I changed kernel thread max: more /proc/sys/kernel/threads-max 14336 I also changed ulimit max open files in the init.d mysql script: ulimit -n 6 -u unlimited I recompiled the kernel with a modified version of limits.h I still can't go beyond 1000 threads. I know this because I type show processlist in mysql. What else can I try ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import data from Dbase3?
I remember seeing some JDBC drivers for Java to read dbase 3 files. So it might be a good idea to use that to understand the files and import the data using the mysql jdbc drivers into the mysql database. We can help you do it (provided you pay) since we have already done similar exercise for a customer using VB.NET. - Navneet BG Mahesh wrote: Actually I prefer to dump the dbase file on a Linux machine and directly import it into MySQL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import data from Dbase3?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 BG Mahesh wrote: Actually I prefer to dump the dbase file on a Linux machine and directly import it into MySQL. There are a bunch of ways of skinning that cat. The easiest of which IMHO is to convert the data to comma separated files first and then use any one of a number of means to get it into the database. - -- Peter L. Berghold [EMAIL PROTECTED] Those who fail to learn from history are condemned to repeat it. AIM: redcowdawgYahoo IM: blue_cowdawg ICQ: 11455958 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCQW7WUM9/01RIhaARArhEAJ9ukrn8JSmmwmtdy9y86TVTcIx6vQCfQwZB G3BfaZB+So4iX6kbPlQCkOA= =cQYJ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to import data from Dbase3?
If you have ms access you can import the db3 into access and then export thru odbc to MySql Osvaldo Sommer -Original Message- From: BG Mahesh [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 10:33 PM To: mysql@lists.mysql.com Subject: How to import data from Dbase3? hi How do I import data from Dbase3 into MySQL? -- B.G. Mahesh [EMAIL PROTECTED] http://www.indiainfo.com/ -- __ IndiaInfo Mail - the free e-mail service with a difference! www.indiainfo.com Check out our value-added Premium features, such as an extra 20MB for mail storage, POP3, e-mail forwarding, and ads-free mailboxes! Powered by Outblaze -- 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 Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I agree in priciple with Alec. Good database design can and should promote good data retreival. However, it sounds from your original question that you need to find all records on any table with some bit of information in any column. That problem is just too generic to provide specific help for. If you can't find certain pieces of data, it _may_ be that you haven't organized your data to the point that there is a place for everything and everything is in its place. There is always (except for the most trivial cases) more than one way to organize any set of data, however different schemas offer different advantages (size, speed, ease of use, etc). Which one will work best for you depends on the nature of your data, your database server's limitations (hardware, software, and operating system limits), and what you need to get from the data once it's organized. Some schemas make it harder to add or update data but make finding it a breeze; others are just the opposite. This sounds like a good time to honestly review your current schema to make sure it's going to meet your usage needs not just your data storage requirements. Ask the people who need to use the data you are storing what they need to find and adjust your schemas to fit. Sometimes it means asking them to look by using a different method as a compromise (you give a little, they give a little). Eventually you can work it out. If you have a schema design issue or if you have a more specific set of tables (please post their SHOW CREATE TABLE... results) and a sample query that you cannot solve (even if it's only a description of what you need to find), I am sure the list will be happy to help. Many of us really enjoy those kinds of puzzles. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
semicolon commands causing problems
Hi, Anyone know why I can't run multiple commands from the query browser using semicolons to separate the commands? ex: The following command works fine: select now(); However the following commands do not select now(); select version(); Your help is appreciated, -JM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql exclusion query with JOIN (request for help)
Hi Daniel, Jigal, thank you very much. However, this does not resolve my issue. even when querying: SELECT * FROM ae_articles AS art LEFT JOIN ae_articlesections AS sec ON art.ID=sec.articleID WHERE art.title LIKE '%bush%' AND sec.sectionID IN (1,2) AND sec.sectionID NOT IN (3) What *is* your issue? The query above is not the one I suggested... I guess you have articles which are in more than one section (which you haven't told us)... it displays all articles, even those which are in section no. 3... If you want to display the data on ae_articles which are not in section no. 3 (assuming there are only three sections): SELECT * FROM ae_articles AS art LEFT JOIN ae_articlesections AS sec ON art.ID=sec.articleID AND sec.sectionID=3 WHERE art.title LIKE '%bush%' AND sec2.sectionID IS NULL You can end up with duplicate articles, since an article might be in both section 1 and 2, but you can filter these out by adding GROUP BY art.ID However, thanks for the tip on syntax. I think MySQL official documentors make it sometimes overcomplicated. I think this is common problem with MySQL documentation - very long, very technical/boring, but with very few examples or suggestions or event pointers as to what to try. I don't share this opinion. If you have basic knowledge about SQL the MySQL documentation makes perfect sense. It is technical, but it should be. It has examples which illustrate for example the way a function works. There are loads of tutorials on the web, there are many books about MySQL at about every level, this list helps in most cases, bugs are often fixed in a matter of days and you can always buy technical support if you need it. If you think certain parts of the documentation should be changed, you can always offer to help or file a bug-report with an improved piece of documentation. Paul du Bois c.s. will be more than happy to incorporate extra documentation, I guess ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: semicolon commands causing problems
Justin Moore [EMAIL PROTECTED] wrote on 03/23/2005 10:38:48 AM: Hi, Anyone know why I can't run multiple commands from the query browser using semicolons to separate the commands? ex: The following command works fine: select now(); However the following commands do not select now(); select version(); Your help is appreciated, -JM Executing more than one SQL statment insequence is not a query but is instead called a script. Try using the Script Editor instead: http://dev.mysql.com/doc/query-browser/en/mysql-query-browser-tour-scripteditor-introduction.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: search through one/several tables
I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I agree in priciple with Alec. Good database design can and should promote good data retreival. However, it sounds from your original question that you need to find all records on any table with some bit of information in any column. That problem is just too generic to provide specific help for. If you can't find certain pieces of data, it _may_ be that you haven't organized your data to the point that there is a place for everything and everything is in its place. There is always (except for the most trivial cases) more than one way to organize any set of data, however different schemas offer different advantages (size, speed, ease of use, etc). Which one will work best for you depends on the nature of your data, your database server's limitations (hardware, software, and operating system limits), and what you need to get from the data once it's organized. Some schemas make it harder to add or update data but make finding it a breeze; others are just the opposite. This sounds like a good time to honestly review your current schema to make sure it's going to meet your usage needs not just your data storage requirements. Ask the people who need to use the data you are storing what they need to find and adjust your schemas to fit. Sometimes it means asking them to look by using a different method as a compromise (you give a little, they give a little). Eventually you can work it out. If you have a schema design issue or if you have a more specific set of tables (please post their SHOW CREATE TABLE... results) and a sample query that you cannot solve (even if it's
Re: search through one/several tables
I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 11:08:56 AM: I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] I agree in priciple with Alec. Good database design can and should promote good data retreival. However, it sounds from your original question that you need to find all records on any table with some bit of information in any column. That problem is just too generic to provide specific help for. If you can't find certain pieces of data, it _may_ be that you haven't organized your data to the point that there is a place for everything and everything is in its place. There is always (except for the most trivial cases) more than one way to organize any set of data, however different schemas offer different advantages (size, speed, ease of use, etc). Which one will work best for you depends on the nature of your data, your database server's limitations (hardware, software,
RE: semicolon commands causing problems
I forgot to mention that it is happening from my ant scripts too. So the problem is occurring outside of the query browser as well. However, from the command line I can execute the two statements. Any ideas what could be causing this? -Original Message- From: Justin Moore Sent: Wednesday, March 23, 2005 10:39 AM To: mysql@lists.mysql.com Subject: semicolon commands causing problems Hi, Anyone know why I can't run multiple commands from the query browser using semicolons to separate the commands? ex: The following command works fine: select now(); However the following commands do not select now(); select version(); Your help is appreciated, -JM -- 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: search through one/several tables
The best I've been able to come up with involves some shell scripting. If you're running Linux, using unpacked MyISAM tables, and have some scripting ability from whatever language you're writing your application in, you can run this in your MySQL directory: strings -f *.MYD | grep search string | cut -f1 -d'.' | sort -u It will give you back the table names that have that string somewhere inside them. Then in your scripting language you can check the columns on just the tables that the shell script returned. Note that it is NOT fast at all. My 1.7GB of database takes about 10 minutes to crunch through the data. If you're going to do something like this make sure you understand the security ramifications of running shell scripts with user input and how to secure it in your language of choice. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 23, 2005 10:09 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: search through one/several tables I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
Shawn, Mel I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter Right, but Mel can emulate your ANY_COLUMN with something like SELECT CONCAT( col1, ..., colN ) AS txt FROM tbl WHERE txt LIKE '%foobar%'; PB - [EMAIL PROTECTED] wrote: I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine "mel list_php" [EMAIL PROTECTED] wrote on 03/23/2005 11:08:56 AM: I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for "Smith" I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: "mel list_php" [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 "mel list_php" [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + "mel list_php" [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see
RE: semicolon commands causing problems
Normally, MySQL queries DO NOT end in a semicolon. The semicolon (;) and the \G terminators are used by the command line client to differentiate between normal (wide output) and vertical output. I have also used semicolons in SQL scripts loaded by the source command to be processed through the command line client but I haven't tried to use the \G terminator so I don't know if it works in that context. In this case the command line client processes each statement of my script, in turn, just as though I had typed (or pasted) them into the interface. I do not know ANT at all so I can't say how each ANT command/statement is to be terminated. But, if you are executing SQL commands through an ANT connection to the database, I think you will need to send (execute, process, whatever the verb is in ANT...) your SQL statements one at a time as your connection library is most likely unable to handle the multiple results that would occur if you tried to execute several statements at once. Each SQL statement should probably NOT end with a semicolon. There is a convention on this list to end each statement with a semicolon or a \G. That is because we are assuming that everyone has at least the MySQL command line client and we are usually writing our scripts or statements for that interface. If you are using a different interface (such as an ANT to MySQL connection library), you need to abide by the rules of that interface and port our examples accordingly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Justin Moore [EMAIL PROTECTED] wrote on 03/23/2005 11:04:40 AM: I forgot to mention that it is happening from my ant scripts too. So the problem is occurring outside of the query browser as well. However, from the command line I can execute the two statements. Any ideas what could be causing this? -Original Message- From: Justin Moore Sent: Wednesday, March 23, 2005 10:39 AM To: mysql@lists.mysql.com Subject: semicolon commands causing problems Hi, Anyone know why I can't run multiple commands from the query browser using semicolons to separate the commands? ex: The following command works fine: select now(); However the following commands do not select now(); select version(); Your help is appreciated, -JM -- 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 server disconnects when executing simple Select statements++
I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server disconnects when executing simple Select statements++
You say it usually crashes near the same record? Could you post the record information and also the query which is being run? Also, is there any information in your hostname.err file? If mysqld is bailing (and it appears that it is), it ought to be writing something useful to the error log. Cheers, --V mos wrote: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server disconnects when executing simple Select statements++
mos [EMAIL PROTECTED] wrote on 03/23/2005 12:04:59 PM: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike Please, PLEASE, tell me you are not creating and dropping a connection for each query within your loop. If you are, try rewriting your code create one connection early and drop it only when it's no longer useful. Always try to reuse an established connection as much as possible and always use the fewest number of connections you need to get the job done. Some statements (particularly extended INSERT statements) can exceed your server's max_allowed_packet size, which I have also seen cause that error. Also, check your MySQL server's error log to see if you are causing a condition you are not seeing reported back to your compiled code. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: search through one/several tables
Unfortunatly I can't use the alias txt in the where clause: Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal: SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt 0 GROUP BY id; http://dev.mysql.com/doc/mysql/en/problems-with-alias.html From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:46:10 -0600 Shawn, Mel I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter Right, but Mel can emulate your ANY_COLUMN with something like SELECT CONCAT( col1, ..., colN ) AS txt FROM tbl WHERE txt LIKE '%foobar%'; PB - [EMAIL PROTECTED] wrote: I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 11:08:56 AM: I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: How to import data from Dbase3?
Hi, How do I import data from Dbase3 into MySQL? My software, libmygis, is mainly designed for loading a set of Shapefiles into MySQL (.SHP, .SHX, .DBF) but it's perfectly well capable of loading just the dBase III/IV component. Grab libmygis-0.5.1 from: http://jcole.us/software/libmygis/ You should be able to load a file, e.g. foo.dbf, by doing: tools/mysqlgisimport -S foo | mysql -u myuser -p mydb It will automatically create a table suitable for the dBase file and load the records in with the above command. Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Standards on Database Export-Import
Actually, exporting data from an Oracle database is one of the weak spots of Oracle. Oracle makes it easy to get data in, but hard to get it back out. There are ways to do it, but nothing easy that an Oracle DBA will have if they only read the Oracle docs. Spooling an output file can get tedius if there are a lot of columns in the table or many tables. There are a number of freeware and open source solutions as well as third party solutions, but in most cases, a good DBA should be able to develop some dynamic scripts to get the data out in the format you need. Features like the use of LONG datatypes may throw a monkey wrench in the works. Good luck. Stephen (Sr Oracle DBA by day - Jr MySQL DBA by night) On Tue, 22 Mar 2005 10:48:53 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Z X C V [EMAIL PROTECTED] wrote on 03/21/2005 08:32:24 PM: Hello, Are there any good rfcs or other documents that would assist in the discussion I'm having with our organization's Oracle programmers in regards the following: Oracle DB - MySQL They recommend: ~ col~col ~col~ col',~col NOTE::I placed the ' in for a reason. I am looking for: col,col,col,col\'\,,col Of course the non-Oracle system is being tasked as the problem in this case for the abnormal data request. Any help on this would be appreciated. Rob. Unfortunately I am not sure any document like you seek actually exists (at least not in any modern literature). There are many ways to move data from memory to media and the format of the data on the media is usually determined by a number of factors (type of data, type of media, is the data intended for human or machine use, etc.). So I am not sure there ARE any standards for database export formatting (I did a quick Google and didn't find any in the top 50 responses for the search: data export standard format CSV (see notes below))... To be honest, the mysqlimport and LOAD DATA INFILE facilities should be flexible enough to read the tilde-delimited format that your Oracle guys want to give you. If not, you should be able to ask them to either create a tab-delimited or comma-delimited dump (both formats are so ancient as to be /de facto/ standards and should be something that Oracle can create without any difficulty). What you were asking for is a comma-delimited format but you need some extra escaping to make it more MySQL compatible. I think that the extra escaping is what's throwing a monkey-wrench into the export process. One viable option is for you to write a quick converter that re-parses their tilde-delimited format into something properly escaped for MySQL. IF you write it correctly, your format converter should be able to process several megabytes of dumped data per second. Yes, it's a pain in the neck to take the extra conversion step but sometimes we DBA's have to assemble our own lemons before we get to make the lemonade. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Notes: a non-normative description of the CSV format: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm An example data export screen, a custom delimiter of a tilde would reproduce the format you are being offered: http://www.jinfonet.com/manualpro/userguide/expt_txt.htm Some export/import formats of various ecological data. Notice how many there are: http://gce-lter.marsci.uga.edu/lter/data/formats.htm A proposed format for Thoughts exchange: http://www.ms.lt/importexport.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
But you can use column aliases in a HAVING clause (a simple mistake when you are typing as fast as you are thinking). The other option is to put the CONCAT(...) into the WHERE clause in place of the alias. Either way, you will absolutely NOT be using an index to search that table. You will need to scan the results of each CONCAT() for each row (at least as long as a full table scan would take). Shawn Green Database Administrator Unimin Corporation - Spruce Pine mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 12:24:42 PM: Unfortunatly I can't use the alias txt in the where clause: Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal: SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt 0 GROUP BY id; http://dev.mysql.com/doc/mysql/en/problems-with-alias.html From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:46:10 -0600 Shawn, Mel I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter Right, but Mel can emulate your ANY_COLUMN with something like SELECT CONCAT( col1, ..., colN ) AS txt FROM tbl WHERE txt LIKE '%foobar%'; PB - [EMAIL PROTECTED] wrote: I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 11:08:56 AM: I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do
Re: MySQL server disconnects when executing simple Select statements++
Lost connection to MySQL server during query The last time I saw this mysqld was segfaulting on a specific query. The solution was to run an extended repair on the tables in question. http://dev.mysql.com/doc/mysql/en/repair-table.html I would reccommend against doing this until after hours as this will lock your MyISAM tables. Quoting mos [EMAIL PROTECTED]: I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL server disconnects when executing simple Select stateme nts++
What device driver are you using to access the database? Can you run a sql trace or maybe turn on the logging on the server side? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 23, 2005 11:05 AM To: MySQL list Subject: MySQL server disconnects when executing simple Select statements++ I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? 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: search through one/several tables
Right, my mistake, you would have to use HAVING. PB mel list_php wrote: Unfortunatly I can't use the alias txt in the where clause: Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This is because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal: SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt 0 GROUP BY id; http://dev.mysql.com/doc/mysql/en/problems-with-alias.html From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:46:10 -0600 Shawn, Mel I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter Right, but Mel can emulate your ANY_COLUMN with something like SELECT CONCAT( col1, ..., colN ) AS txt FROM tbl WHERE txt LIKE '%foobar%'; PB - [EMAIL PROTECTED] wrote: I am not aware of any SQL dialect that supports a query of the kind you are asking about. If there were such a query it _might_ look something like this SELECT column list FROM table reference WHERE ANY_COLUMN(column name list) LIKE search parameter but like I said, I can't think of any SQL server that has a function like ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a column-by-column comparison (especially if you want to use LIKE or RLIKE). Shawn Green Database Administrator Unimin Corporation - Spruce Pine mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 11:08:56 AM: I can find the data, I was just wondering if mysql provides a kind of generic scan of a whole table. I could provide an advanced search, and ask the user what kind of info he wants to retrieve but I first would like a quickSearch that may retrieve too much info but is more intuitive. As I said, my first idea was to create a script to go through all my tables and scan the relevant columns (I don't want to scan the id keys for example), but I was just wondering if given a table it is possible to use mysql to scan all its columns: *** 1. row *** Table: dbLab Create Table: CREATE TABLE `dbLab` ( `labId` tinyint(5) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `fullName` varchar(250) default NULL, `adress` varchar(150) NOT NULL default '', `city` varchar(50) NOT NULL default '', `country` varchar(50) NOT NULL default '', `url` varchar(100) NOT NULL default '', PRIMARY KEY (`labId`) ) TYPE=MyISAM COMMENT='list of partners' 1 row in set (0.00 sec) If I search for Smith I want to retrieve the name Smith, the fullname Smith, the address or city containing Smithbasically look for the word in all the columns, a shortener for select * from dbLab where name like'%Smith%' or fullName like '%Smith%' or adress like '%Smith%'. But maybe this is not a good way to do things? Melanie From: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] wrote on 03/23/2005 06:00:08 AM: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List
Newbie: Searching for empty fields
how do you search for an empty field ? I want to filter out records where the ipAddress field is empty SELECT * FROM 'userLog' WHERE 'ipAddress' IS [empty] ? many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie: Searching for empty fields
On Wednesday, March 23, 2005 12:13, Graham Anderson wrote: how do you search for an empty field ? I want to filter out records where the ipAddress field is empty SELECT * FROM 'userLog' WHERE 'ipAddress' IS [empty] ? Searching for NULL's -- SELECT * FROM userLog WHERE ipAddress IS NULL Searching for empty string -- SELECT * FROM userLog WHERE ipAddress = '' Or either -- SELECT * FROM userLog WHERE ipAddress IS NULL OR ipAddress = '' There is no space between the single quotes. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sharing a Database Between Websites
I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
David Blomstrom said: I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. The problem would be large tables rather than large database. Traversing a table is what costs time. There would be a slight increase in the Operating System finding the files associated with each table, but this is a factor only with very large numbers of tables not 100. This all assumes MyISAM table type. I don't know about the others since they do share a single file. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- 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]
adding an incremented count from page hits
I have an existing database/tables and would like to add a counter to a
table that would increment when a document is downloaded from the
database. I am sure it is an easy addition to the php page but struck-out
looking for info not related to auto-increment in the mysql manual. I
tried adding this to the php page:
(more else statements up here)
elseif ($orderby == 'bg'):
$sql = select * from manuals_list where cat like 'bg' order by
'file_name';
else:
$sql = select * from manuals_list where cat like 'ap' order by
'file_name';
endif;
$result = mysql_query($sql);
while ( $row = mysql_fetch_array($result))
{
printf(tr\ntd class=\link\\n
a
href='../docs/$row[url]'%s/a\n/td\ntd%sKB/td\ntd%s/td\n/tr\n,
$row[file_name], $row[size], $row[rev]);
}
count=count+1; --- This is the added bit ---
?
---
The count doesn't increment in the database. The table has a column called
'count'.
Can someone please please point me in the right direction?
Thanks,
Chip
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
Your theory that more tables won't slow down a client if it doesn't access them is correct, it won't slow the client down. But it's slowing down the server that you should be worried about, not the client. The more tables you have, the more file handles MySQL has to have open along with caching the security information for the tables. 100 tables won't make a noticeable difference, but is your setup something that could possible grow to 1000 or more tables? Then you are going to hit limits of the operating system, thus the design is not scalable. Big tables will have slower queries. But big is relative. Provided indexes are used, querying a 100 record table vs a 10,000 record won't have a noticeable time difference, even though there is 100x difference in the size of the table. Some might think 100,000 records is a large table, it's not. There are advantages to one large table over many smaller tables. A large table will take advantage of query caching better (if you have query cache enabled). Lots of smaller table would probably cause the query cache to get flushed more often. Of course, difference strategies will apply depending on whether you are using InnoDB or MyISAM table types. I would focus on good database design, good performance will usually follow good design. On Mar 23, 2005, at 1:36 PM, David Blomstrom wrote: I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help me: Boolean fulltext searches, AND instead of OR
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN
MODE);
sure there must be an easy way to change the default word separator to AND
instead of OR?
I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was
possible.
black cat is only an example and the real query comes from user input. So
it can be anything like +cat -dog +big nose -horse white black -red
so parsing the input is not what i want, i just want to change the default
word separator to AND instead of OR.
Now i´m running 4.1.10 and also tried with 4.0.24
Please help me, there must be a way to change this?!?!
_
Hitta rätt på nätet med MSN Sök http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help me: Boolean fulltext searches, AND instead of OR
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN
MODE);
sure there must be an easy way to change the default word separator to AND
instead of OR?
I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was
possible.
I know that i could put a + infront of every word but thats not what i want
since the query comes from a searchbox and i dont want to write som code to
manipulate the indata so ite shows correctly i just want to change the
default word separator from OR to AND.
Maybe it´s ft_boolean_syntax that should be changed? If so to what... please
help me before i try to strangle myself :P
_
Chatt: Träffa nya nätkompisar på Habbo Hotel
http://habbohotel.msn.se/habbo/sv/channelizer
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
--- Brent Baisley [EMAIL PROTECTED] wrote: There are advantages to one large table over many smaller tables. A large table will take advantage of query caching better (if you have query cache enabled). Lots of smaller table would probably cause the query cache to get flushed more often. OK, thanks. I don't foresee my database growing to over 200 tables in the near future. How do I determine if I have query cache enabled? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
show variables like 'query%'; That will show you the setting for mainly your query cache. The query_cache_type will tell you if it's on or not. On Mar 23, 2005, at 3:08 PM, David Blomstrom wrote: --- Brent Baisley [EMAIL PROTECTED] wrote: There are advantages to one large table over many smaller tables. A large table will take advantage of query caching better (if you have query cache enabled). Lots of smaller table would probably cause the query cache to get flushed more often. OK, thanks. I don't foresee my database growing to over 200 tables in the near future. How do I determine if I have query cache enabled? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import data from Dbase3?
If Dbase3 has an odbc driver then you can use SQLyog (www.webyog.com) to import data to MySQL. Karam --- Peter L. Berghold [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 BG Mahesh wrote: Actually I prefer to dump the dbase file on a Linux machine and directly import it into MySQL. There are a bunch of ways of skinning that cat. The easiest of which IMHO is to convert the data to comma separated files first and then use any one of a number of means to get it into the database. - -- Peter L. Berghold [EMAIL PROTECTED] Those who fail to learn from history are condemned to repeat it. AIM: redcowdawgYahoo IM: blue_cowdawg ICQ: 11455958 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCQW7WUM9/01RIhaARArhEAJ9ukrn8JSmmwmtdy9y86TVTcIx6vQCfQwZB G3BfaZB+So4iX6kbPlQCkOA= =cQYJ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
I couldn't find much on the variable you were trying to change.
Although it may have changed and now be called ft_boolean_syntax. But
that still won't help you since it doesn't appear to have an option to
change the default separator.
I think you will need to add the + to each word if you want the AND
behavior. Why are you so averse to parsing? It would make it easier for
the user to input search terms.
If it's helpful, below is some php code I use for parsing search
phrases entered by users. It adds + and * based on quotes or
independent words. I wrote it a long time ago and it problem needs to
be looked at to make it better, but it's worked for me for a while now.
It will give you the AND behavior you are looking for.
function prepFullTextSearch($searchVal) {
//Split words into list
$word_List = explode(' ',trim($searchVal));
//Step through word list to get search phrases
$i = 0;
$isPhrase= false;
foreach($word_List as $word) {
$searchItems[$i] = trim( ($isPhrase?$searchItems[$i].' '.$word:$word)
);
//Check for start of Phrase
if(substr($searchItems[$i],0,1) == '') {
$isPhrase = true;
}
//If not building a phrase, append wildcard (*) to end of word
if(!$isPhrase) {
$searchItems[$i] .= '*';
$i++;
}
//Check for end of Phrase
if(substr($searchItems[$i],-1) == '') {
$isPhrase = false;
$i++;
}
}
$searchVal= '+'.implode(' +',$searchItems);
return $searchVal;
}
On Mar 23, 2005, at 2:45 PM, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that
contains both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
BOOLEAN MODE);
sure there must be an easy way to change the default word separator to
AND instead of OR?
I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was
possible.
black cat is only an example and the real query comes from user
input. So it can be anything like +cat -dog +big nose -horse white
black -red
so parsing the input is not what i want, i just want to change the
default word separator to AND instead of OR.
Now i´m running 4.1.10 and also tried with 4.0.24
Please help me, there must be a way to change this?!?!
_
Hitta rätt på nätet med MSN Sök http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Hi!
On Mar 23, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
BOOLEAN MODE);
sure there must be an easy way to change the default word separator to AND
instead of OR?
There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for must be
present and a space ' ' for optionally present words.
You want to put the space first (for must be present words)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Hi that works fine only now if a user puts + in front of a word that word
becomes optional = OR?
What i would like is the search to work exactly like before only that it
defaults to AND instead of OR.
Almost every search engine i have tried, google etc. works like this.
black horse cat dog only show results with all words present.
This is what i did, set global ft_boolean_syntax = ' +-()~*:|'
I really appreciate your help!!
From: Sergei Golubchik [EMAIL PROTECTED]
To: Jessica Svensson [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR
Date: Wed, 23 Mar 2005 22:32:49 +0100
Hi!
On Mar 23, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that
contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
BOOLEAN MODE);
sure there must be an easy way to change the default word separator to
AND
instead of OR?
There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for must be
present and a space ' ' for optionally present words.
You want to put the space first (for must be present words)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
On Wed, 23 Mar 2005 22:22:34 +, Jessica Svensson
[EMAIL PROTECTED] said:
Hi that works fine only now if a user puts + in front of a word that word
becomes optional = OR?
What i would like is the search to work exactly like before only that it
defaults to AND instead of OR.
Almost every search engine i have tried, google etc. works like this.
black horse cat dog only show results with all words present.
I'm a little late in the discussion but...
Are you using a scripting language with MYSQL? It'd be easy w/PHP
This is what i did, set global ft_boolean_syntax = ' +-()~*:|'
I really appreciate your help!!
From: Sergei Golubchik [EMAIL PROTECTED]
To: Jessica Svensson [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR
Date: Wed, 23 Mar 2005 22:32:49 +0100
Hi!
On Mar 23, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that
contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
BOOLEAN MODE);
sure there must be an easy way to change the default word separator to
AND
instead of OR?
There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for must be
present and a space ' ' for optionally present words.
You want to put the space first (for must be present words)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
--
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]
Newvbie:TO_DAYS Question
stupidly My DateTime field is in this format: March 23, 2005, 3:49 pm If I want to run this sql SELECT TO_DAYS(MAX(DateTime)) - TO_DAYS(MIN(DateTime)) AS record FROM userLog I gather I need some extra function like.. SELECT TO_DAYS(MAX(Convert _Function(DateTime))) - TO_DAYS(MIN(Convert _Function(DateTime))) AS record FROM userLog what is that magic function ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: is this Query Reasonably Efficient ?
I have 3 different tables I need data from And, the tables have the potential to get fairly large I am using mysql 4.1.3 This working query below pulls up all media requests for 'Yolanda Perez' in Los Angeles Is there a appreciably speedier way to say the below ? I have begun dipping my toe into more complicated queries Select artist.name,media.name,userLog.city,userLog.DateTime From userLog,media,artist Where city = 'Los Angeles' And userLog.media_id = media.id And media.artist_id=artist.id And artist.name = 'Yolanda Perez' many thanks g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default Date Format
Dear all, How to change the Default Date/DateTime Format of MySQL server? I'm using MySQL 4.1.10-nt on WinXP Pro. Thanks. Shuan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using PHP to select MySQL record
Sorry if this is simple, but I'm a fairly new user and it's got me stumped.
Using PHP and MySql; I want to look up a specific record from a table of
people by using a select query to populate a PHP select /select
statement. I can do it with one field, but I need to do it with more
than one field since I have people with the same last name.
This populates my option box quite well:
?php
$sql = SELECT * FROM chairs order by lastname;
$result = mysql_query($sql);
echo trtd align='right'bChairman's Name:/b/tdtd align='left'
select name='person';
while ($rows = mysql_fetch_array($result)){
echo option.$rows['lastname']., .$rows['firstname'].
.$rows['chairid']./option;
}//end while
echo /select/td/tr;
?
It gives me a Smith, John 42 to select, with 42 being the chairid
(index field)
I need to know how to use this to pick a specific record up for editing.
I appreciate any help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: semicolon commands causing problems
Hi, Which version of Mysql you are using? My query browser executes mutiple commands -semicolon as seperated, and my ant script too works !! The version Im using is 4.0.24. Thanks Usha - Original Message - From: Justin Moore [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 23, 2005 9:34 PM Subject: RE: semicolon commands causing problems I forgot to mention that it is happening from my ant scripts too. So the problem is occurring outside of the query browser as well. However, from the command line I can execute the two statements. Any ideas what could be causing this? -Original Message- From: Justin Moore Sent: Wednesday, March 23, 2005 10:39 AM To: mysql@lists.mysql.com Subject: semicolon commands causing problems Hi, Anyone know why I can't run multiple commands from the query browser using semicolons to separate the commands? ex: The following command works fine: select now(); However the following commands do not select now(); select version(); Your help is appreciated, -JM -- 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] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to rollback in MySql?
Hi All, I have just started using My-sql 4.1.9 mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Is there a way I could rollback my changes , i tried to go through the documentation but it wasn't of much help. regards Digvijoy
How do I get rid of this field???
I was trying to imnport a csv file into an online databse table, but I kept getting error messages alluding to a child row, foreign key, etc. So I decided to delete both keys (primary and index), then import the csv file. I finally managed to delete the primary key, but I can't zap the index. When I try to delete the field itself, I get this error message: #1025 - Error on rename of './geoblue_gypsy/#sql-bb1_1e763' to './geoblue_gypsy/counties' (errno: 150) When I try to delete the primary key, I get this message: #1025 - Error on rename of './geoblue_gypsy/#sql-bb1_1e782' to './geoblue_gypsy/counties' (errno: 150) So how do I get rid of it? Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to rollback in MySql?
Hello, I have just started using My-sql 4.1.9 mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Is there a way I could rollback my changes , i tried to go through the documentation but it wasn't of much help. Transactions are only used when you use the InnoDB or BDB table types. The default MYISAM doesn't support transactions. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql threads and debian woody
Gleb Paharenko wrote: Hello. I have mysql-server 3.23.49-8.9 in a debian woody, chosen for its stability. It looks like the server stops accepting connections when it has created more than 1024 threads. I recommend you to switch to the official binaries. Does the problem remain on the latest release (4.1.10a now)? I thought it was OS related, and the mysql version was not the problem. So I tweaked the limit of bash and linux. In addition I don't want to loose the deb dependency. Is there something else I can try before ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
