Re: Update select fields in all rows
On Tue, 14 Sep 2004 01:07:53 -0400, Seth [EMAIL PROTECTED] wrote: I am fairly new to MySQL and I have been trying to form a way to have select rows numbers put back to 0, sort of like a reset in some rows of the table. The table is ladder_1 and I am trying to have the fields rank,lrank,wins,loss,games,percent,streak,points,kills on all the rows updated back to 0 as a number on all rows. I think you're looking for: update ladder_1 set rank = 0, lrank = 0, wins = 0, et al. You can specify a where clause on an update if you don't want to update absolutely everything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with imported data from mysqldump file
Don't know a whole lot about this but - Is the data escaped when you inserted it? Maybe one of these options for the mysqldump would help --fields-enclosed-by=... --fields-optionally-enclosed-by=... --fields-escaped-by=... or --set-charset ... Respectfully, Ligaya Turmelle Juan Alvarado wrote: I am generating a backup of a database using mysqldump and using that dumped file to repopulate another database. The dump and import process work just fine, BUT I am having some problems in my application with some enconding issues. For example, one of the rows that mysqldump outputs is the following: INSERT INTO ap_step VALUES (9,'Determine if former employees UIDs are re-assigned. And if so, determine if a process exists for evaluating user rights.',3,3); As you can see the second column has some text with some single quotes. When I import the generated dumped file into the new database, I lose the single quotes in the text and this is causing some UTF-8 encoding problems in my application. select ap_step from ap_step where ap_step_id =9; ++ | ap_step| ++ | Determine if former employees UIDs are re-assigned. And if so, determine if a process exists for evaluating user rights. | ++ You will notice from the above query that the single quotes are gone. Whenever I remove this row from the table, my application works just fine. This is why I am almost certain that there is some encoding issues here. I need to be able to load my data back into the new database exactly as it is coming out of the old one. One thing that I do want to note is that the dumped file is generated in Windows XP and it is loaded into a new database in a Unix environment. If anyone could please point out some ideas I would appreciate it as this is basically a show stopper for us right now. Thanks in advance - Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Field Type Problem
Hi I'm using Mysql 4.x... when I try following: CREATE TABLE `MyTable` ( `SNumber` char(32) NOT NULL, `UserID` char(32) NOT NULL default '0', PRIMARY KEY (`SNumber`) ) TYPE=MyISAM; It created the exactly correct table, But when i add any varchar field in the above sql..then generated table is like it has all the fields of type Varchar i mean it converest SNumber , and UserID to varchar too... following is the example , On execution of follwoing the generated table's all the field will be varchar despite char. CREATE TABLE `categories` ( `SerialNumber` char(32) NOT NULL, `UserID` char(32) NOT NULL default '0', `Description` varchar(45) NOT NULL default '', PRIMARY KEY (`SerialNumber`) ) TYPE=MyISAM; What is this behaviour can any one explain me ?..or Wht should i do if i need char and varcha fields in my schema ...? Thanks Sheraz __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime as float
Is there any MySQL built in function to convert datetime types to real numbers (float) i.e. seconds or days since a reference date? I can't find any in the manual. If not, how do I best write a speedy one (conversion of calendar dates to Julian days is a bit tricky)? Thanks in advance for any help or suggestion H Professor Håkan Elmqvist Avdelningen för medicinsk teknik Institutionen för laboratoriemedicin KAROLINSKA INSTITUTET Novum 141 57 Huddinge Tel 46-8-585 837 55, Fax +46 (8) 585 877 79 epost [EMAIL PROTECTED] Organisationsno. 01 202100-2973 01 Vat No. SE 202100297301 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another order by question
HI, I have a table, let's call it item, which has a field containing a comma separated list of IDs of related items ( same table ). I know, this is far away from a correctly normalized structure and this stuff should be handled using another table for the link, but I'm working on a pre-existent environment and I would like not to change it. the question is, is there a way to retrieve the related items in the same order their ID are present on the field? I mean, having a 'related_items_ID' field containing '12,101,7,4', is there some workaround to have a returned recordset with record 12 on the first row, 101 on the second, and so on, , or must I handle this by code? thank you Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking for live connections
Is there a way of checking for live connections prior to using mysql_connect in the C API (asside from mysql_ping) Ruben -- __ Brooklyn Linux Solutions So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://fairuse.nylxs.com http://www.mrbrklyn.com - Consulting http://www.inns.net -- Happy Clients http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from around the net http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RETURNED MAIL: DATA FORMAT ERROR
Your message was undeliverable due to the following reason(s): Your message could not be delivered because the destination computer was not reachable within the allowed queue period. The amount of time a message is queued before it is returned depends on local configura- tion parameters. Most likely there is a network problem that prevented delivery, but it is also possible that the computer is turned off, or does not have a mail system running right now. Your message was not delivered within 8 days: Host 207.96.21.225 is not responding. The following recipients could not receive this message: [EMAIL PROTECTED] Please reply to [EMAIL PROTECTED] if you feel this message to be in error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ft_min_word_len=2... YAY or NAY?
John, give it a try. I had to enable ft_min_word_len=2 on a collection of legal texts because people are searching for abbreviations consisting of 2 letters. It works fine for me. Thomas Spahni On Mon, 13 Sep 2004 [EMAIL PROTECTED] wrote: I'm wondering whether or not I should implement fulltext searching for 2 letter words within a product database (HP, TV, G5, LG, etc)... My searchable text is currently about 600Mb and a 3-letter fulltext index is consuming about 420Mb. Is the trade-off for user convience vs. performance worth it? In general, how much will performance decrease? Or should I look for alternatives such as a 2-letter keyword table that I build on my own? Or do most of you just let 2-letter words slide? YAY or NAY? - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RETURNED MAIL: DATA FORMAT ERROR
What is this? On Tue, Sep 14, 2004 at 10:28:53AM +0200, [EMAIL PROTECTED] wrote: Your message was undeliverable due to the following reason(s): Your message could not be delivered because the destination computer was not reachable within the allowed queue period. The amount of time a message is queued before it is returned depends on local configura- tion parameters. Most likely there is a network problem that prevented delivery, but it is also possible that the computer is turned off, or does not have a mail system running right now. Your message was not delivered within 8 days: Host 207.96.21.225 is not responding. The following recipients could not receive this message: [EMAIL PROTECTED] Please reply to [EMAIL PROTECTED] if you feel this message to be in error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- __ Brooklyn Linux Solutions So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://fairuse.nylxs.com http://www.mrbrklyn.com - Consulting http://www.inns.net -- Happy Clients http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive or stories and articles from around the net http://www2.mrbrklyn.com/downtown.html - See the New Downtown Brooklyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Don't do an @usrDays, just stick the number in there so that the statement you create looks exactly like the one you tested with. If you get a number from a user from a form, just put that value into the string. For instance if the user enters 43 into a field on the first page, put 43 into the query. Let's imagine that you get the value 43 into a variable (off of the request) called $daterange... and let's imagine that you build your query into a variable called $query I think this is how you would build the correct query (my PHP is rusty): $query = SELECT DateEntry from Entry_table WHERE DateEntry = DATE_SUB(CURDATE(), INTERVAL . $daterange. day); See? Pretend you are a typist and BUILD the string as you want it executed. Then do it. I think this is bad advice, even for a novice like Stuart, because it is susceptible to SQL injection attacks. I don't know if PHP has prepared statements like Perl DBI; if not, $daterange should either be quoted or checked in PHP if it's really a number. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Not sure exactly what you mean by a SQL injection attack. I'm thinking a string could be input as opposed to an integer ? The form itself constricts user to a set of choices. Stuart --- Harald Fuchs [EMAIL PROTECTED] wrote: I think this is bad advice, even for a novice like Stuart, because it is susceptible to SQL injection attacks. I don't know if PHP has prepared statements like Perl DBI; if not, $daterange should either be quoted or checked in PHP if it's really a number. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[ANNOUNCE] Let's Party! (table partitioner) 0.1 released
Let's Party! provides any database with table partitioning capabilities (as long as it is accessed using JDBC). Why table partitioning? Because: 1. If you put your partitions on different hard disks you will gain throughput 2. Scans of tables will require less time, because you will access less data - a little table scan can be much faster than an access by index on a big table 3. You can put partitions you use less often on slower hard disks or on the network 4. (MySql) You can compress data on partitions you don't want to write to 5. You can put partitions on different clusters, for example using C-JDBC (beware: selecting from more than one partition from different clusters will not work because C-JDBC can't do unions between two clusters) 6. On future versions there will be the option to remove a column (part of an index) if this has low cardinality and replace it with different partitions Please have a look (http://letsparty.sourceforge.net/) and contact me using the forum (http://sourceforge.net/forum/forum.php?forum_id=382504) in case you need any help. Leonardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
In article [EMAIL PROTECTED], Stuart Felenstein [EMAIL PROTECTED] writes: Not sure exactly what you mean by a SQL injection attack. I'm thinking a string could be input as opposed to an integer ? Exactly - especially an SQL string. The form itself constricts user to a set of choices. This won't help at all. An attacker can construct a query with arbitrary form values. Never put unchecked/unquoted strings into an SQL query. I dunno PHP, but in Perl one would use something like my $sth = $dbh-prepare ( SELECT whatever FROM mytable WHERE date = now() - INTERVAL ? DAY ); $sth-execute ($numdays); In this case $numdays gets properly escaped by Perl - if it contains garbage, the query will fail, but it can't do dangerous things with your database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HelpPlease:Urgent
When I try and log in with the new password I get this error:
Error 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES).
The instructions that I followed required me to kill the mysqld process and
restart it with the --skip-grant-tables option and log into the root account
without a password. Then it told me to run this command:
UPDATE user SET Password=PASSWORD('mynewspecialpassword') WHERE User='root';
MySQL returned with it saying quary compleated 0 rows affected then I was
told to flush privlliges, which I did, and restart the mysqld process
normally. After trying all that it still didn't work.
Thanks and Regards
__
Biswa Ranjan Das(S.E.),
Development,
Olive e-Business Pvt. Ltd.,
B-1/H-2, Mohan Cooperative,
Mathura Road,New Delhi -110044
Ph : +91 11 26991100, 26991101, 26991102
Extn : 221
Fax : +91 11 26991110
Mobile : +91 9811344535
http://www.oliveglobal.com/
---
Information contained in this E-MAIL being proprietary to Olive e-Business
Pvt.Ltd. is 'privileged' and 'confidential' and intended for use only by the
individual or entity to which it is addressed. You are notified that any
use, copying or dissemination of the information contained in the E-MAIL in
any manner whatsoever is strictly prohibited.
- Original Message -
From: Stuart Felenstein [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, September 14, 2004 2:52 PM
Subject: Re: HelpPlease: Conditional loop confusion
Not sure exactly what you mean by a SQL injection
attack. I'm thinking a string could be input as
opposed to an integer ?
The form itself constricts user to a set of choices.
Stuart
--- Harald Fuchs [EMAIL PROTECTED] wrote:
I think this is bad advice, even for a novice like
Stuart, because it
is susceptible to SQL injection attacks. I don't
know if PHP has
prepared statements like Perl DBI; if not,
$daterange should either be
quoted or checked in PHP if it's really a number.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem bringing up mysql on RHEL
I just installed MySQL from the mysql-3.23.58-1.i386 RPM on a vigrin Red Hat Enterprise server. According to the manual, mysql_install_db gets run automatically when you do this, but I don't know what to look for as evidence that this has happened. If I try to run mysql_install_db by hand, I get the error: Didn't find /usr/libexec/mysqld You should do a 'make install' before executing this script There is no /etc/init.d/mysql and no mysqld_safe, so there seems to be no way to start the server: using service mysql start gives me unrecognized service. What am I missing? ///Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query bind-address
Hello, Tell us the OS, the MySQL server and client version and the build information (custom or binary downloaded from MySQL.com). Redhat Enterprise Linux 3, self-build mysql 4.0.20 (server and client). This *might* be a problem of resolver in glibc. I don't see a reason why it should be a resolver problem? The error messages says lost connection _during_ query, this means that the connection is already established, so obviously the resolver already did it's job to find out the ip-address of the mysql-server. And anyway: I used bind-address before and it worked. I just don't understand why it doesn't work again now. Mysql itself is starting correctly and the server log doesn't show anything. But the clients are losing the connection. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Retrieving data from sql file
Hello , I am having some 10 rows of data in my database table. If i dump the database the value retrieving is appending with some other row value also. For example If my one record contains the value as Hello..while dumping as a sql file it is storing as a Hello + from some other row contents. Can anyone pls suggest why this problem coming ? Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving data from sql file
Uma, the answer is 42 (or - probably - mysqldump --help | less). But what is your problem? Are you dumping in --extended-insert mode? Can you give us an example? Regards, Thomas On Tue, 14 Sep 2004, T UmaShankari wrote: Hello , I am having some 10 rows of data in my database table. If i dump the database the value retrieving is appending with some other row value also. For example If my one record contains the value as Hello..while dumping as a sql file it is storing as a Hello + from some other row contents. Can anyone pls suggest why this problem coming ? Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Field Type Problem
On Tue, 14 Sep 2004, Sheraz wrote: when I try following: CREATE TABLE `MyTable` ( `SNumber` char(32) NOT NULL, `UserID` char(32) NOT NULL default '0', PRIMARY KEY (`SNumber`) ) TYPE=MyISAM; It created the exactly correct table, But when i add any varchar field in the above sql..then generated table is like it has all the fields of type Varchar This behaviour is documented (although slightly annoying) here: http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another order by question
* Giulio I have a table, let's call it item, which has a field containing a comma separated list of IDs of related items ( same table ). I know, this is far away from a correctly normalized structure and this stuff should be handled using another table for the link, but I'm working on a pre-existent environment and I would like not to change it. the question is, is there a way to retrieve the related items in the same order their ID are present on the field? I mean, having a 'related_items_ID' field containing '12,101,7,4', is there some workaround to have a returned recordset with record 12 on the first row, 101 on the second, and so on, , or must I handle this by code? Try somthing like: ... ORDER BY FIND_IN_SET(related_items_ID,'12,101,7,4'); URL: http://dev.mysql.com/doc/mysql/en/String_functions.html#IDX1272 -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime as float
* Håkan Elmqvist Is there any MySQL built in function to convert datetime types to real numbers (float) i.e. seconds or days since a reference date? I can't find any in the manual. There are plenty of date and time manipulating functions available. Take a look at to_days() and unix_timestamp(), for instance: URL: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html They both return integers, though. Multiply by 1.0 to get a float ;) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqld segfaults irregulary on debian/opteron platform
Hi,
Our mysqld segfaults from time to time on our system. We don't see any
other programs segfaulting, and therefore suspect a bug in mysql.
I enclose a resolved stacktrace, and any other system information that
might be helpful. I hope someone has any suggestions on what to do in this
matter. Should we file a mysql bug-report?
Please advise. I am happy to provide any additional information that you
should find necesarry to resolve the issue.
Sincerely,
Anders Schau Knatten
Norway
OS: Debian GNU/Linux
Kernel: Linux 2.6.8-rc1 #4 SMP Fri Jul 16 18:03:51 CEST 2004 x86_64 unknown
CPU: Dual AMD Opteron 250
Resolved stack-trace:
0x80eb61f handle_segfault + 423
0x5567ff54 _end + 1294348216
0x832bd1a ft_init_boolean_search + 586
0x832bed7 ft_init_boolean_search + 1031
0x832c57d ft_boolean_find_relevance + 513
0x832c303 ft_boolean_read_next + 835
0x814a7cc ft_read__9ha_myisamPc + 52
0x812037f error_if_full_join__FP4JOIN + 1791
0x811a496 create_myisam_from_heap__FP3THDP8st_tableP15TMP_TABLE_PARAMib
+ 1870
0x811a256 create_myisam_from_heap__FP3THDP8st_tableP15TMP_TABLE_PARAMib
+ 1294
0x8112dd8
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 7000
0x811f963 handle_select__FP3THDP6st_lexP13select_result + 95
0x80f7d7e mysql_execute_command__Fv + 1010
0x80fb438 mysql_parse__FP3THDPcUi + 568
0x80f6ed1 dispatch_command__F19enum_server_commandP3THDPcUi + 1469
0x80fcadd do_command__FP3THD + 153
0x80f6187 handle_one_connection + 639
0x5567d0ba _end + 1294336286
0x55814d6a _end + 1296006606
Additional info from syslog:
Sep 14 03:14:05 mysqld[21855]: Cannot determine thread, fp=0xff3feb58,
backtrace may not be correct.
Sep 14 03:14:05 mysqld[21855]: Stack range sanity check OK, backtrace
follows:
(...)
Sep 14 03:14:05 mysqld[21855]: New value of fp=(nil) failed sanity check,
terminating stack trace!
(...)
Sep 14 03:14:05 mysqld[21855]: Trying to get some variables.
Sep 14 03:14:05 mysqld[21855]: Some pointers may be invalid and cause the
dump to abort...
Sep 14 03:14:05 mysqld[21855]: thd-query at 0x8599938 = SELECT t.tid^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^IFROM ippbe_topics t^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^IWHERE t.last_post
1092531929 AND t.forum_id IN
(27,190,89,11,12,13,14,15,16,17,18,19,20,23,24,25,26,28,87,30,31,32,33,35,37,38,40,41,46,44,48,51,53,
54,57,58,60,61,62,63,64,75,65,66,67,82,69,81,72,73,76,78,79,80,83,84,88,86,91,108,94,95,96,131,98,132,130,101,133,103,104,107,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,
128,129,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,156,155,157,158,159,162,161,160,184,189,179,178,182,181,175,169,172,171,174,177,176,185,183,180,187,188,191,19
2,193,200,194,195,196,197,198,199,203,202,209,206,208,210,212,211,213,215,214,216,217,219,218)^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^I AND t.approved=1 AND
MATCH(title) AGAINST ('resette' IN BOOLEAN MODE)
Sep 14 03:14:05 mysqld[21855]: thd-thread_id=127169
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
I agree and I am sorry I forgot a very basic security practice. You must always check any input from a user. Make sure that the user gives you a valid number and reject the request if it is anything out of your acceptable range or datatype. Otherwise a bad user could compromise your database or worse create havoc on your server. hf0722x, what I was trying to help correct was a basic misunderstanding of how to create dynamic SQL statements with PHP. Stuart is struggling to understand 3 environments at once (MySQL, PHP, and Dreamweaver) and I wasn't even thinking about input validation. Thanks for the catch. Shawn Green Database Administrator Unimin Corporation - Spruce Pine news [EMAIL PROTECTED] wrote on 09/14/2004 05:08:00 AM: In article OFE319C366.FA3FA287-ON85256F0E.006E3CA7-85256F0E. [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Don't do an @usrDays, just stick the number in there so that the statement you create looks exactly like the one you tested with. If you get a number from a user from a form, just put that value into the string. For instance if the user enters 43 into a field on the first page, put 43 into the query. Let's imagine that you get the value 43 into a variable (off of the request) called $daterange... and let's imagine that you build your query into a variable called $query I think this is how you would build the correct query (my PHP is rusty): $query = SELECT DateEntry from Entry_table WHERE DateEntry = DATE_SUB(CURDATE(), INTERVAL . $daterange. day); See? Pretend you are a typist and BUILD the string as you want it executed. Then do it. I think this is bad advice, even for a novice like Stuart, because it is susceptible to SQL injection attacks. I don't know if PHP has prepared statements like Perl DBI; if not, $daterange should either be quoted or checked in PHP if it's really a number. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is 8-byte address
Hi, I found the expression 8 bytes network address in MySQL Manual online manual. I know IPv4(4bytes), IPv6(16bytes). Please let me know what is 8 bytes network address. http://dev.mysql.com/doc/mysql/en/Miscellaneous_functions.html INET_ATON(expr) Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses. INET_NTOA(expr) Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string. Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World [EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html [EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqld segfaults irregulary on debian/opteron platform
Hi,
If you're using 4.0.20, jump to 4.0.21 :
Fixed crash in MATCH ... AGAINST() on a phrase search operator with a
missing closing double quote.
Regards,
Jocelyn
Hi,
Our mysqld segfaults from time to time on our system. We don't see any
other programs segfaulting, and therefore suspect a bug in mysql.
I enclose a resolved stacktrace, and any other system information that
might be helpful. I hope someone has any suggestions on what to do in
this
matter. Should we file a mysql bug-report?
Please advise. I am happy to provide any additional information that you
should find necesarry to resolve the issue.
Sincerely,
Anders Schau Knatten
Norway
OS: Debian GNU/Linux
Kernel: Linux 2.6.8-rc1 #4 SMP Fri Jul 16 18:03:51 CEST 2004 x86_64
unknown
CPU: Dual AMD Opteron 250
Resolved stack-trace:
0x80eb61f handle_segfault + 423
0x5567ff54 _end + 1294348216
0x832bd1a ft_init_boolean_search + 586
0x832bed7 ft_init_boolean_search + 1031
0x832c57d ft_boolean_find_relevance + 513
0x832c303 ft_boolean_read_next + 835
0x814a7cc ft_read__9ha_myisamPc + 52
0x812037f error_if_full_join__FP4JOIN + 1791
0x811a496 create_myisam_from_heap__FP3THDP8st_tableP15TMP_TABLE_PARAMib
+ 1870
0x811a256 create_myisam_from_heap__FP3THDP8st_tableP15TMP_TABLE_PARAMib
+ 1294
0x8112dd8
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UlP13select_result
+ 7000
0x811f963 handle_select__FP3THDP6st_lexP13select_result + 95
0x80f7d7e mysql_execute_command__Fv + 1010
0x80fb438 mysql_parse__FP3THDPcUi + 568
0x80f6ed1 dispatch_command__F19enum_server_commandP3THDPcUi + 1469
0x80fcadd do_command__FP3THD + 153
0x80f6187 handle_one_connection + 639
0x5567d0ba _end + 1294336286
0x55814d6a _end + 1296006606
Additional info from syslog:
Sep 14 03:14:05 mysqld[21855]: Cannot determine thread, fp=0xff3feb58,
backtrace may not be correct.
Sep 14 03:14:05 mysqld[21855]: Stack range sanity check OK, backtrace
follows:
(...)
Sep 14 03:14:05 mysqld[21855]: New value of fp=(nil) failed sanity
check,
terminating stack trace!
(...)
Sep 14 03:14:05 mysqld[21855]: Trying to get some variables.
Sep 14 03:14:05 mysqld[21855]: Some pointers may be invalid and
cause the
dump to abort...
Sep 14 03:14:05 mysqld[21855]: thd-query at 0x8599938 = SELECT t.tid^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^IFROM ippbe_topics t^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^IWHERE t.last_post
1092531929 AND t.forum_id IN
(27,190,89,11,12,13,14,15,16,17,18,19,20,23,24,25,26,28,87,30,31,32,33,35,37,38,40,41,46,44,48,51,53,
54,57,58,60,61,62,63,64,75,65,66,67,82,69,81,72,73,76,78,79,80,83,84,88,86,91,108,94,95,96,131,98,132,130,101,133,103,104,107,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,
128,129,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,156,155,157,158,159,162,161,160,184,189,179,178,182,181,175,169,172,171,174,177,176,185,183,180,187,188,191,19
2,193,200,194,195,196,197,198,199,203,202,209,206,208,210,212,211,213,215,214,216,217,219,218)^M
Sep 14 03:14:05 mysqld[21855]: ^I^I^I^I^I^I^I AND t.approved=1 AND
MATCH(title) AGAINST ('resette' IN BOOLEAN MODE)
Sep 14 03:14:05 mysqld[21855]: thd-thread_id=127169
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: I agree and I am sorry I forgot a very basic security practice. You must always check any input from a user. Make sure that the user gives you a valid number and reject the request if it is anything out of your acceptable range or datatype. Not necessarily - most of these problems can be solved by the DBMS itself as long as you give it a chance to do so. In general DBMSs are smart enough to figure out that WHERE id = '123' really should be a numeric comparison. This means that you just need to transform your parameters to valid strings in order to be on the safe side. Simply surrounding them by single quotes is _not_ enough - you must also properly escape embedded single quotes. Most APIs are able to do that for you (e.g. Perl: $dbh-quote()). Otherwise a bad user could compromise your database or worse create havoc on your server. Yes. hf0722x, what I was trying to help correct was a basic misunderstanding of how to create dynamic SQL statements with PHP. Stuart is struggling to understand 3 environments at once (MySQL, PHP, and Dreamweaver) and I wasn't even thinking about input validation. I know that my remark (as opposed to yours) was not helpful regarding Stuart's actual problems, but precisely because he is a newbie he should not get accustomed to dangerous practices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connections and Pooling
I recently had an issue where I was getting timeout waiting for connection and max pool size type errors. I finally was able to resolve the problem by setting pooling=false in the connection string. While I'm happy to have resolved the error, it made me realize that I have no idea what is going on with connections and pooling on mySQL, so I thought I'd ask the group here for some basic instruction on this issue, and maybe a good link or two to some site or page or page that explains it in detail. The manual either doesn't go into any detail or I am not searching with the appropriate keyword(s). I just need a little direction and some detailed information on how connections and pooling works. I'm using ASP.NET/VB.NET with CoreLab's MySQLDirect on a Windows 2003 IIS server with the latest windows version of MySQL, if that matters. TIA -Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
It's all good information. I need to be thinking security as well as generating proper efficient code. All is appreciated. Stuart Harald Fuchs [EMAIL PROTECTED] wrote: In article , [EMAIL PROTECTED] writes: I agree and I am sorry I forgot a very basic security practice. You must always check any input from a user. Make sure that the user gives you a valid number and reject the request if it is anything out of your acceptable range or datatype. Not necessarily - most of these problems can be solved by the DBMS itself as long as you give it a chance to do so. In general DBMSs are smart enough to figure out that WHERE id = '123' really should be a numeric comparison. This means that you just need to transform your parameters to valid strings in order to be on the safe side. Simply surrounding them by single quotes is _not_ enough - you must also properly escape embedded single quotes. Most APIs are able to do that for you (e.g. Perl: $dbh-quote()). Otherwise a bad user could compromise your database or worse create havoc on your server. Yes. hf0722x, what I was trying to help correct was a basic misunderstanding of how to create dynamic SQL statements with PHP. Stuart is struggling to understand 3 environments at once (MySQL, PHP, and Dreamweaver) and I wasn't even thinking about input validation. I know that my remark (as opposed to yours) was not helpful regarding Stuart's actual problems, but precisely because he is a newbie he should not get accustomed to dangerous practices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connections and Pooling
It matters greatly what software/drivers you are using to connect with. You need to first consult the documentation for CoreLab's MySQLDirect for details about whether it pools connections or not. The connections are pooled client-side, not server-side so I would start with that library. You may also want to glance through the .NET docs too. I don't know if MySQLDirect has it's own objects or if you just use it as you would an ODBC driver through the ADO framework. That could mean that you are getting an additional layer of assistance from the .NET/ODBC manager libraries. That assistance may also be pooling connections for you (in an attempt to help). Personally, I have had to sometimes dig very deep to find adequate documentation about some of Microsoft's assistance features in order to figure out how to either turn them on, turn them off, or learn how to live with their unruly behaviors. Good luck, sorry I wasn't more help... Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Demel [EMAIL PROTECTED] wrote on 09/14/2004 10:30:20 AM: I recently had an issue where I was getting timeout waiting for connection and max pool size type errors. I finally was able to resolve the problem by setting pooling=false in the connection string. While I'm happy to have resolved the error, it made me realize that I have no idea what is going on with connections and pooling on mySQL, so I thought I'd ask the group here for some basic instruction on this issue, and maybe a good link or two to some site or page or page that explains it in detail. The manual either doesn't go into any detail or I am not searching with the appropriate keyword(s). I just need a little direction and some detailed information on how connections and pooling works. I'm using ASP.NET/VB.NET with CoreLab's MySQLDirect on a Windows 2003 IIS server with the latest windows version of MySQL, if that matters. TIA -Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PERL Module installation
Hello: For the last couple of months I have been learning installing LINUX, MySQL opther software. Until last week I installed mysql-4.0.20 and then installed PERL modules without problem. Last week the new MySQL verion became available I started installing it. Now when I install PERL modules, I get the following error: % perl -MCPAN -e shell Note: Answer no to auto-configure perl. cpan install Bundle::CPAN cpan install Data::Dumper cpan install Bundle::DBI cpan install Bundle::DBD::mysql After I enter the last/above cpan command, there are number of output lines then this error: --- Using DBI 1.43 (for perl 5.08 on i386-linux-thread-multi) installed /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/auto/DBI Writing Makefile for DBD::mysql Makefile:89: *** missing seprator. Stop /usr/bin/make --NOT OK Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible Running install for module DBD::mysql Running make for R/RU/RUDY/DBD-mysql-2.9004.tar.gz is already unrapped into directory /root/.cpan/build/DBD-mysql-2.9004 Has already been processed within this session Running make test Can't test without successful make Running make install make had return bad status, install seems impossible Bundle summary: The following items in bundle Bundle::DBD::mysql had installation problems: Mysql DBD::mysql cpan --- I have posted this message on the PERL list with no resulution. Is it possible that the problem is somewhere in the new MySQL Version mysql-4.0.21? Is there a change from mysql-4.0.20 to mysql-4.0.21 which is causing the problem? Has anyone installed PERL modules on mysql-4.0.21 if yes please email the commands sequence (just like the one I have listed above)you used to install PERL modules? Thanks. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update/join question..
hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. searching through mysql.com/google hasn't shed much light!! any ideas/comments/asssistance/thoughts/etc.. thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL speed
Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? That I would find very strange because I wrote several Oracle based server applications with huge queries much more complicated as the one I use here, which take up tot at most 0.1 seconds. Those Oracle tables have millions of records and the full database dump (not sql dump) is 18GB! And I really don't think that Oracle is that much smarter than MySQL. Can anyone tell me what's going on? Are there MySQL parameters that can improve things? Or is MySQL really that slow? I won't believe the last one... I tested these queries on a MySQL 3.3 and a MySQL 4.0 database, all with the same speed-result. Regards, Dirk. -- Schippers Dirk Zaakvoerder Frixx-iT http://www.frixx-it.com Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which MySQL product should I choose for a comparison?
Hello I am doing a technical comparison between two products of which one is MySQL. But I don't exactly know which product that MySQL offers that I should pick. I am thinking of MaxDB, because it's supposed to be the most feature rice, but it does not seem to be MySQL but another product owned by other people. I am a bit confused. I appreciate any help. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
Dirk, Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? Just some ideas for you. 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. Regards, - Csongor Fagyal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql goes down when executing query
Hi, Sorry to disturb but Mysql 4.1.4 gamma goes down when executing this query. I've tryed the same query without the coalesce function and the problem persists. select coalesce(viehc,0), coalesce(vieapellido,0), coalesce(vienombres,0), coalesce(viedoc,0), coalesce(numero,0), coalesce(apellido,0), coalesce(nombres,0), coalesce(f_nacimiento,0), coalesce(sexo,0), coalesce(doc_numero,0) from zzg_int.compara into outfile /tmp/compa.txt fields terminated by ',' lines terminated by '\r\n'; This is what the error log shows. Version: '4.1.4-gamma-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=13 max_connections=100 threads_connected=10 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x4b22efb8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfddeb68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808a183 0x82d3cb8 0x80ae46f 0x809d894 0x8097e4f 0x80977d8 0x8096f17 0x82d146c 0x82fa9fa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x86da708 = EXPLAIN select coalesce(viehc,0),coalesce(vieapellido,0),coalesce(vienombres,0),coalesce(viedoc,0),coalesce(numero,0), coalesce(apellido,0),coalesce(nombres,0),coalesce(f_nacimiento,0),coalesce(sexo,0),coalesce(doc_numero,0) from hrrg_int.compara into outfile /tmp/compa.txt fields terminated by ',' lines terminated by '\r\n' thd-thread_id=632 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 040914 13:15:00 mysqld restarted 040914 13:15:00 [ERROR] Warning: Asked for 196608 thread stack, but got 126976 040914 13:15:00 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 040914 13:15:00 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 281648573. InnoDB: Doing recovery: scanned up to log sequence number 0 281648583 InnoDB: Last MySQL binlog file position 0 79779, file name ./hrrgp01-bin.05 040914 13:15:00 InnoDB: Flushing modified pages from the buffer pool... 040914 13:15:00 InnoDB: Started; log sequence number 0 281648583 /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.1.4-gamma-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: errno: 144
Run myisamchk on the host file if you can. 95% full is not good. Thank's for you're help, it work fine now :) Diego -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
In the last episode (Sep 14), Dirk Schippers said: For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. That's up to 2500 random disk seeks, and even the fastest SCSI disks do only 300 seeks/sec (ATA disks max at ~150). Best case is where all the records are in memory and it doesn't have to hit the disk at all, but depending on how many tables you have and your RAM, that may not always be true. Try creating a multicolumn index on (touser,hidden), which will let mysql process the query without doing any record fetches at all. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Bulk] Re: MySQL speed
You have not mentioned the use of a multiple column index, for that matter, no indexes at all. I would suggest a multiple column index in the form of hidden, touser. That index form should speed up your queries and solve the problem. Mike - Original Message - From: Fagyal Csongor [EMAIL PROTECTED] To: Dirk Schippers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: September 14, 2004 12:24 PM Subject: [Bulk] Re: MySQL speed Dirk, Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? Just some ideas for you. 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. Regards, - Csongor Fagyal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL speed
Why not just add an index on touser+hidden. Problem solved. Donny -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:48 AM To: Dirk Schippers Cc: [EMAIL PROTECTED] Subject: Re: MySQL speed In the last episode (Sep 14), Dirk Schippers said: For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. That's up to 2500 random disk seeks, and even the fastest SCSI disks do only 300 seeks/sec (ATA disks max at ~150). Best case is where all the records are in memory and it doesn't have to hit the disk at all, but depending on how many tables you have and your RAM, that may not always be true. Try creating a multicolumn index on (touser,hidden), which will let mysql process the query without doing any record fetches at all. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
Can anyone tell me what's going on? Are there MySQL parameters that can improve things? To know if you need to tune something, you can send us the copy of SHOW STATUS; and SHOW VARIABLES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to download MySQL
In trying to download the mysql Linux x86 RPM server rpm download from http://mysql.mirrors.ilisys.com.au/Downloads/MySQL-4.0/MySQL-server-4.0.21-0.i386.rpm after an hour or so downloading it, I ended up with an error message; Plugger: no approperiate (sic) application for type audio/x-pn-realaudio-plugin found! Is that really a valid MySQL download web site, as listed on the web page at http://dev.mysql.com/get/Downloads/MySQL-4.0/MySQL-server-4.0.21-0.i386.rpm/from/pick ? Why does MySQL need a realaudio plugin? -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying rows based on query result to another table?
I want to copy rows of data from a query result to another table. How do I
accomplish this? Here's my example for further explanation...
I have 2 tables, candidate and resume. I have 2 other (currently empty)
tables, exact duplicates, candidate_erp and resume_erp. In resume_erp will
be duplicates from resume that contain 'ERP' (Enterprise Resource Planning)
keywords, ie. BAAN, SAP, Peoplesoft, and more. In candidate_erp will be the
associated 'candidate' rows. When I construct my SQL query and run it, I can
successfully view the resulting query which contain the keyword BAAN. How
can I then copy those results into candidate_erp?
A query example...
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
I notice that MySQL CC and MySQL Query Browser both have the ability to
export to a file (ie. CSV), but it doesn't appear they will import neatly
into the tables candidate_erp and resume_erp.
Thanks in advance,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to download MySQL
I would right click on the link and choose to save target... do not left click such that it would try to open it. as rpm could also be used by RealPlayer/RealOne for their media files. On Wed, 15 Sep 2004 01:21:55 +0800, Bret Busby [EMAIL PROTECTED] wrote: In trying to download the mysql Linux x86 RPM server rpm download from http://mysql.mirrors.ilisys.com.au/Downloads/MySQL-4.0/MySQL-server-4.0.21-0.i386.rpm after an hour or so downloading it, I ended up with an error message; Plugger: no approperiate (sic) application for type audio/x-pn-realaudio-plugin found! Is that really a valid MySQL download web site, as listed on the web page at http://dev.mysql.com/get/Downloads/MySQL-4.0/MySQL-server-4.0.21-0.i386.rpm/from/pick ? Why does MySQL need a realaudio plugin? -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to download MySQL (fwd)
Does the message below, mean that everyone who posts a message to this list, will receive a message such as the one below, from the person below, for the next week? -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 -- Forwarded message -- Date: 14 Sep 2004 17:14:04 - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Unable to download MySQL Sorry, but I am out of the office on Monday 13th September until Monday 20th September 2004. I will respond to your e-mail when I return If you need technical support please contact [EMAIL PROTECTED] or [EMAIL PROTECTED] For any other enquiries please call the office on (0113) 272 3191 Thanks for your e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update/join question..
- Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told to you wherever you learned basic relational concepts. If so, that is assuming that everyone working with relational databases had some exposure to theory first; I think that's a very dubious assumption. any ideas/comments/asssistance/thoughts/etc.. There ought to be a clear error message every time you try to update, insert, or delete from a join that says these operations cannot be done on joins. The manuals for every relational database should state that Insert, Update, and Delete do not work on joins, both in the reference section for the Insert, Update, and Delete statements and in the concepts section. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to download MySQL
Bret Busby wrote: In trying to download the mysql Linux x86 RPM server rpm download from http://mysql.mirrors.ilisys.com.au/Downloads/MySQL-4.0/MySQL-server-4.0.21-0.i386.rpm after an hour or so downloading it, I ended up with an error message; Plugger: no approperiate (sic) application for type audio/x-pn-realaudio-plugin found! Is that really a valid MySQL download web site, as listed on the web page at http://dev.mysql.com/get/Downloads/MySQL-4.0/MySQL-server-4.0.21-0.i386.rpm/from/pick ? Why does MySQL need a realaudio plugin? Your browser file association is misconfigured . .rp files are realplayer files. .rpm files are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copying rows based on query result to another table?
try
INSERT INTO other_table
( field1, field2, etc)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
Peter
-Original Message-
From: Eve Atley [mailto:[EMAIL PROTECTED]
Sent: 14 September 2004 18:18
To: Mysql
Subject: Copying rows based on query result to another table?
I want to copy rows of data from a query result to another table. How do I
accomplish this? Here's my example for further explanation...
I have 2 tables, candidate and resume. I have 2 other (currently empty)
tables, exact duplicates, candidate_erp and resume_erp. In resume_erp will
be duplicates from resume that contain 'ERP' (Enterprise Resource
Planning)
keywords, ie. BAAN, SAP, Peoplesoft, and more. In candidate_erp
will be the
associated 'candidate' rows. When I construct my SQL query and
run it, I can
successfully view the resulting query which contain the keyword BAAN. How
can I then copy those results into candidate_erp?
A query example...
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value)
AGAINST ('+BAAN' IN BOOLEAN MODE)
AND c.Candidate_ID = r.Candidate_ID
I notice that MySQL CC and MySQL Query Browser both have the ability to
export to a file (ie. CSV), but it doesn't appear they will import neatly
into the tables candidate_erp and resume_erp.
Thanks in advance,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AutoResponders (was: Unable to download MySQL (fwd))
Does the message below, mean that everyone who posts a message to this list, will receive a message such as the one below, from the person below, for the next week? Yes. Great isn't it. Those people should be smacked in the head. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Subject: Re: Unable to download MySQL Sorry, but I am out of the office on Monday 13th September until Monday 20th September 2004. I will respond to your e-mail when I return If you need technical support please contact [EMAIL PROTECTED] or [EMAIL PROTECTED] For any other enquiries please call the office on (0113) 272 3191 Thanks for your e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update/join question..
Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told to
Re: update/join question..
Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just assumed it would be told to
Re: Unable to download MySQL (fwd)
On Wed, Sep 15, 2004 at 01:33:08AM +0800, Bret Busby wrote: Does the message below, mean that everyone who posts a message to this list, will receive a message such as the one below, from the person below, for the next week? Not any more, because he's been unsubscribed from all MySQL mailing lists. When you receive such emails, you can forward them to [EMAIL PROTECTED] and we'll take care of it. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext doesn't seem to find certain text
If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 -- Joe McGuckin ViaNet Communications 994 San Antonio Road Palo Alto, CA 94303 Phone: 650-213-1302 Cell: 650-207-0372 Fax: 650-969-2124 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HelpPlease: Conditional loop confusion
Shawn, Just wanted to let you know your help was appreciated. I'm close to getting it straight. Away from my console for the day so I won't know till later. However, while I did figure out the correct variable and query name, there is about 20 other lines of code that needed adjusting to make it work :). The generated code can be daunting, especially when it's not commented or documented. Lots of activity! Anyway, I'm now reading up on PHP, and while I'll continue using code generators where it makes sense, at least I'll have a better approach when necessary for manual editing. In regards to the other thread about checking the input, the code seems to be written well with lots of checks and guards for added security. Stuart [EMAIL PROTECTED] wrote: Don't do an @usrDays, just stick the number in there so that the statement you create looks exactly like the one you tested with. If you get a number from a user from a form, just put that value into the string. For instance if the user enters 43 into a field on the first page, put 43 into the query. Let's imagine that you get the value 43 into a variable (off of the request) called $daterange... and let's imagine that you build your query into a variable called $query I think this is how you would build the correct query (my PHP is rusty): $query = SELECT DateEntry from Entry_table WHERE DateEntry = DATE_SUB(CURDATE(), INTERVAL . $daterange. day); See? Pretend you are a typist and BUILD the string as you want it executed. Then do it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein wrote on 09/13/2004 03:35:35 PM: Shawn - You answered the right question. Since a SQL statement is closer to my grasp right now , today, trying that approach. I think this is something close to correct, though I'm stuck on what to assign to @usrDays:= SELECT DateEntry from Entry_Table, WHERE DateEntry = DATE_SUB(CurDate(), Interval @usrDays:= day ) I'll continue to bang away , eventually I should get it. Stuart --- [EMAIL PROTECTED] wrote: Stuart, I feel you pains. I think I can speak for most of us to say we've been there, too First, You want to try to keep your column values on the LEFT side of any inequality. MySQL just goes faster that way. SELECT WHERE EntryDate = DATESUB(CurDate(), Interval 2 day ) About using a variable for your date value, you have two options. First, you could just modify your PHP code to stick a number into the correct place of your SQL statement: $querystring = SELECT WHERE EntryDate = DATESUB(CurDate(), Interval . $daysdiff . day ) Or you can create SQL statement that sets a MySQL variable that contains the # of days you want to subtract then use that variable in your WHERE clause. Either way you are combining the value you get from the user-entry field and using it to create a valid SQL statement. Personally, I would do the first. (my logic: I don't need the variable except to use it in the very next statement and if I have to merge values and text once, anyway, why not just put the correct value into my original query) Please let me know if I answered the wrong question, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein wrote on 09/13/2004 11:54:02 AM: Shawn, partially discourage but mostly confused about what I really need to do. Bear in mind for a moment, that I'm working in a somewhat untradional manner. Meaning a) very new to database and SQL b) (and i realize this list is not for programming languages) using a RAD that allows me to point, click and insert , where it's magically transformed into code. So back to what I'm trying to accomplish. I want a way for the user to be allowed to choose from what point in the timeline records will be returned in a search. i.e. from 30 days or less, 15 days or less, 2 days or less, etc. Now, if I run this query against the entry_table.entry_date_column select EntryDate from EntryTable where Date_Sub(Curdate(), interval 2 day) = EntryDate life is good. I get the records returned just the way I want. They key though is the 2 (after interval) as I want that number to be a variable. That is why I created the table that has values of 1, 2 , 15, 30, etc). I thought if I could use those values, in either a case statement (probably not with 4.0.20) or an if statement , well magic would happen ;) I hope I explained it better this time. Apologies for the confusion. Stuart --- [EMAIL PROTECTED] wrote: Were you able to solve you problem another way or just discouraged by my response? I wasn't trying to make you feel bad, you just covered so much ground in your original post that I really
Re: AutoResponders (was: Unable to download MySQL (fwd))
If you are going to be out of the office... suspend you subscription to the mailing list... so that everyone does not recieve these notices please On Tue, 14 Sep 2004 19:43:29 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Does the message below, mean that everyone who posts a message to this list, will receive a message such as the one below, from the person below, for the next week? Yes. Great isn't it. Those people should be smacked in the head. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Subject: Re: Unable to download MySQL Sorry, but I am out of the office on Monday 13th September until Monday 20th September 2004. I will respond to your e-mail when I return If you need technical support please contact [EMAIL PROTECTED] or [EMAIL PROTECTED] For any other enquiries please call the office on (0113) 272 3191 Thanks for your e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext doesn't seem to find certain text
That might be a word defined in the stopword file, excluding full text lookup on. Stop words are words like The As In If And so on. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: joe mcguckin [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 10:55 AM To: [EMAIL PROTECTED] Subject: Fulltext doesn't seem to find certain text If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 -- Joe McGuckin ViaNet Communications 994 San Antonio Road Palo Alto, CA 94303 Phone: 650-213-1302 Cell: 650-207-0372 Fax: 650-969-2124 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AutoResponders (was: Unable to download MySQL (fwd))
You might not have to unsubscribe. Some mail apps, like Notes, will let you configure your Out of Office agent to NOT send an automated reply to specific incoming emails. I've done this using Notes, and it works quite well. Nobody on list wants to know that you're on holiday in Hawaii while we're still working. ;-P -James On Sep 14, 2004, at 2:58 PM, GH wrote: If you are going to be out of the office... suspend you subscription to the mailing list... so that everyone does not recieve these notices please On Tue, 14 Sep 2004 19:43:29 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Does the message below, mean that everyone who posts a message to this list, will receive a message such as the one below, from the person below, for the next week? Yes. Great isn't it. Those people should be smacked in the head. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com Subject: Re: Unable to download MySQL Sorry, but I am out of the office on Monday 13th September until Monday 20th September 2004. I will respond to your e-mail when I return If you need technical support please contact [EMAIL PROTECTED] or [EMAIL PROTECTED] For any other enquiries please call the office on (0113) 272 3191 Thanks for your e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Mon, Sep 06, 2004 at 12:48:37PM -0700, Miles Keaton wrote: If my company wants to get the best-performing fastest platform for a MySQL server, what would it be these days? Opteron? Dual? Quad? And on a related note... If a 64-bit CPU, then I'm assuming it would need an operating system designed for that 64-bit CPU, to get best performance, right? I know that OpenBSD has an amd64 version and that the OpenBSD developers seem to say that Opteron is their favorite (and most-currently-developed) CPU. I've used OpenBSD in the past and like it a lot. Is anyone here using MySQL on OpenBSD+Opteron in a high-load situation? MySQL works quite well on Opteron machines. However, OpenBSD is a poor platform choice for running MySQL. It's known to run much better on FreeBSD or Linux (depending on your particular preference). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
A guess, its looking for that string. Maybe should be looking something like it example select * from table where var like '%foo%' -Blake joe mcguckin wrote: If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 -- +-+-++ | Blake Schroeder | Owner/Developer |lhwd.net| +--(http://www.lhwd.net)+--/ \--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. Donny -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:11 PM To: Miles Keaton Cc: [EMAIL PROTECTED] Subject: Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? On Mon, Sep 06, 2004 at 12:48:37PM -0700, Miles Keaton wrote: If my company wants to get the best-performing fastest platform for a MySQL server, what would it be these days? Opteron? Dual? Quad? And on a related note... If a 64-bit CPU, then I'm assuming it would need an operating system designed for that 64-bit CPU, to get best performance, right? I know that OpenBSD has an amd64 version and that the OpenBSD developers seem to say that Opteron is their favorite (and most-currently-developed) CPU. I've used OpenBSD in the past and like it a lot. Is anyone here using MySQL on OpenBSD+Opteron in a high-load situation? MySQL works quite well on Opteron machines. However, OpenBSD is a poor platform choice for running MySQL. It's known to run much better on FreeBSD or Linux (depending on your particular preference). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT on duplicate UPDATE?
Hi, I can vaguely remember there was something like INSERT... on duplicate key UPDATE... in MySQL, but the documentation search is almost as useful as I'm used to - it cannot tell me anything about this. Can you please? How does this work, what's the syntax? Is this ANSI-SQL standard (or compatible to other DBMS)? Or is it even documented? -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
I think what you mean is REPLACE. Try http://dev.mysql.com/doc/mysql/en/REPLACE.html Yves Goergen wrote: Hi, I can vaguely remember there was something like INSERT... on duplicate key UPDATE... in MySQL, but the documentation search is almost as useful as I'm used to - it cannot tell me anything about this. Can you please? How does this work, what's the syntax? Is this ANSI-SQL standard (or compatible to other DBMS)? Or is it even documented? -- Schippers Dirk Zaakvoerder Frixx-iT http://www.frixx-it.com Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
On Tue, 14 Sep 2004, joe mcguckin wrote: If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. In addition to the other suggestions, make sure our ft_min_word_len isn't more than 2, because it won't index words shorter than that many characters. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
At 01:54 PM 9/14/2004, you wrote: If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 Joe, By default MySQL has a 4 letter minimum for fulltext searches; words must be at least 4 letters in length otherwise they won't get added. Since you are using MySQL 3.23, you will need to recompile the binaries. With MySQL 4.x, you can simply change a variable in the configuration file and rebuild the fulltext indexes. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
Yves Goergen wrote: Hi, I can vaguely remember there was something like INSERT... on duplicate key UPDATE... in MySQL, but the documentation search is almost as useful as I'm used to - it cannot tell me anything about this. Can you please? How does this work, what's the syntax? Is this ANSI-SQL standard (or compatible to other DBMS)? Or is it even documented? Its described in the INSERT syntax, and is available as of v4.1.1. http://dev.mysql.com/doc/mysql/en/INSERT.html M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
mos wrote: At 01:54 PM 9/14/2004, you wrote: If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 Joe, By default MySQL has a 4 letter minimum for fulltext searches; words must be at least 4 letters in length otherwise they won't get added. Since you are using MySQL 3.23, you will need to recompile the binaries. With MySQL 4.x, you can simply change a variable in the configuration file and rebuild the fulltext indexes. Mike What variable setting is changed in the my.cnf, and is this under the mysqld group ?? What is the proceedure to rebuild the FT indexes and the changes are made ?? TIA -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ Tel: 1.712.395.0670 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
On Tue, 14 Sep 2004, Yves Goergen wrote: Hi, I can vaguely remember there was something like INSERT... on duplicate key UPDATE... in MySQL, but the documentation search is almost as useful as I'm used to - it cannot tell me anything about this. Can you please? How does this work, what's the syntax? Is this ANSI-SQL standard (or compatible to other DBMS)? Or is it even documented? It's covered on the INSERT page: http://dev.mysql.com/doc/mysql/en/INSERT.html If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and already contains the value 1, the following two statements have identical effect: mysql INSERT INTO table (a,b,c) VALUES (1,2,3) - ON DUPLICATE KEY UPDATE c=c+1; mysql UPDATE table SET c=c+1 WHERE a=1; Note: If column b is unique too, the INSERT would be equivalent to this UPDATE statement instead: mysql UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; If a=1 OR b=2 matches several rows, only one row is updated! In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple UNIQUE keys. As of MySQL 4.1.1, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT part of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted if no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example: mysql INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) - ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); That statement is identical to the following two statements: mysql INSERT INTO table (a,b,c) VALUES (1,2,3) - ON DUPLICATE KEY UPDATE c=3; mysql INSERT INTO table (a,b,c) VALUES (4,5,6) - ON DUPLICATE KEY UPDATE c=9; When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
Ops, replied to you only... On 14.09.2004 22:21 (+0200), Dirk Schippers wrote: I think what you mean is REPLACE. Try http://dev.mysql.com/doc/mysql/en/REPLACE.html No, REPLACE *always* replaces the entire row, but I want to update a single value only, and if the row doesn'T exist, it should be created with initial values. But there's a comment on that page that tells that INSERT ... ON DUPLICATE KEY UPDATE is new in mysql 4.1.0. Hm, too bad. OK, I'll have to work with 2 queries then, SELECT and then INSERT or UPDATE. -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
On 14.09.2004 22:44 (+0200), Tobias Asplund wrote: It's covered on the INSERT page: http://dev.mysql.com/doc/mysql/en/INSERT.html If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and (...) Mmh, yes, thank you. There's just too much text. Anyway, v4.1 is too late... I need v4.0 or better still 3.x support for this. -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
Do you guys have metrics on this that you would be willing to share? We are looking at upgrading to the Opteron (from the Xeon) at the moment. Any information would be very helpful. Brian Abbott -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:21 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Miles Keaton' Subject: Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
Also, out of curiosity, who was your vendor for the opteron? Brian -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:21 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Miles Keaton' Subject: Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
At 03:43 PM 9/14/2004, Mike Blezien wrote: mos wrote: At 01:54 PM 9/14/2004, you wrote: If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 Joe, By default MySQL has a 4 letter minimum for fulltext searches; words must be at least 4 letters in length otherwise they won't get added. Since you are using MySQL 3.23, you will need to recompile the binaries. With MySQL 4.x, you can simply change a variable in the configuration file and rebuild the fulltext indexes. Mike What variable setting is changed in the my.cnf, and is this under the mysqld group ?? What is the proceedure to rebuild the FT indexes and the changes are made ?? TIA Mike, See http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html. Also see Attila Nagy's recommendation on using Drop Index/Build Index rather than Repair Table. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
(Hi all, I'm a friend of Dirk and helping him trying to speed up everything) Fagyal Csongor wrote: Dirk Schippers wrote: And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. We're using Debian stable, which means MySQL 3.23. I like to use the packages and versions that come with Debian, but if necessary we'll look into upgrading. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. That's not how it works: it's not the users that are hidden; only some of their messages are hidden. Anyway, I'm confused too... I have a copy of a week-old version of Dirk's database on my home machine. When I run the same query, it takes between 0.00 and 0.02 seconds, on a machine that is in more or less the same performance category as Dirk's server. There is a world of difference between that and the 1 seconds Dirk is gettig. So it seems there is something wrong somewhere. I checked CPU and memory usage on the server, but everything looks pretty normal. Now I'm guessing there's something wrong with the MySQL configuration, but I don't know enough about MySQL to know what it could be. Any pointers in the right direction would be greatly appreciated. -- Codito ergo sum Roel Schroeven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
On Tue, Sep 14, 2004 at 02:31:54PM -0700, Brian Abbott wrote: Do you guys have metrics on this that you would be willing to share? We are looking at upgrading to the Opteron (from the Xeon) at the moment. Any information would be very helpful. I don't have any yet but should in a week or two. I just haven't run any benchmarks... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
MySQL released this a few weeks ago. http://www.mysql.com/news-and-events/press-release/release_2004_27.html As far as personal benchmarks, it's fast. Real fast. With a quad Xeon (which was more expensive than the quad opteron) our master server had a normal load of 2-3. With the the quad opteron it's less than .25. Donny -Original Message- From: Brian Abbott [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 4:32 PM To: [EMAIL PROTECTED]; 'Donny Simonton' Cc: 'Miles Keaton' Subject: RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? Do you guys have metrics on this that you would be willing to share? We are looking at upgrading to the Opteron (from the Xeon) at the moment. Any information would be very helpful. Brian Abbott -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:21 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Miles Keaton' Subject: Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
Tobias Asplund wrote: Can anyone tell me what's going on? Are there MySQL parameters that can improve things? To know if you need to tune something, you can send us the copy of SHOW STATUS; and SHOW VARIABLES; What is preferred in this mailing list: the whole texts in the mail, or as attachments? -- Codito ergo sum Roel Schroeven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
Friendster has for lack of better words HAVE A LOT of Opterons. In fact we have become experts with configuring what would be best with mySQL + Linux + opterons. I have a ton of benchmarks for various kernels and configs on local disk and or SAN configs. Just to give you some really basic stats we do more then 1 billion queries per day on less servers then Live Journal (source from mySQL conference). If I get permission from my bosses to release the benchmarks I will send it to the list. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Donny Simonton [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:15 PM To: 'Brian Abbott'; [EMAIL PROTECTED] Cc: 'Miles Keaton' Subject: RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? MySQL released this a few weeks ago. http://www.mysql.com/news-and-events/press-release/release_2004_27.html As far as personal benchmarks, it's fast. Real fast. With a quad Xeon (which was more expensive than the quad opteron) our master server had a normal load of 2-3. With the the quad opteron it's less than .25. Donny -Original Message- From: Brian Abbott [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 4:32 PM To: [EMAIL PROTECTED]; 'Donny Simonton' Cc: 'Miles Keaton' Subject: RE: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? Do you guys have metrics on this that you would be willing to share? We are looking at upgrading to the Opteron (from the Xeon) at the moment. Any information would be very helpful. Brian Abbott -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:21 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Miles Keaton' Subject: Re: best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE? On Tue, Sep 14, 2004 at 03:05:07PM -0500, Donny Simonton wrote: I can verify that a quad opteron 2.2 runs about a million times better than a quad xeon 3.06. The opteron can handle more than 3 gigs of memory which is a 32 bit limitation. Right now in my quad opteron we have 32 gigs of memory and MySQL is using 16.8 gigs of the memory. We run fedora core 2, with the rpm built by MySQL. We don't run anything else any longer. And we've had good but limited experiences so far with 64 bit FreeBSD 5 on amd64 (also a quad w/32GB). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update/join question..
pray, that you use mysql version =4 in version 3 it doesnt work... Oliver On Tue, 14 Sep 2004 14:09:43 -0400, [EMAIL PROTECTED] wrote: Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set l1.process = '1', l1.status = '13'; i've tried a number of derivatives of this approach with no luck.. i'm missing something simple.. You haven't explained what you meant by with no luck; did the updates fail with an error message or without a message? If there was a message, what did it say? I'm guessing that they failed with an error message but that the message was cryptic I'm not overly fluent with MySQL yet but in 20 years of using DB2, I've always been told that you can't update a join, you can only update an individual table. I assume that is true of all relational databases, including MySQL, but I don't know that for an absolute fact. searching through mysql.com/google hasn't shed much light!! Perhaps because this is such a fundamental concept that no one thought it worth putting in a manual; they just
slow join
MySQL 3.23.58:
ive got 2 tables with ca. 3 entrys each
now i wanna join, if one entry is in the one table, which isnt in the
other one
so this will be a normal join:
Tabelle1:
Artikelnummer, blah, blah, blah
Tabelle2:
Artikelnummer, blah, blah, blah
select Tabelle1.Artikelnummer from Tabelle1 LEFT JOIN Tabelle2 ON
Tabellle1.Artikelnummer=Tabelle2.Artikelnummer where
Tabelle2.Artikelnummer is null
now i have the problem that this join on my Xeon 2.4GHz with
Serial-ATA-disks after 1 1/2 hours isnt ready?!!?
a test with 2 tables with ca. 1000 entries each gave a response after only
2 seconds.
now... can someone tell me... isnt it faster possible???
i am at least faster with some b*lls**t like...
$query = select artikelnummer from tabelle1;
while ($row = mysql_fetch_array($result)) {
$query = select artikelnummer from tabelle2 where
artikelnummer='.$row['artikelnummer'].';
if (mysql_fetch_array(mysql_query($query))) echo $row['artikelnummer']
}
Oliver
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: update/join question..
Sorry, I meant to send this reply to the whole mailing list, not just to Shawn. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:18 PM Subject: Re: update/join question.. Okay, I stand corrected. Apparently, MySQL allows updates of joins. My apologies, I didn't mean to steer anyone down the wrong path. Thanks for setting me straight, Shawn! Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 2:07 PM Subject: Re: update/join question.. Rhino, I hate to burst your bubble but JOINS __are allowed__ in MySQL updates. I use them all the time. http://dev.mysql.com/doc/mysql/en/UPDATE.html At the top of the page are the syntaxes for single-table and multiple-table updates: The key here is that whatever you put _between_ the words FROM and WHERE in a normal query will be what you need to UPDATE. (I personally discourage anyone from using the comma join method of declaring table joins so I won't use it in my examples. It's a valid syntax but if you forget to put the right comparisons into your WHERE clause, you end up with problems. It's even easier with using JOINS in an UPDATE as the WHERE clause is even farther away from the tables) This query will give me a list of all of the companies who have contracts managed by manager 15: SELECT DISTINCT t1.* FROM Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_id WHERE t2.Manager_ID = 15 Imagine a situation where you needed to update contract.ManagerID with a new manager (Manager 15 was promoted) but you are not reassigning ALL of the contract accounts to the same person. You want to split it up so that companies A-L go to manger 12 and companies M-Z to manager 34. You *CAN* do this in a single statement but that would confuse my example. I will do the reassignments in two statements so that you can see the pattern better: UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 12 WHERE t2.Manager_ID = 15 AND t1.Name 'm'; UPDATE Company t1 INNER JOIN Contract t2 on t1.id = t2.Company_ID SET t2.Manager_Id = 34 WHERE t2.Manager_ID = 15 AND t1.Name = 'm'; The WHERE clause didn't need to change but what came after the FROM in a query has been moved to right after the UPDATE. The SET clause can assign values to or get values from ANY column from any of the tables participating in the UPDATE clause. Let me get back on topic... Bruce, you started with this SELECT: SELECT u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status FROM university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID WHERE u1.universityID='40'; You should be able to transform this query into: UPDATE university_urlTBL as u1 RIGHT JOIN parsefileTBL as p1 on u1.ID =p1.university_urlID INNER JOIN latestParseStatusTBL as l1 on p1.fileID = l1.itemID SET WHERE ... Your SET and WHERE clauses can refer to any column in any of the three tables participating in the JOINs. Guessing from your example they would look like SET l1.process = '1', l1.status = '13' WHERE u1.universityID = '40' BEGIN RANT If process, status, and universityID are numeric fields... DROP THOSE QUOTES. They are not necessary and require the engine to take an extra conversion. END RANT sorry... just had to get that off my chest. :-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 09/14/2004 01:24:19 PM: - Original Message - From: bruce [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:45 AM Subject: update/join question.. hi... a question on how to do an update on a table that depends on 'left/right' joins with other tables... i have the following select that works. select u1.urltype as type, p1.fileID as fileID, l1.process as process, l1.status as status from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40'; i simply wnat to be able to update the latestParseStatusTBL based upon the joins between the tables... (something like...) update latestParseStatusTBL, university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID = l1.itemID where u1.universityID='40' set
SV: Mysql goes down when executing query
Hi Try setting these variables in you conf: set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_size=10M you have to experiment with the size as it depends on how much ram you hardware has. Nickolai Nielsen -Oprindelig meddelelse- Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sendt: 14. september 2004 22:39 Til: MySql List Emne: Mysql goes down when executing query Hi, Sorry to disturb but Mysql 4.1.4 gamma goes down when executing this query. I've tryed the same query without the coalesce function and the problem persists. select coalesce(viehc,0), coalesce(vieapellido,0), coalesce(vienombres,0), coalesce(viedoc,0), coalesce(numero,0), coalesce(apellido,0), coalesce(nombres,0), coalesce(f_nacimiento,0), coalesce(sexo,0), coalesce(doc_numero,0) from zzg_int.compara into outfile /tmp/compa.txt fields terminated by ',' lines terminated by '\r\n'; This is what the error log shows. Version: '4.1.4-gamma-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=13 max_connections=100 threads_connected=10 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x4b22efb8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfddeb68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808a183 0x82d3cb8 0x80ae46f 0x809d894 0x8097e4f 0x80977d8 0x8096f17 0x82d146c 0x82fa9fa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x86da708 = EXPLAIN select coalesce(viehc,0),coalesce(vieapellido,0),coalesce(vienombres,0),coalesce(vi edoc,0),coalesce(numero,0), coalesce(apellido,0),coalesce(nombres,0),coalesce(f_nacimiento,0),coalesce(s exo,0),coalesce(doc_numero,0) from hrrg_int.compara into outfile /tmp/compa.txt fields terminated by ',' lines terminated by '\r\n' thd-thread_id=632 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 040914 13:15:00 mysqld restarted 040914 13:15:00 [ERROR] Warning: Asked for 196608 thread stack, but got 126976 040914 13:15:00 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 040914 13:15:00 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 281648573. InnoDB: Doing recovery: scanned up to log sequence number 0 281648583 InnoDB: Last MySQL binlog file position 0 79779, file name ./hrrgp01-bin.05 040914 13:15:00 InnoDB: Flushing modified pages from the buffer pool... 040914 13:15:00 InnoDB: Started; log sequence number 0 281648583 /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.1.4-gamma-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to download MySQL (fwd)
Because I have received off-list responses from different people, rather than send the same response to everyone who responds to me off-list, my response is below. It appears that a problem lies in the web browser trying to open the RPM, rather than downloading it, and, as it is trying to open the RPM, and, incorrectly applying a real audio plugin (or trying to do that, in the absence of such a plugin), I am unable to download the RPM. I had tried to download the RPM using galeon, and I have also tried using Mozilla, and neither appears to be given the option to save the file to disk - both appear to be required to open the RPM. I have tried different URL's, including trying the ftp URL at Oakland, USA, and the Australian URL previously cited. I get the same problem - instead of getting a dialogue box giving me the option of saving the file to disk (and, in what path), the browser commences downloading the file, to open rather than to save the file. -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 The thing is, that I right clicked on the link, to open in a new tab, as I expected, as the link was a URL for a different web site than the web site where I was, that the browser tab/window would open up at the URL, rather than automatically commencing to open or download the file. As the download had began when I went ot the tab that had opened, I had assumed that the file was being downloaded to be saved on my disk, not being downloaded to be opened. It was not clear, that clicking on the link, would commence to open the file rather than going to the URL to enable a person to download the file to be saved to disk. -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unable to download MySQL (fwd)
What happens when you right click on the link, should give you the option to save as? Works for me w/ Mozzila -Original Message- From: Bret Busby [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 9:37 PM To: [EMAIL PROTECTED] Subject: Re: Unable to download MySQL (fwd) Because I have received off-list responses from different people, rather than send the same response to everyone who responds to me off-list, my response is below. It appears that a problem lies in the web browser trying to open the RPM, rather than downloading it, and, as it is trying to open the RPM, and, incorrectly applying a real audio plugin (or trying to do that, in the absence of such a plugin), I am unable to download the RPM. I had tried to download the RPM using galeon, and I have also tried using Mozilla, and neither appears to be given the option to save the file to disk - both appear to be required to open the RPM. I have tried different URL's, including trying the ftp URL at Oakland, USA, and the Australian URL previously cited. I get the same problem - instead of getting a dialogue box giving me the option of saving the file to disk (and, in what path), the browser commences downloading the file, to open rather than to save the file. -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 The thing is, that I right clicked on the link, to open in a new tab, as I expected, as the link was a URL for a different web site than the web site where I was, that the browser tab/window would open up at the URL, rather than automatically commencing to open or download the file. As the download had began when I went ot the tab that had opened, I had assumed that the file was being downloaded to be saved on my disk, not being downloaded to be opened. It was not clear, that clicking on the link, would commence to open the file rather than going to the URL to enable a person to download the file to be saved to disk. -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to download MySQL (fwd)
Bret Busby wrote: Because I have received off-list responses from different people, rather than send the same response to everyone who responds to me off-list, my response is below. It appears that a problem lies in the web browser trying to open the RPM, rather than downloading it, and, as it is trying to open the RPM, and, incorrectly applying a real audio plugin (or trying to do that, in the absence of such a plugin), I am unable to download the RPM. I had tried to download the RPM using galeon, and I have also tried using Mozilla, and neither appears to be given the option to save the file to disk - both appear to be required to open the RPM. Right-click should give you a list of options. Also shift-click should *force* the save operation. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query error need help please
Hello.
I have a subroutine which checks for multiple entries and if a user has
voted once in the same day, it will not calculate the vote. However, I
found out that it does not really do this.
What it does is only look at the last entry. If the user IP address is
the last entry it does not calculate the vote, but if it is not, it goes
ahead and let the user vote.
So, basically it only looks at the very last entry.
Can someone please tell me what needs to be done to fix this?
Thanks so much.
if(isset($_POST['submit_rating']) isset($_POST['user_id'])
($_POST['submit_rating'] = 3 $_POST['submit_rating'] =
10)){
$user_id = (int) $_POST['user_id'];
if(isset($_SESSION['ra'])){
$_SESSION['ra'] .= $user_id . ,;
} else {
$_SESSION['ra'] = $user_id . ,;
}
$rating = (int) $_POST['submit_rating'];
$rater_id = isset($_SESSION['userid']) ? $_SESSION['userid'] :
0;
$check_ip_sql =
select
*
from
$tb_ratings
where
user_id = '$user_id'
order by
timestamp desc
;
$check_ip_query = mysql_query($check_ip_sql) or
die(mysql_error());
$last_rater_ip = @mysql_result($check_ip_query, 0,
rater_ip);
$last_rater_id = @mysql_result($check_ip_query, 0,
rater_id);
$last_rated = @mysql_result($check_ip_query, 0, timestamp);
$yesterday = date(YmdHis,
mktime(date(H), date(i), date(s),
date(m), date(d)-10, date(Y)));
$same_ip = false;
$too_soon = false;
$same_user = false;
if($last_rater_ip == $HTTP_SERVER_VARS['REMOTE_ADDR']) $same_ip
= true;
if($last_rated $yesterday) $too_soon = true;
if($user_id == $rater_id) $same_user = true;
if(!$same_user (!$same_ip || !$too_soon)){
$rating_accepted = true;
$is_sql =
insert into $tb_ratings (
id,
user_id,
rating,
rater_id,
rater_ip
) values (
'',
'$user_id',
'$rating',
'$rater_id',
'$_SERVER[REMOTE_ADDR]'
)
;
$is_query = mysql_query($is_sql) or die(mysql_error());
$gs_sql =
select
total_ratings,
total_points,
average_rating
from
$tb_users
where
id = '$user_id'
;
$gs_query = mysql_query($gs_sql) or die(mysql_error());
$total_ratings = mysql_result($gs_query, 0,
total_ratings);
$total_points = mysql_result($gs_query, 0,
total_points);
$total_ratings++;
$total_points += $rating;
$average_rating = $total_points / $total_ratings;
$ps_sql =
update
$tb_users
set
total_ratings = '$total_ratings',
total_points = '$total_points',
average_rating = '$average_rating'
where
id = '$user_id'
;
$ps_query = mysql_query($ps_sql) or die(mysql_error());
}
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Renaming the name of an index
Hi, I added a composite index to a MyISAM table e.g. ALTER TABLE foo ADD INDEX (column1, column2). Because I didn't specify a name for the index, SHOW KEYS is showing the Key name to be column1. Is there a command to change the Key name ? I don't want to drop/recreate the index. Thanks Xanana -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query error need help please
On Tue, 14 Sep 2004 19:55:42 -0700, Soheil Shaghaghi [EMAIL PROTECTED] wrote: I have a subroutine which checks for multiple entries and if a user has voted once in the same day, it will not calculate the vote. However, I found out that it does not really do this. What it does is only look at the last entry. If the user IP address is the last entry it does not calculate the vote, but if it is not, it goes ahead and let the user vote. So, basically it only looks at the very last entry. Can someone please tell me what needs to be done to fix this? Thanks so much. This is fixed in the latest version: http://sourceforge.net/projects/destiney/ If not, let me know and I'll look into it. I'm still maintaining the 0.3 series. -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unable to download MySQL (fwd)
On Tue, 14 Sep 2004, Jonathan Jesse wrote: Date: Tue, 14 Sep 2004 21:50:45 -0400 From: Jonathan Jesse [EMAIL PROTECTED] To: Bret Busby [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Unable to download MySQL (fwd) What happens when you right click on the link, should give you the option to save as? Works for me w/ Mozzila -Original Message- From: Bret Busby [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 9:37 PM To: [EMAIL PROTECTED] Subject: Re: Unable to download MySQL (fwd) Because I have received off-list responses from different people, rather than send the same response to everyone who responds to me off-list, my response is below. It appears that a problem lies in the web browser trying to open the RPM, rather than downloading it, and, as it is trying to open the RPM, and, incorrectly applying a real audio plugin (or trying to do that, in the absence of such a plugin), I am unable to download the RPM. I had tried to download the RPM using galeon, and I have also tried using Mozilla, and neither appears to be given the option to save the file to disk - both appear to be required to open the RPM. I have tried different URL's, including trying the ftp URL at Oakland, USA, and the Australian URL previously cited. I get the same problem - instead of getting a dialogue box giving me the option of saving the file to disk (and, in what path), the browser commences downloading the file, to open rather than to save the file. -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 The thing is, that I right clicked on the link, to open in a new tab, as I expected, as the link was a URL for a different web site than the web site where I was, that the browser tab/window would open up at the URL, rather than automatically commencing to open or download the file. As the download had began when I went ot the tab that had opened, I had assumed that the file was being downloaded to be saved on my disk, not being downloaded to be opened. It was not clear, that clicking on the link, would commence to open the file rather than going to the URL to enable a person to download the file to be saved to disk. -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 When I right click on the link, using Mozilla, a menu is displayed, with an option (the closest to save as) being Save Link Target. When the Save As dialogue box appears, the file name displayed, is HTTP and the file type displayed, is All Files (*.*) (That is in right clicking on the HTTP link for Australia - AusGamers, Sydney) I think that the problem may be in the links on the downloads page, pointing directly to the files, rather than to the web pages that list file downloads, at each of the mirrors. But, that is just my perception. This is using Mozilla 1.0.2, running on Red Hat Linux 7.3 . -- Bret Busby Armadale West Australia .. So once you do know what the question actually is, you'll know what the answer means. - Deep Thought, Chapter 28 of The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts, written by Douglas Adams, published by Pan Books, 1992 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
