Re: Re: sleeping processes
Hi Carl, thank you for your reply. did you have a look at my original posting where I included the code? your code (omitting the error routines) is essentially like this: sock=mysql_init(0)) mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0) mysql_select_db(sock,gvDatabase) // possibly looped mysql_real_query(sock, query, strlen(query)) mysql_free_result(tableRes); // eoloop // At the end of the program, I close the socket. 1. I don't open a connection for each query, using the already open socket instead (good for some things, not good for other ones.) where in your code are you closing the connection? I tried both: to embrace the query/free_result into mysql_init/mysql_close commands within the loop as well as doing the mysql_init/mysql_close at the beginning and at the end of the program. (just as you do and as I posted in my first message) 2. You have to free the result set after every select. I free the result set after every select until NULL. hmmm... anyway many thanks, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not able to connect to Mysql server from network machine
Hello. Check with netstat if MySQL is listening on 3306 port on the server. Use traffic analyzers to see if your packets reach the server. Enable the general query log on the server to see if server receives your requests to connect. See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html http://dev.mysql.com/doc/mysql/en/query-log.html Reema Troiana [EMAIL PROTECTED] wrote: Hi All, I'm trying to connect to MySql server from MySql Administrator on a network machine. I specify the Server host as IP of the machine where DB server is running and Port as 3306. Username as 'root' and the password I have tried with other usernames and specifying machine name instead of IP. It doesn't work in any case. But i always get this error: Could not connect to the specififed instance. MySQL Error number 2003 and it says: If you want to check network connection, please ping When i click ping i get reply from the machine i'm trying to connect to I'm able to connect to localhost, i.e., if i have the DB server on the same machine. MySql server is running on XP machine. Is there any option that i have to enable to make network machine to log on to Mysql DB server?? I'll appreciate any help. Thanks, Reema -- 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]
Re: Slow LIMIT Query
On 05/06/2005, Doug V wrote: In your followup message [from [EMAIL PROTECTED], you mention reverse sorting the query. I imagine on the application side I would need to reverse sort again to get the correct order. Are there any other ways to speed up such a query? I find similar behaviour with one of my standard testtables: CREATE TABLE dtfoo ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, dt DATETIME, d VARCHAR(100), KEY (dt) ) ENGINE = MyISAM; which is filled with 25 records with random dt columns BETWEEN '2000-01-01 00:00:00' AND '2005-12-31 23:59:59', and in which the d column just contains a character copy of dt (to have *some* other data ;-). Essentially: LIMIT clauses from the start of the SELECT are extremely fast, while LIMIT clauses from the end of the SELECT are extremely slow (even more so when the result set includes not only the id but also the the dt column and/or the d column.) - we're talking factors 1000 between fast and slow when both dt and d are included in the result set. I guess this is because the index on dt can be used to *locate* a record, (as in 'WHERE dt @some_datetime'), but *not* to count how many records come before a certain @some_datetime, wihch is needed for a LIMIT clause. By the way, if you don't want the reverse ordering from SELECT id FROM dtfoo ORDER BY dt DESC LIMIT 0, 10; you can use a subquery (if your on MySQL 4.1.x): SELECT dtfoo2.id FROM (SELECT id, dt FROM dtfoo ORDER BY dt DESC LIMIT 0, 10) AS dtfoo2 ORDER BY dtfoo2.dt ASC; which is still very fast. Personally, I never feel comfortable with LIMIT n,m clauses where n is high (perhaps maybe for implementing pagination on web pages). I always prefer to set my 'start' in the WHERE clause, so the index can do its work, e.g: SELECT id, dt FROM dtfoo WHERE dt @some_datetime ORDER BY dt ASC LIMIT 0, 10; -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mirroring oracle database
Hello Edward, I'm trying to convince some people here to adopt MySql as a relational database here. However, we can't start from a clean slate; we have a very mature oracle database that applications point to right now, and so we need a migration path. I was thinking of taking the following steps: a) finding a Java API that transparently supports both MySQL and Oracle data access and stored procedure calls. b) instrumenting the Oracle database so that all tables support timestamps on data rows. c) mirroring the Oracle database in MySQL. d) making interface code connecting the MySQL database to the Oracle database (and both applying updates to the database as well as data. In other words, I'm looking to make a MySQL - Oracle mirroring tool, and was wondering if anybody had experience with this sort of thing. As I see it, if we pull this off we could save quite a bit in licensing costs - we'd still have oracle around, but it would only be a datastore for talking to other oracle databases, and run by batch, not accessed by end users. Ed ( ps - here are the concerns I have right now about doing this... How well can stored procs be translated over? how about views, triggers and indexes? ) MySQL doesn't have CHECK constraints. Only version 5 (which is in early beta) has Views, Triggers and Stored Procedures. IF you can convert your existing application to MySQL is heavily depending on what you're using with Oracle... As a personal note: if you want to save license costs, did you ever take a look at Fyracle? http://www.janus-software.com/fb_fyracle.html With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems through gateway
Hi, The performance of the data transfers using the direct socket connection goes from 15 milli sec (in the lab) to ~32 milli sec (in pseudo production env). But the database calls go from 1 sec to several seconds (have not measured this yet). The database was exactly the same in both trials. We are moving small amounts of data (100 bytes) in any query. bogus ethernet cards or network equipements ? last year one ethernet cards on our firewall start to produce errors, resulting in a really slow transfert rate and long latency, could this apply to you ? Does this shed any light? Celona, Paul - AES wrote: I am running mysql 4.0.18 on Windows 2003 server which also hosts my apache tomcat server. My applet makes a connection to the mysql database on the server as well as a socket connection to a service on the same server. In the lab with only a hub between the client and server, the application performs well and data is transferred quickly. In the deployed environment with a pair of gateways in between, socket performance is not affected much, but the application gui bogs down on the database queries. Performance is so slow that some simple GUI updates take up to 5-7 seconds with only a simple 1 table update occurring. Does anyone have experience with this and/or can provide some insight? From: gerald_clark If your applet is making connections on each page, you might be having reverse dns problems. From: Shawn Green It sounds like you don't have all of your indexes declared on your production database. There could also be an issue of network lag between your application server and your database server. The best performing applications use the fewest trips to the database to accomplish what they need. You may want to examine your application design and minimize the number of trips you make to the server. For example, assume you run two queries, one to get a list of departments and another to list the people in each department. If you design your application to perform one lookup to get the departments list then loop through that list to find the department's people, you are making way too many trips to the database. A more efficient design is to JOIN the two tables and submit just one query. Then, as you process the results, you detect when the Department value changes and adjust your OUTPUT accordingly. Could it be the volume of data you are trying to present is just that much larger with your production data set than it was with your development dataset that it's taking that much longer to format the output? You provided so FEW details of the actual issue, it's VERY hard to be more helpful. Can you provide more details of what isn't working the way you want and why? Shawn Green Database Administrator Unimin Corporation - Spruce Pine This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail. -- 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]
reporting tools for mysql- OLAP functionality possibly
Hi, I have a system that im busy designing using PHP, mysql apache and linux. I will need to do alot of intense reporting from the system, like viewing statistics for country, province, district etc... so its basically drill down and roll up functionality (OLAP). This will probably run ontop of MySQL. It will also doing nice graphing etc... I have googled and searched on sourceforge but havent found too much, especially that run on linux. Is there anything that any of you can recommend, also that you would not recommend. Please feel free to give comments and links. thanks in advance -- Angelo Zanetti Z Logic www.zlogic.co.za [c] +27 72 441 3355 [t] +27 21 469 1052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: sleeping processes
Ronny, Oops, no I didn't. Odd that my code (which looks very similar to your code) has no problems but your code does. Has to be something in your code (always work from something that works to something that doesn't.) Is there any way you can subset your code to just a few lines to get it to work and then expand it to what you really want to accomplish? Can you run a debugger on it to make certain what you think is happening is really what is happening? Thanks, Carl - Original Message - From: Ronny Melz [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 06, 2005 3:58 AM Subject: Re: Re: sleeping processes Hi Carl, thank you for your reply. did you have a look at my original posting where I included the code? your code (omitting the error routines) is essentially like this: sock=mysql_init(0)) mysql_real_connect(sock,ipNumber,userName,password,gvDatabase,3306,NULL,0) mysql_select_db(sock,gvDatabase) // possibly looped mysql_real_query(sock, query, strlen(query)) mysql_free_result(tableRes); // eoloop // At the end of the program, I close the socket. 1. I don't open a connection for each query, using the already open socket instead (good for some things, not good for other ones.) where in your code are you closing the connection? I tried both: to embrace the query/free_result into mysql_init/mysql_close commands within the loop as well as doing the mysql_init/mysql_close at the beginning and at the end of the program. (just as you do and as I posted in my first message) 2. You have to free the result set after every select. I free the result set after every select until NULL. hmmm... anyway many thanks, Ronny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.2 - Release Date: 6/4/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delivery reports about your e-mail
The original message was received at Mon, 6 Jun 2005 11:40:11 +0200 from mysql.com [50.161.69.24] - The following addresses had permanent fatal errors - mysql@lists.mysql.com - Transcript of session follows - while talking to lists.mysql.com.: MAIL From:[EMAIL PROTECTED] 501 [EMAIL PROTECTED] Refused -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: If statment in query
Sebastian [EMAIL PROTECTED] wrote on 06/05/2005 02:23:45 AM: I have two fields: topic | title topic does not always have data in it, so i want to select `title` when `topic` is null.. i thought i could do this (does not work): IF(title IS NULL, topic, title) AS heading Thanks. You can also use the COALESCE() function to get the _first_ non-null value from a list of values. To use your example (and extend it by one condition) assume for a moment that if title is also null you want the value 'n/a' to appear. This is simple two-field failover (with a null result if title is also null) exactly like the IF() and IFNULL() solutions already posted: SELECT COALESCE(topic, title) as heading from tablename; This has the final default value, avoiding a null result completely: SELECT COALESCE(topic, title, 'n/a') as heading from tablename; It works like this: a) if topic is not null, return topic b) if topic is null and title is not, return title c) if both topic and title are null, return 'n/a' IF there were 16 fields,formulas, or values in its list, COALESCE() would have moved from term to term looking, checking all 16 items in turn, for the first non-null. If every term is null, COALESCE() returns a NULL. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Not able to connect to Mysql server from network machine
Hi All, I'm trying to connect to MySql server from MySql Administrator on a network machine. I specify the Server host as IP of the machine where DB server is running and Port as 3306. Username as 'root' and the password I have tried with other usernames and specifying machine name instead of IP. It doesn't work in any case. But i always get this error: Could not connect to the specififed instance. MySQL Error number 2003 and it says: If you want to check network connection, please ping When i click ping i get reply from the machine I'm trying to connect to I'm able to connect to localhost, i.e., if i have the DB server on the same machine. MySql server is running on XP machine. Is there any option that i have to enable to make network machine to log on to Mysql DB server?? I'll appreciate any help. Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201
Re: Slow LIMIT Query
Hi Doug, with a desc index on stuffed_date, an optimiezd table, the query runs in : mysql select * from stuff order by stuffed_date desc limit 18,10; +---+--+ | id| stuffed_date | +---+--+ | 88233 | 2005-07-08 | | 88228 | 2005-07-08 | | 88218 | 2005-07-08 | | 88198 | 2005-07-08 | | 88153 | 2005-07-08 | | 88148 | 2005-07-08 | | 88138 | 2005-07-08 | | 88118 | 2005-07-08 | | 88078 | 2005-07-08 | | 87993 | 2005-07-08 | +---+--+ 10 rows in set (0.17 sec) This is not 0s, buti don't think you can have it. A workaroud should be an auto_increment with no gap, then a select ... from stuff where id = 18 limit 10, hoping an index rang scan, for a covering index. Mathias Selon Doug V [EMAIL PROTECTED]: Hi, I have tried to simply the problem and it exists without any JOINs. have you given the query ? SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 18, 10 - .43 sec SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 0, 10 - .0007 sec have you described your tables ? stuffed_date is INDEXed have your given the size of each table ? The table is about 200k rows. have you list the indexes ? stuff table has several indices, including 'id' and 'stuffed_date'. have you specify the storage type ? MYISAM In your followup message, you mention reverse sorting the query. I imagine on the application side I would need to reverse sort again to get the correct order. Are there any other ways to speed up such a query? Thanks. -- 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: Not able to connect to Mysql server from network machine
rtroiana [EMAIL PROTECTED] wrote on 06/06/2005 08:42:18 AM: Hi All, I'm trying to connect to MySql server from MySql Administrator on a network machine. I specify the Server host as IP of the machine where DB server is running and Port as 3306. Username as 'root' and the password I have tried with other usernames and specifying machine name instead of IP. It doesn't work in any case. But i always get this error: Could not connect to the specififed instance. MySQL Error number 2003 and it says: If you want to check network connection, please ping When i click ping i get reply from the machine I'm trying to connect to I'm able to connect to localhost, i.e., if i have the DB server on the same machine. MySql server is running on XP machine. Is there any option that i have to enable to make network machine to log on to Mysql DB server?? I'll appreciate any help. Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201 Not everyone works or lurks on the weekends. Have you tried to RTFM? http://dev.mysql.com/doc/mysql/en/post-installation.html http://dev.mysql.com/doc/mysql/en/problems.html (especially) http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html (and) http://dev.mysql.com/doc/mysql/en/starting-server.html Use the TELNET test connection method (described in the readings) to verify you have a working network path from your remote server to the MySQL server. By the domain in your return address, I believe you work for someone who is more likely than not to have several restrictions on your network traffic. Anything that prevents traffic through port 3306 (unless you told your MySQL daemon to use a different port) between your two machines will prevent your connection. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
re: file my.cnf is missing for Solaris 8, for mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am getting the following error, when I try to use bin/mysqld_safe, Could not open require defaults file: $MYSQL_HOME/data/my.cnf Fata error in defaults handling. Program aborted Did I d/l a bad .tar.gz file from a mirror? Thank you. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpFVBikQgpVn8xrARAhJaAJ0YINBpRLhq3VZ+YFkCSHMr1arxUACeKLTe 1ld+80ihBsZC54SCp7FSuJA= =Mmb6 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
noob question
How do i explicilty create an innodb table ? I tried searching thro Manual...but did not get anything important in the create t table section. Thanks DIgz *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: noob question
How do i explicilty create an innodb table ? I tried searching thro Manual...but did not get anything important in the create t table section. CREATE TABLE ... ENGINE=InnoDB With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: solution found for my.cnf problem in mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I followed the steps in http://lists.mysql.com/internals/25364 and fixed my script, so, it would appear that the script is bad for the max version of mysql 5.0.6, for Solaris 8. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpFlmikQgpVn8xrARAh9qAJ0ekN/cqiuFPRs2urLkU5e2ulbFlQCfQ5+r TJknK26B3tkDnFGa6hrlcXM= =oLsU -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: noob question
Digvijoy Chatterjee [EMAIL PROTECTED] wrote on 06/06/2005 10:09:15 AM: How do i explicilty create an innodb table ? I tried searching thro Manual...but did not get anything important in the create t table section. Thanks DIgz *** CAUTION - Disclaimer ** This e- mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** End your CREATE TABLE command with an ENGINE=INNODB like this: CREATE TABLE ( .. column and index definitions here ... ) ENGINE=InnoDB; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: file my.cnf is missing for Solaris 8, for mysql 5.0.6
James Black wrote: I am getting the following error, when I try to use bin/mysqld_safe, Could not open require defaults file: $MYSQL_HOME/data/my.cnf Fata error in defaults handling. Program aborted Don't remember where I've read it but mysql binary packages don't read anymore the my.cnf from $MYSQL_HOME/data/my.cnf . for alternative paths try #mysqld --help --verbose | grep my.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: noob question
Thank You for the quick reply , now i wanted to create INNoDb table such that i could rollback my changes ,but here i am as I issue a rollback command ; nothing happens...is there some thing like autocommit on...or rather how do i alter standard settings of mysql client... MY MAIN QUESTION is : HOW DO I COMMIT AND ROLLBACK Thanks and Regards Digz On Mon, 2005-06-06 at 19:39, Digvijoy Chatterjee wrote: How do i explicilty create an innodb table ? I tried searching thro Manual...but did not get anything important in the create t table section. Thanks DIgz *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** Aut disce Aut Discede Aut Vincere Aut Mori Either learn or leave Either conquer or die [EMAIL PROTECTED] #4043 *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied/password change
Is there any way I can change passwd of database without knowing the administrator . I'm unable to get into the mysql prompt due to access denied message.I want to change passwd.How to do this? After change passwd do you think we need to grant some priviledges? thanks Anoop kumar V [EMAIL PROTECTED] wrote: The reason i sthat you have not provided authorisation privileges to the database or to the tables within the database for that user... Very often we think granting all to the database is enough to make our app work with a particular user - this may be true for some databases - but in mysql you must do a grant all on your_database.* for that user - you must explicitly authorise the user for each table (using * or each table name) within the database. Hope that helps, Anoop On 6/4/05, Gleb Paharenko [EMAIL PROTECTED] wrote:Hello. See: http://dev.mysql.com/doc/mysql/en/access-denied.html http://dev.mysql.com/doc/mysql/en/resetting-permissions.html Seena Blace [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 17 lines --] Hi, I have been noticing following error when trying to connect mysql. ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) or ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What could be reason? Is there any way I can connect to database without changing passwd? thanks - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out! -- 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] -- Thanks and best regards, Anoop - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out!
Re: noob question
Hi, look at this : mysql SET AUTOCOMMIT=0; mysql start transaction; mysql insert into inno values(2); mysql select * from inno; +--+ | t| +--+ |1 | |2 | +--+ 2 rows in set (0.00 sec) mysql rollback; mysql select * from inno; +--+ | t| +--+ |1 | +--+ 1 row in set (0.00 sec) more at http://dev.mysql.com/doc/mysql/en/commit.html Mathias Selon Digvijoy Chatterjee [EMAIL PROTECTED]: Thank You for the quick reply , now i wanted to create INNoDb table such that i could rollback my changes ,but here i am as I issue a rollback command ; nothing happens...is there some thing like autocommit on...or rather how do i alter standard settings of mysql client... MY MAIN QUESTION is : HOW DO I COMMIT AND ROLLBACK Thanks and Regards Digz On Mon, 2005-06-06 at 19:39, Digvijoy Chatterjee wrote: How do i explicilty create an innodb table ? I tried searching thro Manual...but did not get anything important in the create t table section. Thanks DIgz *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** Aut disce Aut Discede Aut Vincere Aut Mori Either learn or leave Either conquer or die [EMAIL PROTECTED] #4043 *** CAUTION - Disclaimer ** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** -- 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: Not able to connect to Mysql server from network machine
Thanks Gleb. I just had to manually make changes in my firewall. I allowed requests to port 3306 mysqld.exe in exceptions tab and it solved the problem. The only reason I didn't think about firewall setting before since whenever I try to run new service, I wud get a pop up from Firewall asking to allow the service or not. Thanks, Reema
Re: mysql UNION
did'n arrive. re-submitted -- sorry
Selon [EMAIL PROTECTED]:
Hi,
If we forget the first method which i mis-adviced, i can give a third which
is
generic.
suppose that you have an indexed type column on each table (what i did).
You
can work with 3 variables. If they are different, you query for a join, if
they
are equal, you transform the join to a simple query.
The only condition is to add a where clause a the column type which will
retreive empty set for the non selected conditions.
Example :!
set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where
[EMAIL PROTECTED];
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
When you have only one value, the same query gives :
mysql set @cat1='news'; set @cat2='news'; set @cat3='news';
Query OK, 0 rows affected (0.00 sec)
here the 3 variables are the same, so 2 queries will find an empty set.
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where
[EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
performance will not be affected since the index will be used for non used
tables.
Hope that helps :o)
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum;
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*
mysql set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql select * from (
- select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
- ) Temp
- where [EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*
set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+--+--+
| id | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql execute stmt;
+--+--+
| id | selected |
+--+--+
|2 | faq |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
A+
Mathias
The first method is horribly inefficient (and requires mysql 4.1+).
It reads all 3 tables, unions the resulting rows, checks for (and
removes) duplicate rows, then finally throws away roughly 2/3 of the
results (the rows from the 2 unwanted tables. Compare that to the
simple query which only addresses the 1 desired table. Mathias is
aware of this, which is why he gives the second method. It creates
the simple, one-table query using the value of $cat to choose which
table.
The big problem here is that neither of these methods actually do what
you asked for. That is, neither works if $cat is not set. With both
methods, you will get no rows unless $cat is set. In fact, the second
method will give a syntax eror, as there will be no table name in the
FROM clause.
Now, I never said this couldn't be done in SQL. Assuming $cat is
already set, the statement in $sql below should do what you asked for:
$sql = SELECT id, 'news' AS type, FROM news
Re: MySQL (SQL) Newbie.. Need help with a Query
resubmitted Selon [EMAIL PROTECTED]: hi, that's the same. If you use between, mysql do the rest for you : mysql explain SELECT * FROM passengers WHERE - reservation_date_time = '2005-01-01 12:10:00' - AND reservation_date_time = '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.01 sec) mysql explain SELECT * FROM passengers WHERE - reservation_date_time between '2005-01-01 12:10:00'AND '2005-05-01 12:10:00'; ++-++---+---++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+---++-+--+--+--+ | 1 | SIMPLE | passengers | range | reserv| reserv | 9 | NULL |1 | Using where; Using index | ++-++---+---++-+--+--+--+ 1 row in set (0.00 sec) Mathias Selon Cory Robin [EMAIL PROTECTED]: I'm trying to return all records between two dates.. The fields are datetime fields... Which is better? The following or using BETWEEN? (A little lost here) SELECT * FROM passengers WHERE reservation_date_time = '2005-01-01 12:10:00' AND reservation_date_time = '2005-05-01 12:10:00'; -- 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: mirroring oracle database
resubmitted Selon [EMAIL PROTECTED]: Hi, what is tour oracle version ? such tool can be done easily if you put your oracle database in archivelog. Be carrefull to datatypes and create your mysql database with innodb storage. Beginning the game, you can use LogMiner. A simple batch can extract the redo SQL statements and apply them to your mysql database. This will be another Heterogeneous DataGuard architecture. Why not if you have not stored procedures, triggers, views ... in your oracle database. This will surprise me if you answer me i haven't. Since it's a test like, you can install mysql v5 which supports those concepts. A+ Mathias Selon Edward Peschko [EMAIL PROTECTED]: On Sun, Jun 05, 2005 at 04:41:16PM -0700, sol beach wrote: IMO, you have much more a lively imagination than realistic, in depth technical knowlege in either MYSQL or Oracle. Current production MYSQL does NOT have stored procedures. Current production mysql doesn't, but current development does (5.02). Given that this is something that is coming online about 6 months down the fly, and is a direction that we are thinking about moving, and given how much that such an effort would save you - and given the fact that all the data in question is being backed up in an oracle database, as far as I can see, the risk is minor and the rewards major. All it really has to do is keep data for a minor interval (say, a day). Then it can be synced with the oracle database in a batch job. I say its worth a shot. If its not doable now, its perhaps doable in 6 months. And some people agree with me apparently: http://www.convert-in.com/ora2sql.htm which I was thinking about reverse engineering to an extent as a starting point. Thanks for the vote of confidence btw, and the elegent, almost statesman-way that you expressed it.. But seriously, why the testy response? Are you affiliated in any way with oracle? Isn't the whole point of mysql to ultimately provide a RDBMS that can be used instead of DB2 or Oracle anyways? And does anybody have helpful, real, experience along these lines that they'd like to share rather than just opinions? Ed -- 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: error 1418 when creating stored procedure using mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious what has changed in 5.0.6 that leds to this error: ERROR 1418: This routine is declared to be non-deterministic and to modify data and binary logging is enabled What should I be looking at changing in my CREATE PROCEDURE call to enable it to work now. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpHrkikQgpVn8xrARAsU9AJwOp7hjiQNliBEze8699S+9VnPYwACglT0N IQJ12hARPu9odCU1jRxxdts= =+DhI -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty database name error
resubmitted Selon [EMAIL PROTECTED]: variable basedir else change a param. in what you have as file, stop start and see. Mathias Selon Ed Kasky [EMAIL PROTECTED]: I added the line to the configuration file and removed --log-error= line from startup line but it still creates the /usr/local/mysql/var/yoda2.err as well as /var/log/mysql/error.log I am assuming it is reading from my.cnf as I get the following when I run mysqladmin variables log_error | /var/log/mysql/error.log I also checked for possible duplicate my.cnf files but there is only one. Is there another way to check to be sure it's reading the configuration file? At 12:40 PM Sunday, 6/5/2005, Gleb Paharenko wrote -= Unfortunately I could give suggestion only about your second question. Is it possible that /var/log/mysql/error.log created by mysqld_safe (you're specifying it with --log-error command line option) and /usr/local/mysql/var/yuda2.err is created by mysqld process if it founds problems before applying location of error log to it's internal variable? Specify log-error = /var/log/mysql/error.log in your configuration file, and check that MySQL Server actually reads this file. Ed Kasky wrote: Hello there - I have a couple of questions regarding a new install of MySql 4.1.12 on RH 7.2. Being new to this list, I sure do hope this hasn't been covered before. I have scrubbed Google and searched the archives for this list but can't find an explanation or a solution to 2 issues: 1. I get the following error when starting the daemon: 050605 7:08:51 [Warning] Found an entry in the 'db' table with empty database name; Skipped Is this something that should be fixed and if so, how does one go about it? 2. I have set the error log to /var/log/mysql/error.log in the init script: LOG_ERROR=/var/log/mysql/error.log $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file --log-error=$LOG_ERROR However, I am still getting 2 error logs: What I am assuming is the default /usr/local/mysql/var/yoda2.err and the one specified - /var/log/mysql/error.log Is this expected behavior? Is there another place aside from /etc/my.cnf that I might look? Thanks in advance for any tips and/or suggestions. Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (116 of 975): It had long since come to my attention that people of accomplishment rarely sat back and let things happen to them. They went out and happened to things. - Elinor Smith -- 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] . . . . . . . . . . . . . . . . . . Randomly Generated Quote (203 of 975): To climb steep hills requires slow pace at first. - William Shakespeare -- 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: mirroring oracle database
Hi, what is tour oracle version ? such tool can be done easily if you put your oracle database in archivelog. Be carrefull to datatypes and create your mysql database with innodb storage. Beginning the game, you can use LogMiner. A simple batch can extract the redo SQL statements and apply them to your mysql database. This will be another Heterogeneous DataGuard architecture. Why not if you have not stored procedures, triggers, views ... in your oracle database. This will surprise me if you answer me i haven't. Since it's a test like, you can install mysql v5 which supports those concepts. A+ Mathias Selon Edward Peschko [EMAIL PROTECTED]: On Sun, Jun 05, 2005 at 04:41:16PM -0700, sol beach wrote: IMO, you have much more a lively imagination than realistic, in depth technical knowlege in either MYSQL or Oracle. Current production MYSQL does NOT have stored procedures. Current production mysql doesn't, but current development does (5.02). Given that this is something that is coming online about 6 months down the fly, and is a direction that we are thinking about moving, and given how much that such an effort would save you - and given the fact that all the data in question is being backed up in an oracle database, as far as I can see, the risk is minor and the rewards major. All it really has to do is keep data for a minor interval (say, a day). Then it can be synced with the oracle database in a batch job. I say its worth a shot. If its not doable now, its perhaps doable in 6 months. And some people agree with me apparently: http://www.convert-in.com/ora2sql.htm which I was thinking about reverse engineering to an extent as a starting point. Thanks for the vote of confidence btw, and the elegent, almost statesman-way that you expressed it.. But seriously, why the testy response? Are you affiliated in any way with oracle? Isn't the whole point of mysql to ultimately provide a RDBMS that can be used instead of DB2 or Oracle anyways? And does anybody have helpful, real, experience along these lines that they'd like to share rather than just opinions? Ed -- 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: error 1418 when creating stored procedure using mysql 5.0.6
James Black [EMAIL PROTECTED] wrote on 06/06/2005 12:33:40 PM: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am curious what has changed in 5.0.6 that leds to this error: ERROR 1418: This routine is declared to be non-deterministic and to modify data and binary logging is enabled What should I be looking at changing in my CREATE PROCEDURE call to enable it to work now. Thanx. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpHrkikQgpVn8xrARAsU9AJwOp7hjiQNliBEze8699S+9VnPYwACglT0N IQJ12hARPu9odCU1jRxxdts= =+DhI -END PGP SIGNATURE- I think it may help everyone if you actually posted your CREATE STORED PROCEDURE statement. Then we can compare that against the changes logged in the manual between your original version (which was what?) and 5.0.6 and see what part of your SPROC would have become conflicted At the very least give us your previous MySQL version so that we have somewhere to start from. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Access denied/password change
hi , see -skip-grant-tables in dev.mysql.com/doc Mathias Selon Seena Blace [EMAIL PROTECTED]: Is there any way I can change passwd of database without knowing the administrator . I'm unable to get into the mysql prompt due to access denied message.I want to change passwd.How to do this? After change passwd do you think we need to grant some priviledges? thanks Anoop kumar V [EMAIL PROTECTED] wrote: The reason i sthat you have not provided authorisation privileges to the database or to the tables within the database for that user... Very often we think granting all to the database is enough to make our app work with a particular user - this may be true for some databases - but in mysql you must do a grant all on your_database.* for that user - you must explicitly authorise the user for each table (using * or each table name) within the database. Hope that helps, Anoop On 6/4/05, Gleb Paharenko [EMAIL PROTECTED] wrote:Hello. See: http://dev.mysql.com/doc/mysql/en/access-denied.html http://dev.mysql.com/doc/mysql/en/resetting-permissions.html Seena Blace [EMAIL PROTECTED] wrote: [-- text/plain, encoding 8bit, charset: iso-8859-1, 17 lines --] Hi, I have been noticing following error when trying to connect mysql. ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) or ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What could be reason? Is there any way I can connect to database without changing passwd? thanks - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out! -- 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] -- Thanks and best regards, Anoop - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed with complex Query
Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
Philip Lawatsch [EMAIL PROTECTED] wrote on 06/06/2005 01:37:37 PM: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this as a starting point: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2; Wherever the column `number_of_parts` equals the computed value of part_count, you have a complete widget. Here is a query that returns only completed widgets: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2 HAVING `number_of_parts` = `part_count`; Here is one that returns incomplete widgets: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2 HAVING `number_of_parts` `part_count`; Here is the query that tell you that construction on these widgets hasn't even started: SELECT w.`widget-id` , w.`number_of_parts` , count(pm.`part_id`) as part_count FROM widgets w LEFT JOIN part_mapping pm on w.`widget-id` = pm.`widget-id` GROUP BY 1,2 HAVING `part_count` = 0; I think you were having two mental problems: 1) how to GROUP BY across tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer To the Fine Manual (RTFM) you can get examples and more explanations of both processes. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help needed with complex Query
Philip Lawatsch wrote: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this: SELECT widgets.widget-id, number_of_parts, count(partid) AS cnt FROM widgets INNER JOIN part_mapping ON widgets.widget-id = part_mapping.widget-id GROUP BY widgets.widget-id, number_of_parts HAVING cnt = number_of_parts; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
Unless you are keeping track of whether a widget in complete or not, there is no hope but to scan the entire table to determine if a widget is complete. That's something you don't want to do. You should mark a widget as complete when it is completed. This would mean checking if a particular widget is completed when a modification occurs. Really what you are going to be doing is running the loop you spoke of in bits and pieces of time and only for widgets that require checking/updating. Then you only need to query on the completed field, which will be very fast. To do it in a single query, you going to need to use count and group by and then check for completion using HAVING (as opposed to where). On Jun 6, 2005, at 1:37 PM, Philip Lawatsch wrote: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error 1418 when creating stored procedure using mysql 5.0.6
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here are two of the stored procedures I tried, as I had forgotten to send that with my query. I am hoping that the stored procedure will now work, as it hasn't since mysql 5.0.1. Thanx for any help. CREATE PROCEDURE assignItem ( user CHAR(15), rid int, start int) BEGIN INSERT INTO curuse(rid, start, badge, card_type, dept, college, campus, fullname, ip) SELECT rid, start, n.badge, a.role, a.deptid, a.college, a.campus, concat(na.fname, , na.lname), 0 FROM nams.names na, items i, nams.netids n, nams.affiliations a WHERE i.rid=rid AND n.netid=user AND na.badge=n.badge AND a.badge=na.badge AND i.status='A' AND a.source='B' LIMIT 1; UPDATE items SET status='U' WHERE rid=rid; END; CREATE PROCEDURE deassignItem ( rid int, endtime int) BEGIN INSERT INTO transactions(start,badge,card_type,dept,college,campus,finish,lid,itemtype,rid) SELECT c.start, c.badge, c.card_type, c.dept, c.college, c.campus, endtime, i.lid, i.itemtype, rid FROM curuse c, items i WHERE i.rid=rid AND c.rid=rid; UPDATE items set status='A' where rid=rid; DELETE FROM curuse WHERE rid=rid; END; - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCpJYGikQgpVn8xrARAlslAKCMf8ChA6s+pngbJ82D5WWOjZYLvACeJDPD F4dI37k5oEv1H8SeaLfLB24= =7MBu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
[EMAIL PROTECTED] wrote: I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this as a starting point: snip I think you were having two mental problems: 1) how to GROUP BY across tables (creating a JOIN). and 2) How to use a HAVING clause. If you Refer To the Fine Manual (RTFM) you can get examples and more explanations of both processes. Thanks a lot, this did the trick! kind regards Philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem building 4.1.12 on HP-UX 10.20
I'm trying to build mysql-4.1.12 on HP-UX 10.20 with gcc-3.4.3. HP-UX
10.20 has CMA threads.
I have the following build failure:
gmake[4]: Entering directory `/opt/build/mysql-4.1.12/innobase/srv'
gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I./../../include
-I../../include -I/opt/TWWfsw/readline50/include
-I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/ncurses54/include
-I/opt/TWWfsw/ncurses54/include/ncurses -D_REENTRANT -DDBUG_OFF
-DDBUG_OFF -O2 -march=1.1 -D_REENTRANT -DHAVE_BROKEN_SNPRINTF
-DSIGNALS_DONT_BREAK_READ -DDO_NOT_REMOVE_THREAD_WRAPPERS -DHPUX10
-DSIGNAL_WITH_VIO_CLOSE -DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT
-DHAVE_POSIX1003_4a_MUTEX -DDEBUG_OFF -DUNIV_MUST_NOT_INLINE
-DUNIV_HPUX -DUNIV_HPUX10 -c srv0srv.c
srv0srv.c: In function `srv_suspend_thread':
srv0srv.c:675: error: aggregate value used where an integer was expected
srv0srv.c: In function `srv_release_threads':
srv0srv.c:739: error: aggregate value used where an integer was expected
gmake[4]: *** [srv0srv.o] Error 1
gmake[4]: Leaving directory `/opt/build/mysql-4.1.12/innobase/srv'
The errant code:
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
(ulong) os_thread_get_curr_id(), (ulong) slot_no,
(ulong) srv_meter[SRV_RECOVERY]);
}
The prototype for os_thread_get_curr_id() is:
os_thread_id_t
os_thread_get_curr_id(void);
os_thread_id_t is defined as:
typedef struct CMA_T_HANDLE {
cma_t_address field1;
short int field2;
short int field3;
} cma_t_handle;
typedef cma_t_handle cma_t_thread;
typedef cma_t_thread pthread_t;
typedef pthread_t os_thread_t;
typedef os_thread_t os_thread_id_t;
So, gcc is complaining about the cast from cma_t_handle to ulong. What
should I do?
--
albert chin ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE with INNODB
hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal
Re: Problem building 4.1.12 on HP-UX 10.20
In the last episode (Jun 06), Albert Chin said:
I'm trying to build mysql-4.1.12 on HP-UX 10.20 with gcc-3.4.3. HP-UX
10.20 has CMA threads.
I have the following build failure:
gmake[4]: Entering directory `/opt/build/mysql-4.1.12/innobase/srv'
gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I./../../include
[...]
-DUNIV_HPUX -DUNIV_HPUX10 -c srv0srv.c
srv0srv.c: In function `srv_suspend_thread':
srv0srv.c:675: error: aggregate value used where an integer was expected
srv0srv.c: In function `srv_release_threads':
srv0srv.c:739: error: aggregate value used where an integer was expected
gmake[4]: *** [srv0srv.o] Error 1
gmake[4]: Leaving directory `/opt/build/mysql-4.1.12/innobase/srv'
The errant code:
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
(ulong) os_thread_get_curr_id(), (ulong) slot_no,
(ulong) srv_meter[SRV_RECOVERY]);
}
Shouldn't os_thread_get_curr_id() be
os_thread_pf(os_thread_get_curr_id()) ? Try that, or just remove the
fprintfs; they are debugging code that assumes that a pthread_t is a
printable type (there is no such guarantee).
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem building 4.1.12 on HP-UX 10.20
On Mon, Jun 06, 2005 at 04:19:16PM -0500, Dan Nelson wrote:
In the last episode (Jun 06), Albert Chin said:
I'm trying to build mysql-4.1.12 on HP-UX 10.20 with gcc-3.4.3. HP-UX
10.20 has CMA threads.
I have the following build failure:
gmake[4]: Entering directory `/opt/build/mysql-4.1.12/innobase/srv'
gcc -DHAVE_CONFIG_H -I. -I. -I.. -I./../include -I./../../include
[...]
-DUNIV_HPUX -DUNIV_HPUX10 -c srv0srv.c
srv0srv.c: In function `srv_suspend_thread':
srv0srv.c:675: error: aggregate value used where an integer was expected
srv0srv.c: In function `srv_release_threads':
srv0srv.c:739: error: aggregate value used where an integer was expected
gmake[4]: *** [srv0srv.o] Error 1
gmake[4]: Leaving directory `/opt/build/mysql-4.1.12/innobase/srv'
The errant code:
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
(ulong) os_thread_get_curr_id(), (ulong) slot_no,
(ulong) srv_meter[SRV_RECOVERY]);
}
Shouldn't os_thread_get_curr_id() be
os_thread_pf(os_thread_get_curr_id()) ? Try that, or just remove the
fprintfs; they are debugging code that assumes that a pthread_t is a
printable type (there is no such guarantee).
Thanks. Patch below.
--
albert chin ([EMAIL PROTECTED])
-- snip snip
--- innobase/srv/srv0srv.c.orig Mon Jun 6 17:07:35 2005
+++ innobase/srv/srv0srv.c Mon Jun 6 17:09:10 2005
@@ -672,8 +672,8 @@
if (srv_print_thread_releases) {
fprintf(stderr,
Suspending thread %lu to slot %lu meter %lu\n,
- (ulong) os_thread_get_curr_id(), (ulong) slot_no,
- (ulong) srv_meter[SRV_RECOVERY]);
+ (ulong) os_thread_pf(os_thread_get_curr_id()),
+ (ulong) slot_no, (ulong) srv_meter[SRV_RECOVERY]);
}
slot = srv_table_get_nth_slot(slot_no);
@@ -735,7 +735,8 @@
if (srv_print_thread_releases) {
fprintf(stderr,
Releasing thread %lu type %lu from slot %lu meter %lu\n,
- (ulong) slot-id, (ulong) type, (ulong) i,
+ (ulong) os_thread_pf(slot-id),
+ (ulong) type, (ulong) i,
(ulong) srv_meter[SRV_RECOVERY]);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE with INNODB
Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Empty database name error
variable basedir else change a param. in what you have as file, stop start and see. Mathias Selon Ed Kasky [EMAIL PROTECTED]: I added the line to the configuration file and removed --log-error= line from startup line but it still creates the /usr/local/mysql/var/yoda2.err as well as /var/log/mysql/error.log I am assuming it is reading from my.cnf as I get the following when I run mysqladmin variables log_error | /var/log/mysql/error.log I also checked for possible duplicate my.cnf files but there is only one. Is there another way to check to be sure it's reading the configuration file? At 12:40 PM Sunday, 6/5/2005, Gleb Paharenko wrote -= Unfortunately I could give suggestion only about your second question. Is it possible that /var/log/mysql/error.log created by mysqld_safe (you're specifying it with --log-error command line option) and /usr/local/mysql/var/yuda2.err is created by mysqld process if it founds problems before applying location of error log to it's internal variable? Specify log-error = /var/log/mysql/error.log in your configuration file, and check that MySQL Server actually reads this file. Ed Kasky wrote: Hello there - I have a couple of questions regarding a new install of MySql 4.1.12 on RH 7.2. Being new to this list, I sure do hope this hasn't been covered before. I have scrubbed Google and searched the archives for this list but can't find an explanation or a solution to 2 issues: 1. I get the following error when starting the daemon: 050605 7:08:51 [Warning] Found an entry in the 'db' table with empty database name; Skipped Is this something that should be fixed and if so, how does one go about it? 2. I have set the error log to /var/log/mysql/error.log in the init script: LOG_ERROR=/var/log/mysql/error.log $bindir/safe_mysqld --datadir=$datadir --pid-file=$pid_file --log-error=$LOG_ERROR However, I am still getting 2 error logs: What I am assuming is the default /usr/local/mysql/var/yoda2.err and the one specified - /var/log/mysql/error.log Is this expected behavior? Is there another place aside from /etc/my.cnf that I might look? Thanks in advance for any tips and/or suggestions. Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (116 of 975): It had long since come to my attention that people of accomplishment rarely sat back and let things happen to them. They went out and happened to things. - Elinor Smith -- 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] . . . . . . . . . . . . . . . . . . Randomly Generated Quote (203 of 975): To climb steep hills requires slow pace at first. - William Shakespeare -- 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: LOAD DATA INFILE with INNODB
i used start transaction before using SET AUTOCOMMIT=0; also i dont see any difference between the two. if there is please elaborate. - Original Message - . From: [EMAIL PROTECTED] To: °l||l° Jinxed °l||l° [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, June 07, 2005 3:17 AM Subject: Re: LOAD DATA INFILE with INNODB Hi, you transaction is implicit, so there has been an autocommit. Look at this example ! mysql start transaction; ^^ mysql load data infile 'd:\\ldfile.txt' into table ldfile; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql select * from ldfile; +--+ | i| +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) mysql rollback; Query OK, 0 rows affected (0.03 sec) mysql select * from ldfile; Empty set (0.00 sec) This a not a good idea if the file is big. Ideally, truncate the table if there has been a problem witha big file. Mathias Selon °l||l° Jinxed °l||l° [EMAIL PROTECTED]: hi, I have been pulling my hair for last couple of days.i want to put few sol statements in TRANSACTION BLOCK. all the tables involved are of type innodb. the first SQL statement in the block is LOAD DATA INFILE. inside the block ( using PHP ) i am checking for errors and incase of error i want to rollback. but strangely when i tried to rollback it just wouldn't do. i thought may be PHP is giving problems. then i did this === SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) select @@autocommit as autocommit; +-+ | autocommit | +-+ | 0 | +-+ 1 row in set (0.00 sec) LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date); Query OK, 27265 rows affected (4.48 sec) Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0 rollback; Query OK, 0 rows affected (0.00 sec) === when i looked in tbltemp i found out that the CSV file has been loaded although i rolled back the transaction. i used insert statement and rolled back with no problem, so the problem was narrowed down to LOAD DATA INFILE. i have read about LOAD DATA INFILE and found nothing about this strange behavior. is there anything that i am missing out? Regards Haseeb Iqbal ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Slave Read Only
Is there a way to make MySQL Slaves read only in regards to all UPDATE, INSERT, DELETE, DROP, TRUNCATE, etc, except for the replication thread? I know you can just change the user accounts to disallow write access to the tables, but I'm thinking it would be convenient to simply have a switch that restricts all non-replicated writes to ensure that the slave stays consistent with the master. I've had a few users with administrator accounts mistakenly write to slaves, screwing up replication. Anyone else think this might be useful? If so, I might just implement it if it isn't implemented already. Jeremiah Gowdy Senior Software Engineer FreedomVOICE Systems http://www.freedomvoice.com
JOINs and composite indexes
Conceptually, what I'd like to do is join two tables on a hostid for a result like this non-join version where hostid in the 'IN' come from the other table. mysql explain select avg(load5min) from kstatHostData_20050513 where hostid IN (250, 400) and localdate '2005-06-06 13:00:00'\G *** 1. row *** id: 1 select_type: SIMPLE table: kstatHostData_20050513 type: range possible_keys: hostid_gmtdate_idx,hostid_localdate_idx key: hostid_localdate_idx key_len: 11 ref: NULL rows: 385 Extra: Using where 1 row in set (0.01 sec) This works great. It correctly picks the hostid_localdate_idx index and the query is fast. If I convert this to JOIN a table which contains exactly the same values ( two rows: 250, 400) as in the IN clause above, mysql picks either key and only using hostid portion to complete the join, e.g., mysql explain select avg(load5min) from kstatHostData_20050513 ks, hostinfo hi WHERE ks.hostid = hi.hostid and localdate '2005-06-06 13:00:00'\G *** 1. row *** id: 1 select_type: SIMPLE table: hi type: index possible_keys: bar_idx key: bar_idx key_len: 4 ref: NULL rows: 2 Extra: Using index *** 2. row *** id: 1 select_type: SIMPLE table: ks type: ref possible_keys: hostid_gmtdate_idx,hostid_localdate_idx key: hostid_localdate_idx key_len: 3 ref: postinistats.hi.hostid rows: 32338 Extra: Using where 2 rows in set (0.00 sec) mysql This query, needless to say, is abysmally slow. The actual number of applicable rows needed, as indicated in the first example where the full index is used, is approximately 400. It makes sense that either index will do as they both start with the hostid and you can only use one index, I'm just not sure how to work around it when the WHERE date condition signifincantly limits the result. I tried a subquery which yield results that actually appear worst than the join. (See below). Short of populating my IN block in the application with a pre-query, or re-organizing data into summaries to limit rows, I'm out of ideas on how to improve this. Maybe I'm missing some obvious solution. If anyone has any thoughts or explanations, I'd really appeciate them. Mysql is 4.1.12-standard. Thanks, John mysql explain select avg(load5min) from kstatHostData_20050513 WHERE hostid IN ( SELECT hostid FROM hostinfo) AND localdate '2005-06-06 13:00:00'\G *** 1. row *** id: 1 select_type: PRIMARY table: kstatHostData_20050513 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 26797461 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: hostinfo type: index_subquery possible_keys: bar_idx key: bar_idx key_len: 4 ref: func rows: 2 Extra: Using index 2 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Slave Read Only
There is an option starting in 4.0.14 called read-only that will allow only the slave i/o thread and users with the super priv to execute write queries. Jeremiah Gowdy wrote: Is there a way to make MySQL Slaves read only in regards to all UPDATE, INSERT, DELETE, DROP, TRUNCATE, etc, except for the replication thread? I know you can just change the user accounts to disallow write access to the tables, but I'm thinking it would be convenient to simply have a switch that restricts all non-replicated writes to ensure that the slave stays consistent with the master. I've had a few users with administrator accounts mistakenly write to slaves, screwing up replication. Anyone else think this might be useful? If so, I might just implement it if it isn't implemented already. Jeremiah Gowdy Senior Software Engineer FreedomVOICE Systems http://www.freedomvoice.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave Dying
We are running 4.0.20 on two servers (AMD Opteron and Xeon). Our slave has died twice in the last month with the following error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. I've tried resetting replication by setting the master log file and position to the values that are given by show slave status in case it was a network hiccup, but the same error. After I did this, the slave's binary log file shows, /*!40019 SET @@session.max_insert_delayed_threads=0*/; # at 4 #691231 16:00:00 server id 1 log_pos 0 Rotate to colossus-bin.030 pos: 12435199 # at 47 #691231 16:00:00 server id 1 log_pos 0 Rotate to colossus-bin.030 pos: 12435199 So I went to the master, and turned the binary log into a text file using mysqlbinlog and scanned by hand the approximate time it died; I didn't see anything particularily interesting. I then use mysqlbinlog with the -j option (to start parsing at a particular spot; in this case, 12435199). The error I got was, ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1701209458, event_type: 44 Could not read entry at offset 12435199:Error in log format or read error Googling on some of the phrases in that error message didn't turn up much, other than it could be potentially be a hardware or disk-controller issue (we are using 3ware, self-built drivers) Anyone have any thoughts? This has been fairly recent (we had some max-allowed-packet issues till I bumped that up and reduced the size of the binary logs). The hardware and software has been in place nearly a year (except the kernel, which we bumped up to try to get around corruption in the Innodb data files on the Opteron master). David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how can I close the cache for mysql
I am running a test on mysql. In a specific conditiob I need avoid using the cache system in mysql how can I do this? If I can't, is there anyway to remove the cached tables from cache manually? _ MSN Hotmail http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - what is the path to file?
Thank you for your detailed response. It seems my problem is trying to define the path to my data file and this is where I seem to be missing something. Permissions on all directories in the path are by default set to 755 except for the director at the top of the directories in my hosting account public_html which is set to 750. If I look at: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which works from my tmp directory. The tmp directory is at the same level as public_html and has permissions 700. So I guess I don't know why I can't specify the location of my data file from '/public_html/path_to_my_file/datafile.txt' Thanks, Chris Michael Stassen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Chris wrote: I have been using LOAD DATA INFILE to load an ASCII data file into my database. The datafile is uploaded to the server temp area and the name of the file is passed to LOAD DATA INFILE query like: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I now want to load data using LOAD DATA INFILE from a data file located within my http_public directory. I can create a path to the file from my DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates this error: (NOTE: I set the file permissions to 777) Don't do that, it's horribly insecure. Anybody could modify this file before mysql loads it. You should probably never make a file world-writable. Mysql only needs to read the file, so set the permissions to 744. Better yet, make it owned by the mysql group, and set permissions to 740. Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) ~: perror 13 OS error code 13: Permission denied In order to read the file, the mysql user must have read permission on the file (you've done that), and must have execute permission on every directory in the path to the file. So, for mysql to read /home/path/to/file/datafile.txt, you will need to set permissions of 711 on /home, /home/path, /home/path/to, and /home/path/to/file, in addition to the 744 permissions on datafile.txt. Now if I just create a query like: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' I get this error File './my_database_name/datafile.txt' not found (Errcode: 2) ~: perror 2 OS error code 2: No such file or directory Which seems to tell me that LOAD DATA INFILE is looking for my data file in a location that is outside my hosting account. I just have an account with a shared hosting service provider. Without a leading /, the path is treated as a relative path -- relative to the server's data directory. Your file isn't there, hence the error. So how would I specify a path to a file that is outside the directory where my database is located? With a full path, as you did originally. You just have to make sure mysql has all the permissions neede to access it. OBSERVATION: It appears the tmp directory must be in the database path because, files uploaded to the tmp dir can be loaded using LOAD DATA INFILE. No, /tmp works because it (usually) has 1777 permissions, so mysql has the necessary execute permission to access /tmp's contents. Thanks for replies, Chris Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how can I close the cache for mysql
I am running a test on mysql. In a specific condition I need avoid using the cache system in mysql how can I do this? If I can't, is there anyway to remove the cached tables from cache manually? _ MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how can I close the cache for mysql
That all depends on how you have configured your caching, but it is easy to do. 'mysqld --help': --query_cache_type=# 0 = OFF = Don't cache or retrieve results. 1 = ON = Cache all results except SELECT SQL_NO_CACHE ... queries. 2 = DEMAND = Cache only SELECT SQL_CACHE ... queries. (http://dev.mysql.com/doc/mysql/en/select.html) Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 7 Jun 2005, qin lei wrote: I am running a test on mysql. In a specific conditiob I need avoid using the cache system in mysql how can I do this? If I can't, is there anyway to remove the cached tables from cache manually? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql UNION
Hi,
If we forget the first method which i mis-adviced, i can give a third which is
generic.
suppose that you have an indexed type column on each table (what i did). You
can work with 3 variables. If they are different, you query for a join, if they
are equal, you transform the join to a simple query.
The only condition is to add a where clause a the column type which will
retreive empty set for the non selected conditions.
Example :!
set @cat1:='news'; set @cat2:='faq'; set @cat3:='forum';
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where [EMAIL
PROTECTED];
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
When you have only one value, the same query gives :
mysql set @cat1='news'; set @cat2='news'; set @cat3='news';
Query OK, 0 rows affected (0.00 sec)
here the 3 variables are the same, so 2 queries will find an empty set.
mysql select id,@cat1 as selected, type from news where [EMAIL PROTECTED]
- union select id,@cat2 as selected, type from faq where [EMAIL
PROTECTED]
- union select id,@cat3 as selected, type from forum where [EMAIL
PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
performance will not be affected since the index will be used for non used
tables.
Hope that helps :o)
Mathias
Selon Sebastian [EMAIL PROTECTED]:
Michael Stassen wrote:
[EMAIL PROTECTED] wrote:
Hi Sebastian;
There is always crazy things somewhere.
I'll give you two methods for that :
mysql select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum;
+--+--+---+
| id | selected | type |
+--+--+---+
|1 | news | news |
|2 | faq | faq |
|3 | forum| forum |
+--+--+---+
3 rows in set (0.00 sec)
FIRST CRAZY METHOD :
*
mysql set @cat='news';
Query OK, 0 rows affected (0.00 sec)
mysql select * from (
- select id,'news' as selected, type from news
- union select id,'faq' as selected, type from faq
- union select id,'forum' as selected, type from forum
- ) Temp
- where [EMAIL PROTECTED];
+--+--+--+
| id | selected | type |
+--+--+--+
|1 | news | news |
+--+--+--+
1 row in set (0.00 sec)
SECOND CRAZY METHOD (I prefer):
*
set @cat := 'news';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
+--+--+
| id | selected |
+--+--+
|1 | news |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
* another click with ?cat=faq
set @cat := 'faq';
set @sql:=concat('select id,',,@cat,,' as selected from ',@cat);
select @sql;
prepare stmt from @sql ;
execute stmt;
mysql execute stmt;
+--+--+
| id | selected |
+--+--+
|2 | faq |
+--+--+
1 row in set (0.00 sec)
deallocate prepare stmt;
OTHER CRAZY METHODS - coming emails :o)
A+
Mathias
The first method is horribly inefficient (and requires mysql 4.1+).
It reads all 3 tables, unions the resulting rows, checks for (and
removes) duplicate rows, then finally throws away roughly 2/3 of the
results (the rows from the 2 unwanted tables. Compare that to the
simple query which only addresses the 1 desired table. Mathias is
aware of this, which is why he gives the second method. It creates
the simple, one-table query using the value of $cat to choose which
table.
The big problem here is that neither of these methods actually do what
you asked for. That is, neither works if $cat is not set. With both
methods, you will get no rows unless $cat is set. In fact, the second
method will give a syntax eror, as there will be no table name in the
FROM clause.
Now, I never said this couldn't be done in SQL. Assuming $cat is
already set, the statement in $sql below should do what you asked for:
$sql = SELECT id, 'news' AS type, FROM news
WHERE ($cat = '' OR $cat = 'news')
UNION
SELECT id, 'faq' AS type, FROM faq
WHERE ($cat = '' OR $cat = 'faq')
UNION
SELECT id, 'forum' AS type, FROM
fulltext/boolean search
I created a search app with fulltext, boolean, etc. i have two forms, one that allows the user to just enter a basic search in a single input field and a more advanced form with additional input areas for advanced boolean searches.. now, when you do not specify a boolean operator, what does it default to? i am thinking it defaults to a search similar to using LIKE %string% Basically i want to know what default boolean operator i should use for a 'basic' search or should i not use a boolean for the 'basic' search? i want to provide decent search results without forcing the user to go to advanced mode. just looking for tips/suggestions to tweak the results it returns and anything else i should know. lastly... this might be more related to php, if anyone knows of a reliable function/class to highlight search terms, please let me know. TIA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
