Re: Computer reboots during long table join
On 15 Apr 2005 at 9:47, mos wrote: I'm running MySQL 4.1.10 on XP and I was doing a 2 table equi join join on a date field that was indexed. There are It was taking quite a long time so I let it run overnight. There are 200 million rows in the first table, but a Where clause on the second table would have reduced this to around 50 million rows. This morning I got in and the computer had rebooted itself sometime during the night. (This is usually not a good sign before the first cup of coffee.g) There were no errors in the MySQL.Err log file. Explain said they were using an index for both tables. 1) Can MySQL join two tables of this size? 2) Is there a problem using a Date join? I noticed before doing date joins was extremely slow, even when indexed. 3) Is throwing more RAM at the problem going to help? Is there any way of knowing how much RAM is enough? Hi, The first thing to do is turn off that anoying XP *feature* of rebooting when there is an error - most of the time you don't need it. I think the option is in My Computer Properties somewhere. Mysql may get a chance to print an error message then, or it will be displayed on screen. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where mysql_config?
On 24 Feb 2005 at 12:50, schlubediwup wrote: Hi this is the error message you get when trying to access mysql from php using a userid which has been created under mysql 4.1 whithout taking the precaution to apply the old_password function to create the password: Client does not support authentication protocol requested by server; consider upgrading MySQL client Hi, Doing a search on google for Client does not support authentication protocol requested gives this as the first result: http://dev.mysql.com/doc/mysql/en/old-client.html which describes your problems and how to fix it. Regards Ian --
Error compiling from source rpm 4.1.10 on Cobalt raq4i
Hi, I am trying to compile the official source rpm on a Cobalt Raq4i using the command: rpm --rebuild --clean MySQL-4.1.10-0.src.rpm But I get the follwing error: examples/ha_tina.cc: In method `int ha_tina::rnd_init(bool = 1)': examples/ha_tina.cc:612: `MADV_SEQUENTIAL' undeclared (first use this function) examples/ha_tina.cc:612: (Each undeclared identifier is reported only once examples/ha_tina.cc:612: for each function it appears in.) make[4]: *** [ha_tina.o] Error 1 make[4]: Leaving directory `/home/redhat/BUILD/mysql-4.1.10/sql' make[3]: *** [all-recursive] Error 1 make[3]: Leaving directory `/home/redhat/BUILD/mysql-4.1.10/sql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/redhat/BUILD/mysql-4.1.10/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/home/redhat/BUILD/mysql-4.1.10' make: *** [all] Error 2 Bad exit status from /var/tmp/rpm-tmp.35848 (%build) rpm version:rpm-build-3.0.5-9.6x gcc:gcc-2.95.3-1c1r4 uname -sr: Linux 2.2.16C35_III This worked fine for version 4.1.7 Does anyone have any ideas? Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 Character set problem ( Ithink?)
On 12 Feb 2005 at 14:09, Gleb Paharenko wrote: Hello. Please tell us, what output the following statement produces: SHOW VARIABLES LIKE '%char%'; Hi Gleb, mysql SHOW VARIABLES LIKE '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.08 sec) I assume the character_set_system being utf8 is the problem, but how do I change it? You can use hexademical values for inserting the data. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html I've taken a look at this and it could prove very useful. I wish I had the time to read the whole manual! Thanks Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.7 Character set problem ( Ithink?)
Hi List, I am having a strange problem on Linux Fedora Core 3 with MySQL 4.1.7 ( offical mysql rpms). The data was originally stored in MySQL 3.something and was placed into the database via a MySQLDump file. It is too late to reload the data. I have a table called fees: CREATE TABLE `fees` ( `refID` int(11) NOT NULL default '0', `price` text, `tuitionFee` tinyint(4) default NULL, `examFee` tinyint(4) default NULL, `otherFee` tinyint(4) default NULL, `feeText` text, `pending` tinyint(4) default '0', PRIMARY KEY (`refID`), KEY `refID` (`refID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin2 When I try updating the price field for one record, it doesn't seem to recognise the pound sign (£): mysql UPDATE fees SET price= '£45' WHERE refID=732; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql select price from fees where refID=732; +---+ | price | +---+ | ?45 | +---+ 1 row in set (0.00 sec) The same result ?45 is returned via php as well, so its not a console display problem. I have also tried this with the latin1 character set with the same results. I know I am probably better off changing the field type to a double and placing the pound sign in my php code, but I am curious as to why this happens. Is it a problem with the character sets? Should I be using a different character set for English language text ( no international chars ). Any help will be appreciated. Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where can I download The MySQL C API??
On 25 Jan 2005 at 16:07, Shuva, Judith wrote: Hi J.R. Thanks for your answer. I saw that the API suppose to be in the MySQL server, so I downloaded mysql-essential-4.1.9-win32.msi, but after the installation, I can't find the API in the MySQL folder... Please, HELP! Hi, The MySQL API ( libmysql.dll ) is usually in the MYSQL PATH\bin folder. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Embedding MySQL in application, probably developed in C#
On 19 Jan 2005 at 21:28, elizabeth baker wrote: We are building an application for myths of the world. It started out in Access, but when we decided to distribute it via CD to attendees at an art retrospective (about myths of the world), we began looking for an alternative db, since not everyone has Access on their computer. There will be no charge for the retrospective or the application CD. The application will eventually be web-based and available on the internet. MySQL was recommended to us, as was developing in C#. i have read that MySQL can be embedded in applications. Is this an advisable route for us? to develop and C# and embed MySQL in the application? i'm somewhat at a loss as how to proceed, and time is running out. Any help/advice is greatly appreciated. Hi Elizabeth, I understand from you email that you have no personal preference to the database / language you write this application, only that you have been recommended MySQL and C#. Therefore I would recommend you ignore the CD for now (bare with me!) and go ahead and write your web application with whatever DB and language you feel comfortable with. All though I recommend you stay away from MS Access for a web based database as there all sorts of problems with locking / updating. When you have completed your website you can then use a mirroring tool to create a copy of the website ( for windows I recommend HTTrack ). You can then distribute this copy as a CD based website. This also has the advantage of working on any platform that has a web browser and a CD drive. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL DBD not following my.cnf for socket file
On 13 Jan 2005 at 10:47, Sid Lane wrote: hope this isn't considered too off-topic but... I have been working on standardizing the directory trees on our MySQL servers (a la OFA for those who speak Oracle) but when I repoint the socket parameter in my.cnf all my perl scripts barf w/: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at ./somescript.pl line 666 I can connect from a shell w/the mysql client no problem. it's only DBD that's having this problem and pointing socket back in my.cnf and restarting mysql fixes it which leads me to believe this is statically configured into DBD when you build it. does anyone know if this is the case? is there anything short of rebuilding DBD that will get it to use the new location? any help would be appreciated. Hi, If you are using *nix platform then you can create a symbolic link to the new location of the socket file: ln -s /new/location/mysql.sock /var/lib/mysql/mysql.sock I have to do this because sometimes Chiliasp looks for the socket in /tmp. You could also investigate whether DBD needs / reads a special entry in the my.ini / my.cnf file You might be able to do something similar under windows using SysInternals *junction* tool ( freeware: http://www.sysinternals.com/ntw2k/source/misc.shtml ) but I believe this only works with directories. Ian --
Re: Reset permissions
On 23 Nov 2004 at 22:36, Tim Trice wrote: How can I reset the permissions on my database? I have no clue when I did it (at least several months ago), haven't used it until today but I cannot for the life of me figure out how I can create a new user with all permissions. The only user I have does not have grant or create privileges. Am I screwed? Hi, Take a look at: How to Reset the Root Password http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Starting mysql as a service once installed
On 22 Nov 2004 at 9:34, James Sherwood wrote: Hello, I have installed mysql but did not install it as a service. All I do is runmysqld-max-nt.exe Now I need it to run as a service. It is on a win2003 machine. Can I make it run as a service once I already have it installed. I cannot lose data and do not want to do a mysqldump, uninstall and reinstall as this would bring the server down for too long. Is there a way to make it start running as a service? Thanks in advance James Hi James, You can install / uninstall the MySQL service by running the program with a special command line switch. I am not sure of your level of windows experience so I will describe the process in its most basic form. Open a command prompt and change directories to the MySQL programs directory. If you installed MySQL to the folder c:\mysql, this command would be: cd c:\mysql\bin Then run the mysqld-max-nt.exe program with the switch *-install* : mysqld-max-nt.exe -install You can also uninstall the service using this command: mysqld-max-nt.exe -remove For more command line install options run this command and then read the txt file that is created ( help.txt ) mysqld-max-nt.exe --help help.txt There are options to install the service under a different name and with different start up options. Hope this helps Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC Initial Setup Problems
On 5 Nov 2004 at 10:01, Rick Dwyer wrote: Hello All: I am trying to configure ODBC for MySQL but have been unable to make a connection. Hi, I have no experience of Mac OS X, but I believe it is linux-ish which means case- sensetive path names. The line below shows library with a lower case L: We are running MySQL 4.0.15 supplied by Server Logistics on OS X 10.3.5. I have installed their ODBC Driver and 4 files show up in the library/MyODBC/Lib/ directory: ^ libmyodbc3_r-3.51.06.bundle libmyodbc3_r.bundle (alias) libmyodbc3-3.51.06.bundle libmyodbc3.bundle (alias) Under the ODBC Admin I have added a driver with the name mysqlreporting specifying the driver file path as : This shows library with a capital L: /Library/MyODBC/lib/libmyodbc3-3.51.06.bundle. ^ Could it be that simple? I expect to be wrong ;) Hope this helps Ian -- Didn't know what to put for setup file so the path is the same. I then setup 3 key values: keyword Value user userloginname password userpasswordname database mydatabase Under UserDSN, I add the above driver naming the data source name also mysqlreporting. I also enter the same keyvalues. When I go to test the connection locally, I get the following: Last login: Fri Nov 5 09:26:40 on ttyp1 Welcome to Darwin! My-Computer:~ myuserlogin$ /usr/bin/odbctest iODBC Demonstration program This program shows an interactive SQL processor Enter ODBC connect string (? shows list): ? DSN| Description --- mysqlreporting | mysqlreporting Enter ODBC connect string (? shows list): dsn=mysqlreporting [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQLSTATE=IM002 Have a nice day. My-Computer:~ myuserlogin$ Can someone explain what I am doing wrong. Because their are two different drivers in the MyODBC Lib folder, I tried both but get the same message. Thanks. Rick -- 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: Upgrade - Downgrade problems
On 4 Nov 2004 at 0:59, Schalk Neethling wrote: Since installing the latest 4.1 version of MySQL I have not been able to run phpMyAdmin. I have since gathered that the current version of phpMyAdmin is not compatible. I therefore ran Add/Remove programs on Windows and installed 4.0.22 but nothing is working correctly, the server starts as a service using NET START MySQL but, phpMyAdmin continues to say: | #2003 - The server is not responding|. First, how can I detect, on localhost, which port MySQL is using? And second, how can I ensure that everything related to MySQL, including the app itself, is removed before making a clean install of MySQL 4.0.22. Hi, From a command prompt: netstat -an or gui (free) - this shows which prohrams have the ports open as well http://www.sysinternals.com/ntw2k/source/tcpview.shtml Regards Ian -- I would appreciate any help or pointers. I am on WindowsXP as the development machine. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] Global: www.volume4.com We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- 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: tree structure
On 18 Oct 2004 at 15:27, Melanie wrote: Hi, Does anybody has a simple idea to store a tree structure under mySQL 4.1.5? I have one table with id, familyType,superFamily,family, for example: Hi, This tutorial (PHP + MySQL) shows a few different methods: http://www.sitepoint.com/article/hierarchical-data-database I found this by typing: storing tree structure in mysql database into Google ;) Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving field characteristics
On 27 Sep 2004 at 11:36, Rhino wrote: snip I really have no idea how Java is getting the information. I haven't tried very many of the metadata methods yet so I don't know how much Java can actually see and what is hidden. But I do know that it can see the descriptions of the columns. I had assumed that Mark Matthews, the guy who develops the JDBC drivers, had persuaded the other MySQL developers that the JDBC drivers *had* to be able to see the metadata and got permission to do that well in advance of the developers making the metadata available via the command line in the normal way for SQL catalogs. But that was strictly a wild guess; you may be totally correct in your assumptions. Hi, Maybe the Java driver is simply running this query: USE database; SHOW FIELDS FROM `tablename`; and caching the result? Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Secure logon from VB.net
On 21 Sep 2004 at 10:14, Thomas Trutt wrote:
Hello all,
Ok i know this may be a simple question but i need a little help.
I am writing a program in VB.net that uses MySQL as a backend. My net admin
wants the log on to be encrypted??
This is currently how i am connecting:
Public LocalSYS As New OdbcConnection(DRIVER={MySQL ODBC 3.51
Driver}; _
SERVER=192.168.0.1; _
DATABASE=DB; _
UID=User; _
PASSWORD=Password; _
OPTION=3;)
As you can see its a public variable that i have declared so that i don't
have to continuously add it for every form in the program.. So the question
i have is how do i change this so that it is encrypted??? Any ideas and
suggestions would be greatly appreciated..
Many thanks,
Tom T
Hi Tom,
There is nothing you can do to encyrpt the password *within* you program, a
determined hacker will be able to decompile your program and get the username /
password if they have access to it.
If the potential hackers are on your network and can sniff traffic between your client
and the server, then you need to use some sort of SSL connection.
Seeing as you are using vb.net I am assuming your program runs on windows.
So far I have not seen a SSL aware windows MySQL client. You can compile one
yourself, but this doesn't seem to be an easy route.
The best option I can think of is to install stunnel ( http://www.stunnel.org ) on the
server and the client systems. ( It is available for both unix/linux and windows ).
This will SSL encyrpt all traffic on the designated ports between the client and
server, you can also set it to only accept connections with the right client
certificates.
I have succesfully used this setup in production systems.
You will have to modify your MySQL user settings so that the host is *localhost*.
Hope this helps
Regards
Ian
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.4 still reports itself as 4.1.3b-beta
On 2 Sep 2004 at 16:01, Terry Riley wrote: Victor C:\MYSQL\BIN\mysqld-opt --defaults-file=C:\WINDOWS\my.ini MySql is what the path-to-executable states. If I remember rightly, the new version should be mysqld, period. Is that what's wrong? Will I have to tweak the registry to change that? You can uninstall the service my running this from a prompt: C:\MYSQL\BIN\mysqld-opt -remove Then install the new binary: C:\MYSQL\BIN\mysqld -install For a full list of command line options run this: C:\MYSQL\BIN\mysqld --help --verbose help.txt then read help.txt Hope this helps If not try this (joking): http://foldoc.doc.ic.ac.uk/foldoc/foldoc.cgi?RTFM Ian --
Re: Crash on Windows XP SP2 when not run as an administrator
On 31 Aug 2004 at 17:33, Todd O'Bryan wrote: I've tested mysql in my high school lab running from an administrator and from a student account, both from within the Cygwin terminal, and at the Windows command prompt. From the administrator account, it runs fine for hours. From the student account, it promptly (within 15 seconds or so) crashes with the same error each time. I managed to write down the error: Hi, First of all: why are you running the server from a students account? The server should be running as a system service. Open up the services control panel and check whether MySQL is listed. If it is not you need to install it by running the command c:\mysql\bin\mysqld.exe -install from a command prompt whilst logged in as an adiminstrator. You can then set the service to run automatically at boot in the services control panel ( I think this the default anyway ). Is there something that non-administrators need access to that could be causing the problem? If you REALLY need to run the server under an unprivelaged account then make sure the student has full permissions on the c:\mysql folder ( or wherever its installed) and ALL subdirectories. You may need to do more - I have never run MySQL this way (check the manual : http://dev.mysql.com) . Could it be an SP2 issue? You should also set the permissions in the new SP2 firewall so that MySQL can open TCP port 3306. Or switch it off and install a real firewall ;) Hope this helps Ian --
RE: excel
On 20 Aug 2004 at 10:09, [EMAIL PROTECTED] wrote: Hmm... I use ASP.net, is there any instruction that I can use to use ASP.net to export into excel or is there another way? Well, I know nothing of ASP.net, but you should be able to select from mysql, then instantiate excel via COM to write the binary. Jeff How do I export from mysql into excel format? There is also a method of exporting to excel via HTML tables. The following: table tr tdRow1 cell1/td tdRow2 cell2/td /tr /table When saved with a .xls extension ( or sent to a browser via mime type *application/vnd.ms-excel* ) will be opened directly by Excel. You can also include styles / text formating / functions etc within the cells. I ues this as a method for customers to download data easily. You will still have to write your own code to do the export though. Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: proxy a connection to remote host?
On 8 Aug 2004 at 20:04, Steve Leibel wrote: What I'm wondering is if there is a cleaner and simpler way, for example to set up some sort of proxy server on host X that will establish a connection with the mysql database and then relay requests and responses back and forth to my code on host Y. Hi, You could set up a Stunnel Server on host x which listens on a user defined port e.g. 3307 then tunnels any connections to the real server. You then just need to set up a Stunnel client on host X to connect to Y. www.stunnel.org This also gives you the advantage of SSL. If you want to protect the tunnel you could set up firewall rules or even client certificates. Hope this helps Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OT] Email addresses shown in archive at lists.mysql.com
Dear list, After posting a question last week from an email address I only use for this list, I have already started to receive spam and virus reports. Looking at the archives at lists.mysql.com I notice that the from address is not shown, just the name. But... If someone else quotes your message in a reply ( which is considered best practice in all mailing list I have ever been a member of ) email addresses are displayed. Is there anyway that these addresses can be removed/ blanked from the archives? I understand that my address could have ( and is likely to have ) been harvested by a list member infected with a virus, but with the growing increase in spam shouldn't the archives be clean by default? Thoughts? I will be unsubscribing from the list and re-subsubscrbing with a new address shortly, so don't be surprised if direct replies are bounced... Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1.3-beta-nt-log Select Count(1) returns as unknown type in ASP/vbscript
Hi, Whilst testing some asp websites with Mysql Beta 4.1.3-nt-log I have noticed that doing a Select Count(1) as var FROM tableName returns as an unknown variable type in vbscript. Previously with the 3 / 4.0 version it returned as a long. I am using the latest MyODBC 3.51.08.00 Is there anything I can do to correct this behaviour? Is there a version of MyODBC designed for 4.1 ? I know I can just do a CInt(var) or CLng(var) but its a pain going through every page... The exact error returned is: Error: Variable uses an Automation type not supported in VBScript Code: 800A01CA Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1.3-beta-nt-log Select Count(1) returns as unknown type in
On 8 Jul 2004 at 7:28, Victor Pendleton wrote: Does the older MyODBC version properly work? I have run into some oddities with the latest MyODBC driver. Forgot to mention... Same result with 2.50.33.00 Ian -- -Original Message- From: Ian Gibbons To: [EMAIL PROTECTED] Sent: 7/8/04 7:08 AM Subject: 4.1.3-beta-nt-log Select Count(1) returns as unknown type in ASP/vbscript Hi, Whilst testing some asp websites with Mysql Beta 4.1.3-nt-log I have noticed that doing a Select Count(1) as var FROM tableName returns as an unknown variable type in vbscript. Previously with the 3 / 4.0 version it returned as a long. I am using the latest MyODBC 3.51.08.00 Is there anything I can do to correct this behaviour? Is there a version of MyODBC designed for 4.1 ? I know I can just do a CInt(var) or CLng(var) but its a pain going through every page... The exact error returned is: Error: Variable uses an Automation type not supported in VBScript Code: 800A01CA Regards Ian -- -- 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: MySQL Website
On 21 Apr 2004 at 9:14, Lou Olsten wrote: I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Hi, No luck with www.mysql.com but http://dev.mysql.com/ works fine. Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem searching table
On 15 Jan 2004 at 12:33, Matthew Stuart wrote: ASP and VB I have created a search web page searching a MySQL table with the following code: CREATE TABLE tbl_allarticles ( fld_ID mediumint(8) unsigned NOT NULL auto_increment, fld_category tinyint(2) unsigned NOT NULL default '0', fld_updateddate timestamp(8) NOT NULL, fld_createddate timestamp(8) NOT NULL, fld_mainstory char(1) NOT NULL default '', fld_reldate date NOT NULL default '-00-00', fld_headline varchar(255) default NULL, fld_summary text, fld_body mediumtext, fld_displayname tinyint(1) unsigned default NULL, fld_show tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (fld_ID), KEY fld_category (fld_category,fld_updateddate,fld_createddate,fld_mainstory,fld_reldate, fld_headline,fld_displayname,fld_show), FULLTEXT KEY fld_body (fld_body), FULLTEXT KEY fld_summary (fld_summary) ) TYPE=MyISAM; SELECT * FROM tbl_allarticles WHERE fld_headline OR fld_summary OR fld_body LIKE '%userinput%' AND fld_show = 1 with the variable of: userinput'Request(qstextfield) 'qstextfield' is the name of the text area in which the user inputs their search criteria. It is working fine as a search but it seems only to be looking in one field, that being fld_body. I have done exact searches to match content in fld_headline and I am getting zero results. I have also tried AND instead of OR in the sql, but that returned no results on any search... I suspect it is something to do with KEY, I created the table with MySQL-Front and expected it to create INDEX's, but it has created KEY, something I have never come across before. Any help in how I might make this work will be appreciated, thanks. Mat Hi Mat, You might want to try: SELECT * FROM tbl_allarticles WHERE ( fld_headline LIKE '%userinput%' OR fld_summary LIKE '%userinput%' OR fld_body LIKE '%userinput%' ) AND fld_show = 1 Also, are you validating the user input so you don't get any SQL injection ? I created this ASP function to make sure data is quoted properly userinput= MySQLescape( Request(qstextfield) ) function MySQLescape (strData) MySQLescape = Replace( strData, ', \') MySQLescape = Replace( MySQLescape , \) MySQLescape = Replace( MySQLescape Chr(0) , \0) end function You should really check the type of data as well, to make sure its a string. If you are going to have a lot of data to search you may want to try using a FULLTEXT index on the searchable data: http://www.mysql.com/doc/en/Fulltext_Search.html Regards Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
