Re: Query help
On Tue, 25 May 2004 11:50:11 +0100, Paul Owen [EMAIL PROTECTED] wrote: What I have so far is: from people, invoice, payments where people.pid=invoice.pid and people.pid=payments.pid group by people.pid; Though it doesn't fix the problem you're asking about, I wanted to note that you'll want to use a left joins. Otherwise, your search only returns people who have made a payment. from people, invoice ;eft join payments on payments.pid=people.pid where people.pid=invoice.pid group by people.pid; As for your original question, it seems like the problem is grouping incorrectly, but it doesn't jump out at me. It's still early here this morning. Ryan Fox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add/Edit/Delete Form Woes
http://localhost/geowebworks/php/exp3.php?CCode=caf That looks right; caf (for continent-Africa) is the value for the CCode field on the Africa row. But if I type something in the form and press submit, the change isn't reflected in my MySQL table. My thought is that the tutorial used the column id as int auto_increment . When you changed to CCode (a char), you didn't quote it in sql. So this line: $sql = UPDATE Continents SET CCode='$CCode',Name1='$Name1',Type1='$Type1',Group='$Group',Hemisphere='$Hemisphere',ID1='$ID1' WHERE CCode=$CCode; Should be changed to $sql = UPDATE Continents SET CCode='$CCode',Name1='$Name1',Type1='$Type1',Group='$Group',Hemisphere='$Hemisphere',ID1='$ID1' WHERE CCode='$CCode'; Repeat everywhere you're using CCode without quotes. Ryan Fox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add/Edit/Delete Form Woes
$sql = UPDATE Continents SET CCode='$CCode',Name1='$Name1',Type1='$Type1',Group='$Group',Hemisphere='$Hemisphere',ID1='$ID1' Also, this has a pretty severe logic flaw. If someone changes the CCode field, this sql statement won't function as desired. :) Ryan Fox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replacing server online
On Tuesday 17 June 2003 03:35 am, George Chelidze wrote: Hello, I have two RH boxes with mysql on them and replication is enabled from box 1 to box 2 and from box 2 to box 1. The amount of data on each of them is round 400-500MB. Now I have to replace one of them with new box. As both boxes are productional ones, I'd like to replace one of them online without stopping them. I know I can tar the tables (MyISAM) and move to a new box but I have to make sure the data won't change, so I have to offline the boxes. Is there any way to do this replace transparetely? Any recommendations would be great. Thanks in advance. For replacing box 1: 1. Direct requests to box 2 2. Stop slave on box 2 3. Record log file and position on box 1 using show slave status 3. Stop mysql on box 1 4. Move tables from old box 1 to new box 1 5. On new box 1, run CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='replication user name', MASTER_PASSWORD='replication password', MASTER_LOG_FILE='recorded log file name', MASTER_LOG_POS=recorded log offset; filling in box 2's name, and the log name and position recorded in step 3. 6. Start slave on box 1. 7. Start slave on box 2. 8. Verify box 1 looks ok 9. Direct requests to both boxes again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query takes long time to execute
On Friday 13 June 2003 09:29 pm, Mark Stringham wrote: I'm querying a table of approximately 42 records based on 3 criteria. The results are then displayed in a pop-up window. When the query is executed the first time - the window opens and stalls for about 3 seconds before the results are displayed. Try running your query from the MySQL command line client, and see how it takes then. The querying you describe is quite simple, and shouldn't be taking MySQL 3 secs to run. If the query runs in 0.01 secs on the command line, then you know MySQL is not the cause of the delay. :) If, OTOH, the query actually does take 3 secs to run, start by running 'explain select * ...', which will tell you how the query is being processed. More info in optimizing queries can be found at http://www.mysql.com/doc/en/Query_Speed.html . Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wednesday 11 June 2003 08:47 pm, JJ wrote: I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restoring from a .sql file
On Wednesday 19 March 2003 12:31 pm, Tab Alleman wrote: I am using the following command, following the only example I could find in the documentation (mysql prompt shown for clarity): mysql mysql u:/db backup 20030318/cm_central.sql; Run this from a command line, not the mysql client. Ryan sql,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
Re: Enterprise Resources Planning(ERP SYSTEM) + MYSQL
- Original Message - From: yeong Tze En [EMAIL PROTECTED] I am trying to develop an ERP system(Enterprise Resources Planning).. I am wondering wheater if MYSQL suitable for this system..? Any opinion or suggestion from you all? Maybe Oracle or SQL server are more suitable.. at least the existing systems in the market are using this 2 type of database? OSSuite, for one, is ERP using MySQL. http://www.ossute.org Sorry for the on-topic advertisement. :) Ryan - 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 optimization problem
Taking a blind stab, how about.. SELECT a.name, minimum(0,COUNT(b.id)) as votes FROM poll_options as a LEFT JOIN poll_votes as b on a.id=b.oid WHERE b.pid='poll_id' GROUP BY b.oid Ryan Fox - Original Message - From: Blaster [EMAIL PROTECTED] However! Here comes the problem, if no vote is cast on an option, it will not show up in the list! I want it to print 0% for any options that havn't received a vote, like it would with my old query system. This is basically what I want to do: SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b WHERE b.pid='poll_id' AND b.oid=a.id GROUP BY b.oid With one exception, if votes = 0, it should be listed in the result aswell! sql, 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
Re: Weird WHERE clause possible?
- Original Message - From: Lefevre, Steven [EMAIL PROTECTED] The problem (as I see it) is that I'm storing the last name and the first name in two seperate fields. I can make an SQL statement like Select * from Students Where LastName Like Smith%;, but can I make something like SELECT * FROM Students WHERE (LastName, , , FirstName) AS Name LIKE Smith, J%; Backing up, is the problem that you need to uniquely identify the student's name after returning the results? If so, a simple, efficient way is to add an auto_increment column to your students table, so each name is given a unique ID. Then, when results are returned, you can create a link for each student that includes the student's ID. If this isn't the case, and you really want to do a statement like that above, how about: SELECT * FROM Students WHERE LastName='Smith' and FirstName LIKE 'J%'; or worse, SELECT * FROM Students WHERE concat(LastName, , , FirstName) LIKE 'Smith, J%'; Ryan Fox sql, query, sausage - 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: Why unsigned doesn't work?
- Original Message - From: Octavian Rasnita [EMAIL PROTECTED] mysql create table aaa(id int not null unsigned, name text); Can you tell me why doesn't it work? You want: create table aaa(id int unsigned not null, name text); Your way doesn't work as 'unsigned' is a modification of the type, and so needs to be next to the type declaration 'int'. sql, query, anti-spam splooge. - 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? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)
- Original Message - From: Qunfeng Dong [EMAIL PROTECTED] We are trying to develop a simple biology database to maintain some DNA Sequence information. My problem is coming from the following two tables: snip Making indexes smaller will help. Does it need to be varchar(50)? Also, I'd consider creating a numeric auto_increment primary key on your NewSequence table, and using it to relate the 2 tables together. It may make for some more complex SQL statements to describe the relationship, but you'll gain the time back in performance. Consider using the following. CREATE TABLE NewSequence ( id int(11) not null auto_increment, Seq_ID varchar(50) NOT NULL, GenBank_Acc varchar(10), Organismvarchar(50) NOT NULL, Seq_Type enum(EST,GSS,EST Contig,EST Singlet,GSS Contig,GSS Singlet,GSS Plasmid Contig,Protein) NOT NULL, Seq_Length int NOT NULL, Seq_Title textNOT NULL, Comment text, Entry_Date dateNOT NULL, PRIMARY KEY (id), UNIQUE (Seq_ID), UNIQUE (GenBank_Acc), INDEX (Seq_Type), INDEX (Organism) ); CREATE TABLE NewSequence_Homolog ( id int(11) NOT NULL, Homolog_PID int NOT NULL, Homolog_Descvarchar(50) NOT NULL, Homolog_Species varchar(50), PRIMARY KEY (id, Homolog_PID) ); This would make your example query: select count(*) from NewSequence s left join NewSequence_Homolog h on s.id = h.id; And this would run much quicker, as instead of searching through 50 character indexes for each table, it would only have to look at 11 digit indexes. Much quicker. Ryan sql, query and stuff - 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: Join-question
- Original Message - From: Michelle de Beer [EMAIL PROTECTED] I believe this question is solved by a join, but I haven't really got a hang of it. My table: -- | uid | rootid | parentid | name | -- | 1 | 0 | 0| name1| | 2 | 1 | 1| name2| | 3 | 1 | 2| name3| | 4 | 1 | 3| name4| | 5 | 1 | 2| name5| ... How do I get this (WHERE uid=5): -- | rootid_name | parentid_name | name | -- | name1 | name2 | name5| -- Here you go. select a.name, b.name, c.name from yourtable as a, yourtable as b, yourtable as c where a.uid=c.rootid and b.uid=c.parentid and uid=5; or, as left joins: select a.name, b.name, c.name from yourtable as c left join yourtable as a on a.uid=c.rootid left join yourtable as b on b.uid=c.parentid where uid=5; Ryan sql to the hizzo query to the hizza - 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: syntax error near unexpected token
- Original Message - From: Trask [EMAIL PROTECTED] I'm trying to configure MySQL and PHP so that PHP can access MySQL for function calls and database access. It's my understanding that I need to use the command - mysql_connect(address, username, password); I tried that and got a syntax error, I tried: [root@www phpBB2]# mysql_connect(localhost, root, mypassword) bash: syntax error near unexpected token `mysql_connect(localhost,' Any ideas what I'm doing wrong here? Many, many things. :) I'm assuming you're trying to set up phpBB (from this error message being thrown from your shell, in a directory named phpBB2). If so, look at their support page at http://www.phpbb.com/support.php and follow the directions very carefully. Any questions you come across while setting up this package will almost certainly be more appropriately directed there. If, in some sick twisted world, you're trying to code php, you want to use the mysql_connect function in a PHP script, not from a bash shell. I hope this is not the case, as you appear to have a _long_ road ahead of you. If so, the PHP mailing lists will provide you with better support to get started. Good luck... Ryan - 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: The dreaded move from Oracle to MySQL
Can you explain this procedure to me a little better or point me to something I can read? I still have to convince the person who manages the Oracle box to open up an NFS share. The point of using NFS to transfer the files from the oracle box to the mysql box is to avoid copying the data twice. If this isn't possible (because the oracle box doesn't already have nfs set up, and the admin doesn't want to make changes...), you can copy the data via any other means. CD-R's, DLT, FTP, scp, wget, . NFS would likely be the easiest to use, and cuts out an intermediate step of copying the data to a second location, but it is not necessary. SQL Ryan Another way we could increase load times would be to use 4 scripts to load data from 4 tables at once. We have the bandwith to handle that and the MySQL box seems to be handling request just fine at 11,000 inserts its still not tapping out both procs or killing the disk I/O. Though I don't think you'd see much (any?) speed up by doing this, I'll defer to the good judgement of other list members than have more data than me. - 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: Inserting values from PHP.
- Original Message - From: Damien Buttler [EMAIL PROTECTED] I hope someone can help. I have written a PHP script which adds a row to a MySQL table. The columns are all of type 'char'. The script works fine when only numerical values are passed, but when I try to pass values a-z A-Z the script fails. When I attempt to add the same values straight from the MySQL CLI it also works. Your php script is broke. (possibly a quoting issue) :) For more help, send your php script to a php list. SQL - 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: Problem Installing on RH 7.3
First, nice username :) Anyhow, use the /etc/init.d/mysqld script as root to start/stop mysql, not the safe_mysqld binary. The init.d script really calls the safe_mysqld binary, but ti also makes a couple sanity checks like ensuring that the initial database files exist and are owned by the proper user. Example: # /etc/init.d/mysqld start This should get you past the problem you are currently having, which is that mysqld can't find it's database files. This is mostly due to redhat's mysql rpm installing the database files in a different location than mysql's distibution does. Hope this helps, Ryan Fox [EMAIL PROTECTED] On Thu, 2002-07-18 at 11:44, Richard Fox wrote: The mysqld.log file says: 020717 13:05:05 mysqld started 020717 13:05:05 /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020717 13:05:05 mysqld ended But I do see the host.frm file: -rw-rw1 root root 8958 Jul 17 13:04 /var/lib/mysql/mysql/host.frm How did you fix this problem? I do have the /var/lib/mysql/mysql.sock file... Thanks Rich I'm near enough in the same position as you are. Check the file /var/log/mysqld.log to see what the problem is. I had a problem accessing the file './mysql/host.frm', but I think that I've fixed this now. What happens when you try the 'mysql' command? I get the response can't connect to local MySQL server through socket '/var/usr/mysql/mysql.sock' And indeed there is no such file. Do you have this file? -Original Message- From: Richard Fox [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 17, 2002 10:41 PM To: [EMAIL PROTECTED] Subject: MySQL: Problem Installing on RH 7.3 The MySQL daemon does not run. I installed the binary RPM's from the RH cd, MySQL 3.23.49. I run mysql_install_db, outputs some text and appears to execute correctly. The I run safe_mysqld , and I get: [root@thor rfox]# /usr/bin/safe_mysqld [1] 2212 [root@thor rfox]# Starting mysqld daemon with databases from /var/lib/mysql 020717 15:21:28 mysqld ended That's it! I tried both the rpm and compiling it myself from source with BOTH gcc 2.96 and gcc 3.0.4. Exact same behavior I just need a little hint, please! How do I troubleshoot this? Thanks, Rich sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to Count(*) with LIMIT
- Original Message - From: mos [EMAIL PROTECTED] I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? You could use least(). mysql select least(count(*),30) from ct; ++ | least(count(*),30) | ++ | 30 | ++ 1 row in set (0.00 sec) mysql select count(*) from ct; +--+ | count(*) | +--+ | 43 | +--+ 1 row in set (0.00 sec) Cheers, Ryan Fox, Geek email: [EMAIL PROTECTED] BackWatcher, Inc. web: http://backwatcher.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: How to Count(*) with LIMIT
- Original Message - From: mos [EMAIL PROTECTED] I have a Where clause like: select count(*) from table where LIMIT 100 Unfortunately the Count(*) ignores the LIMIT clause entirely. Why? If You want to know the number of rows in the recordset returned by the query then You should use mysql_num_rows(). This seems like the most economical way to approach it. Actually, I think you've got it completely backwards. :) Using mysql_num_rows() on a result set will force you to do a query that returns all of the rows, while using count(*) will (much more efficiently) use the index to return the number of rows. Cheers, Ryan Fox, Geek email: [EMAIL PROTECTED] BackWatcher, Inc. web: http://backwatcher.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BETWEEN: So, what happens here?
So I do:
SELECT * FROM table1 WHERE datum BETWEEN ('-00-00' + INTERVAL 1 DAY)
AND '2002-03-10';
MySQL returns an empty result set, but it shouldn't (the table contains
dates year 2000 and up.
MySQL refers to the date -00-00 as null. Null+1 is null. Why don't you
rewrite the query as
SELECT * FROM table1 WHERE datum = '2002-03-10'
? If you can't do that, using a valid date like '1900-01-01' instead of
'-00-00' should work.
-
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: why too long entries get _cut_ without error
Doesn't matter: If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit. ok, so it isn't depending on fixed length as you first said, and the reason for it is just the simple design of sql, right? As I see it, the question is what should an SQL server do in this case of a value being assigned to a CHAR (or VARCHAR) column that exceeds the column's maximum length. The current action is that this value is truncated to fit the column. The other option would be to automagically expand the column's length so the value would fit. Despite what the original poster may think, they _really_ don't want that to happen. Apart from applications that depend on only receiving a certain length string from a query (think buffer overflow), having an insert unexpected alter an indexed char column on a 200,000 row table would be A Bad Thing. I wouldn't be terribly opposed to having an option to be set to allow this, but it shouldn't be on by default, and most MySQL users who can read the manual and find the option to do this should already know why this is a bad thing and not want to use it. Ryan - 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: Installation of mySQL on a Win2000 Pro machine
- Original Message - From: Andrew Aragi [EMAIL PROTECTED] Sent: Friday, January 11, 2002 8:30 AM Subject: Re: Installation of mySQL on a Win2000 Pro machine So pardon my ignorance but what is the difference between besides the symbol? What does the do. When I use the latter it say that this cmd is not recognized. c:\mysql\bin\mysqld-max --standalone c:\mysql\binmysqld-max --standalone The '' character is a traditional DOS/Windows prompt symbol. In documentation such as the above, it would mean the current working directory is c:\mysql\bin, the command executed is mysqld-max, and the argument to the command is --standalone. Ryan - 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 and SMP?
I'm running ok on my Dual PII machine, if this helps anyone. Redhat 7.1, 2.4.2-SGI_XFS kernel, MySQL 3.23.43. [root@willyjr /root]# uptime 1:07pm up 95 days, 4:08, 1 user, load average: 0.08, 0.04, 0.01 [root@willyjr /root]# cat /etc/redhat-release Red Hat Linux release 7.1 (Seawolf) [root@willyjr /root]# uname -a Linux willyjr.noguska.com 2.4.2-SGI_XFS_1.0smp #1 SMP Fri Apr 27 19:07:34 CDT 2001 i686 unknown mysql status -- mysql Ver 11.15 Distrib 3.23.43, for pc-linux-gnu (i686) Connection id: 5774 Current database: Current user: root@localhost Current pager: stdout Using outfile: '' Server version: 3.23.43-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 9 days 50 min 33 sec Threads: 31 Questions: 103436 Slow queries: 0 Opens: 3449 Flush tables: 1 Open tables: 64 Queries per second avg: 0.133 -- - 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 spool a file to your server from mysql
Check out select into outfile. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SEL ECT Ryan - Original Message - From: Cindy Yu [EMAIL PROTECTED] I am new to mysql. I know, in Oracle, when you spool file, then spool off, this will create a file on your server. What is the equivalent command for Mysql. For example: sqlplus Spool C:\temp\table_names sqlplus select table_name from user_tables; sqlplus spool off; This will create a file on the server. Cindy - 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: Decrypting PASSWORD() from MySQL
- Original Message -
From: Zach Curtis [EMAIL PROTECTED]
an encrypted PASSWORD('password') field in the MySQL table. How can I
retrieve the decrypted password in a SELECT statement?
MySQL's password() function is a one way hash. It does not have a
decryption function. Regardless, a better solution is to change the user's
password to a random string, then e-mail the user their new password.
Hope this helps,
Ryan Fox
-
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 query it too slow in big table
###query the last time where ip='172.017.011.253' mysql select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; +-+ | max(THE_TIME) | +-+ | 2001-11-26 14:35:18 | +-+ 1 row in set (6.77 sec) It's too slowly Try storing the IP address as a numeric type, possibly decimal(10), and using the functions INET_NTOA and INET_ATON to convert from the address back and forth between dotted hexadecimal and decimal. This will help because instead of searching for MySQL having to search a varchar(50) field, it can search a numeric field, which of course is faster. Ryan - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do I get version information from a MySQL server?
- Original Message - From: Colin Hill [EMAIL PROTECTED] So, is there a way to retrieve the version information directly from the server using some pseudo SQL query? show variables like 'version'; Ryan Fox - 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 on Win2000
- Original Message - From: Alex Shi [EMAIL PROTECTED] Okay, I will try MySQL GUI later BTW, do I have to use ODBC if I use PHP? I'm told PHP has its own very API for MySQL... You can access MySQL from PHP using PHP's ODBC functions (http://www.php.net/manual/en/ref.odbc.php), it's MySQL functions (http://www.php.net/manual/en/ref.mysql.php), or an abstration layer such as ADODB (http://php.weblogs.com/adodb) The latter might be the best, as you trade a tiny loss of speed (but not nearly as much as when using odbc) for the ability to migrate to or support other databases than MySQL easily. Hope this helps, Ryan Fox - 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 round problem
From : http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fun ctions ROUND(X) Returns the argument X, rounded to the nearest integer: mysql select ROUND(-1.23); - -1 mysql select ROUND(-1.58); - -2 mysql select ROUND(1.58); - 2 Note that the behavior of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always towards zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead. ROUND(X,D) Returns the argument X, rounded to a number with D decimals. If D is 0, the result will have no decimal point or fractional part: Also, From the mailing list: Before posting, please check: http://www.mysql.com/manual.php (the manual) Ryan Fox - Original Message - From: Attila Soki [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 10, 2001 12:40 PM Subject: mysql round problem hi, is this bug already in buglist, maybe in newer verisons of mysql is already corrected? if yes then please let me know please from what version. the probmlem: select round(9.065,2) ++ | round(9.065,2) | ++ | 9,06 | ++ why not 9,07 ?? cheers, Attila Soki - 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 connection with mysql?
In your situation, MySQL sees the client to be the web server, as it is the box that connects to the database and executes queries. If you need finer granualarity on the access controls at the MySQL level, you can pass different usernames/passwords to MySQL through the php mysql_connect() function dependant on what user is authenticated in to your web server. If you need to restrict clients on a per-IP basis, you can do this at the php level by checking the $REMOTE_ADDR variable and acting accordingly. Hope this helps, Ryan Fox - Original Message - From: Wee Chua [EMAIL PROTECTED] I have a question regarding the client IP address between PHP and MySql. The built-in mysql database have different privilege tables. When I log on to mysql through the internet browser, does mysql know where I am connecting from if I use mysq_connect(.) function? From what I see, this function doesn't tell where I am connecting from, if this is true, those privilege tables are not helping to check on the privileges if the client is from browser. Does it have any other ways to tell mysql server where the client is connection from besides default localhost? Thank you, Calvin - 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 do temporary tables die?
How do tamporary tables die? Is there a time limit, og do I have to drop them manually? They are dropped when the client connection that created them disconnects. They can also be dropped manually. Ryan - 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: Data Normalization Paradox
Among other criteria, it mentions that in first order normalization No repeating groups of data are allowed. It then gives a table with 3 rows of data, and shows that one of the columns (company name) has the same entry in two of the rows. It says this table is not in 1st normal form. For the correct way of doing it, it shows the same table, with that column changed to (company name id) and links it to another table that has two columns, company name id, and company id. Right. The main reasons for doing this are to minimize data storage space (the number '1' takes far less space to store than the string 'Foo Company'), ensure data integrity (make sure you don't have entries such as 'Foo Company' and 'foo company'), and to speed queries (matching integers can be performed by the sql server MUCH qiucker than matching strings). Hope this helps, Ryan Fox - 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
