Re: Change default character_set_client, connection, results
Gleb, thanks for your response, but that doesn't help me much because I'm using ADO components to connect to MySQL and not MySQL API functions. Regards, Dusan Pavlica - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 23, 2005 4:35 PM Subject: Re: Change default character_set_client, connection, results Hello. Use MYSQL_SET_CHARSET_NAME option for mysql_options() function. See: http://dev.mysql.com/doc/mysql/en/mysql-options.html [snip] Hello, could someone tell me if it is possible to change default settings for character_set_client, character_set_connection and character_set_results variables? They are always set to latin1 and I didn't find any way how to change their default value. I'm using MySQL 4.1.9-nt and MySQL ODBC 3.51. My applications are written in C++ Builder and I don't want to send command SET NAMES 'cp1250' whenever application opens connection. Thanks in advance [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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: Innodb - raw partition vs filesystem store?
Greg, - Original Message - From: Greg Whalin [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, February 24, 2005 12:04 AM Subject: Innodb - raw partition vs filesystem store? What are pros/cons as far as performance, reliability, and ease of backup/restore? Anyone have any experience running Innodb on raw partition? raw partitions are beneficial only in some old OS/hardware configurations where fsync is extremely slow. In most computers, you only get a 5 % performance improvement from raw partitions. Any thoughts as to best filesystem for Innodb? What about pros/cons of journaled filesystems when in use with Innodb (i.e. transactions)? All major Linux file systems seem to have almost the same performance. How do the recent experiences of LiveJournal/Wikipedia sway these answers? A journaling file system like ReiserFS does not help if fsync does not work. A journaling file system itself is actually a bit like a transactional database. A broken fsync might cause bad damage there. I would be happy if users tested the 'pull-the-plug' performance of Linux-2.6.10/InnoDB. Jens Axboe might have solved most fsync problems: http://groups-beta.google.com/group/linux.kernel/browse_frm/thread/bbe45994b0277f7a/cc6d86c50514da81?q=axboe+fsync+linux_done=%2Fgroups%3Fas_q%3Daxboe+fsync+linux%26safe%3Dimages%26as_scoring%3Dd%26lr%3D%26hl%3Den%26_doneTitle=Back+to+Searchd#cc6d86c50514da81 Just going over some thoughts in my head and want to see if any good discussion can come from this? Greg -- [EMAIL PROTECTED] Meetup.com Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Limit function in MySQL 4.1.9
Hi, I have the following problem, I'm converting from version 3.23.53 to 4.1.9 The following query was used SELECT * FROM news WHERE languages_id = 'english' AND expiry_ts = CURDATE() AND FCode = '999' AND DCode = 'Z999' ORDER by id DESC LIMIT 0, 3 it work perfectly on the version 3.23.53 (the tables are perfect copys on version 3 and 4) returning 3 results. when I run the query in version 4.1.9 nothing comes back. When I remove the LIMIT 0,3 version 4.1.9 returns all 5 rows. what I also observed was that if I change the query to SELECT * FROM news WHERE languages_id = 'english' AND expiry_ts = CURDATE() AND FCode = '999' AND DCode = 'Z999' ORDER by id DESC LIMIT 3 still nothing geet returned but if I change it to SELECT * FROM news WHERE languages_id = 'english' ORDER by id DESC LIMIT 0, 3 or SELECT * FROM news WHERE expiry_ts = CURDATE()ORDER BY id DESC limit 3 or any query with just one where clause it works. Can someone help/explain what is happening here?? It seems that with multiple where clauses and a limit that nothing get return. Thx for the help. Cecil Mnr Cecil J.C. Brand Internet Office RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2645 Email :[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SUM and DECIMAL field
Hi Mark, Thank you for your help. I have the same problem when I execute the query with the MySQL client on my AIX server : mysql select sum(AMFTPF) from DWH_AMF; +-+ | sum(AMFTPF) | +-+ |12000,00 | +-+ 1 row in set (0,03 sec) The problem occurs only when I use an operator like SUM, AVG, MAX, MIN... mysql select AMFTPF from DWH_AMF LIMIT 1; ++ | AMFTPF | ++ | 15.00 | ++ 1 row in set (0,01 sec) Moreover the local on the client (swing GUI) is already set depending on the preferences of the user that's connected. Hence I cannot force it. I don't think the problem is with Connector/J. I thing the getBigDecimal method fail because of the comma returned by MySQL. But to me the MySQL server shouldn't be returning a comma in that case. It seems like a MySQL bug to me, but maybe I'm doing something wrong. Regards, Julien -Message d'origine- De : Mark Matthews [mailto:[EMAIL PROTECTED] Envoyé : lundi 21 février 2005 16:24 À : LAFONTAINE Julien - LYO Cc : mysql@lists.mysql.com Objet : Re: Problem with SUM and DECIMAL field -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 LAFONTAINE Julien - LYO wrote: Hi everyone, I've recentely upgarded my servers from MySQL 3.23.49 to MySQL 4.1.9. Now I have a problem when using the SUM operator on DECIMAL field. The value returned by the SUM operator when used with DECIMAL field has a coma (,) as decimal separator while it used to be have a dot (.) . If I query my table to display the DECIMAL fields (SELECT * FROM ...) I get a dot as decimal separator as expected. This doesn't look like a big issue but it prevents Connector/J from retieving the data properly. Connector/J can't parse the value of the field as it's expecting a dot as decimal separator. Here is the stack trace : java.sql.SQLException: Bad format for BigDecimal '12660,95' in column 1( . () . SUM(AMFTPF)()). at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:493) I'm using Connector/J 3.0.16. One last thing : this seems to happen only on AIX. I have tried on Linux and Windows XP and everything works as expected. Is there something wrong with some of my database parameters or is this a bug ? Please let me know if someone is interested by a testcase. Reagrds, Julien LAFONTAINE Julien, Connector/J uses the locale of the _client_ computer to parse numbers. It seems your AIX box and your MySQL compile are 'sensitive' to the locale, and thus returning numbers formatted different than your client expects them. If you use the 'mysql' client, does it show decimal numbers with comma separators as well? Since MySQL doesn't actually have configurable locale, and doesn't expose this information in any status variable, you will have to set your client to the same locale as your server to get these numbers to parse, see: http://java.sun.com/j2se/1.5.0/docs/guide/intl/locale.doc.html and http://java.sun.com/j2se/corejava/intl/reference/faqs/index.html#set-default -locale -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity www.mysql.com MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: http://www.mysqluc.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCGf0ctvXNTca6JD8RAvBCAKCedPifB6OwMK0KWmqtDSo71dLmKwCgi99W 1NQrbWDzt3BrP4YcySewcFI= =xry/ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC 3.51.06 for win32
Hi, I'm looking for an older version of the ODBC connector, the 3.51.06 version for windows, but I could not get a hand on it. I'v been there : http://downloads.mysql.com/archives.php?p=myodbc-3.51 but no luck at all. Any chance someone could point me in the right direction ? Thanks, -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Images to MySql Database in PHP or JAVA
Hi, Have you try google ? http://www.google.fr/search?hl=frq=storing+image+in+mysqlbtnG=Recherche+Googlemeta= I see pretty much a lot of stuff there ;) btw, storing image in a table in considering a bad practice, let the filesystem handle it it does it more efficiently. I would suggesdt to write the image on the disk, and then store the path in you db with useful thing like image format, width, height, etc... HIMH. Christopher Molnar wrote: Hello list- I am programing both in Java and PHP. Can anyone point me to sample code for either on saving an image to a table? Thanks, -Chris -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT Exact phrase search including quotes
Hi list, I'm trying to figure out how to use the exact phrase search in fulltext boolean mode when the phease to search includes double quotes. For instance, what if I want to search this exact phrase : I like football on TV I think I've tried all the solution I'm aware of without any results. Any help would be appreciated! Thanks -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary Keys, Multiple Index and Searching
Hiya,
I've been trying to find out about this for a few days, but can't seam
to find much information about it on the web.
At the moment I've got a few tables, one of this looks like:
CREATE TABLE `news` (
`section` TINYINT UNSIGNED ZEROFILL NOT NULL,
`article` SMALLINT(4) NOT NULL AUTO_INCREMENT,
`status` ENUM('show', 'awaiting', 'hide') NOT NULL DEFAULT 'awaiting',
more column defitions here...
PRIMARY KEY (`section`, `article`),
INDEX news_search (`section`, `status`),
)
There is another table with defines the sections of the site, and each
section can have it's own articles. Hence the Primary Key. However, most
of the searches are going to be with the status aswell, as I want to
display articles marked with status='show', i.e.
SELECT * FROM news WHERE section='x' AND status='show';
At the moment, the indexes for section are being duplicated, which I
suppose is a waste of space, and slows the updates (although that's not
a problem, as updates aren't anywhere near as common as searches).
However, would it be better to knock `section` out of news_search and
have just `status`? Does MySQL (currently 4.0.14, although it'll be
running on 3.23.53 eventually) allow the searching of multiple indexes,
or is it better to specify multiple indexes with similar columns to
maximize performance?
Thanks,
--
jonathan wright
// mail at djnauk.co.uk // running on gentoo linux
// life has no meaning unless we can enjoy what we've been given
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text scoring (MySQL 4.1 and future)
Hello,
Full-Text in MySQL 4.1 allows you to calculate a row score using
full-text search and calculation from the same table as the full-text.
For example:
SELECT A.field1,B.field2,B.field3,
MATCH(B.name,B.description) AGAINST('Hello World' IN BOOLEAN
MODE)+10*B.field2-5*B.field3 AS score
FROM A INNER JOIN B USING (key)
WHERE MATCH(B.name,B.description) AGAINST('Hello World' IN BOOLEAN
MODE)+10*B.field2-5*B.field3
ORDER BY score DESC
LIMIT 0,10;
This search is fast, since it doesn't use temporary table, because all
fields on the score calculation are from table B. The query time is
monotone (grows as you ask for farther results from the start limit).
If I want to add to the score +3*A.field1 then MySQL ends the search
with a temporary table that makes him run over all matched rows, which
makes the search run slower, and the query time is constant.
Does anyone know if MySQL tends to calculate scoring also from fields
out of the full-text table? (Mabye it is available for MySQL 5 even in
its beta?)
Please, that's important for me to design my DB.
-thanks, Eli
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg Text field data
Hi, How to read a text type field from the data base. By simple select I am not able to see the whole data at a time. Please advise me here. Thanks, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 9:06 PM To: mysql@lists.mysql.com Subject: Re: Change default character_set_client, connection, results Hello. Use MYSQL_SET_CHARSET_NAME option for mysql_options() function. See: http://dev.mysql.com/doc/mysql/en/mysql-options.html [snip] Hello, could someone tell me if it is possible to change default settings for character_set_client, character_set_connection and character_set_results variables? They are always set to latin1 and I didn't find any way how to change their default value. I'm using MySQL 4.1.9-nt and MySQL ODBC 3.51. My applications are written in C++ Builder and I don't want to send command SET NAMES 'cp1250' whenever application opens connection. Thanks in advance [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where mysql_config?
Hi this is the error message you get when trying to access mysql from php using a userid which has been created under mysql 4.1 whithout taking the precaution to apply the old_password function to create the password: Client does not support authentication protocol requested by server; consider upgrading MySQL client suomi Hi listers i am fiddling around with the problem of not being able to access mysql 4.1.* from php with new users created in mysql 4.1. no official statement can be found nor from mysql nor from php concerning this problem. both just avoid to talk about it. the only hint i found is create php with the mysqli api. but to create this type of api i need a program called mysql_config, which is said to come with any mysql distro later than 4.1. this is not true. it is not included in mysql 4.1.9 which i am currently using. in the news, everyone was talking about mysql_config, nowone knew where to find it. so, where is it? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change default character_set_client, connection, results
I finally found the solution. It's possible to set everything in MySQL ODBC/Connector on tab Connect options. I had to write to the field Initial Statement statement SET NAMES 'wanted_character_set' and that solved the problem. Dusan - Original Message - From: Duan Pavlica [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 24, 2005 9:53 AM Subject: Re: Change default character_set_client, connection, results Gleb, thanks for your response, but that doesn't help me much because I'm using ADO components to connect to MySQL and not MySQL API functions. Regards, Dusan Pavlica - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 23, 2005 4:35 PM Subject: Re: Change default character_set_client, connection, results Hello. Use MYSQL_SET_CHARSET_NAME option for mysql_options() function. See: http://dev.mysql.com/doc/mysql/en/mysql-options.html [snip] Hello, could someone tell me if it is possible to change default settings for character_set_client, character_set_connection and character_set_results variables? They are always set to latin1 and I didn't find any way how to change their default value. I'm using MySQL 4.1.9-nt and MySQL ODBC 3.51. My applications are written in C++ Builder and I don't want to send command SET NAMES 'cp1250' whenever application opens connection. Thanks in advance [snip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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: wikipedia down, slashdot covering, mysql mentioned
Many data centers do not allow customers to install their own UPS inside the rack. The questions is not whether they have (wish to have, can afford etc ) UPS or not, or if the OS is buggy ( fsync() function or others ), the problem is the database management system failed to ensure data integrity under stressed conditions. Of cause ensuring persistance of transactions when underlying hardware/software is unreliable is a very difficult task requiring multiple trade-offs and that's why i find it impossible to compare performance of two given DBMS where one of them ensures data integrity and the other one cannot. What would you prefer - the system that fails in 1/billion's occasions but runs 10 times slower or the system that fails 1/100 occasions even though it runs 10 times faster? Or is it better to have a system that never fails but runs 100 times slower? Probably fsync() had failed to flush some part of a 16 kB page to disk. so what ? one of trade-offs would be to re-read the data from the disk and compare it with what it should be (another copy on the disk) and only after that fix the transaction, otherwise roll it all back. When you have multiple users keeping db copy for each of them (versioning) becomes a nightmare... Solutions do exist but they require expensive trade-offs and more complex algorithms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to add a new user, that can create new users itself
Hi, which privileges i have to grant such a user, except grant option and the privileges such new users created by this user will have? Are there any side effects, for example, that this new user creating user also can delete other users? Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to send queries to a database periodically
Hi, is there a possibillity in mysql to send periodically queries to a database, for example all 5 minutes? Or, a liitle bit off topic, is there a possibility to this via php? Or, is it normally possible to set up such a query via cron tab (unix based web servers only)? Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to send queries to a database periodically
- Original Message - From: Peter PeterDresden [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 24, 2005 8:05 AM Subject: How to send queries to a database periodically Hi, is there a possibillity in mysql to send periodically queries to a database, for example all 5 minutes? Or, a liitle bit off topic, is there a possibility to this via php? Or, is it normally possible to set up such a query via cron tab (unix based web servers only)? Thanx. I use cron to do a daily backup of my MySQL databases; it runs a Bash script that does the work. You could just as easily write a Bash script that queries the database and runs every five minutes. I don't know php but I expect that you could set up a batch php job - assuming php supports batch programs! - and run it whenever you want via cron. There are even versions of cron for Windows which could presumably do the same on Microsoft platforms. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to send queries to a database periodically
[snip] is there a possibillity in mysql to send periodically queries to a database, for example all 5 minutes? Or, a liitle bit off topic, is there a possibility to this via php? Or, is it normally possible to set up such a query via cron tab (unix based web servers only)? Thanx. [/snip] Yes, you can do this via CRON, for example 0,5,10,15,20,25,30,35,40,45,50,55 * * * * mysql databasename mySQLScript.sql or you can create a PHP file and run it from CRON 0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/local/bin/php -q /path/to/my/php/script.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to send queries to a database periodically
Where can I find cron for Windows? That will make my life a whole lot better! On Thu, 24 Feb 2005 08:14:13 -0500, Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Peter PeterDresden [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 24, 2005 8:05 AM Subject: How to send queries to a database periodically Hi, is there a possibillity in mysql to send periodically queries to a database, for example all 5 minutes? Or, a liitle bit off topic, is there a possibility to this via php? Or, is it normally possible to set up such a query via cron tab (unix based web servers only)? Thanx. I use cron to do a daily backup of my MySQL databases; it runs a Bash script that does the work. You could just as easily write a Bash script that queries the database and runs every five minutes. I don't know php but I expect that you could set up a batch php job - assuming php supports batch programs! - and run it whenever you want via cron. There are even versions of cron for Windows which could presumably do the same on Microsoft platforms. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Power to people, Linux is here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to send queries to a database periodically
[snip] Where can I find cron for Windows? That will make my life a whole lot better! [/snip] It is called Task Scheduler http://www.iopus.com/guides/winscheduler.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to send queries to a database periodically
SQLyog (www.webyog.com) has an option called SQLyog Notification Services. It can execute a set of queries and even send you the report over email. Using this you can schedule it to run a set of queries anytime you want. Karam --- Peter PeterDresden [EMAIL PROTECTED] wrote: Hi, is there a possibillity in mysql to send periodically queries to a database, for example all 5 minutes? Or, a liitle bit off topic, is there a possibility to this via php? Or, is it normally possible to set up such a query via cron tab (unix based web servers only)? Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. 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]
Re: wikipedia down, slashdot covering, mysql mentioned
On Thu, Feb 24, 2005 at 02:38:46PM +0200, DebugasRu [EMAIL PROTECTED] wrote: Probably fsync() had failed to flush some part of a 16 kB page to disk. so what ? one of trade-offs would be to re-read the data from the disk and compare it with what it should be (another copy on the disk) and only after that fix the transaction, That didn't get rid of the race condition, it just moved it. So now you write the transaction, and you read it off, and then you write that it was OK -- but what if that write gets interrupted? So you write the transaction, and you read it off, and then you write that it was OK, and then you read that off, and then you write that the OK was OK. No, that could get interrupted to... Meanwhile, it turns out that you've been reading back from cache instead of platters all along. Or perhaps it turns out that the battery-backed cache in the disk controller has a dead battery, or simply doesn't work. If the disk promises data is written but it is not then there is nothing MySQL can do to *tell* that something is amiss. I think you've confused an RDBMS with a system administrator. Next thing you know you'll be complaining that MySQL isn't ACID-compliant because it can't survive a fire. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Approximately when is a beta with useable stored procedures
5.0.2 has a huge bug, you cant use most gui's with it so all command line 5.0.3 fixes, but I cant get it to compile on vc++ 6.0 always errors out :( most say use 5.0.1, but that's got a ton of bugs that 5.0.2 fixed we are patiently waiting for 5.0.3 so we can test out stored procedures -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
Rich Lafferty wrote: On Thu, Feb 24, 2005 at 02:38:46PM +0200, DebugasRu [EMAIL PROTECTED] wrote: Probably fsync() had failed to flush some part of a 16 kB page to disk. so what ? one of trade-offs would be to re-read the data from the disk and compare it with what it should be (another copy on the disk) and only after that fix the transaction, That didn't get rid of the race condition, it just moved it. So now you write the transaction, and you read it off, and then you write that it was OK -- but what if that write gets interrupted? So you write the transaction, and you read it off, and then you write that it was OK, and then you read that off, and then you write that the OK was OK. No, that could get interrupted to... Meanwhile, it turns out that you've been reading back from cache instead of platters all along. Or perhaps it turns out that the battery-backed cache in the disk controller has a dead battery, or simply doesn't work. If the disk promises data is written but it is not then there is nothing MySQL can do to *tell* that something is amiss. I think you've confused an RDBMS with a system administrator. Next thing you know you'll be complaining that MySQL isn't ACID-compliant because it can't survive a fire. -Rich Exactly. No ACID database can ensure integerity in such a situation. Postgres, Oracle, or any other transactional DB would have suffered the same fate in these two cases (LiveJournal, Wiki). Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copy DB data from FreeBSD to Redhat9.0
Hi, I just fresh install mysql4.1 in Redhat9.0. I have another MySQL5.0 installed in FreeBSD5.3, and just tried to copy all data in /var/db/mysql/ from FreeBSD5.3 to /var/db/mysql/data/ in Redhat9.0. After finished copied the data over to Redhat, MySQL4.1 can't start. The DB data in MySQL5.0 in the FreeBSD box has database and tables with indexes. I don't want to rebuild the entire database from scratch in Redhat, how can I transfer all db files from FreeBSD to Redhat and in another version of MySQL? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
On Thu, Feb 24, 2005 at 09:10:32AM -0500, Greg Whalin [EMAIL PROTECTED] wrote: Exactly. No ACID database can ensure integerity in such a situation. Postgres, Oracle, or any other transactional DB would have suffered the same fate in these two cases (LiveJournal, Wiki). FWIW, my understanding of LiveJournal's integrity problem after the power outage involved tables they weren't yet able to migrate off of MyISAM, and getting replication content in all directions. The stuff migrated to InnoDB already came up fine. Their replication setup is a bit complex -- see http://www.danga.com/words/2004_mysqlcon/mysql-slides.pdf for an overview. (My kit at the same facility was hit too, and recovered correctly, for what it's worth. :-) At least this mailing list has progressed beyond Why didn't they have a UPS?, I suppose. :-) -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy DB data from FreeBSD to Redhat9.0
sam wun wrote: Hi, I just fresh install mysql4.1 in Redhat9.0. I have another MySQL5.0 installed in FreeBSD5.3, and just tried to copy all data in /var/db/mysql/ from FreeBSD5.3 to /var/db/mysql/data/ in Redhat9.0. After finished copied the data over to Redhat, MySQL4.1 can't start. The DB data in MySQL5.0 in the FreeBSD box has database and tables with indexes. I don't want to rebuild the entire database from scratch in Redhat, how can I transfer all db files from FreeBSD to Redhat and in another version of MySQL? I also want to copy the user accounts in MySQL5.0 db over to MySQL4.1 in Redhat as well. I supposed this is sort of data backup, but more generic than that because it is copying data between different version of MySQL, and from higher version to a lower version of MySQL. Sam Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb - raw partition vs filesystem store?
Heikki Tuuri wrote: A journaling file system like ReiserFS does not help if fsync does not work. A journaling file system itself is actually a bit like a transactional database. A broken fsync might cause bad damage there. I would be happy if users tested the 'pull-the-plug' performance of Linux-2.6.10/InnoDB. Jens Axboe might have solved most fsync problems: http://groups-beta.google.com/group/linux.kernel/browse_frm/thread/bbe45994b0277f7a/cc6d86c50514da81?q=axboe+fsync+linux_done=%2Fgroups%3Fas_q%3Daxboe+fsync+linux%26safe%3Dimages%26as_scoring%3Dd%26lr%3D%26hl%3Den%26_doneTitle=Back+to+Searchd#cc6d86c50514da81 I am running 2.6.10 here. I will see if I can set up a test case this weekend. Also, I know you and others have mentioned that Linux 2.6 + Opteron + Innodb is a problematic situation. Could you expand on this? From our personal experience (running mysql on Opteron + linux 2.6.10 w/ myisam tables), we have seen very slow performance when running intensive IO operations (deleting 20 million rows from a 50 col table) and we have experienced a greater number of index corruption on the opterons than on our intel dbs. Greg -- [EMAIL PROTECTED] Meetup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
Rich Lafferty wrote: On Thu, Feb 24, 2005 at 09:10:32AM -0500, Greg Whalin [EMAIL PROTECTED] wrote: Exactly. No ACID database can ensure integerity in such a situation. Postgres, Oracle, or any other transactional DB would have suffered the same fate in these two cases (LiveJournal, Wiki). FWIW, my understanding of LiveJournal's integrity problem after the power outage involved tables they weren't yet able to migrate off of MyISAM, and getting replication content in all directions. The stuff migrated to InnoDB already came up fine. Their replication setup is a bit complex -- see http://www.danga.com/words/2004_mysqlcon/mysql-slides.pdf for an overview. (My kit at the same facility was hit too, and recovered correctly, for what it's worth. :-) At least this mailing list has progressed beyond Why didn't they have a UPS?, I suppose. :-) -Rich They had most of their dbs transitioned to innodb, but even some of those came up corrupted due to write caching being enabled on individual drives. Their myisam tables simply needed indexes rebuilt (which is a pain in the butt and takes forever). Their complete story: http://www.livejournal.com/community/lj_dev/670215.html Needless to say, between LJ and Wiki, I am fairly paranoid about db corruption now (though our writes are considerably less than either of those two places as we are 99% reads ... our reads are considerably less than either of those sites as well :) ) Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help needed
I asked for help with a version of this query a few months
ago, and subsequently made some improvements to it, and also
fooled around with other parts of my app that were in need of
tuning. I've since done some more extensive benchmarking and
realized that this query really is somewhat slow. Even though
the data set is rather small and everything is (I think)
properly indexed and the joins are sensible, I can't seem to
get rid of the using temporary and using filesort in my
EXPLAIN. I'd be grateful for any suggestions for improving
this.
Here's the query (obviously I run it with different values for
subject.name and different LIMIT values, but this is
representative):
SELECT citation.*, DATE_FORMAT(citation.last_modified, '%e %M, %Y') AS
last_modified
FROM citation, subject, citation_subject
WHERE subject.name = 'History'
AND citation_subject.subject_id = subject.id
AND citation_subject.citation_id = citation.id
AND citation.deleted = 0
ORDER BY citation.stripped_word, FIELD(citation.part_of_speech, 'NOUN',
'ADJECTIVE', 'ADVERB', 'VERB'), citation.id
LIMIT 150, 50
and EXPLAIN gives me this:
*** 1. row ***
table: subject
type: ref
possible_keys: PRIMARY,name
key: name
key_len: 50
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
table: citation_subject
type: ref
possible_keys: citation_id,subject_id
key: subject_id
key_len: 4
ref: subject.id
rows: 169
Extra: Using index
*** 3. row ***
table: citation
type: eq_ref
possible_keys: PRIMARY,deleted
key: PRIMARY
key_len: 4
ref: citation_subject.citation_id
rows: 1
Extra: Using where
Finally, here are the three tables involved. I've trimmed out the
irrelevant columns:
CREATE TABLE `citation` (
`id` int(11) NOT NULL auto_increment,
`word` varchar(50) NOT NULL default '',
`stripped_word` varchar(50) default NULL,
`part_of_speech` enum('NOUN','VERB','ADJECTIVE','ADVERB') NOT NULL default
'NOUN',
`last_modified` timestamp(14) NOT NULL,
`deleted` datetime default '-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `word` (`word`),
KEY `stripped_word` (`stripped_word`)
) TYPE=MyISAM
CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `citation_id` (`citation_id`,`subject_id`),
KEY `subject_id` (`subject_id`,`citation_id`)
) TYPE=MyISAM
CREATE TABLE `subject` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`deleted` datetime default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) TYPE=MyISAM
Thank you for any suggestions.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where mysql_config?
On 24 Feb 2005 at 12:50, schlubediwup wrote: Hi this is the error message you get when trying to access mysql from php using a userid which has been created under mysql 4.1 whithout taking the precaution to apply the old_password function to create the password: Client does not support authentication protocol requested by server; consider upgrading MySQL client Hi, Doing a search on google for Client does not support authentication protocol requested gives this as the first result: http://dev.mysql.com/doc/mysql/en/old-client.html which describes your problems and how to fix it. Regards Ian --
Subquery speed : IN (SELECT ...) versus IN (X, Y, Z, ...)
Hello list, We are currently tuning our queries speed and we found out that the ones using subqueries are quite slower than the 'usual' ones. Here is an example of a wierd behavior. We have a city list associated with zipcode, and user can search a database of people living in a given city. The problem is that a city can have several zip codes. Our first request is : SQL1 = SELECT zip FROM tblcity WHERE cityname = 'Paris' This request actually returns something like 20 results. The second request list the people living in areas with those zip codes: SQL2 = SELECT people FROM tblpeople WHERE zip IN (###) In ### we can either put - A : SQL1 - B : the list build from a recordset opened on SQL1 which would give something like : '75000', '75001', '75002', '75003', etc... Queries speed are 0.16s for A, and 0.05s for B. Can anybody explain this behavior, and maybe offer some advices on optimizing our queries. Thanks -- HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
It appears you can't combine an insert-select with an on-duplicate-key-update. I would find it very useful if you *could* do this. I know it would be complicate how you would handle the syntax for what to do when you hit a duplicate key, could do this: update all the columns that are *not* involved in duplicate-key clashes. This would help in situations like the following: -- You have a table with some columns that are unique, other columns which are descriptive of those unique combinations. You also have an auto-increment key. -- You have a lot of data you want to insert into this table, add new entries where you haven't before heard of that combination of unique-keys, otherwise update the non-unique columns. Possibilities: 1. INSERT IGNORE: doesn't update the non-unique columns. 2. REPLACE: screws up the auto-increment columns. Any other ideas? Note: the situation described is one you find when implementing data-warehouse so-called slowly changing dimensions. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: wikipedia down, slashdot covering, mysql mentioned
At least this mailing list has progressed beyond Why didn't they have a UPS?, I suppose. :-) Yes that was my primary intend :) I'd really like people to care more about their data safety and more awareness about the problems involved. That didn't get rid of the race condition, it just moved it. So now you write the transaction, and you read it off, and then you write that it was OK -- but what if that write gets interrupted? I simply will not delete the old original data (data state of my DB before transaction was started) that i keep just in case i will need to roll back. So you write the transaction, and you read it off, and then you write that it was OK, and then you read that off, and then you write that the OK was OK. No, that could get interrupted to... I don't see why i need to care to write that Ok was Ok. I just go and check if it was Ok with original data. I don't see why i would need to have additional log file on data log file. Meanwhile, it turns out that you've been reading back from cache instead of platters all along. Or perhaps it turns out that the battery-backed cache in the disk controller has a dead battery, or simply doesn't work. That's completely different problem - it is a problem of how the interface between hard drive and OS software is defined. Is there a function in this interface that will return physically written data and not cashed one. At this point no DBMS will save you indeed. I think you've confused an RDBMS with a system administrator. Next thing you know you'll be complaining that MySQL isn't ACID-compliant because it can't survive a fire. if DB is resident across several servers there should be protection from one server permanently going off line (kind of fire isn't it ?) GW Exactly. No ACID database can ensure integerity in such a situation. GW Postgres, Oracle, or any other transactional DB would have suffered the GW same fate in these two cases (LiveJournal, Wiki). I can't talk about how Oracle deals with such problems because i have no access to its source code that's where mysql has a huge advantage! :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication, table db already exists
Hello All,
Here is a problem I have been trying to debug for a few days. I had a mysql
server running for about a month. I did a mysql dump of all the tables on the
master and setup a second machine to be the slave, loading all the tables. I
set the replication stuff up in the slave my.cnf. Now as soon as I start the
slave I get good data flow between the two machines but the slave does not
update with the error below. Is this because I did not do a binary snapshot of
the master machine to setup the slave? I originally was not going to setup
replication, just going to do dumps of tables, but then changed directions.
Thanks! If you need more info, please ask.
--ja
mysql show slave status \G;
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repster
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rhea-bin.17
Read_Master_Log_Pos: 405948310
Relay_Log_File: dione-relay-bin.02
Relay_Log_Pos: 121
Relay_Master_Log_File: rhea-bin.01
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1050
Last_Error: Error 'Table 'db' already exists' on query.
Default database: 'mysql'. Query: ' CREATE TABLE db ( Host char(60) binary
DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User
char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL, Update_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Create_priv enum('N','Y') DEFAULT 'N' NOT NULL, Drop_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') DEFAULT 'N'
NOT NULL, References_priv enum('N','Y') DEFAULT 'N' NOT NULL, Index_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, PRIMARY KEY Host
(Host,Db,User), KEY User (User) ) comment='Database privileges''
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 11454541057
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 12172794
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
Tom Cunningham [EMAIL PROTECTED] wrote on 02/24/2005 11:31:31 AM: It appears you can't combine an insert-select with an on-duplicate-key-update. I would find it very useful if you *could* do this. I know it would be complicate how you would handle the syntax for what to do when you hit a duplicate key, could do this: update all the columns that are *not* involved in duplicate-key clashes. This would help in situations like the following: -- You have a table with some columns that are unique, other columns which are descriptive of those unique combinations. You also have an auto-increment key. -- You have a lot of data you want to insert into this table, add new entries where you haven't before heard of that combination of unique-keys, otherwise update the non-unique columns. Possibilities: 1. INSERT IGNORE: doesn't update the non-unique columns. 2. REPLACE: screws up the auto-increment columns. Any other ideas? Note: the situation described is one you find when implementing data-warehouse so-called slowly changing dimensions. Tom. According to http://dev.mysql.com/doc/mysql/en/insert.html it seems you can. What leads you to your conclusion that what you are trying to do is an illegal statement? Can you post your test statement, any error messages you get, and the results of SHOW CREATE TABLE x\G for each of the tables used in your statement? What version of MySQL are you trying to execute this statement against? ON DUPLICATE KEY only works for versions =4.1.0 . Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Excessive Server Load Problems
On Thu, 24 Feb 2005, Chris Aitken (The Web Hub) wrote: Hi all, I have just created a databased website that is working with a fairly large number of records (well, 10 million records is the biggest database ive ever worked on so far) but also have a large number of searches of these records happening regularly. The problem ive come across, is my hosting providers box is setup so that if the server load gets to 5, sendmail packs it in. packs it in? Do you mean like you say that sendmail actually crashes when load goes above 5? I would guess sendmail is rather throttling itself and that you are seeing log messages about sendmail refusing connections. Is this a correct assumption? Sendmail can be configured to operate with higher loads. If sendmail actually dies, there's something really unusual going on. When someone does a search of the records, mysql jumps up to 100% cpu usage (which is fine) but the more searches that are being done (the website is getting more users daily) the longer the cpu is at 100% and the higher the load gets. To me it sounds like you are having problems with your database design and that you could benefit from some indexes perhaps. Do you have any indexes? If you list your table structure and queries that are bogging things down, the mysql list will be more than happy to help you. :) One thing your server admin can do in the meantime is to lower max number of concurrent connections, assuming you are the only one using the mysql server. Atle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficulty starting mysql
Thanks guys! I did follow the procedure, but somewhere during the first and second attempt I mucked it up. Followed the directions slowly and carefully a third time and we are up and running. Thanks for your help! Kelly Kelly S. Brace Information Technology Exchange Center Twin Rise 200 1300 Elmwood Avenue Buffalo, NY 14222 http://www.itec.suny.edu Main: 716-878-4832 Fax: 716-878-3485 Office: 716-878-3984 Cell: 716-432-4978 [EMAIL PROTECTED] Eric Bergen [EMAIL PROTECTED] 02/23/2005 04:11 PM Please respond to Eric Bergen [EMAIL PROTECTED] To John Trammell [EMAIL PROTECTED] cc [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: Difficulty starting mysql errno 13 is permission denied. It looks like mysql doesn't have permission to access your grant tables. -Eric On Wed, 23 Feb 2005 14:00:58 -0600, John Trammell [EMAIL PROTECTED] wrote: Did you follow the procedure in http://dev.mysql.com/doc/mysql/en/quick-install.html ? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 1:29 PM To: mysql@lists.mysql.com Subject: Difficulty starting mysql Hello, I'm attempting to start mysql 4.1.10 compiled source code on Red Hat Advanced Server 2.1 with apache 2.0.49. ./configure, make and make install went successfully. But when I attempt to start mysql using this command: sudo mysqld_safe --user=mysql I get the following errors: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050223 11:31:46 mysqld ended The error log states: 050223 11:16:03 mysqld started 050223 11:16:03 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... 050223 11:16:03 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43634. InnoDB: Doing recovery: scanned up to log sequence number 0 43634 050223 11:16:03 InnoDB: Flushing modified pages from the buffer pool... 050223 11:16:03 InnoDB: Started; log sequence number 0 43634 050223 11:16:03 [ERROR] /usr/local/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050223 11:16:03 mysqld ended I also did a find on mysld and found this: ./var/run/mysqld ./etc/rc.d/init.d/mysqld ./etc/logrotate.d/mysqld ./usr/libexec/mysqld ./usr/local/libexec/mysqld ./home/webcollab/mysql-4.1.10/sql/mysqld What do I need to do to make it start properly? Thanks in advance! Kelly S. Brace Information Technology Exchange Center Twin Rise 200 1300 Elmwood Avenue Buffalo, NY 14222 http://www.itec.suny.edu Main: 716-878-4832 Fax: 716-878-3485 Office: 716-878-3984 Cell: 716-432-4978 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Remote Connection via Toad for MySQL
Hello, I have downloaded Toad for MySQL to give it a test run. MySQL 4.1.10 is on Redhat AS 2.1 and Toad for MySQL is on Win XP. Everytime I attempt to connect via Toad, I get the following error: Unable to connect to the database. Host 'XX.XX.XX.XX' is not allowed to connect to this MySQL server What do I need to do to allow the host to connect? Thanks, Kelly Kelly S. Brace Information Technology Exchange Center Twin Rise 200 1300 Elmwood Avenue Buffalo, NY 14222 http://www.itec.suny.edu Main: 716-878-4832 Fax: 716-878-3485 Office: 716-878-3984 Cell: 716-432-4978 [EMAIL PROTECTED]
Are my databases dragging down my page?
Someone sent me the following comments on one of my webpages: Global Statistics Total HTTP Requests: 51 Total Size: 133537 bytes Object Size Totals Object type Size (bytes) HTML: 26538 Images: 69020 Javascript: 12863 CSS: 25116 Multimedia: 0 Other: 0 External Objects External Object QTY Total Images: 40 Total Scripts: 5 Total CSS imports: 5 * * * * * * * * * * If I interpret it correctly, my total page size is a whopping 133 KB (133537 bytes). Yet I get a little less than that when I add up the numbers. I just wondered if my databases could be contributing to the figure 133 KB. If so, would that number decrease if I made more effient use of keys and/or called up only the fields I need, rather than querying every field in a table (*)? Also, how do I obtain these sorts of statistics, so I can check my page again after I've made some improvements? Thanks. __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. 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]
RE: Are my databases dragging down my page?
[snip] Total Size: 133537 bytes HTML: 26538 Images: 69020 Javascript: 12863 CSS: 25116 Multimedia: 0 Other: 0 [/snip] It's all right there and has nothing to do with your database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remote Connection via Toad for MySQL
Good good. On 24 Feb 2005, at 20:46, [EMAIL PROTECTED] wrote: BINGO!!! Thanks Dan! Kelly Daniel Walker [EMAIL PROTECTED] What version of MySQL are you using? 4.1.1 uses an enhanced password hashing system that isn't compatible with clients built for older versions. This page on MySQL's own documentation provides a number of workarounds, one of which will hopefully work in your case (assuming the problem is caused by the version number of your DBM, of course ;) http://dev.mysql.com/doc/mysql/en/old-client.html HTH Daniel Walker On 24 Feb 2005, at 20:35, [EMAIL PROTECTED] wrote: Client does not support authentication
Re: Excessive Server Load Problems
At 05:59 AM 25/02/2005, Atle Veka wrote: packs it in? Do you mean like you say that sendmail actually crashes when load goes above 5? I would guess sendmail is rather throttling itself and that you are seeing log messages about sendmail refusing connections. Is this a correct assumption? Sendmail can be configured to operate with higher loads. If sendmail actually dies, there's something really unusual going on. By packs it in I mean that its configured currently to cease working at load of 5 and above. It doesnt crash as such, but is set to shut down in the config of sendmail if load gets too high. To me it sounds like you are having problems with your database design and that you could benefit from some indexes perhaps. Do you have any indexes? If you list your table structure and queries that are bogging things down, the mysql list will be more than happy to help you. :) Its highly possible as this is the first database ive had to use with a large number of records. I shall look into indexing part of databasing now. Any suggested places to start that explains it helpfully ? Regards Chris Aitken The Web Hub Designer and Programmer Phone : 02 4648 0808 Mobile : 0411 132 075 - Turning your business into an e-business today. The Web Hub http://www.thewebhub.com.au/ [EMAIL PROTECTED] - Confidentiality Statement: This message is intended only for the use of the Addressee and may contain information that is PRIVILEDGED and CONFIDENTIAL. If you are not the intended recipient, dissemination of this communication is prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately
RE: Are my databases dragging down my page?
Oops, I guess I added wrong. Nevertheless, unrestricted linking to database tables with no keys would increase loading time, right? Is there some way to gauge the effect, other than tweaking all my scripts and tables and checking the load time again? Thanks. --- Jay Blanchard [EMAIL PROTECTED] wrote: [snip] Total Size: 133537 bytes HTML: 26538 Images: 69020 Javascript: 12863 CSS: 25116 Multimedia: 0 Other: 0 [/snip] It's all right there and has nothing to do with your database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excessive Server Load Problems
On Fri, 25 Feb 2005, Chris Aitken (The Web Hub) wrote: Its highly possible as this is the first database ive had to use with a large number of records. I shall look into indexing part of databasing now. Any suggested places to start that explains it helpfully ? One way to do it, would be to set up an identical table with a smaller dataset that you can run tests on. You need to analyze your SELECT statements and figuring out where an index would be appropriate. You should use EXPLAIN to see what index changes have resulted in; it will show you how the storage engine finds the data and if it was able to use any indexes. Links: http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/mysql-indexes.html If you're still having issues, show us your table design. :) Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery speed : IN (SELECT ...) versus IN (X, Y, Z, ...)
Hi, You may put indexes on the following fields: 1. cityname (in tblcity) 2. zip (in tblpeople) I assume it will speed up your queries. If you already do have those indexes, then try to look at the 'EXPLAIN' of the first query (with sub-query), and see the column 'type' that describes you how the tables are joined. (see: http://dev.mysql.com/doc/mysql/en/explain.html ) You may post here your EXPLAIN results. Also note that when you use the second query (B), you also spend time on the SQL1 query that you didn't consider about its time. -Eli HMax wrote: Hello list, We are currently tuning our queries speed and we found out that the ones using subqueries are quite slower than the 'usual' ones. Here is an example of a wierd behavior. We have a city list associated with zipcode, and user can search a database of people living in a given city. The problem is that a city can have several zip codes. Our first request is : SQL1 = SELECT zip FROM tblcity WHERE cityname = 'Paris' This request actually returns something like 20 results. The second request list the people living in areas with those zip codes: SQL2 = SELECT people FROM tblpeople WHERE zip IN (###) In ### we can either put - A : SQL1 - B : the list build from a recordset opened on SQL1 which would give something like : '75000', '75001', '75002', '75003', etc... Queries speed are 0.16s for A, and 0.05s for B. Can anybody explain this behavior, and maybe offer some advices on optimizing our queries. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment insert-delete-insert
Say a row is inserted into a table with an auto_increment column and then deleted before another record is inserted. When a new row is inserted, will the value of the auto_increment column be the same as the deleted record's, or will it be one greater? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SUM and DECIMAL field
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 LAFONTAINE Julien - LYO wrote: Hi Mark, Thank you for your help. I have the same problem when I execute the query with the MySQL client on my AIX server : mysql select sum(AMFTPF) from DWH_AMF; +-+ | sum(AMFTPF) | +-+ |12000,00 | +-+ 1 row in set (0,03 sec) The problem occurs only when I use an operator like SUM, AVG, MAX, MIN... mysql select AMFTPF from DWH_AMF LIMIT 1; ++ | AMFTPF | ++ | 15.00 | ++ 1 row in set (0,01 sec) Julien, I went ahead and reported this as a bug, you can track it via http://bugs.mysql.com/bug.php?id=8794 (You might want to comment on it, that way you will get e-mails as its status changes). -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity www.mysql.com MySQL User Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: http://www.mysqluc.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCHk0dtvXNTca6JD8RAoYBAJ4qTvbLE/1+HJbccqr7W8LEs2m4ygCfZW24 /mWAYntne1Gvv+K78DLgECY= =guxR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment insert-delete-insert
Jim McAtee wrote: Say a row is inserted into a table with an auto_increment column and then deleted before another record is inserted. When a new row is inserted, will the value of the auto_increment column be the same as the deleted record's, or will it be one greater? Greater. -- 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]
RE: Are my databases dragging down my page?
David Blomstrom [EMAIL PROTECTED] wrote on 02/24/2005 04:10:17 PM: Oops, I guess I added wrong. Nevertheless, unrestricted linking to database tables with no keys would increase loading time, right? Is there some way to gauge the effect, other than tweaking all my scripts and tables and checking the load time again? Thanks. --- Jay Blanchard [EMAIL PROTECTED] wrote: [snip] Total Size: 133537 bytes HTML: 26538 Images: 69020 Javascript: 12863 CSS: 25116 Multimedia: 0 Other: 0 [/snip] It's all right there and has nothing to do with your database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: Inasmuch as slow query performance will result in slow data retrieval, bad queries and poor database design can slow down the production of data-driven web pages. The total time it takes to see a requested web page depends on several factors: A) The time it takes to request the page from the server B) The time it takes for the server to validate the user and process the request C) The time it takes for the server to produce the requested page D) The time it takes to move the requested page from the server to the user E) The time it takes for the user's browser to render the requested page. A) you cannot control. B) is determined by the resources available to your web server. Generally this takes less than a millisecond but depending on how much preprocessing is required could take longer (like CGI strings) C) is what you affect most with your application and database design. Building complex forms and lots of slow data transfers could make this step take several seconds or more. Check your queries for efficiency. Do not ask for the same data more than once, if you don't need to. Avoid most WYSIWYG web page builders as their code is usually not as efficient as hand-written. D) is related to 2 factors: how much data is being sent to the user (which you may or may not be able to control) and how fast is the user's connection. If you have gobs and gobs of information (like huge pictures or long lists) then the amount of information the user has requested is large and it will take a while to get there. The faster the connection, the less time it takes to transfer the same quantity of data. E) Believe it or not, you can have an effect on this. Poorly designed HTML can sometimes take quite a while to render even on very fast equipment. The stats you were given tell you how much information a user needs to render a page of your site. 25K of just formatting information sounds like overkill (your CSS content) as you are only formatting 26K of HTML. Try to simplify your presentation so that you can reduce the size of your CSS file(s). To answer your question: No, doing lots of queries on web pages does not slow them down unless you have written slow queries. Sure there is some overhead (usually on the order of sub-seconds) for each query (query request+server processing+data returned+data formatting) but if you have well-tuned queries, it shouldn't hurt you too much. I have one page that has to render nearly 1MB of data on a single page. It takes less than a second to get the info from the database but several seconds to format it and send it down the wire to the user. In this case there is no tweaking I can do to the database to get at the data any faster so I am working on faster formatting and trying to simplify the final output so that it takes less time to head down the wire (smaller packet) and render in front of the user(less work for the browser = faster presentation). Benchmarking any application's performance requires you to measure, tweak, repeat. Sometimes the tweak is a major change but you always keep the end goal in mind, the user's impression. If it seems fast because you can render your information on-the-fly, your users will think it's a fast site. If you leave the user hanging for several seconds while waiting for their screen to change, you end up giving the impression that you have a slow site. Why do you think so many programs have a splash screen when they start up? It distracts the user with graphics and text so that they don't count the rest of the ticks until the program finishes setting itself up. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: auto_increment insert-delete-insert
Jim McAtee [EMAIL PROTECTED] wrote on 02/24/2005 04:50:11 PM: Say a row is inserted into a table with an auto_increment column and then deleted before another record is inserted. When a new row is inserted, will the value of the auto_increment column be the same as the deleted record's, or will it be one greater? One greater. Once an auto_increment value is used, it's used. Gaps in these numbers are no big deal as they aren't intended for end-user consumption. They are intended to aid the DBA in uniquely identify each and every row in a table, even if those rows contain identical data in all of their other columns. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
mysqldump and auto_increment
The reason I asked about auto_increment behavior is that I'm looking at the output from mysqldump --opt and there are no commands to preserve/set the auto_increment value. Is there a mysqldump option to do this? Or will restoring from a dump always leave the auto_increment value one greater than the value of the largest id inserted? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SLOW Mysql Subquery
I am new to MySql. I have a query with a subselect that is running very slow (28 seconds). SELECT *,MATCH(title, descr_part) AGAINST (Project Manager IN BOOLEAN MODE) AS score FROM listings WHERE MATCH(title, descr_part) AGAINST (Project Manager IN BOOLEAN MODE ) and zip in (SELECT zipcode FROM zipData WHERE (POW((69.1*(lon-37.57348)*cos(122.3225/57.3)),2)+POW((69.1*(lat-122.3225)),2))(10*10) ) limit 10; Both tables do have a lot of Records. When I run them seperatly, they are both very fast (see below). As stated above, I am very new to this, so please be gentel. ;-) Any sugestions on how to do this better/different would be greatlfuly appreciated. Table 1 (listings); +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | lst_id | bigint(20) | | PRI | NULL| auto_increment | | cat_id | bigint(20) | | | 0 || | sub_cat_id | bigint(20) | | | 0 || | mem_id | bigint(20) | | | 0 || | trb_id | bigint(20) | | | 0 || | added | bigint(20) | | | 0 || | title | varchar(250) | | MUL | || | description | text | | | || | descr_part | varchar(250) | | MUL | || | photo | varchar(250) | | | || | privacy | char(1) | | | n || | anonim | char(1) | | | n || | zip | int(10) | | MUL | 0 || | show_deg| char(3) | | | any || | stat| char(1) | | | p || | live| bigint(20) | | | 0 || +-+--+--+-+-++ Table 2 (zipdata); +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | country | bigint(20) | | | 0 | | | zipcode | varchar(5) | | PRI | | | | lon | varchar(8) | | MUL | | | | lat | varchar(8) | | MUL | | | | city| varchar(250) | | MUL | | | | state | char(3) | | MUL | | | +-+--+--+-+-+---+ Query 1 : SELECT zipcode FROM zipData WHERE (POW((69.1*(lon-37.57348)*cos(122.3225/57.3)),2)+POW((69.1*(lat-122.3225)),2))(5*5); +-+ | zipcode | +-+ | 94002 | | 94010 | | 94070 | | 94401 | | 94402 | | 94403 | | 94404 | | 94497 | +-+ 8 rows in set (0.33 sec) Query 2 SELECT *, MATCH(title, descr_part) AGAINST (manager IN BOOLEAN MODE) AS score FROM listings WHERE MATCH(title, descr_part) AGAINST (manager IN BOOLEAN MODE ) limit 5; Won't put all the output here but here is an example | 93 | 9000 | 9001 | 63 | 0 | 1109027856 | Architect project manager | ARCHITECTS Do you fearlessly create? Do you intelligently realize? Do you collaboratively develop? We do. Come join us! Looking for designers (Architects Interiors) that want to make a difference. ...brPlease visit a href=http://www.careersite.com/perl/vaui/Search/top/job/9F064-1B68B?pid=295matches.page=5;bTHIS LINK /b/afor more information and to apply.br | ARCHITECTS Do you fe | no| n | n | 95401 | any | a| 2592000 | 1 | +++++++---++--+---+-++---+--+--+-+---+ 5 rows in set (0.00 sec) Explain for query 1: ++-+-+---+---+-+-+--+---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+-+---+---+-+-+--+---+--+ | 1 | SIMPLE | zipData | index | NULL | zip_lat_lon | 21 | NULL | 42037 | Using where; Using index |
mysql vs postgresql
hi, just want to know the main benefits of mysql over postgresql. thanks, Payam Shabanian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
latest GPL version
what is the latest GPL licensed version of mysql? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
modulo operation
Hi, I just wanted to know if there is a special reason for the mathematically incorrect implementation of the modulo-operation in mysql. Using a correct modulo operation on a negative number would still result in a positive number: -1 % 2 == 1 (mysql: -1) -5 % 3 == 1 (mysql: -2) -1 % 4 == 3 (mysql: -1) (For the exact definition see http://en.wikipedia.org/wiki/Modulo_operation ) AFAIK most programming languages implement modulo in this wrong way (except pascal i think), but I don't have a clou why it should stay that way as I find this behaviour rather disturbing... Cheers, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I find the records that are NOT IN the list
I have an IN() list of 107 IDs (PK) out of about 6000 possible. I do this query and I get 105 rows back. I want to know which two [107 - 105 = 2] of the IDs in the IN() list are absent? # 105 rows SELECT count(*) FROM mytable WHERE id IN (11704, 10144, 11842, 11299, 11192, 11563, 11378, 10343, 10752, 10626, 11916, 11456, 11813, 11337, 10219, 11356, 11800, 10243, 10251, 11357, 11353, 11267, 12110, 200044, 11875, 11060, 10233, 10865, 10264, 10857, 10659, 10266, 10987, 11317, 11868, 10858, 11087, 11088, 10260, 11321, 11350, 10247, 10258, 10248, 11232, 10588, 11086, 11828, 10055, 11347, 10278, 11349, 10261, 11499, 11351, 11316, 12284, 12240, 12265, 11340, 10708, 11041, 11853, 12255, 11507, 11788, 10067, 10888, 11875, 11333, 10867, 10938, 11030, 10654, 10538, 10918, 11068, 11237, 11060, 10127, 10495, 10035, 10294, 10173, 10098, 10282, 10383, 10049, 10076, 10277, 10106, 10541, 10176, 10131, 10252, 11051, 11152, 11932, 11318, 10886, 10605, 10029, 11857, 10549, 10329, 11510, 10539); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there something broken with IN()? -- SOLVED
My bad. As it turns out, there are duplicates in the IN() listing. It must
be that mysql 'uniques' the list before operating on it.
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 24, 2005 5:14 PM
To: mysql@lists.mysql.com
Subject: Is there something broken with IN()? WAS: How can I
find the records that are NOT IN the list
Now I'm really confused. I decided to just brute force this
while I waited
for a real answer. I threw in a couple test numbers I knew would fail
555 and 6. How come none of the values aside from the
two I threw in
for good measure are failing?! Is there something broken with
IN() on
v4.0.18, for pc-linux-gnu on i686
?php
$foo = array (555, 11704, 10144, 11842, 11299, 11192,
11563, 11378,
10343, 6, 10752, 10626, 11916, 11456, 11813, 11337,
10219, 11356, 11800,
10243, 10251, 11357, 11353, 11267, 12110, 200044, 11875, 11060, 10233,
10865, 10264, 10857, 10659, 10266, 10987, 11317, 11868,
10858, 11087, 11088,
10260, 11321, 11350, 10247, 10258, 10248, 11232, 10588,
11086, 11828, 10055,
11347, 10278, 11349, 10261, 11499, 11351, 11316, 12284,
12240, 12265, 11340,
10708, 11041, 11853, 12255, 11507, 11788, 10067, 10888,
11875, 11333, 10867,
10938, 11030, 10654, 10538, 10918, 11068, 11237, 11060,
10127, 10495, 10035,
10294, 10173, 10098, 10282, 10383, 10049, 10076, 10277,
10106, 10541, 10176,
10131, 10252, 11051, 11152, 11932, 11318, 10886, 10605,
10029, 11857, 10549,
10329, 11510, 10539);
echo foo = .count($foo).BR;
$i = 1;
foreach ($foo as $k = $v)
{
echo $i++. ;
$sth = SQL_QUERY('SELECT id FROM mytable WHERE id =
'.$v.' LIMIT 1',
true);
if ($sth)
{
if (SQL_NUM_ROWS($sth) 1) echo BCANNOT FIND:
.$v./BBR;
}
}
?
foo = 109
1 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 555
LIMIT 1
CANNOT FIND VULN: 555
2 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11704
LIMIT 1
3 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10144
LIMIT 1
4 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11842
LIMIT 1
5 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11299
LIMIT 1
6 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11192
LIMIT 1
7 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11563
LIMIT 1
8 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11378
LIMIT 1
9 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10343
LIMIT 1
10 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 6
LIMIT 1
CANNOT FIND VULN: 6
11 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10752
LIMIT 1
12 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10626
LIMIT 1
13 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11916
LIMIT 1
14 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11456
LIMIT 1
15 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11813
LIMIT 1
16 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11337
LIMIT 1
17 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10219
LIMIT 1
18 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11356
LIMIT 1
19 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11800
LIMIT 1
20 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10243
LIMIT 1
21 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10251
LIMIT 1
22 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11357
LIMIT 1
23 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11353
LIMIT 1
24 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11267
LIMIT 1
25 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 12110
LIMIT 1
26 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 200044
LIMIT 1
27 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11875
LIMIT 1
28 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11060
LIMIT 1
29 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10233
LIMIT 1
30 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10865
LIMIT 1
31 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10264
LIMIT 1
32 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10857
LIMIT 1
33 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10659
LIMIT 1
34 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10266
LIMIT 1
35 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 10987
LIMIT 1
36 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE
scan_id = 11317
LIMIT 1
37 SQL:
Is there something broken with IN()? WAS: How can I find the records that are NOT IN the list
Now I'm really confused. I decided to just brute force this while I waited
for a real answer. I threw in a couple test numbers I knew would fail
555 and 6. How come none of the values aside from the two I threw in
for good measure are failing?! Is there something broken with IN() on
v4.0.18, for pc-linux-gnu on i686
?php
$foo = array (555, 11704, 10144, 11842, 11299, 11192, 11563, 11378,
10343, 6, 10752, 10626, 11916, 11456, 11813, 11337, 10219, 11356, 11800,
10243, 10251, 11357, 11353, 11267, 12110, 200044, 11875, 11060, 10233,
10865, 10264, 10857, 10659, 10266, 10987, 11317, 11868, 10858, 11087, 11088,
10260, 11321, 11350, 10247, 10258, 10248, 11232, 10588, 11086, 11828, 10055,
11347, 10278, 11349, 10261, 11499, 11351, 11316, 12284, 12240, 12265, 11340,
10708, 11041, 11853, 12255, 11507, 11788, 10067, 10888, 11875, 11333, 10867,
10938, 11030, 10654, 10538, 10918, 11068, 11237, 11060, 10127, 10495, 10035,
10294, 10173, 10098, 10282, 10383, 10049, 10076, 10277, 10106, 10541, 10176,
10131, 10252, 11051, 11152, 11932, 11318, 10886, 10605, 10029, 11857, 10549,
10329, 11510, 10539);
echo foo = .count($foo).BR;
$i = 1;
foreach ($foo as $k = $v)
{
echo $i++. ;
$sth = SQL_QUERY('SELECT id FROM mytable WHERE id = '.$v.' LIMIT 1',
true);
if ($sth)
{
if (SQL_NUM_ROWS($sth) 1) echo BCANNOT FIND:
.$v./BBR;
}
}
?
foo = 109
1 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 555
LIMIT 1
CANNOT FIND VULN: 555
2 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11704
LIMIT 1
3 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10144
LIMIT 1
4 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11842
LIMIT 1
5 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11299
LIMIT 1
6 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11192
LIMIT 1
7 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11563
LIMIT 1
8 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11378
LIMIT 1
9 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10343
LIMIT 1
10 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 6
LIMIT 1
CANNOT FIND VULN: 6
11 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10752
LIMIT 1
12 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10626
LIMIT 1
13 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11916
LIMIT 1
14 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11456
LIMIT 1
15 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11813
LIMIT 1
16 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11337
LIMIT 1
17 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10219
LIMIT 1
18 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11356
LIMIT 1
19 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11800
LIMIT 1
20 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10243
LIMIT 1
21 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10251
LIMIT 1
22 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11357
LIMIT 1
23 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11353
LIMIT 1
24 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11267
LIMIT 1
25 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 12110
LIMIT 1
26 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 200044
LIMIT 1
27 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11875
LIMIT 1
28 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11060
LIMIT 1
29 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10233
LIMIT 1
30 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10865
LIMIT 1
31 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10264
LIMIT 1
32 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10857
LIMIT 1
33 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10659
LIMIT 1
34 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10266
LIMIT 1
35 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10987
LIMIT 1
36 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11317
LIMIT 1
37 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11868
LIMIT 1
38 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10858
LIMIT 1
39 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11087
LIMIT 1
40 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11088
LIMIT 1
41 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 10260
LIMIT 1
42 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11321
LIMIT 1
43 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE scan_id = 11350
LIMIT 1
44 SQL: SELECT
