Re: MySQL: Selecting a patricular row
On 3/29/03 7:55 AM, Marc Bakker [EMAIL PROTECTED] wrote: Hello, I am working on a PHP/MySQL/Apache website. I have a MySQL table with 5 rows. I want to select a patricular row using it's absolute row number. How can I do this? I found mysql_row_seek() n te MySQL docs but this requires me to get the whole table and then lets me within the result set select the desired row. That's too much overhead - I want to use SQL to select a particular row using the row number thanks, Marc Maybe I am missing something, but the fifth row is by no means an absolute thing, depending on what you order the records by. If you refer to the order in which the records where inserted into the table I would add an auto_increment field or if you already have another field that needs to be primary key add a timestamp field (but you might get identical values). With the auto increment field you could easily do: SELECT * FROM yourtable WHERE auto_increment_field = 5; I am not sure how reliable you can do a (see below) and get what you want. SELECT * FROM yourtable LIMIT 5, 1; http://www.mysql.com/doc/en/SELECT.html Sure, if you table will always contain 5 rows then it should be easy to just add a column 'rownumber' and give each row the number you want it to have. Cheers/h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Allowing a user to change their password
That is all ok but what do you suggest? Row level privileges are not available in MySQL (yet). Or did I miss something? Cheers/h On 3/24/03 12:18 PM, Shawn P. Garbett [EMAIL PROTECTED] wrote: On 3/24/03 10:37 AM, R. Hannes Niedner [EMAIL PROTECTED] wrote: On 3/24/03 7:41 AM, Shawn P. Garbett [EMAIL PROTECTED] wrote: How can one allow a user to change their mysql password securily? If I do a grant update on the user table, then a user could change anyone's password. I just want a user to be able to change their password. Is this possible? Shawn One way of doing it is to wrap this functionality in your middleware (perl, php, java...). Then you can grant the database user used by the middleware update privileges on the whole user table and authorize the user identity f. E. via web form and let the user only change it's own username after he successfully reproduced it's own userid/password. This defeats the purpose of using MySQL's user table to manage users and privileges. The middleware now has to keep somewhere a user/password combo, increasing the chance of a security leak. Now if the user hacks the middleware, then they have control of everyone's password. There should be some way to allow a user of mysql to change their own password, without opening up security problems. One of the principles of security is that of least privilege. Meaning restrict a user to the least privileges required to do their work at the lowest level. MySQL offers a nice set of privilege control. If user accounts are tracked in MySQL and a user hacks the middleware, then they still can't wreck much havoc. This is because their user/password combo is very limited in what it can do. Now on the converse if they had a widely privileged database user controlling the middleware, the sky is the limit. Shawn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqladmin processlist = weird in version 4.0.12
The change log for 4.012 mentions (Lenz Grimmer just posted this a couple of messages ago): Functionality added or changed: * `SHOW PROCESSLIST' will now include the client TCP port after the hostname to make it easier to know from which client the request originated. I guess whenever the hostname lookup fails you just see the IP address. HTH/h On 3/18/03 2:36 PM, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,query - 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: Newbie Question
On 3/14/03 9:31 AM, Paul DuBois [EMAIL PROTECTED] wrote: At 8:04 -0800 3/14/03, Thomas Knight wrote: How do I change Mysql default database location? Please explain what you mean by that? Maybe you need to look at the commandline options or the my.cnf files. You can specify the data directory (where the database files are located) both ways. The mysql manual is your friend thus check out things like : safe_mysqld --datadir=path http://www.mysql.com/doc/en/safe_mysqld.html mysqld --datadir=path http://www.mysql.com/doc/en/Command-line_options.html my.cnf http://www.mysql.com/doc/en/Option_files.html Cheers/h - 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: multiple simultaneous DBI connections?
On 3/13/03 6:47 AM, Tom Norwood [EMAIL PROTECTED] wrote: I am currently looking at building a Perl object to handle data from a a database. I'm not really sure if it is best to have one connection to MySQL and let the object just deal with its own properties. Or to allow the object to initiate a DBI connection each time an instance of the object is created, and to disconnect() using garbage disposal. Although the object initially would only have one instance per script, I can't help feeling it is a bad idea. Any thoughts welcome, and greatly appreciated, Tom Norwood. I believe you make that decision by either using or not using mod_perl. If I remember correctly it was also pointed out on this list that the connection overhead in mysql is rather small thus if you did not use mod_perl so far, the permanent connection to mysql should not be your only argument to switch :-) Cheers/h - 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 book TOC
On 2/7/03 2:20 AM, Vikram Vaswani [EMAIL PROTECTED] wrote: Hello all, My name is Vikram Vaswani, and I'm currently working on the outline for a MySQL reference book. This is supposed to be a comprehensive reference to MySQL 4, covering all aspects of the software, including the new transaction/subquery features. I have put together a draft table of contents (TOC), but am a little hesitant about some aspects of it. I have a nagging feeling that I'm missing out on a lot of things here, and also that it might bs structured better. Specifically, I was hoping that this list would help me in evaluating the structure and content of Section 2 below. Since I'm not that experienced with MySQL, I thought I'd open this up to the list and see if you guys could help me figure out what's bugging me :) I'd appreciate as much feedback as possible, since my aim here is to produce something that would hopefully be useful to you in your usage of MySQL (and also stand out from the crowd of other MySQL books in the market) Thanks! I look forward to hearing back. Vikram Vaswani Maybe you come a little bit late: First there is an excellent manual out (referenced in every list message footer - and now even in print) and on top of that we already have the great books from Paul DeBois. http://www.kitebird.com/ http://www.mysql.com/doc/en/index.html I would strongly recommend that you check out both sources before you start writing. It might well be that topics such as subselects and other advanced features are not as well covered since they will be introduced in V 4.1 but still there is plenty of general SQL books out here already. But in any case I am as a single person don't define the market anyway thus lets hear what others have to say. You certainly came to the right place to find out. Best/h - 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: Fulltext Index
Thank you Paul and Diana, I the 6 hours that passed before my message appeared on the list I rechecked the excellent MySQL documentation and found the answer as indicated by both of you. I just missed to look for the answer under restrictions... my fault. Best/h On 1/31/03 11:12 AM, Diana Soares [EMAIL PROTECTED] wrote: Hi, The MATCH() column list must exactly match the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. from: http://www.mysql.com/doc/en/Fulltext_Restrictions.html On Thu, 2003-01-30 at 21:40, R. Hannes Niedner wrote: If I create a FULLTEXT index for 2 or more columns in a table will I be able to use it for a MATCH only against a single column (of the above) or do I have to create additional FULLTEXT indices for each of these columns? Thanks/h On 1/31/03 10:44 AM, Paul DuBois [EMAIL PROTECTED] wrote: At 13:40 -0800 1/30/03, R. Hannes Niedner wrote: If I create a FULLTEXT index for 2 or more columns in a table will I be able to use it for a MATCH only against a single column (of the above) or do I have to create additional FULLTEXT indices for each of these columns? The latter. When you use MATCH, there must be a FULLTEXT index on exactly the columns named in the MATCH. Thanks/h sql,query,queries,smallint - 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
Fulltext Index
If I create a FULLTEXT index for 2 or more columns in a table will I be able to use it for a MATCH only against a single column (of the above) or do I have to create additional FULLTEXT indices for each of these columns? Thanks/h sql,query,queries,smallint - 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: cannot access mysql and see privileges
On 1/28/03 3:42 PM, Jon Miller [EMAIL PROTECTED] wrote: Warning: Access denied for user: 'root@localhost' (Using password: NO) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) in /var/www/html/phpMyAdmin/lib.inc.php3 on line 255 Error MySQL said: Access denied for user: 'root@localhost' (Using password: NO) Also I get an error message when trying to show grants privileges using: show grants; Thanks -- Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd Even though you didn't ask a questionit may be answered right here: http://www.mysql.com/doc/en/Resetting_permissions.html /h - 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: Recovery in MySql
On 1/29/03 5:13 AM, Inbal Ovadia [EMAIL PROTECTED] wrote: Hi All, I have MySql on Windows. Today i had an electrical power interruption in the middle of working. The database remain not consistent and i could not continue working with it. Is there any Recovery after crash mechanism in mySql? Thanks, Inbal If you tables are of type MYISAM (find out with SHOW CREATE TABLE table) then most of the answers are here (myisamchk): http://www.mysql.com/doc/en/Table_maintenance.html Hth/h - 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: sql query using select and row functions
On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - 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: sql query using select and row functions
On 1/28/03 12:26 PM, Christopher Lyon [EMAIL PROTECTED] wrote: I do have a timestamp field would I be better off using that? Granted it might not be the same amount of rows every time but how would that work? Select * from tbl_name where TIMESTAMP ( ) -3 hours; Well, that is not what you asked for you just wanted to get the last N records inserted into the database which is different from what you asked now. Your original question is best answered with having an autoincrement field in your table and do a: SELECT field FROM table WHERE whatever = don'tknow ORDER BY autoincrement_field DESC LIMIT N; Using the timestamp field to retrive all records inserted within the last n years/months/weeks/days/hours/minutes/second requires some string conversion of the timestamp. Your best bet in that case is to look into the Date and Time Functions: Check comments also! http://www.mysql.com/doc/en/DATETIME.html http://www.mysql.com/doc/en/Date_and_time_functions.html After reading through the excellent documentation it should be rather straight forward how to do it. Hth/h SQL, QUERY, TABLE P.s. I also strongly recommend to keep the discussion on the list for your benefit (there are much better experts on the mysql list than me) but also for the benefit of others with a similar problem (although there is a rather big lag from posting to appearing on the list). The list archives also getting way to little attention. -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 11:50 AM To: Christopher Lyon; MySQL Mailinglist Subject: Re: sql query using select and row functions On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - 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
Why does mysqld_safe look for mysqld in libexec
Iwhat am I doing wrong: After downloading the binary for mysql 4.09-max for Mac OS 10.2 safe_mysql complains: % sudo bin/mysqld_safe The file /usr/local/mysql/libexec/mysqld doesn't exist or is not executable Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe. It's clear that the binary needs no configure so it should be clear to the contained script that it will find mysqld in mysql/bin and not in mysql/libexec! What am I missing. Thanks/h - 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, TCP/IP, Mac OS X --- HELP PLEASE !
On 1/20/03 6:18 AM, Ram Kumar [EMAIL PROTECTED] wrote: greetings can anyone help to get mySQL on Mac OS X (entropy pkg), work on TCP/IP rather than unix socket, please ? Is Marc Liyanage on this list ? regards, ram I don't know if Mark is on the list, but I might have some suggestions nevertheless. Mysql listens by default on port 3306 (if you specified a different port you'd probably know it). Do you have this port open on you computer (that runs mysqld) and on your router (in case you have one)? As beautifully explained in the manual in most cases permissions are granted for 'localhost ' (which will use the socket) and for any other machine % or specific IP, host name , domain etc. Did you grant the user the correct privileges to connect from the host the client is running on? http://www.mysql.com/doc/en/GRANT.html Hth/h - 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: Mac OS X 10.1 build
If you download the binary, there is nothing to compile. Just follow the instructions in the file INSTALL_BINARY and you are ready to go. I did the same on 10.2.2 server and it worked great. Hth/h On 1/20/03 2:21 PM, James LaFountain [EMAIL PROTECTED] wrote: I've been trying to compile MySQL 4.0.8 on Mac OS X Server 10.1.4 with the supplied Development Tools CD. Thing so far appear to work fine. But when I connect remotely (from another machine on socket 3306), the server restarts (crashes). I've read the news threads and I see this was also a problem in Linux. However I don't know how to fix this on Mac OS X Server 10.1.4. We are unable to upgrade to the Jaguar release, and your site does not post binaries for OS X 10.1.4 Server. Perhaps if there were some notes posted on how to properly build the binaries, it may help. Thank You James - 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: converting text to hypertext
On 1/8/03 8:34 AM, Rick Tucker [EMAIL PROTECTED] wrote:
I just imported a .csv file and one of the columns of data was websites
addresses. Those addresses aren't being recognized as links when I output
an html table from my queries. I'm scratching me head on how to make the
conversion. I figured there would by a hypertext datatype of some sort, but
I can't find any information regarding this issue. If someone could point me
in the right direction, I would appreciate it.
Thanks,
rick
Sorry I was to fast: (mailto: was missing, but you probably figured that by
yourself).
SELECT CONCAT('a href=\mailto:' ,email, '\'', email, '/a') FROM
mytable;
And 'email' is the name of the column that stores your email address.
http://www.mysql.com/doc/en/String_functions.html
Best/h
-
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: converting text to hypertext
On 1/8/03 8:34 AM, Rick Tucker [EMAIL PROTECTED] wrote:
I just imported a .csv file and one of the columns of data was websites
addresses. Those addresses aren't being recognized as links when I output
an html table from my queries. I'm scratching me head on how to make the
conversion. I figured there would by a hypertext datatype of some sort, but
I can't find any information regarding this issue. If someone could point me
in the right direction, I would appreciate it.
Thanks,
rick
How about :
a href=mailto:[EMAIL PROTECTED];[EMAIL PROTECTED]/a
Thus if you just store:
[EMAIL PROTECTED]
in your database then you need to retrieve it with the CONCAT function
SELECT CONCAT('a href=\' ,email, '\'', email, '/a') FROM mytable;
And 'email' is the name of the column that stores your email address.
http://www.mysql.com/doc/en/String_functions.html
Best/h
-
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: host.frm
On 1/1/03 8:43 PM, Jason Steig [EMAIL PROTECTED] wrote: ./myqld: Can't find file : './mysql/host.frm' (errno:13) does anyone know how to solve this problem? This file is part of the host table in your mysql database 'mysql'. Did you by any chance copy the mysql data directory or parts of it or do any actions that would change permissions or even delete file in this directory (possibly /usr/local/mysql-version/data) ? In the worst case you need to back up your data directory and have to run the install_db script again after you have set the root password to '' (nothing). HTH/h - 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: user/root
On 1/1/03 5:46 PM, Richard Nagle [EMAIL PROTECTED] wrote: Well it would appear that I finally have a connection Congrats. q. since I am root, do I still need to GRANT a database to myself? or just a to a new user. aka: bob@localhost If you are the only person using the computer that the mysql server runs on then theoretically you don't need to create any other user you don't even need a password for root then. But it is generally not a good thing to do things as root user if you don't have to (and you don't for most of the time) in Unix and in Mysql. Thus I strongly recommend that you do your database tutorials as a non root user for example as bob@localhost as you suggested. Lets say the database in your tutorial is called 'tutorialdb' you would do the following: shell mysql -u root -p password: mysql CREATE DATABASE tutorialdb; mysql GRANT ALL ON tutorialdb.* to bob@localhost; mysq quit; This would give you (well bob@localhost) all priviliges (except GRANT) on the tutorialdb and all tables in this database when you are using the socket connection i.e. You are logged in and call mysql on the machine that runs the mysql server (mysqld). shell mysql -u bob mysql use tutorialdb; Or shell mysql -u bob -D tutorialdb As you see bob can login without a password, which maybe what you want. If not you can either set a password now or change the GRANT command that you used in the first place to (replace pass with the password you like - the quotes are part of the command syntax: mysql GRANT ALL ON tutorialdb.* to bob@localhost IDENTIFIED BY 'pass'; HTH/h - 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: host.frm
Dave, Please trace the author (Jason) of the problem correctly, but I am sure you just tried to be helpful - no harm done. :-)/h On 1/2/03 11:37 AM, Christensen, Dave [EMAIL PROTECTED] wrote: Again, this specific problem is mentioned in the reference guide. From that and my experiences, we're looking at a permissions issue. Hannes needs to look at the permissions on his data and logging directories to make sure that they are set to the default mysql:mysql. -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 10:45 AM To: Jason Steig; MySQL Mailinglist Subject: Re: host.frm On 1/1/03 8:43 PM, Jason Steig [EMAIL PROTECTED] wrote: ./myqld: Can't find file : './mysql/host.frm' (errno:13) does anyone know how to solve this problem? This file is part of the host table in your mysql database 'mysql'. Did you by any chance copy the mysql data directory or parts of it or do any actions that would change permissions or even delete file in this directory (possibly /usr/local/mysql-version/data) ? In the worst case you need to back up your data directory and have to run the install_db script again after you have set the root password to '' (nothing). HTH/h - 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: user/root
On 1/1/03 2:31 PM, Richard Nagle [EMAIL PROTECTED] wrote:
Well after 3 times installing mysql, finally
got it working (tks to those whom help)
now I need to take care of user and root.
first root.
mysql mysql -u root mysql
just want to confirm this is correct before I push enter.
mysql update user set password=password('tko')
where user='root';
Then next this,
mysql flush privileges;
So, this should add a password for root, yes?
and flush all other privileges?
Thanks-
Rick
You have a recent installation of mysql then you can just run
SET PASSWORD FOR root@localhost=PASSWORD('tko');
and skip the flush privileges. All explained here:
http://www.mysql.com/doc/en/Default_privileges.html
Best/h
-
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: An Idea
On 12/31/02 8:11 PM, JamesD [EMAIL PROTECTED] wrote: lists work, and faq's work, some like to call...etc. personally, I'd prefer a search engine style... like google, but only for mySQL topics, and with a visible list of most popular search terms. something that can be based upon pages of htm and emails that exists, and that can sit under a few web pages using Htdig or alkaline or something... Guten Rutsch Jim Isn't that funny: if I have a mysql related question and search google I end up in the mysql online documentation in 90% of cases. JM2Cs /h - 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
Joins are slow?
Scott Ambler recommends in his publication Mapping Objects to Relational Databases not to do joins but to traverse tables. He claims that 'several small accesses are usually more efficient than one big join'. Is that true for mysql? I am particularly interested in a scenario where I would retrieve only one row from each table involved in the join. Thanks/h - 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
To join or not to join
I am looking for an expert opinion on the speed difference between fetching related data from 2 tables with a join and fetching them in to single selects. The scenario is kind of the following: SELECT a , b, c FROM table1 WHERE a='x'; # gets b='y' SELECT b, d , e, f FROM table2 WHERE b='y'; instead SELECT a , b, c, d , e, f FROM table1, table2 WHERE a='x' AND table1.b = table2.b; Background: I wrote a little Perl module that automatically instantiates a object for each table in the database connected to and each table object allows you to access any record in that table or create a new one. So the above SQL looks like: my $DB = DB-new($config); my $record_a_b_c = $DB-table1-new(primary_key_value); my $field_b_value = $record_a_b_c-fieldname; my $record_b_d_e_f = $DB-table2-new($field_b_value); In this scenario it very easy to retrieve related from several tables without doing a join, but I am not sure how hard the performance hit actually is, since MySQL would have to look up the first select before it can do the join on the second table. Thanks for your input. /h mysql, query, table - 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 handle 120 million records? - Impressive! How doyou guys do that?
On 12/18/02 9:48 AM, Qunfeng Dong [EMAIL PROTECTED] wrote: But I am indeed seeing not-so-good performance (join on tables much smaller than yours takes minutes even using index) and I seem to read all the docs I could find on the web about how to optimize but they are not working for me Why don't you just post the table structures and the join query that you have trouble with? There are enough expert here on this list who are happy to help you further optimize your database if possible. /h MySQL, TABLE - 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
FW: Can MySQL handle 120 million records? - Impressive! How doyou guys do that?
-- Forwarded Message From: Joseph D [EMAIL PROTECTED] Date: Wed, 18 Dec 2002 13:55:47 -0800 (PST) To: R. Hannes Niedner [EMAIL PROTECTED] Subject: Re: Can MySQL handle 120 million records? - Impressive! How do you guys do that? thanks i actually can't post anything to the newsgroup because i'm using Yahoo mail and there seems to be attachment error. here is my table structure and where clause. explain SELECT distinct Medias.pk_media_id, Organisms.common_name, Tissues.type As tiss_type, Cells.type As cell_type, Organelles.type As org_type, Macromolecules.type As macro_type, Authors.last_name, Authors.organization, Medias.file_name, Medias.format, Medias.label FROM Medias, Organisms, MediaAuthors, MediaTissues, MediaCells, MediaOrganelles, MediaMacromolecules, Authors, Techniques, Tissues, Cells, Organelles, Macromolecules, Admin WHERE Macromolecules.pk_macromolecule_id = 1 and Medias.fk_organism_id = Organisms.pk_organism_id and Medias.fk_technique_id = Techniques.pk_technique_id and Medias.pk_media_id = MediaAuthors.fk_media_id and MediaAuthors.fk_author_id = Authors.pk_author_id and Medias.pk_media_id = MediaTissues.fk_media_id and MediaTissues.fk_tissue_id = Tissues.pk_tissue_id and Medias.pk_media_id = MediaCells.fk_media_id and MediaCells.fk_cell_id = Cells.pk_cell_id and Medias.pk_media_id = MediaOrganelles.fk_media_id and MediaOrganelles.fk_organelle_id = Organelles.pk_organelle_id and Medias.pk_media_id = MediaMacromolecules.fk_media_id and MediaMacromolecules.fk_macromolecule_id = Macromolecules.pk_macromolecule_id and Medias.fk_admin_id = Admin.pk_admin_id //-- //Entity-relationship diagram //-- // //---------- //OrganismsMediasMediaAuthorsAuthors //---------- //pk_organism_id--pk_media_id|fk_media_id --pk_author_id // |fk_organism_id |fk_author_id--| //fk_technique_id-| | //fk_admin_id-|| | //|| | //|| | //| | | //|| | //|| |----- //|| |MediaTissuesTissues //|| |----- //|| |fk_media_id |--pk_tissue_id //|| |fk_tissue_id-- // || | //|| |----- //|| |MediaCellsCells //|| |----- //|| |fk_media_id |--pk_cell_id //|| |fk_cell_id //|| | //|| |----- //|| |MediaOrganellesOrganelles //|| |----- //|| |fk_media_id |--pk_organelle_id //|| |fk_organelle_id--- //|| | //|| |----- //|| |MediaMacromoleculesMacromolecules //|| |----- //|| |fk_media_id |--pk_macromolecule_id //|| fk_macromolecule_id--- //|| //|| ----- //|| Techniques //|| ----- //||---pk_technique_id //| //| ----- --- //| StudentsStudentComments //| ----- --- //| pk_student_id-| pk_comment_id //| |-fk_student_id (not unique) //| //| ----- //| AdminAdminComments //| ----- //|---pk_admin_id--|pk_comment_id //|--fk_admin_id //| //|-- //|AdminMessages //|-- //|pk_message_id
Re: mysql port number
On 12/4/02 9:04 AM, Mike At Spy [EMAIL PROTECTED] wrote: How can I tell what port number mysql is running on? I need it for a chat program. :) Thanks, -Mike Default is 3306 /h - 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: alter table syntax
ALTER TABLE town_db1 DROP PRIMARY KEY; ALTER TABLE town_db1 ADD PRIMARY KEY(town, subcity ); On 12/5/02 10:31 PM, kayamboo [EMAIL PROTECTED] wrote: sql, mysql, query Hello folks This is my table structure with MySql 4.0.3-beta-max-nt with InnoDB +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | town| varchar(50) | | PRI | | | | subcity | varchar(50) | | MUL | | | | rankt | int(10) unsigned | | | 0 | | +-+--+--+-+-+---+ 3 rows in set (0.01 sec) Now I want to have the primary key on both town and subcity fields like this mysql describe town_db1 ; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | town| varchar(50) | | PRI | | | | subcity | varchar(50) | | PRI | | | | rankt | int(10) unsigned | | | 0 | | +-+--+--+-+-+---+ 3 rows in set (0.00 sec) I am able to create new table but can't alter the existing one to achieve this. Any suggestions with alter syntax is highly appreciated Thanks for your time Regards - 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: Installing MySQL on Jaguar?
On 12/3/02 11:37 PM, Brian Radford [EMAIL PROTECTED] wrote: Hello all, Could someone be so kind and share with this newbie where he might find crystal clear step-by-step instructions on how to install MySQL v3.23 on Mac OS X 2.2. I've looked through the manual and all I could find was one sentence talking about what doesn't have to be done but no directions on what does have to be done to install it. Am I overlooking something? Thank you for your time! Brian Yes google :-)... http://www.entropy.ch/software/macosx/mysql/ or download the binary for mac os x from the mysql.com web site unzip it and have a look at the file called INSTALL_BINARY Best/h - 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: CREATE TABLE and CHECK clausole
Maybe it's just to late for me to think clearly but there are NO INTEGERS between 0 and 1, try FLOAT or DECIMAL. Best/h On 12/3/02 1:08 AM, Achille M. Luongo [EMAIL PROTECTED] wrote: Dear subscribers, MySQL seems to support the CHECK() clausole in the implementation of CREATE TABLE statement, but MySQL (Ver 11.15 Distrib 3.23.39, for pc-linux-gnu) returns a syntax error on such a command. For example: mysql CREATE TABLE example (var INTEGER, CHECK(var BETWEEN 0 AND 1)); ERROR 1064: You have an error in your SQL syntax near '(var BETWEEN 0 AND 1))' at line 1 If I remove the CHECK clausole, the sql statement works: mysql CREATE TABLE example (var INTEGER); Query OK, 0 rows affected (0.01 sec) Can anyone suggest me how to correctly use the CHECK clausole in MySQL (even if for compatibility issues with other sql databases) ? Thanks in advance for your suggestions. Achille - 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: CREATE TABLE and CHECK clausole
On 12/3/02 4:32 AM, Achille M. Luongo [EMAIL PROTECTED] wrote: Dear Sir, I have tried with FLOAT or DECIMAL data types, but MySQL always returns the same error. After your suggestion, I double checked the MySQL data types definition list and the INTEGER (alias INT) data type is supported. Thanks in advance for any other suggestion. Achille. R. Hannes Niedner wrote: Maybe it's just to late for me to think clearly but there are NO INTEGERS between 0 and 1, try FLOAT or DECIMAL. Best/h You are certainly correct, I tried all kinds of expressions and datatypes and couldn't get it to work, but my point was rather that there are no INTEGERs between 0 and 1. In any case the manual entry is very slim for the CHECK clause in the CREATE TABLE statement, and somebody already asked for and example. Where are the experts on this list? /h - 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 db create error
On 11/27/02 1:24 PM, Tim V [EMAIL PROTECTED] wrote: I am trying to create a new db in phpmyadmin with MySQL 3.23.53a running on localhost. SQL-query : CREATE DATABASE `` gulf `` MySQL said: You have an error in your SQL syntax near 'gulf``' at line 1 Why am i getting this? It looks like you used 2 sets of single quotes... Try: CREATE DATABASE gulf; /h - 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 subquery that works
On 11/24/02 4:30 PM, Bruno Batarelo [EMAIL PROTECTED] wrote: The question is what does Access do in order to perform subqueries on MySQL via MyODBC driver since it is obvious that they work? What can I do, but I do not want to use access database as a layer between my application and mysql server? I use Visual Basic for accessing mysql server. Thank You very much Bruno Not that I knew the real answer but I suspect that Access just retrieves the results in a temporary table and then finishes the job by including the results table with a join to the rest of the tables as required. Or maybe it wraps the subquery in VB/VB script and loops through the subselect and does the select in every loop. /h - 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: Best MYSQL for MaxOSX Server 10.2.2
On 11/22/02 4:32 AM, Stu Duncan [EMAIL PROTECTED] wrote: Try going to http://www.entropy.ch/software/macosx/mysql/ It's the best Mac MySQL reference around (but seems to be down this Friday AM). I've used version 3.23.52-entropy.ch on a low bandwidth website with no problems at all. HTH, Thanks for the hint. I knew Marc's site but unfortunately entropy.ch seems to be down. In addition I believe that (when I went there last time) he did not put any binaries for 10.2.2 on his site since mysql.com provides precompiled versions for Jagur. I was hoping for advice from users who would already run mysql 4.x on MacOSX 10.2.2. Best/h - 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
Best MYSQL for MaxOSX Server 10.2.2
I am planning on setting up mysql server on a Mac OS 10.2.2 Server running on a Xserve machine. It will be a development system for now, but I still want end up with a good usability status. The server will run Perl, PHP, Apache/Tomcat, Mysql to start with. Are there any recommendations or experience on the list which version to pick. I really like to get started with version 4. Thanks in advance for your advice /h - 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 link tables in MySQL
On 11/12/02 12:41 PM, tmb [EMAIL PROTECTED] wrote: 2 - In MS Access you have to graphically connect the table id fields to tell Access how the tables relate. How do you do this In MySQL... from the command line I'm sure... just a code snippit or reference to one would be nice... You join the tables using : WHERE fieldA.table1 = fieldB.table2 So check out the join syntax in the manual. /h - 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: Impossible SQL ???
On 11/12/02 12:43 PM, Charlie [EMAIL PROTECTED] wrote: I can't seem to come up with a solution to the following SQL query, and have a project dependent on a solution. I'd rather not load the whole database and then parse it in the code. With the following Database structure: Field0 Integer Autonumber Field1 Integer Unique Field2 Integer not unique where the starting record would be selected by Field1 (an indexed field of unique values). The ending record required would be the first succeeding record where the value of Field2 is equal to the value of Field2 in the 'starting' record. Field2 is NOT unique and not ordered. The result would be ordered by Field0 (the autonumber field) Is this possible? I am not sure if I understood your mail correctly but you can try this: If you've not already done it, create an UNIQUE index for field1 and an INDEX for field2 and then try the query with a self-join on your table. SELECT foo1.* FROM yourtable foo1, yourtable foo2 WHERE foo1.field0 = foo2.field0 AND foo1.field2 = foo2.field2 AND foo1.field1 = your_starting_value ORDER BY foo1.field0 ASC /h - 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: Count Rows in two tables
On 11/12/02 5:36 PM, Alan McDonald [EMAIL PROTECTED] wrote: You can't count the join? Alan Hello all, Does anybody have a SQL string to count the rows in two different tables and give you a total number of rows? I have been trying to find an answer for a couple of days and seems like a simple string. Thank you Rick I guess he wants something like: SELECT SUM(COUNT(t1.*) , COUNT(t2.*)) FROM table1 t1, table2; Which obviously doesn't work. Maybe once we have subselects we can do it in one query: /h - 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: Problems
On 11/12/02 7:05 PM, Daniel Griggs [EMAIL PROTECTED] wrote: Hi there, I am having trouble with connecting to a Mysql host, the host version is 3.23.51 on debian stable. After countless re- reads of the documentation and checking the permissions all I get when I try to connect regardless of username and password combinations is ERROR 2013: Lost connection to MySQL server during query. Is there any common thing that causes this?? It is definitely not a permissions problem but I don't know what else it could be. Daniel You are not the first to experience this problem: http://www.geocrawler.com/archives/3/8/1999/11/50/2931397/ http://www.bitmechanic.com/mail-archives/mysql/current/0535.html http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:124399:200211:cgggaeehnkfbhij djcec Maybe you find an answer here, I guess it depends if you compiled your binaries yourself. If yes download a precompiled binary from the mysql site and try again. /h - 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: support table synonyms?
On 11/12/02 8:59 PM, Phillip Rhodes [EMAIL PROTECTED] wrote: I can not find anywhere that says that mysql supports table name synonyms. For example, I have two databases (test1 and test2) in the same mysql instance. In test1 schema, there is a table called table1 I would like to create a synonym whereby I could refer to test1.table1 as test2.table1 where test2.table1 does not exist. This sounds like a CREATE VIEW statement ... And is not the same as a synonym, If I am not wrong views will be introduced not before version 5 :-( I could do this in oracle as .. create public synonym table1 as test1.table1; But you can have synonyms, they are just not persistent and exit only within the query: SELECT t1.fieldX, t2.fieldY FROM table1 t1, table2 t2 WHERE t1.fieldY = t2.fieldX t1, t2 are synonyms for table1 and table2 respectively. HTH/h - 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
Error on Mysql Doc website
I try to access thesearchable documentation with user comments: http://www.mysql.com/doc/en/index.html And get: Forbidden You don't have permission to access /doc/en/index.html on this server. Apache/1.3.26 Server at www.mysql.com Port 80 Is it only me, or did somebody screw up the permissions on the server? /h - 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 denied
On 11/11/02 9:10 AM, Inbal Ovadia [EMAIL PROTECTED] wrote: Hi all I am working with mysql, in windows, visual c++. I am trying to run this query from c++ program: select * into outfile 'table_name.txt' FIELDS TERMINATED BY '\t' ESCAPED BY '\0' LINES TERMINATED BY '\r' from table_name And i get exception with this error: access is denied for user (If i run this query from the data base client, i am success) How can i solve this problem? Thanks, Inbal The error message indicates that you don't have trouble with the query syntax but rather permission problems when executing it from the program. How do you connect to mysqld from the client and how do you connect from within the program: the actual question: Is it the same database user? If yes, do you try to connect from the same computer or do you use a local client but try to connect from a remote computer with your program? /h - 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: S.O.S. with mysql
On 11/11/02 9:26 AM, Victoria Meza [EMAIL PROTECTED] wrote: I've a problem whit some tables in mysql, you know how repair damages tables? this is the message error: Didn't find any fields in table 'criterios_evaluacion' Didn't find any fields in table 'mensaje' Didn't find any fields in table 'registro_sistema' Didn't find any fields in table 'temporal' Sounds like you either have corrupted tables then use myisamchk http://www.mysql.com/doc/en/myisamchk_syntax.html or you've copied the data files of these tables but forgot some files: Every table (myisam) consists of 3 files: x.MYI (your data), x.FRM (data dictionary), x.MYD (index) in a subdirectory (with the same name as your database) in the mysql data directory. or they don't have correct permissions to be accessed by mysqld. My advise check out your data directory. /h - 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: Problem with MySQL-GUI
On 11/10/02 1:50 PM, Allan J Horwitz [EMAIL PROTECTED] wrote: Hi there -- I'm relatively new to LINUX (I have RedHat 8.0 on my system) and I just downloaded MySQL-GUI and can't seem to get it working. It keeps telling me that it cannot connect through the socket (111). Can someone tell me what I'm doing wrong? I tried to set the path using a shell program in /etc/profile.d/mysqlgui.sh and I even exported the value of PATH. At present, my version of MySQL-GUI is in /usr/local/mysqlgui. Any help would be appreciated. I believe MySQL-GUI tried by default to connect to the mysql server (mysqld) running on localhost using the socket connection (as supposed to tcp/ip ). The error message you are getting seems to indicate that your computer does not run mysqld. Check out here how to get it started: http://www.mysql.com/doc/en/Automatic_start.html /h - 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: Problem with MySQL-GUI
On 11/10/02 9:14 PM, daniel [EMAIL PROTECTED] wrote: running on localhost using the socket connection (as supposed to tcp/ip ). what are you saying that mysql can connect another way other than through mysql.sock ? If I am not mistaken the socket works only for a connection between server and client running on the same host. It is more effective since it skips the network stacks and you can see it as a kind of shortcut. But whenever you connect from the outside (or use mysql -h localhost -P 3306 for the localhost connection) the communication between client and server happens via tcp/ip via port 3306 (standard port you may choose another if you see fit or run several servers on the same host). This is very much like a browser contacting a webserver via port 80 (again that is just the default). I am sure other members on this excellent list will fill in the gaps or correct me where I am in error. Best/h - 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: Problem with MySQL-GUI
On 11/10/02 9:42 PM, daniel [EMAIL PROTECTED] wrote: i've made connections to a remote mysql from one server to our main one and the main one is running socket connections ? I guess I lost you. All I wanted to say is this: If you run mysql (client) and mysqld (server) on the same computer you can just type: 'mysql' on the command line and then the client automatically connects you to the server using the socket. The computer in this case is called 'localhost': local: because everything happens local, and is completely independent if you are connected to any network or not host: because your computer hosts the mysql server As soon as you connect to a mysql server running on a remote computer you can either telnet or ssh into that remote computer, call mysql and connect via the socket on that computer but in many cases (if you have remote connect privileges) you can just call: mysql -h the.remote.dbhost -P 3306 -u dbusername -p and use your local client (running on your computer) to connect to the mysqld (server) running on the remote computer and then you use tcp/ip and not the socket. ...but you knew all this anyway :-)/h - 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 and UPDATE query
On 11/9/02 3:01 AM, Bruno Batarelo [EMAIL PROTECTED] wrote: Greetings I have problems executing very simple UPDATE query. I do not know whether it is a bug or not, but it works in Access and also works with mySQL, but only from access using linked tables. Query is as followes: UPDATE Table_name SET Field_name = Left(Field_name, 4) 'a' Right(Field_name, 4); This query should change every Field_name's 5'th character to 'a'. One more thing - every record in this table has fixed size value so every value is a string 10 characters long. When using access database or mysql database over access everything goes well, but when using VB and MyODBC or just mysql client, every Field_name's value bocomes 0 (zero). What did I do wrong? Thank you all Bruno Batarelo Maybe CONCAT() is the answer, haven't tried it though: UPDATE Table_name SET Field_name = CONCAT(LEFT(Field_name, 4), 'a', RIGHT(Field_name, 4)); http://www.mysql.com/doc/en/String_functions.html /h - 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: little problem, I need some help...
On 11/9/02 3:14 PM, 3mip1s4la-Emilio Pisanty [EMAIL PROTECTED] wrote: hello everyone: to begin with, I apologise for asking a question which is probably obvious. My name is Emilio Pisanty and I and one of the two makers of my school's website. recently we updated the database system (on mysql) from version 3.23 to version 4. since then, any attmepts we've made to update tables have met with a 1175 error (You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column). can anyone tell me how I work around this? sorry again, and thanks in advance, Emilio Pisanty Nothing is obvious and is hard to advise you if you don't give us some more info on the table structure and the update query you have trouble with. /h - 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: PHP bias
On 11/10/02 5:12 AM, Robert Macwange [EMAIL PROTECTED] wrote: I am bothers me. It bothers me that that the MySQL people have a bias towards PHP. PHP is an inferior language. Deal with perl instead. Robert Ouch.Death to all fanatics!!! Do you have any question or are you just boiling? /h - 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: PHP bias Stop feeding the troll
On 11/9/02 11:31 PM, Georg Richter [EMAIL PROTECTED] wrote: Looks like you're not very familar with the spirit of Open Source: Perl, PHP, Python, Ruby etc. are very excellent languages. They all have benefits and of course some disadvantages. But this competition helps each one to improve and become more popular. The same with MySQL, Postgres, SleepyCat, ... etc. There is no better or inferior: Just define your needs and choose whats the best for you. Regards Georg These are all valid points - There is lid for every pot e.g. language for every task but It has been said before the guy raising the thread was most likely a troll. So lets go back to business and answer real questions. :-)/h - 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: PHP bias
On 11/9/02 11:04 PM, Paul DuBois [EMAIL PROTECTED] wrote: I agree. It's really a drag that you can't use databases very well from within Perl very easily. I wish that someone would invent a database inferface for Perl. And it'd be really cool if they'd do it in such a way that the general interface was portable, so that you could plug in drivers for particular database engines like MySQL or PostgreSQL or Oracle or Informix or even flat-file formats like CSV. And then it'd be neat if someone would invent Perl modules to that would use this database interface so that you could do things like transfer data in and out of databases to send information to and from entities like XML documents or Excel spreadsheets. Man! The more I think about it, the more of a bummer it is that you just can't do this stuff from Perl. :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) Though I think it is not really necessary - for anyone not getting it: http://www.amazon.com/exec/obidos/ASIN/0735710546/ref%3Dcm%5Fbg%5Fd/002-226 0661-0130404 /h - 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: PHP bias
On 11/9/02 11:04 PM, Paul DuBois [EMAIL PROTECTED] wrote: I agree. It's really a drag that you can't use databases very well from within Perl very easily. I wish that someone would invent a database inferface for Perl. And it'd be really cool if they'd do it in such a way that the general interface was portable, so that you could plug in drivers for particular database engines like MySQL or PostgreSQL or Oracle or Informix or even flat-file formats like CSV. And then it'd be neat if someone would invent Perl modules to that would use this database interface so that you could do things like transfer data in and out of databases to send information to and from entities like XML documents or Excel spreadsheets. Man! The more I think about it, the more of a bummer it is that you just can't do this stuff from Perl. :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) :) Though I think it is not really necessary - for anyone not getting it: http://www.amazon.com/exec/obidos/ASIN/0735710546/ref%3Dcm%5Fbg%5Fd/002-226 0661-0130404 /h - 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: Cannot change column type from varchar(32) to char(32)
Paul DeBois book knows the answer if you have other VARCHAR fields in the table: ...you need to change all the columns (of type VARCHAR) at once in the same alter table statement.The reason is that if you change a single column at a time, MySQL notices that the table still contains variable-length columns and reverts the changed column back to VARCHAR to save space. To deal with this, change all the VARCHAR columns at the same time:... ALTER TABLE branch MODIFY codebase CHAR(32) NOT NULL DEFAULT 'Ipriori', MODIFY other_varcharfield CHAR(?) ; Hope that helps Hannes On 8/1/01 12:31 PM, Lezz Giles [EMAIL PROTECTED] wrote: I'm running 3.21.33b (yes, I know it's old), and I run the command: alter table branch change codebase codebase char(32) not null default IPriori; where column codebase has type varchar(32) - but the type does not change to char(32). There's no error message or other indication of what went wrong, and if I create a little play table I can succesfully change the column type to char(32) from varchar(32). The table branch has just 180 rows in it. I'd like to change the column types from varchar to char to speed up queries etc. Can anybody shed any light on what I'm doing wrong? Thanks, Lezz Giles - 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: double quotes and single quotes
On 7/30/01 7:38 AM, Mark Lo [EMAIL PROTECTED] wrote: Hi, I have a problem with double quotes and single quotes in mysql command, if I let my user input the form data and it contains double quotes, the values cut off at the double quote. How to override the problem ?? Thank you Eric Chan You could escape the the double quotes before sending them to mysql like so: $formdata =~ s//\/sg; #this is valid for perl Hope that helps. Hannes - 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: Parsing text file into mysql database.
Hi Kit,
I am not sure whether you deliberately choose to send the email not to the
list, but it might have been a wise thing considering the long list of
emails. ;-) (which I deleted right away for your privacy).
Unfortunately I am no PHP expert but rather familiar with Perl but I don't
think that is a major problem since you know the basict and shuld be able to
translate the code with the help on some good online help and your own
experience. Otherwise repost to the mysql list (or php list).
Text sample
LastFirstIDEmail
LastnameFirstname M.1[EMAIL PROTECTED]
LastnameFirstname M.2[EMAIL PROTECTED]
LastnameFirstname M.3[EMAIL PROTECTED]
In Perl the code to grab the information out of the textfile would look like
Sorry for possible code errors - was the first thing in the morning:
open INPUT, /path/data.txt || die Cannot open input file!;
my $counter = 0;
my $last;
my $first;
my $id;
my $email;
while (my $line = INPUT) {
counter++;
next unless counter1;
$line =~ /^(\w+)\s+(.+)\s+(\d+/)\s+(\S+)\s*$/;
# grabs the first group of word characters till the first white space
character
$last = $1;
# grabs the second all the characters until the white space before the
digits of the ID (since you might have a middle initial with a dot)
$first = $2;
# grabs all the digits between white space characters
$id = $3;
# grabs all non white space characters before the end of the line
$email = $4;
# the last \s* is for any number of trailing white space characters at the
end of the line which you probably don't need but it doesn't hurt
your insert statement to put the data into MySQL
}
close INPUT || die Cannot close input file!;
Some explanation to help you with the translation.
1) I declare the variables ('my' statements), you might not have to do that.
Variables are preceded by a '$', I think you don't need that in PHP.
2) I inserted the counter variable and the next statement to skip the first
line with field headers.
3) 'while' loops through all lines in the input file and assigns them to
$line one by by one.
4) The matching part of the line within parenthesis () gets put into the
anonymous variables $1 - $4 form left to the right
5) Square brackets [] indicate a character class
6) The backslash \ is the escape character (needed for special expressions).
7) \s = white space character; \S non-white space character
8) \d = digits = [0-9]; \w word character
9) * = 0 or any number of the preceding expression
10) + = 1 or any number of the preceding expression
11) ^ = start of the line: $ end of the line
12) . = any character except a new line (\n)
13) # = comment
I hope that gets you started.
Hannes
And remember clicking just the reply button in response to messages from the
MySQL list will send just to the person who posted the message, you have to
add the mysql-list address yourself.
On 7/29/01 1:05 AM, Kit Kerbel [EMAIL PROTECTED] wrote:
Sorry about the vagueness. The language I am using is PHP4. I am needing a
php script that will parse the text file into the mysql database. I have
attached a sample of the datafile.
Thanks for your help,
Kit
Original Message Follows
From: Hannes Niedner [EMAIL PROTECTED]
To: Kit Kerbel [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Parsing text file into mysql database.
Date: Sat, 28 Jul 2001 14:00:03 -0700
You need to provide some more details about your task. What programming
language you want to use, and probably a sample from the file that matches a
repetitive subunit within the text. This information is needed since there
is a high chance that you need to exploit regular expression matching for
your goal.
Hannes
On 7/28/01 10:46 AM, Kit Kerbel [EMAIL PROTECTED] wrote:
Hello,
I was wondering if anyone could give any advice on how to go about
parsing a
plain text file into a mysql database. I have a class Roster table that
I
need to parse a text file into. I already have the uploading part of the
file done. Now I just need to add some code to parse the file into the
database. Any suggestions would be appreciated.
Thanks,
Kit
_
Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp
-
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
Re: Parsing text file into mysql database.
You need to provide some more details about your task. What programming language you want to use, and probably a sample from the file that matches a repetitive subunit within the text. This information is needed since there is a high chance that you need to exploit regular expression matching for your goal. Hannes On 7/28/01 10:46 AM, Kit Kerbel [EMAIL PROTECTED] wrote: Hello, I was wondering if anyone could give any advice on how to go about parsing a plain text file into a mysql database. I have a class Roster table that I need to parse a text file into. I already have the uploading part of the file done. Now I just need to add some code to parse the file into the database. Any suggestions would be appreciated. Thanks, Kit _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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: Copy table structure to another database?
When I need recreate a table structure I do it like CREATE db_target.table_copy SELECT * FROM db_source.table WHERE 1=0; The only thing that you have to do now is DESCRIBE db_target.table_copy; DESCRIBE db_source.table; Since you need to manually reestablish the indices in the new table. Although there might well be smarter methods out there. Hannes On 7/4/01 8:19 AM, Graham Nichols [EMAIL PROTECTED] wrote: Hi, Can I copy a table (structure only) to another database? I've searched though my manuals without success so far. kind regards, Graham - 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
Update table using regular expressions MySQL
I want to update 160 records in a table. The field contains a variable value and a constant prefex like 'constant: unique value for this record' Is there such an UPDATE statement that would update this field into 'unique value for this record' They way I would do it otherwise is using Perls regular expressions like (this is rather pseudocode just to make the point) $foo = 'constant: unique value for this record'; $foo =~ s/constant:(.+)//sg; $bar = $1; #(and hopefully $1 would hold 'unique value for this record') UPDATE my_table SET field = $bar WHERE field = $foo; Any ideas? Help greatly appreciated. Hannes - 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: Update table using regular expressions MySQL
Thanks Karel,
This worked almost out of the box, just needed to find out that 'len+1' has
to be replaced with the actual number and is not automatically set (like len
= length('CONSTANT%'). But the manual also explains this very well, I just
didn't think of substring.
Hannes
On 7/3/01 9:13 AM, karel pitra [EMAIL PROTECTED] wrote:
len = strlen(CONSTANT);
UPDATE my_table SET field=substring(field,len+1) WHERE field LIKE 'CONSTANT%'
On Tue 3. July 2001 17:49, you wrote:
I want to update 160 records in a table. The field contains a variable
value and a constant prefex like
'constant: unique value for this record'
Is there such an UPDATE statement that would update this field into
'unique value for this record'
They way I would do it otherwise is using Perls regular expressions like
(this is rather pseudocode just to make the point)
$foo = 'constant: unique value for this record';
$foo =~ s/constant:(.+)//sg;
$bar = $1; #(and hopefully $1 would hold 'unique value for this record')
UPDATE my_table
SET field = $bar
WHERE field = $foo;
Any ideas? Help greatly appreciated.
Hannes
-
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
Mac OS X MySQL GUI
Has anybody successfully compiled MySQL GUI on the Mac OS X platform (or rather Darwin)? Hannes - 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
Update based on subselect
I have seen the smart way to insert data into tables based on a select statement posted recently on this list. Is this also possible for an update query? I want to do something like: UPDATE TABLE foo SET foo.field1 = SELECT bar.field1 FROM bar WHERE foo.field2 = bar.field2; Thanks in advance hannes - 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: Creating Table with a Default Datetime field
Sorry, for quoting the manual but this might be just another example where it could have the answer right away: Hannes http://www.mysql.com/doc/D/A/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: *The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. *The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) *You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). You can set any TIMESTAMP column to a value different than the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later: *Let MySQL set the column when the row is created. This will initialize it to the current date and time. *When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates. On 6/27/01 8:54 AM, Pete Harlan [EMAIL PROTECTED] wrote: TIMESTAMP is not the solution to his problem; he wants the date to default to now() when the record is created, not updated. There's currently no way to do this in MySQL; default values must be constants. --Pete hi. check out the TIMESTAMP column type...maybe TIMESTAMP(14) as the column type? you can then format the date accordingly with...uh...DATE_FORMAT() in your select statements... hth. -ravi -Original Message- From: Richard Bates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: Creating Table with a Default Datetime field How do I get a default date time in the create table clause... I have tried Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT NOW(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT SYSDATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); Create table( CreationDateDATETIMEDEFAULT CURRENT_DATE(), Infovarchar(100) ); All without the quote return with invalid syntax. With the quote inserts 000-00-00 00:00:00 meaining invalid date. == === Richard Bates === TELEHOUSE America == - 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 - 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:
Re: DISTINCT
On 6/27/01 8:48 AM, Sherzod Ruzmetov [EMAIL PROTECTED] wrote: That should work without errors and was one of my suggested ways (can still be seen down below). The group by query works for me the same way like the distinct query, given that all selected fields give you a unique combination (so you could assign a combined primary key from those columns). But you are certainly right that this is not needed if you don't need the feature to access the differences created by extra fields among these identical tuples by using count(), max(), min() and so on. Cheers Hannes Try this: CEATE TABLE new_table SELECT DISTINCT NAME, EMAIL, AGE FROM whatever_table On Wed, 27 Jun 2001, tom harrow wrote: Actually I now realise that im solving the wrong proplem. here is a simplified version of my dilema * NAMEEMAILAGE tomtom@mail23 dicktom@mail76 petepete@email54 davecool@mail21 stevesteve@mail17 marycool@mail89 thomastom@mail13 richrich@mail65 richdick@mail33 tomtom@mail23 tomtom@mail23 so what i want to do is cut it down so there are no duplicate email addresses. I want the table looking like this: * NAMEEMAILAGE tomtom@mail23 petepete@email54 davecool@mail21 stevesteve@mail17 richrich@mail65 richdick@mail33 Cheers tom -Original Message- From: Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 5:45 PM To: tom harrow; [EMAIL PROTECTED] Subject: Re: DISTINCT Hi Tom, The solution to your problem could be simple if the redundancy is across all fields. Then you could simply issue a CREATE table distinct_records SELECT distinct field_1,.field_last FROM table_duplicate_records WHERE 1=1; Or CREATE table distinct_records SELECT field_1,.field_last FROM table_duplicate_records GROUP BY field_1,.field_last; The latter will not work properly if one or more of the fields selected is not identical for identical combinations of the remaining fields. Otherwise if you have one or more fields that is not identical among a group of otherwise (apart from those fields) duplicate records) you must decide if this difference matters to you or not. If they don¹t matter just select all where the duplicates are identical. Make sure that the fields in the select and group by part are the same. Example table_duplicate records field_1 field_2 field_3 a b c a b c a b d f g h f g h d j k k i o CREATE table distinct_records SELECT field_1, field_2, field_3 FROM table_duplicate_records GROUP BY field_1, field_2, field_3; table_distinct_records field_1 field_2 field_3 a b c a b d f g h d j k k i o Got the idea? Hope that helps Hannes On 6/26/01 4:00 AM, tom harrow [EMAIL PROTECTED] wrote: Hi Hannes I saw your reply to a question someone had regarding the DISTINCT keyword and doing the opposite. I too have the same problem... basically need to know the values that arnt distinct and get rid of them but keeping one of course... so there are no duplicates. i am trying to write an asp applicatiojn to do it at the mo but its getting quitre complex. Anyway I thinkk im looking far to deep into the problem and there is probably a much simpler way of doing it. any ideas cheers Tom Harrow Web Developer Netpoll Ltd 9-12 Long Lane London EC1A 9HA TEL 020 7710 2800 FAX 020 7710 2828 - 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: HELP NEEDED: Problems with SELECT in combination with HAVING
Thomas You might want to consider to post a sample select from the table, some things become apparent just then. Hannes On 6/27/01 3:35 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hey Hans - it didn't work (damn) but thank you for your reply and sorry about the accidental mail you got. The result that was produced had the correct time but it was not attatched to the correct row. It was instead attached to the first row that MySql found in the RobotRun table. I have found another way around it, which goes like this: SELECT max(runId), robotId FROM RobotRun WHERE endTime IS NOT NULL GROUP BY RobotId This builds on the assumption that runId's are assigned in a strictly ascending order, so it only works for my domain ... It also seems that I have made a small typo in my initial request (sorry). The purpose of the SQL is to find the runId of the newest run for each robotId. Thus I reformed your SQL to: SELECT runId, max(startTime) I can solve my problem by using GROUP BY and HAVING when running against SyBase like this: SELECT * FROM RobotRun WHERE endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Alas, this does not work with MySql. Is there a general understanding within the MySql community that GROUP BY and HAVING doesn't conform to the standard ? Regards Thomas -Original Message- From: Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: 26. juni 2001 17:42 To: [EMAIL PROTECTED] Subject: Re: HELP NEEDED: Problems with SELECT in combination with HAVING Try SELECT robotId, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId This should return the robotId and the most recent startTime labeled 'crit' for all records specified in the where clause. And have a look in the manual for the group by statement - since this statement works like a enhanced 'select distinct' query it will produce ill results applied to non unique combinations in the select and group by statement. I suppose that¹s the case for your 'robotId, startTime' pair. If I got this wrong forgive me it was first thing in the morning. Hannes On 6/26/01 4:05 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hello there - I have this weird problem using MySql (version 3.23.39). It seems that MySql interpretation of a SELECT statment using HAVING is completely random. I have a table called RobotRun which stores information about when a robot has been running (start, stop and the id of the robot). This translates to columns: runId (primary key), robotId (the id of the robot), startTime and endTime. A robot can run several times during its life time, thus several records will appear in RobotRun for the same robotId, however, each row will always have a unique runId. The purpose of the SQL is to produce one RobotRun row for each robot, with the added restriction that it is only the row with the newest startTime value that gets selected. Now if I do the following SQL things start to get strange: SELECT robotId, startTime, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = crit This query produces 321 rows. Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is correct and expected since the input set is larger. NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows What is going on. I cannot see how this is possible. The input set is only getting larger. The number of rows involved in the table is in the vicinity of 5. The theoretical maximum number of rows produced from the SQL is in the neighbourhood of 5000 rows. That shouldn't be a problem. I have also tried the following statement with the same result (problem): SELECT robotId, startTime FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Any help would be appreciated. Regards Thomas Koch - 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
Re: select query for duplicate records
Peter, That comes pretty close to the distinct -thread in this mailinglist. You might want to try: SELECT device, count(hostname) FROM your_table GROUP BY device HAVING count(hostname)1; This would display all devices with at least 2 (or more) hostnames. If you always have the same IP address, and the same comments allocated to a certain device, then and only then you can add IP and comments to the SELECT and GROUP BY statement. Vary this as needed or pleased Hope that helps And the manual is great too: http://www.mysql.com/doc/G/r/Group_by_functions.html http://www.mysql.com/doc/S/E/SELECT.html Hannes On 6/27/01 1:13 PM, Pete Kuczynski [EMAIL PROTECTED] wrote: Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete - 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: HELP NEEDED: Problems with SELECT in combination with HAVING
Try SELECT robotId, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId This should return the robotId and the most recent startTime labeled 'crit' for all records specified in the where clause. And have a look in the manual for the group by statement - since this statement works like a enhanced 'select distinct' query it will produce ill results applied to non unique combinations in the select and group by statement. I suppose that¹s the case for your 'robotId, startTime' pair. If I got this wrong forgive me it was first thing in the morning. Hannes On 6/26/01 4:05 AM, Thomas Michael Koch [EMAIL PROTECTED] wrote: Hello there - I have this weird problem using MySql (version 3.23.39). It seems that MySql interpretation of a SELECT statment using HAVING is completely random. I have a table called RobotRun which stores information about when a robot has been running (start, stop and the id of the robot). This translates to columns: runId (primary key), robotId (the id of the robot), startTime and endTime. A robot can run several times during its life time, thus several records will appear in RobotRun for the same robotId, however, each row will always have a unique runId. The purpose of the SQL is to produce one RobotRun row for each robot, with the added restriction that it is only the row with the newest startTime value that gets selected. Now if I do the following SQL things start to get strange: SELECT robotId, startTime, max(startTime) as crit FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = crit This query produces 321 rows. Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is correct and expected since the input set is larger. NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows What is going on. I cannot see how this is possible. The input set is only getting larger. The number of rows involved in the table is in the vicinity of 5. The theoretical maximum number of rows produced from the SQL is in the neighbourhood of 5000 rows. That shouldn't be a problem. I have also tried the following statement with the same result (problem): SELECT robotId, startTime FROM RobotRun WHERE startTime = '2001-06-26 00:00:00' AND endTime IS NOT NULL GROUP BY robotId HAVING startTime = max(startTime) Any help would be appreciated. Regards Thomas Koch - 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: Odd problem issueing commands at the console
Could it be that you have two mysql client binaries accessing two different config files - some remainders of a previous MySQL installation? Hannes On 6/26/01 9:02 AM, Peter Matulis [EMAIL PROTECTED] wrote: Just installed 3.23.37 on Red Hat 7.0 from source. I've done this a few times but this time I encountered a mysterious problem. a) server is started and runs under my designated user (mysqladm:mysqlgrp) b) /usr/local/mysql/bin is in PATH of both root and mysqladm c) using either of these users, when I issue a mysql command (here mysqlshow): mysqlshow: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) d) when I cd to /usr/local/mysql/bin and issue the command as shown it succeeds: $ ./mysqlshow +-+ | Databases | +-+ | mysql | | test | +--+ What's going on here? Peter Matulis - 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: DISTINCT
Hi Tom, The solution to your problem could be simple if the redundancy is across all fields. Then you could simply issue a CREATE table distinct_records SELECT distinct field_1,.field_last FROM table_duplicate_records WHERE 1=1; Or CREATE table distinct_records SELECT field_1,.field_last FROM table_duplicate_records GROUP BY field_1,.field_last; The latter will not work properly if one or more of the fields selected is not identical for identical combinations of the remaining fields. Otherwise if you have one or more fields that is not identical among a group of otherwise (apart from those fields) duplicate records) you must decide if this difference matters to you or not. If they don¹t matter just select all where the duplicates are identical. Make sure that the fields in the select and group by part are the same. Example table_duplicate records field_1 field_2 field_3 a b c a b c a b d f g h f g h d j k k i o CREATE table distinct_records SELECT field_1, field_2, field_3 FROM table_duplicate_records GROUP BY field_1, field_2, field_3; table_distinct_records field_1 field_2 field_3 a b c a b d f g h d j k k i o Got the idea? Hope that helps Hannes On 6/26/01 4:00 AM, tom harrow [EMAIL PROTECTED] wrote: Hi Hannes I saw your reply to a question someone had regarding the DISTINCT keyword and doing the opposite. I too have the same problem... basically need to know the values that arnt distinct and get rid of them but keeping one of course... so there are no duplicates. i am trying to write an asp applicatiojn to do it at the mo but its getting quitre complex. Anyway I thinkk im looking far to deep into the problem and there is probably a much simpler way of doing it. any ideas cheers Tom Harrow Web Developer Netpoll Ltd 9-12 Long Lane London EC1A 9HA TEL 020 7710 2800 FAX 020 7710 2828 - 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: DISTINCT AND NON DISTINCT
On 6/25/01 9:10 AM, anna soobrattee [EMAIL PROTECTED] wrote: Hi All, I've been reading up on how to do joins on the mysql site. Is there not an inverse function for DISTINCT, so that I can pull out duplicate data as opposed to DISTINCT data...or will I have to get this information by using joins? Thanks very much for your help. Anna You could use 'group by' like so. x = value that is either distinct or not distinct: Select x, count(*) from table_where_x_is_in group by x having count(*)1; Hope that helps. (BTW, you can also combine fields, but they have to appear in the 'select' and in the 'group by' part. More in the really good manual). I hope that accomplishes what you wanted. Hannes - 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: Perl DBI Error 19 -solved
Just if somebody is interested:
I solved the mystery with Error 19. All I needed to do was introducing a
second statement handle for the update query.
(BTW, the script runs probably faster using '$sth = $dbh-do' instead of
'$sth = $dbh-prepare', followed by '$sth = $dbh-execute').
Hannes
On 6/24/01 4:05 AM, Hannes Niedner [EMAIL PROTECTED] wrote:
I am having trouble with DBI. I wrote a little script that should update
fields in one table (uid_test) based on values in another table (merge). I
updates one row and then dies with:
os prompt: blah blah
1 1011877 101
Error during processing for table uid_test
Error 19 (fetch() without execute())
The selected values (primary_id, other_id) are both INT(12) and so are the
fields of the target table (superceded_by, uid_new).
Please have a look at the Perl code snippet below. As I said it works for
the first row. BTW if there is pure sql code that would do the job, I would
be delighted to learn about.
Thanks Hannes
-snippet-
#issue query
$sth = $dbh-prepare ( SELECT primary_id, other_id
FROM merge
ORDER BY other_id
) or bail_out (Cannot prepare query from merge);
$sth-execute () or bail_out (Cannot execute query from merge);
while (@ary = $sth-fetchrow_array ()) {
$counter++;
my ($primary_id) = $ary[0];
my ($other_id) = $ary[1];
print $counter\t$primary_id\t$other_id\n;
#update the data in the target table
$sth = $dbh-prepare ( UPDATE $table_name
SET superceded_by=$primary_id, status=\'1\',
time=null
WHERE uid_new=$other_id
) or bail_out (Cannot prepare sql (UPDATE
$table_name)!);
$sth-execute () or bail_out (Cannot execute sql(UPDATE
$table_name)!);
}
if (!defined($DBI::err)) {
print $counter sequences retrieved from table merge, cleaned out
and successfully updated in table niedner.$table_name.\n;
}else {bail_out (Error during processing for table $table_name\n);
}
#clean up
$sth-finish () or bail_out (Cannot finish query from database);
$dbh-disconnect () or bail_out (Cannot disconnect from database);
exit (0)
--snippet end
-
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
Perl DBI Error 19
I am having trouble with DBI. I wrote a little script that should update
fields in one table (uid_test) based on values in another table (merge). I
updates one row and then dies with:
os prompt: blah blah
1 1011877 101
Error during processing for table uid_test
Error 19 (fetch() without execute())
The selected values (primary_id, other_id) are both INT(12) and so are the
fields of the target table (superceded_by, uid_new).
Please have a look at the Perl code snippet below. As I said it works for
the first row. BTW if there is pure sql code that would do the job, I would
be delighted to learn about.
Thanks Hannes
-snippet-
#issue query
$sth = $dbh-prepare ( SELECT primary_id, other_id
FROM merge
ORDER BY other_id
) or bail_out (Cannot prepare query from merge);
$sth-execute () or bail_out (Cannot execute query from merge);
while (@ary = $sth-fetchrow_array ()) {
$counter++;
my ($primary_id) = $ary[0];
my ($other_id) = $ary[1];
print $counter\t$primary_id\t$other_id\n;
#update the data in the target table
$sth = $dbh-prepare ( UPDATE $table_name
SET superceded_by=$primary_id, status=\'1\',
time=null
WHERE uid_new=$other_id
) or bail_out (Cannot prepare sql (UPDATE
$table_name)!);
$sth-execute () or bail_out (Cannot execute sql(UPDATE
$table_name)!);
}
if (!defined($DBI::err)) {
print $counter sequences retrieved from table merge, cleaned out
and successfully updated in table niedner.$table_name.\n;
}else {bail_out (Error during processing for table $table_name\n);
}
#clean up
$sth-finish () or bail_out (Cannot finish query from database);
$dbh-disconnect () or bail_out (Cannot disconnect from database);
exit (0)
--snippet end
-
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: Interupting client-server-link during longlasting queries
Hi! First of all, it is unlikely that you will see some status bar as operations in RDBMS server are quite complicated and time table can not be predicted except in some extremely simple cases. Regarding aborting a client, server operation will continue until OS on which server runs reports to server that a thread is killed. Then it depends on the stage in which the operation is when will it stop and when will cleanup operation be executed. In some cases , like in multi-table delete (MySQL 4.0) delete will finish even if a thread is killed. This is due to the entity integrity rules. Thanks Sinisa, So is there any command suffix in mysql that sends the query in the background (letting the server doing the query without talking back to the client) like using the on the unix commandline - resulting in any query being completed after quitting the client. Hannes - 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
Interupting client-server-link during longlasting queries
Hi Folks, I just started a create table ... select from ...left-join query from my laptop. It seems to take a while? What happens if disconnect my client from the mysql daemon running on another machine. Will the interupted mysqld-client crosstalk abrupt the query, or will it continue to run on the server and die when it needs to report back to the client, or will it even finish the job without the client listening (the one that started the job in the first place). This also raises the question for some kind of progress status bar etc.. All I can do now is to monitor the processlist with mysqladmin which doesn' give me any clue how long I still have to wait. Comments appreciated Hannes - 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: No identical entries in a field
On 6/11/01 11:11 PM, Jari Mäkelä [EMAIL PROTECTED] wrote: Hi, how a field ( names of products ) of database should be defined so that you could not enter identical entries at the field? Or does one need to do the validation before inserting info? Jari Mäkelä How about creating a unique index for that field? Details in the manual. Good luck Hannes - 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: Select query
On 6/7/01 5:33 PM, Gary Huntress [EMAIL PROTECTED] wrote: I'll bet it's a roundoff problemtry select * from sequence_protein where mol_wt 53211.62 and mol_wt 53211.63 Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: Hannes Niedner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 5:48 PM Subject: Select query What do I do wrong? The query result is not supposed to be an empty set (please cc your response to [EMAIL PROTECTED]) mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select distinct sequence_id from sequence_protein - where mol_wt=53211.62; Empty set (0.12 sec) mysql describe sequence_protein; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | sequence_id | int(10) unsigned | | PRI | 0 | | | length | int(10) unsigned | | | 0 | | | mol_wt | float(10,2) | YES | | 0.00| | +-+--+--+-+-+---+ Thank you Hannes - 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 You guys are great and solved my case mysql select sequence_id from sequence_protein - where truncate(mol_wt,2) =53211.62; +-+ | sequence_id | +-+ | 100368 | +-+ 1 row in set (0.18 sec) I do apologize, I was following the wrong thread in the manual. Thanx Hannes - 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
Select query
What do I do wrong? The query result is not supposed to be an empty set (please cc your response to [EMAIL PROTECTED]) mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select distinct sequence_id from sequence_protein - where mol_wt=53211.62; Empty set (0.12 sec) mysql describe sequence_protein; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | sequence_id | int(10) unsigned | | PRI | 0 | | | length | int(10) unsigned | | | 0 | | | mol_wt | float(10,2) | YES | | 0.00| | +-+--+--+-+-+---+ Thank you Hannes - 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: Select query
On 6/7/01 4:50 PM, Eric Fitzgerald [EMAIL PROTECTED] wrote: When comparing float values, you have to use '' around the value. Your query should look like this: select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; Ok, I should have mentioned that I tried the quotes. I am running out of ideas. Hannes mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select sequence_id from sequence_protein - where mol_wt = '53211.62'; Empty set (0.15 sec) mysql select sequence_id from sequence_protein - where mol_wt = 53211.62; Empty set (0.16 sec) - 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 Port number
On 6/5/01 7:35 PM, sanborn [EMAIL PROTECTED] wrote: How is MySQL used over a network? I assume there is a port involved, and some kind of transaction server built into mysql? How do I configure, test this? In the several documents I have read so far, I haven't found much on the subject. Thanks for your input. Gene Sanborn Standard port is 3306 Hope that helps Hannes - 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
TIMESTAMP
Hi everybody, I am new to the list so if my question was answered recently I do apologize but I could not find the answer neither in the archive or the manual. Problem: I try to load data into a table using LOAD DATA LOCAL INFILE. Into a table containing a TIMESTAMP(14) field. The loading works well except that I don't get the current timestamp in this field no matter if I have NULL, NULL, or 'NULL' (the same applies to NOW()) I always ending up with 00 in the field. I am using the current release of MySQL on a Linux box (RH 6.2). Please help. BTW if I try an INSERT with the same data (1 row of the file) everything is fine. Thanks Hannes - 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
