RE: inquiry
Philip, For starters, you are not wasting my time. I know the difficulties, as it took me a while to get it installed and running. I would suggest you take a look over your book a little more carefully. I could be wrong, but it should have mentioned that mysql_install_db is a shell script for Linux, not usable on Windows. I don't have the book you are using so I can only refer you to the windows installation section of the MySQL manual to get the ball rolling. After that (after you have it all set up), you "should" be able to follow your book. http://www.mysql.com/doc/en/Windows_installation.html ...also see the subsections and the windows specific links. Also, from what you said, I gather you have not set up one of the my.ini or my.cfg files. This is most likely where you are having your troubles. For more info about them, start here. http://www.mysql.com/doc/en/Option_files.html If after all that you still have troubles, drop me an email (use addy in my sig below). State what you did and any error messages you received and I'll be glad to help get you on the right road again. -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: Philip McCarthy [mailto:[EMAIL PROTECTED] Sent: Sunday, March 09, 2003 5:06 AM To: [EMAIL PROTECTED] Subject: inquiry Loren Firstly thanks heaps for your reply. I will try and be as specific as possible to not waste your time. If indeed the connection is defaulting to the user ODBC and it is set in one of the my.* files, where can I see this? Fix this? I should go back a bit and mention that as I was working through my book, the first hurdle I encountered was setting up the initial grant tables. When I typed c:\mysql\scripts\mysql_install_db I got the error message: 'mysql_intall_db' is not recognized as an internal or external command, operable program or batch file. I uninstalled mysql, resintalled and still got the same message. I gather this should have worked? I did this by opening 'run' from the start bar, typing in cmd to open the command line, cd \ to change to the root directory, cd mysql to change to the mysql folder, cd scripts to change to the scripts folder and then mysql_install_db to run this. No luck. Yours Sincerely Philip McCarthy Australia -Original Message- From: Loren McDonald [mailto:[EMAIL PROTECTED] Sent: 09/03/2003 7:15 PM To: 'daniel'; 'Philip McCarthy'; [EMAIL PROTECTED] Subject: RE: inquiry First, for some reason the connection attempt is defaulting to the user ODBC (probably set in one of the my.* files). If this user does not exist in the database, it will naturally fail. Second, concerning the root user, there are actually 2 root user entries that need the password changed or one user removed. Specifically: From 4.2.11 Causes of Access denied Errors A very common error is to insert a new entry with Host='%' and User='some user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to remove the entry with Host='localhost' and User=''. See the full documentation: http://www.mysql.com/doc/en/Access_denied.html -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Sunday, March 09, 2003 1:15 AM To: Philip McCarthy; [EMAIL PROTECTED] Subject: RE: inquiry have u set the pass or is it correct ? usually mysqladmin -u root password newpassword then mysqladmin -u root -p create sample_db >= Original Message From "Philip McCarthy" <[EMAIL PROTECTED]> = >Dear mysql > > > >I have just downloaded mysql 3.23 for Windows and I am running XP. > > > >I have started the service ok and create a database. I thought I had >set my database password ok. I have been using the book 'Teach Yourself >MySQL in 21 days' by SAMS (Mark Maslakowski). > > > >When I try the statement > > > >bin\mysqladmin -p CREATE sample_db > > > >the command asks for a password. I type in the one I set and the system >says > > > >mysqladmin: connect to server at 'local host' failed > >error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' > > > >Does it appear that I need to install an ODBC driver? > > > >Your help is appreciated. > > > >Yours Sincerely > > > >Philip McCarthy &g
RE: inquiry
First, for some reason the connection attempt is defaulting to the user ODBC (probably set in one of the my.* files). If this user does not exist in the database, it will naturally fail. Second, concerning the root user, there are actually 2 root user entries that need the password changed or one user removed. Specifically: From 4.2.11 Causes of Access denied Errors A very common error is to insert a new entry with Host='%' and User='some user', thinking that this will allow you to specify localhost to connect from the same machine. The reason that this doesn't work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to remove the entry with Host='localhost' and User=''. See the full documentation: http://www.mysql.com/doc/en/Access_denied.html -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Sunday, March 09, 2003 1:15 AM To: Philip McCarthy; [EMAIL PROTECTED] Subject: RE: inquiry have u set the pass or is it correct ? usually mysqladmin -u root password newpassword then mysqladmin -u root -p create sample_db >= Original Message From "Philip McCarthy" <[EMAIL PROTECTED]> = >Dear mysql > > > >I have just downloaded mysql 3.23 for Windows and I am running XP. > > > >I have started the service ok and create a database. I thought I had >set my database password ok. I have been using the book 'Teach Yourself >MySQL in 21 days' by SAMS (Mark Maslakowski). > > > >When I try the statement > > > >bin\mysqladmin -p CREATE sample_db > > > >the command asks for a password. I type in the one I set and the system >says > > > >mysqladmin: connect to server at 'local host' failed > >error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' > > > >Does it appear that I need to install an ODBC driver? > > > >Your help is appreciated. > > > >Yours Sincerely > > > >Philip McCarthy > >NSW Australia > > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL newbie confusion
...and your problem is? -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: Fernando Martins [mailto:[EMAIL PROTECTED] Sent: Sunday, March 02, 2003 6:00 PM To: [EMAIL PROTECTED] Subject: mySQL newbie confusion Hi, my experience comes from VB6 with access databases. I'am trying to get one quick and practical overview about the mySQL capabilities (web online database). But so far i'am really loss and confuse regarding the mySQL setup and installation procedure. I'am using WIN98 with mySQL4.01.11 and follow step by step http://www.mysql.com/doc/en/index.html manual. So far i did install the mySQL and create the my.ini file in windows folder. [mysqld] basedir=C:\Progra~1\Mysql datadir=C:\Progra~1\Mysql\Data At this point i'am really confuse and thinking what dummy am i ... can someone help me please to turn this frustrating stage. Thanks Fernando - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Database Backup Question
That's exactly what I was looking for and the way I hoped it could be done, only I hadn't seen anything about this 'exec' function. Thanks. I should be able to figure it out myself but I will get back with you if I need further help. -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com -Original Message- From: KH Chiu [mailto:[EMAIL PROTECTED]] Sent: Monday, February 17, 2003 9:19 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Database Backup Question Sorry I am in a bit hurry and just post some hints on your question. It is more than one way to do your job. One of the trival way is to use php function 'exec'. This function can execute shell programs. It means that you can execute mysqldump etc. and create dump file. >From my experience, you should exp. with exec for a while to get thru. all the tricks like trustee and getting output back. You should also zip or bzip the file using similar technique. File download is trival. If you need further help, you may let you know. Regards, > This is actually more a PHP question but since it refers to a MySQL > database I thought I would ask here. > I may not be looking in the right places but, I can't seem to find a > way to do a database backup through PHP (other than BACKUP TABLE, > which I don't want to use if possible) > > What I mean is this: I want to create a page that will allow me to > click one button to initiate a backup process. That action will then > start a dump of a specified database to a text file that could then > be shown on a succeeding (success) page as a link for download (If there's > a way to zip prior to downloading, which would be even better). I > intend on using this as a manual backup process, so I want it to work > like the mysqldump.exe or mysqlhotcopy.exe. (All the tables are > MyISAM) > > The only way I see this working would be to write in PHP a long > script that will manually create that dump file itself by using > SELECT and so forth. This seems a bit redundant and extravagant a > way to go. > > Any help would be appreciated. > > -- > Loren McDonald > [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database Backup Question
This is actually more a PHP question but since it refers to a MySQL database I thought I would ask here. I may not be looking in the right places but, I can't seem to find a way to do a database backup through PHP (other than BACKUP TABLE, which I don't want to use if possible) What I mean is this: I want to create a page that will allow me to click one button to initiate a backup process. That action will then start a dump of a specified database to a text file that could then be shown on a succeeding (success) page as a link for download (If there's a way to zip prior to downloading, which would be even better). I intend on using this as a manual backup process, so I want it to work like the mysqldump.exe or mysqlhotcopy.exe. (All the tables are MyISAM) The only way I see this working would be to write in PHP a long script that will manually create that dump file itself by using SELECT and so forth. This seems a bit redundant and extravagant a way to go. Any help would be appreciated. -- Loren McDonald [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can MySQL work on Windows XP?
Does it (mysqld-shareware.exe) show up in the task managers listed processes? If not, it is not running (which is what I suspect). Through my experience with it, the DOS box does not close. Try opening a DOS box and starting it (type 'cmd' in the run box) and see what error comes up. If you are still stumped there, write again and include the error message and the c:\windows\my.ini and-or c:\my.cnf settings. -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: Marg Brazil [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 06, 2003 11:31 AM To: MySQL Subject: Can MySQL work on Windows XP? Hello, Can anyone tell me if MySQL will work on Windows XP Home Ed.? I have MySQL installed according to the instructions on the CreateASite web page. I tried contacted CreateASite with this description of the problem: "The start up looks good: when I use the Run function C:\mysql\bin\mysqld-shareware.exe, the black DOS box comes and goes, but I'm getting this error when using the MySQL test script (See below): Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'localhost' (10061) in c:\usr\htdocs\mysql_test.php on line 7 Couldn't Connect I have followed the instructions for installing IndigoPerl, CreateASite, PHP, and for MySQL on www.createafreewebsite.net/mysql.html. Apache was running well at the time, and the perl and php test scripts were A-OK. Should MySQL show in WindowsXP task manager? I can only see 2 Apache.exe' s and HTMel30.exe. There's no C:\mysql\bin\mysqld-shareware.exe listed. " CreateASite response was: "I worked with a guy from Germany for 2 days trying to get the mysql package you are using to work on XP. No luck. I should probably put that on the site. I'm not saying you can't get the present package to work. Just saying I didn't on XP. " Has anyone in this discussion group had success getting MySQL to work on Windows XP? Thanks! MB MySQL test script: MySQL Test "; mysql_close($link); ?> - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What are the best MySQL and PHP resource books?
Thanks again to everyone who replied! I finally decided on: MySQL - Second Edition Professional PHP Programming - Second Edition After all the responses here and the feedback through the online bookstores, those are what I'm after. -- Loren McDonald [EMAIL PROTECTED] > -Original Message- > From: Loren McDonald [mailto:[EMAIL PROTECTED]] > Sent: Saturday, February 01, 2003 5:16 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: What are the best MySQL and PHP resource books? > > HI > > Before I just go out and buy, I wanted to get some opinions / advice on > what you all think the best MySQL and PHP resource books are. I am not > web design illiterate (have HTML and Perl experience), but I havent the > first clue about PHP and using MySQL with it. In other words, I am NOT > looking for basic step by step books, but ones that have good, working > examples, are formatted so to make finding things fairly simple, and > also make note of Windows (IIS) specifics that I may need to know. > > Thanks in advance to all who reply! > > > -- > Loren McDonald > [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What are the best MySQL and PHP resource books?
HI Before I just go out and buy, I wanted to get some opinions / advice on what you all think the best MySQL and PHP resource books are. I am not web design illiterate (have HTML and Perl experience), but I havent the first clue about PHP and using MySQL with it. In other words, I am NOT looking for basic step by step books, but ones that have good, working examples, are formatted so to make finding things fairly simple, and also make note of Windows (IIS) specifics that I may need to know. Thanks in advance to all who reply! -- Loren McDonald [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What are the best MySQL and PHP resource books?
HI Before I just go out and buy, I wanted to get some opinions / advice on what you all think the best MySQL and PHP resource books are. I am not web design illiterate (have HTML and Perl experience), but I havent the first clue about PHP and using MySQL with it. In other words, I am NOT looking for basic step by step books, but ones that have good, working examples, are formatted so to make finding things fairly simple, and also make note of Windows (IIS) specifics that I may need to know. Thanks in advance to all who reply! -- Loren McDonald [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access XP crashes regularly when linked to MySQL via MyODBC
Have you done all the office (or at least Access) XP updates? If not, it is possible that one of them my resolve your crash problem. I have been testing Access XP with the same versions of MyODBC and MySQL for a few weeks now and have yet to see a crash. About the only problem I have ran into is MySQL shutting down, for no apparent reason, every so often. I have just installed .55, so I'm waiting to see if it is still a problem. -- Loren McDonald [EMAIL PROTECTED] > -Original Message- > From: M Wells [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 28, 2003 10:19 AM > To: [EMAIL PROTECTED] > Subject: Access XP crashes regularly when linked to MySQL via MyODBC > > Hi All, > > My MySQL Server is 3.23.53-max-nt. I'm linking to it via MyODBC > 3.51.05.00 and accessing its tables via Access XP linked tables. > > For some reason, Access XP crashes regularly in this setup. No > meaningful error messages, just crashes and reloads after a repair. Even > more odd (and I only have subjective observation to back this up), it > appears to only crash when left unattended for a period of time. > Although, maybe I have that impression because when left alone for a > period of time equates to running long enough to have encountered a > problem? > > Is this a known problem with MySQL 3.23.53-max-nt / MyODBC 3.51.05.00 / > Access XP? > > Regards and best wishes, > > Murray Wells > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is there anything like Query Analyser for MySQl
You're right. Perhaps it had something to do with the massive worm attack yesterday. It was erroring out with a file not found when I tried last night, but today it worked. -- Loren McDonald [EMAIL PROTECTED] -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > -Original Message- > From: Barry G. Sumpter [mailto:[EMAIL PROTECTED]] > Sent: Sunday, January 26, 2003 5:06 PM > To: [EMAIL PROTECTED]; 'Karam Chand'; 'Scott Lewis'; > [EMAIL PROTECTED] > Subject: RE: Is there anything like Query Analyser for MySQl > > I've just downloaded it and it works for me. > > > Thanks, > Barry G. Sumpter > > > > -Original Message- > From: Loren McDonald [mailto:[EMAIL PROTECTED]] > Sent: Monday, 27 January 2003 7:26 AM > To: 'Karam Chand'; 'Scott Lewis'; [EMAIL PROTECTED] > Subject: RE: Is there anything like Query Analyser for MySQl > > > That seems to be a dead download link. > > -- > Loren McDonald > Gods Of Music Reviewer/Editor > [EMAIL PROTECTED] > http://www.GodsOfMusic.com > > > > -Original Message- > > From: Karam Chand [mailto:[EMAIL PROTECTED]] > > Sent: Sunday, January 26, 2003 11:42 AM > > To: Scott Lewis; [EMAIL PROTECTED] > > Subject: Re: Is there anything like Query Analyser for MySQl > > > > Hey > > > > Try SQLyog at http://www.webyog.com/sqlyog > > > > Similar interface like MS Query Analyzer and extremely > > fast. > > > > Karam > > --- Scott Lewis <[EMAIL PROTECTED]> wrote: > > > I was wondering, is there a program simialar to MS > > > SQL server - Query > > > Analyser, where you can write sql queries to query > > > MySQl databases? > > > > > > > > > Scott > > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is there anything like Query Analyser for MySQl
That seems to be a dead download link. -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > -Original Message- > From: Karam Chand [mailto:[EMAIL PROTECTED]] > Sent: Sunday, January 26, 2003 11:42 AM > To: Scott Lewis; [EMAIL PROTECTED] > Subject: Re: Is there anything like Query Analyser for MySQl > > Hey > > Try SQLyog at http://www.webyog.com/sqlyog > > Similar interface like MS Query Analyzer and extremely > fast. > > Karam > --- Scott Lewis <[EMAIL PROTECTED]> wrote: > > I was wondering, is there a program simialar to MS > > SQL server - Query > > Analyser, where you can write sql queries to query > > MySQl databases? > > > > > > Scott > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: weird comparison problem
>From the manual: 6.3.2.2 Case-Sensitivity BINARY The BINARY operator casts the string following it to a binary string. This is an easy way to force a column comparison to be case-sensitive even if the column isn't defined as BINARY or BLOB: mysql> SELECT "a" = "A"; -> 1 mysql> SELECT BINARY "a" = "A"; -> 0 -- Loren McDonald [EMAIL PROTECTED] > -Original Message- > Am having a bit of a weird problem with a SELECT on my MYSQL > database: > > If i use this: > > SELECT * FROM tablehere WHERE userid = '$uid' AND password = '$pwd' > > with $uid and $pwd being vars from a login form, and "Miguel" being > userid > on the table, i can login if i type "miguel" on the form. No matter if i > type uppercase or lowercase, it always logs in, how can that be? "Miguel" > is != to "miguel". (but if i misspel it, it fails, as it should :-) > > How can i make a correct check with upper case distinction? > Thanks > > Pag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to speed things up in MySQL ?
Just to toss something into the fray: I had a similar problem, where any query was taking a very long time to run. I never did discover what the problem was but suddenly, one day later the problem was gone. I had rebooted my server twice in the mean time with no resolve. There is quite a bit running on the server but for the first week or two of testing I hadn't experienced any kind of slow down. Obviously, for me, something in the background WAS getting in the way, but I couldn't figure out what in the short amount of time is was a problem. -- Loren McDonald [EMAIL PROTECTED] > -Original Message- > Hi. > > On Sat 2003-01-25 at 13:36:01 +0100, [EMAIL PROTECTED] wrote: > [...] > > mysql> select count(id) as Films, ProductionYear from DVD > > -> group by ProductionYear > > -> order by ProductionYear desc; > [...] > > 39 rows in set (13.32 sec) > > > > mysql> > > *** > > > > How is this possible ?? 13.32 seconds ??? > > Good question. I do not know the answer, but I can tell you that there > is something seriously screwed up. It should only take a fraction of a > second. When I run a similar query on my database, it displays 0.00 > sec, i.e. it took less than 0.01 secs. > > The same query on a database with 600.000 records takes about 4.7 secs. > > > And this was when I was directly logged on to the Database (via > Localhost)! > > > > When Using my Paradox table this took 1.5 second through my CGI script > over > > the Intranet. > [...] > > What can I do to speed this up ? > > As I said, I am not sure. This is not a question of optimizing your > database or queries. Your installation of MySQL does not behave > normally. Usually, there is no way any operation on 320 records would > take more than at most 0.1 seconds or such. > > > PS: The DVD table containing the 320 records is the only table on the > > server. (except from the MySQL database and the empy Test Database). > Both > > the Paradox Table and the MySQL server is installed on the same > Harddrive so > > harddisk speed shouldn't be a factor. > > Harddrive speed shouldn't be a factor at all, as 320 records need so > few memory that they usually are kept in memory after first access. > > > Appreciate any help to solve the above :-))) > > Just to be sure to exclude anything I might not have thought of, can > you post the result of > > EXPLAIN for the select above > SHOW CREATE TABLE DVD > SHOW INDEX FROM DVD > SHOW TABLE STATUS LIKE 'DVD' > > Is there anything running on the server while you do your tests? > > Regards, > > Benjamin. > > -- > [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Need nulls in my join
Seeing a little more data on your situation does change things quite a bit. It sounds like you are also needing to use a SELECT DISTINCT of specific criteria, so that you avoid pulling the same thing more than once. What those criteria are, naturally depends on what the various possibilities are and how (exactly) you want to pull the data. -- Loren McDonald [EMAIL PROTECTED] > -Original Message- > From: Hoffman, Geoffrey [mailto:[EMAIL PROTECTED]] > Sent: Saturday, January 25, 2003 3:48 PM > To: [EMAIL PROTECTED] > Subject: RE: Need nulls in my join > > Unfortunately I tried to implement what you suggested, but in my case it > didn't work. > > I have stories that can have zero or more photos, one or more author, one > or > more sections, and one or more active dates. > > Trying to join all those items together using your suggestion actually did > return stories with no photo, but I would then get 3 copies of a story > that > had 3 photos, and 6 copies of a 2-photo story that appears on 3 days. > > What I really need is mysql 4's SELECT blah WHERE (SELECT blah) subselect > feature, but that's not an option either due to the host I'm using. I > ended > up writing a simpler query get all the unique story id's for a section, > then > doing a foreach loop in PHP to query for all the additional data for the > story where ID = $loopindex. > > I'm sure there's a supermysqlguru out there who cringes when you issue a > bunch of queries in a PHP loop, but it works fine for now. > > Thanks though - I'm sure I'll use your suggestion somewhere in the app I'm > working on. > > Geoff > > > -Original Message- > > From: Loren McDonald [mailto:[EMAIL PROTECTED]] > > Sent: Saturday, January 25, 2003 1:41 PM > > To: Hoffman, Geoffrey; [EMAIL PROTECTED] > > Subject: RE: Need nulls in my join > > > > > > Oopssorry. That should have been GROUP BY and not GROUP ON > > > > That's what I get for answering a post at 2am. :) > > > > -- > > Loren McDonald > > [EMAIL PROTECTED] > > > > > > > This one is relatively simple: > > > > > > Drop the WHERE evtphoto.phtusage = 1. > > > SELECT it and then GROUP ON it. > > > > > > -- > > > Loren McDonald - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Need nulls in my join
Oopssorry. That should have been GROUP BY and not GROUP ON That's what I get for answering a post at 2am. :) -- Loren McDonald [EMAIL PROTECTED] > This one is relatively simple: > > Drop the WHERE evtphoto.phtusage = 1. > SELECT it and then GROUP ON it. > > -- > Loren McDonald - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Need nulls in my join
This one is relatively simple: Drop the WHERE evtphoto.phtusage = 1. SELECT it and then GROUP ON it. -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > -Original Message- > From: Hoffman, Geoffrey [mailto:[EMAIL PROTECTED]] > Sent: Friday, January 24, 2003 7:28 PM > To: [EMAIL PROTECTED] > Subject: Need nulls in my join > > I have an SQL query that LEFT JOINs four different tables to a main table, > based on several foreign keys. > > It's returning only stories in a section that have photos, > but I need it to return all the stories in a section whether > it has a photo or not. > > I think the problem is in the > WHERE evtphoto.phtusage = 1 -- if there's no photo, > then there's no phtusage. > > Should I split this up into two queries, to get all > the story IDs for a section/day and then get the > photos for the stories in a 2nd query? > Or do I need a different join type? > Or do I put the WHERE clauses in a different order? > > Here's the query: > > > SELECT > evtstorysection.secid, > evtstory.styid, > evtstory.styheadline, > evtstory.stysummary, > evtstory.styintro, > evtstory.stypubdate, > evtphoto.phturl, > evtphoto.phtcaption, > evtstoryorder.stoorder, > evtlayoutsum.lytstring > > FROM > evtstory > > LEFT JOIN evtlayoutsumON evtstory.slytid = evtlayoutsum.lytid > LEFT JOIN evtstorysection ON evtstory.styid = evtstorysection.styid > LEFT JOIN evtstoryorder ON evtstory.styid = evtstoryorder.styid > LEFT JOIN evtphotoON evtstory.styid = evtphoto.styid > > WHERE > evtphoto.phtusage = 1 AND > evtstorysection.secid = 3 AND > evtstoryorder.secid = 3 AND > evtstoryorder.stodate = '2003-01-24' AND > evtstory.stypubdate <= '2003-01-24' AND > evtstory.styexpdate > '2003-01-24' > > ORDER BY > evtstoryorder.stoorder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql.users read only?
What I have discovered is that, when mysqld Windows service is running, I can't do anything to the user table. If I start it on the command line with the service stopped but set automatic, it works just fine. An interesting bug? that creates a nice security block. -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > -Original Message- > From: gerald_clark [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 14, 2003 9:20 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: mysql.users read only? > > Check that mysql has write privileges at the operating system level. > > Loren McDonald wrote: > > >Just tried. Nope... > > > > > >mysql> grant all privileges on mysql.* to ext identified by 'ext1tex2' > >with grant option; > > > >ERROR 1036: Table 'user' is read only > > > > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can't connect to MySQL server & Access denied for root
Sounds like the same problem I'm having (for which I'm still stumped and don't know what has caused it): table mysql.user is read only. Test this by logging into mysql and using the GRANT or REVOKE statements. -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > ... > shell>mysqladmin -u root password 'new-password' > shell>mysqladmin -u root -h hostname password 'new-password' > mysqladmin: connect to server at 'hostname' failed > error: 'Can't connect to MySQL server on 'hostname' (110)' > Check that mysqld is running on hostname and that the port is 3306. > You can check this by doing 'telnet hostname 3306' > shell>telnet hostname 3306 > Trying IP_address... > telnet: connect to address IP_address: Connection timed out > > > shell>mysqladmin -u root password 'new-password' > mysqladmin: connect to server at 'localhost' failed > error: 'Access denied for user: 'root@localhost' (Using password: NO)' > > I can only use mysql with mysql user: > shell>mysql -u mysql > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 9 to server version: 3.23.54 > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql.users read only?
Just tried. Nope... mysql> grant all privileges on mysql.* to ext identified by 'ext1tex2' with grant option; ERROR 1036: Table 'user' is read only -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com > > Does the user 'mysql' have write access to the mysql database and files? > > Loren McDonald wrote: > > >I'm not sure what has gone wrong but the mysql.users table is set as > >read only. I am trying to grant user privileges but can't because of > >this. How can I change the status so that I can add to it? > > > >I am logging in (localhost) using the (unchanged default) -u root > >account, so I know that's not the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql.users read only?
Im not sure what has gone wrong but the mysql.users table is set as read only. I am trying to grant user privileges but cant because of this. How can I change the status so that I can add to it? I am logging in (localhost) using the (unchanged default) u root account, so I know thats not the problem. -- Loren McDonald Gods Of Music Reviewer/Editor [EMAIL PROTECTED] http://www.GodsOfMusic.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php