MySQL Query problem, possible bug?
Hello, Today, I tried to move a part of one of my websites, a forum (vBulletin, www.vbulletin.com) to a new server: a FreeBSD server. I installed the package mysql-3.23.40-unknown-freebsdelf4.3-i386.tar.gz on that server, and everything seemed to work perfectly: installation complete, and phpMyAdmin worked perfectly. I reinstalled the forums on the new FreeBSD server (the old one was RH-Linux 7.0), that also went good: I entered the admin, it worked good. Then, I tried to enter the forums, and got a database error: SQL Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'1') ORDER BY replacementsetid DESC,replacementid DESC Error: Lost connection to MySQL server during query I found out the connection was lost because MySQL crashes: /usr/libexec/ld-elf.so.1: ./mysqld: Undefined symbol strtoll Anyone is familliar with this problem? This query DID work on the previous server, the RedHat one, but just doesn't work on the FreeBSD server. The databases are EXACTLY the same, so there shouldn't be any problems. Or should it? Anyway, thanks in advance for your replies, Leon Mergen [EMAIL PROTECTED] President of Operations BlazeBox, Inc. ICQ: 55677353
Memory usage
Hello, I was wondering, how can I lookup how much memory MySQL is currently allowed to use, and how can I change this value? Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query to display per page
In PHP, I used something like this:
?
$result = $db-query(select * from table limit $x,25);
while ($row=$db-fetch_array($result))
{
echo $row[column];
}
$next = ($x+25);
echo a href=\script.php?x=$next\Next/a\n;
?
So that's the idea...
Leon Mergen
[EMAIL PROTECTED]
President of Operations
BlazeBox, Inc.
T: +31 31 735 03 03
F: +31 31 735 03 08
ICQ: 55677353
The information transmitted in this email is intended only for the
person(s)or entity to which it is addressed and may contain confidential
and/or privileged material. Any review, retransmission, dissemination or
other use of, or taking of any action in reliance upon, this information by
persons or entities other than the intended recipient is prohibited. If you
received this email in error, please contact the sender and permanently
delete the email from any computer.
- Original Message -
From: Sommai Fongnamthip [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 11, 2001 11:52 AM
Subject: query to display per page
Dear,
I am preparing my web page to display search result. How could I use SQL
command to handle display specific row per page and next button at the
bottom page (like most search engine display result).
Sommai Fongnamthip
Remark: Could I use this SQL command with PHP or perl?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Again: ORDER BY key_part1,key_part2 does not use index
I have some sort of problem too... Look at this: mysql explain select * from game3 order by memberid; +---+--+---+--+-+--+--+- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+- ---+ | game3 | ALL | NULL | NULL |NULL | NULL | 1245 | Using filesort | +---+--+---+--+-+--+--+- ---+ 1 row in set (0.02 sec) mysql explain select * from game3 where memberid = '1'; +---+---+---+--+-+---+--+--- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+--+-+---+--+--- + | game3 | const | memberid | memberid | 2 | const |1 | | +---+---+---+--+-+---+--+--- + 1 row in set (0.00 sec) As you can clearly see in the 2nd query, memberid is a key, and is used there. But why isn't it used in the ORDER BY clause? Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353 - Original Message - From: Lukas Knecht [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 11, 2001 11:48 AM Subject: Again: ORDER BY key_part1,key_part2 does not use index Hi, I know this subject has been treated several times in this list (see e.g. http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:sss:65398:200102:ipomopcphmhamkmig oob#b ), but I think it deserves further discussion. My problem is the following: I have a rather large table (~430M records in 21G with a 4.4G packed index) created by: CREATE TABLE T ( `Id` int(11) NOT NULL default '0', `Id2` int(11) NOT NULL default '0', `F1` smallint(6) default NULL, `F2` smallint(6) default NULL, `F3` smallint(6) default NULL, `F4` smallint(6) default NULL, `F5` float default NULL, `F6` mediumtext, `F7` float default NULL, `F8` float default NULL, KEY `TIds`(`Id`,`Id2`), KEY `TId2`(`Id2`) ) TYPE=MyISAM MAX_ROWS=25 PACK_KEYS=1; The query I want to run is: SELECT * FROM T ORDER BY Id, Id2; I would expect MySQL to use the index to retrieve the rows in sorted order, but this is not what happens (MySQL 3.23.38): EXPLAIN SELECT * FROM T ORDER BY Id, Id2; +---+--+---+--+-+--+---+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+---+ + | T | ALL | NULL | NULL |NULL | NULL | 431476682 | Using filesort | +---+--+---+--+-+--+---+ + Using filesort means that MySQL does the sorting itself without using the index. I can think of two possible implementations: (1) Create a temporary 21G file with 430M complete records, sort these by (Id, Id2) and then read the data sequentially from this file. The sorting can be done on the fly using a good mergesort implementation and will use almost no random accesses with large enough buffers. I expect the time required for this to be the time to read, write and reread 21G: with 50MB/s I/O bandwidth, 3*21G/50MB ~ 21 min. With the CPU time required for the sorting, I would expect less than an hour. The time to process the query would be dominated by reading the sorted data into the application, even with a Gigabit network. (2) Create a temporary 16*430M file with 430M records (Id, Id2, record pointer), sort this by (Id, Id2), and then retrieve the complete records doing 430M random accesses. From earlier experience, I assume this is what MySQL does, despite the fact that it is entirely equivalent to using the index... I expect the time rqeuired for this to be dominated by the 430M random reads. On a fast RAID with 2 ms avg access time, this may require up to 430M * 2ms ~ 10 days, but may be substantially less if the data file is already partially sorted by Id, Id2 (which it is in our case). For me, this discussion boils down to three questions: A) Does MySQL sort the entire file (1), or just the ORDER BY criteria with subsequent random reads (2)? B) If what I believe is correct (MySQL does (2)), why does it create a temporary file containing the keys and sort it instead of using the index? C) The MySQL manual in http://www.mysql.com/documentation/mysql/bychapter/manual_Performance.html#M ySQL_indexes says: ...The following queries will use the index to resolve the ORDER BY part: SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3; Isn't this information wrong or at least incomplete? Can anyone shed some light on this? Thanks. Lukas Knecht infox Neptunstr. 35 CH-8032 Zürich
Re: Password encryption
Hello Rolf, I need the decoding option, since I need to have a password lookup function... Hmmm... offcourse, I *COULD* completely rewrite it, and instead of lookup up a password, make it so that you can reset your password if you have verified your email address or something. However, I wonder if rewriting this is worth the effort. I mean, I probably will be busy 3 hours with it, to completely rewrite it, and fully test everything. And that is why I wonder if it's worth it, because I also like the option to decode the passwords of my members, if I have the encryption password... But basically, my question is: how great is the performance penalty caused by DECODE(), or is it low enough to forget about it? Because my system checks the password each page a member visits, and a member usually visits around 500 pages/day. So performance is quite important here ;) Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353 - Original Message - From: Rolf Hopkins [EMAIL PROTECTED] To: Leon Mergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, June 10, 2001 3:43 PM Subject: Re: Password encryption I used the function password() if that helps but of course you can't decode it. - Original Message - From: Leon Mergen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, June 09, 2001 22:44 Subject: Re: Password encryption Anyone has any idea how much this encryption method sucks up server load? _ Leon Mergen [EMAIL PROTECTED] President of Operations BlazeBox, Inc. ICQ: 55677353 - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: Leon Mergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 08, 2001 9:47 PM Subject: Re: Password encryption Look in the manual about the ENCODE/DECODE functions. j- k- On Friday 08 June 2001 10:28, you wrote: Hello all, I have some questions about password encryption. I want to make some sort of encryption method that disables a human to read the password, but does allow my (php) script to convert the encoded password to a human-readable password, the member originally entered and the member enters in the form (if he or she has the password right) . In other words, I want a string to be encrypted and decrypted, if it's possible in the query. An example: insert into table values (encrypt(secret)); select decrypt(passfield) as pass from table; and here, pass will contain the value of secret. -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Password encryption
Anyone has any idea how much this encryption method sucks up server load? _ Leon Mergen [EMAIL PROTECTED] President of Operations BlazeBox, Inc. ICQ: 55677353 - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: Leon Mergen [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 08, 2001 9:47 PM Subject: Re: Password encryption Look in the manual about the ENCODE/DECODE functions. j- k- On Friday 08 June 2001 10:28, you wrote: Hello all, I have some questions about password encryption. I want to make some sort of encryption method that disables a human to read the password, but does allow my (php) script to convert the encoded password to a human-readable password, the member originally entered and the member enters in the form (if he or she has the password right) . In other words, I want a string to be encrypted and decrypted, if it's possible in the query. An example: insert into table values (encrypt(secret)); select decrypt(passfield) as pass from table; and here, pass will contain the value of secret. -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Password encryption
Hello all, I have some questions about password encryption. I want to make some sort of encryption method that disables a human to read the password, but does allow my (php) script to convert the encoded password to a human-readable password, the member originally entered and the member enters in the form (if he or she has the password right) . In other words, I want a string to be encrypted and decrypted, if it's possible in the query. An example: insert into table values (encrypt(secret)); select decrypt(passfield) as pass from table; and here, pass will contain the value of secret. Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353
Delayed insert
Hello, I am having some troubles with INSERT DELAYED statement... even when there are NO pending select, update or delete statements (I can see at the SHOW PROCESSLIST query) , still it keeps waiting... This is the info of the delayed process that doesn't want to execute: db: antrophi command: delayed_insert time: 277099 state: waiting on cond info: game3_msg (this is the table that needs to be altered by the insert statement) ALL the other queries have the Sleep command, so I assume it is safe to execute the insert statement, but it doesn't... anyone know what's causing this and how to fix it? Thanks in advance, Leon Mergen [EMAIL PROTECTED] BlazeBox, Inc. ICQ: 55677353 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
