DIV-function?
Hi there! I wonder how I'll get a div-function in SQL? I want to divide to columns and make a new column based on the result. /G @varupiraten.se
DIV-function?
Hi there! I wonder how I'll get a div-function in SQL? I want to divide to columns and make a new column based on the result. /G @varupiraten.se
Re: DIV-function?
Gustav Wiberg wrote: Hi there! I wonder how I'll get a div-function in SQL? I dunno, maybe by looking in the manual? From [1]: Division: mysql SELECT 3/5; - 0.60 [1] http://dev.mysql.com/doc/mysql/en/arithmetic-functions.html -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
assistance
dear sir, i am an IT engineer but i have a specialization in network, internet service hardware. presently i hv setup my WISP company and i am now free to pursue other endeavours, and i want to get to know more about the software angle of IT. I need assistance on how to develop my skills from the basics in using SQL and oracle which will improve my credentials in the IT industry. i will appreciate your kind assistance. - To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre.
assistance
dear sir, i am an IT engineer but i have a specialization in network, internet service hardware. presently i hv setup my WISP company and i am now free to pursue other endeavours, and i want to get to know more about the software angle of IT. I need assistance on how to develop my skills from the basics in using SQL and oracle which will improve my credentials in the IT industry. i will appreciate your kind assistance. chizoba acet technologies ltd Nigeria - How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos. Get Yahoo! Photos
Re: [Replication] Problem connecting to master
Hello. If nothing helps, trace files could spill the light on your problem. See: http://dev.mysql.com/doc/mysql/en/making-trace-files.html Jan Roehrich [EMAIL PROTECTED] wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ -- 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]
Slow Queries Log and Index-less queries
Hey list, I have a question regarding the slow queries log, and queries not using index. I have a small table, with say 10 entries, like that : ID | Element - 1 | One 2 | Two 3 | Three 4 | Four 5 | Five 6 | Six 7 | Seven 8 | Eight 9 | Nine 10 | Ten I want to get all those entries: SELECT ID, Element FROM tblentries; The problem is that this query, even if very fast, is logged in the slow query log because it does not use index (I activated this option in the slow query log). Woudln't it be better if such queries would be logged only in case there is a WHERE, ORDER or GROUP/HAVING clause ? Also, is it better to do : SELECT ID, Element FROM tblentries; or SELECT ID, Element FROM tblentries WHERE ID 0; (In this last case, it won't be logged in the slow query log beause it uses an index...) Thank you, HMax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries Log and Index-less queries
I have a question regarding the slow queries log, and queries not using index. I have a small table, with say 10 entries, like that : ID | Element - 1 | One 2 | Two 3 | Three 4 | Four 5 | Five 6 | Six 7 | Seven 8 | Eight 9 | Nine 10 | Ten I want to get all those entries: SELECT ID, Element FROM tblentries; The problem is that this query, even if very fast, is logged in the slow query log because it does not use index (I activated this option in the slow query log). Woudln't it be better if such queries would be logged only in case there is a WHERE, ORDER or GROUP/HAVING clause ? Slow query log, especially with long-long-format turned on, isn't made for manual browsing anyway. You'll have to use mysqldumpslow or some custom-made script to analyze it. Queries that don't have 'where' are easy to filter then. Also, is it better to do : SELECT ID, Element FROM tblentries; or SELECT ID, Element FROM tblentries WHERE ID 0; (In this last case, it won't be logged in the slow query log beause it uses an index...) It won't be logged if it actually will use index. In your example it won't use index, full table scan will be used instead, because query optimizer is able to determine that all records match where condition. -- Alexey Polyakov
Re[2]: Slow Queries Log and Index-less queries
Thanks Alexey, This is enough explanation for me ;) Cheers, HMax AP I have a question regarding the slow queries log, and queries not using index. AP I have a small table, with say 10 entries, like that : AP ID | Element AP - AP 1 | One AP 2 | Two AP 3 | Three AP 4 | Four AP 5 | Five AP 6 | Six AP 7 | Seven AP 8 | Eight AP 9 | Nine AP 10 | Ten AP I want to get all those entries: AP SELECT ID, Element FROM tblentries; AP The problem is that this query, even if very fast, is logged in the slow query log because it does not use index (I AP activated this option in the slow query log). Woudln't it be better if such queries would be logged only in case there AP is a WHERE, ORDER or GROUP/HAVING clause ? AP AP Slow query log, especially with long-long-format turned on, isn't made for manual browsing anyway. AP You'll have to use mysqldumpslow or some custom-made script to analyze it. AP Queries that don't have 'where' are easy to filter then. AP AP Also, is it better to do : AP SELECT ID, Element FROM tblentries; AP or AP SELECT ID, Element FROM tblentries WHERE ID 0; AP (In this last case, it won't be logged in the slow query log beause it uses an index...) AP AP It won't be logged if it actually will use index. In your example it won't use index, full table scan will be AP used instead, because query optimizer is able to determine that all records match where condition. AP AP AP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A lot of instances of mysqld
Hi ALL, Is this normal? 26527 ?SW 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid 26554 ?S 23:08 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 23531 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 23753 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24545 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24801 ?S 0:04 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24806 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24834 ?S 0:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24849 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24850 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24851 ?S 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24852 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24854 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24856 ?S 0:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24858 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24910 ?S 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24911 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24914 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24921 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24922 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24923 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock tia, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: assistance
acet technologies [EMAIL PROTECTED] wrote on 08/30/2005 04:01:46 AM: dear sir, i am an IT engineer but i have a specialization in network, internet service hardware. presently i hv setup my WISP company and i am now free to pursue other endeavours, and i want to get to know more about the software angle of IT. I need assistance on how to develop my skills from the basics in using SQL and oracle which will improve my credentials in the IT industry. i will appreciate your kind assistance. chizoba acet technologies ltd Nigeria Congratulations on your new company. You would like to know more about the programming side of IT? The best way to learn is by doing. There are literally dozens of languages used to write modern computer programs. I refuse to start a flame war by suggesting that you learn one before another. You need to decide that for yourself based on what types of applications you want to write, what software utilities you can muster to support your language, and what educational resources are available to you. One of the best things you can do as a student is to locate a mentor. Look in your community or on the web for others with the skills you want and learn from them as much as possible. You ask about how to improve your skills with SQL and Oracle. Unfortunately, this mailing list does not support Oracle, we support a different relational database management system (RDBMS) called MySQL. Please visit its home page for more details: http://www.mysql.com/ We do answer questions on SQL very frequently here but we are not a tutorial service. There are many excellent tutorials and walkthroughs on the web to help you get started. Be prepared to read, read, read. The manuals are your friends. Find them and learn how to use them. Every RDBMS has its own dialect (variety) of SQL so there will be a few commands that will work on one server but not another. The core SQL grammar, though, is nearly identical for all systems and once you learn it for one, using it on another is laughably simple (usually). Again, which RDBMS system you learn on should be your decision. MySQL is robust, easy to use and maintain, and contains many enterprise-quality features. Of course, I recommend you start with it but I am not an impartial observer. If you decide to start with MySQL, please start with the online manual: http://dev.mysql.com/doc/mysql/en/index.html Not only does it cover setting up and testing an installation of MySQL on several platforms (operating systems) but it also has a fine introductory tutorial. If English is not your preferred language, it has translations for French, German, Japanese, Portuguese, and Russian. If you still do not understand something AFTER reading the appropriate sections of the manual, look on the web. There are many excellent sites that support MySQL. Before asking a question to the list, check the archives: http://lists.mysql.com/ Odds are, you aren't the first person to have that problem and the answer is probably waiting for you right there. I wish you luck! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Regarding the usage of mutex in the mysql connection threading
Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding the usage of mutex in the mysql connection threading
[EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: mysqldump: Error 2013
Have a look here: http://dev.mysql.com/doc/mysql/en/gone-away.html Gleb, Thanks for the response. The only one that seems to apply is this one: You may also see the MySQL server has gone away error if MySQL is started with the --skip-networking option. I do start mySQL without networking enabled - it's only accessible from the local machine (for security reasons). I can tell you for certain that the mySQL server is definitely not crashing itself - it chugs along happily without incident. Interestingly, I ran the backup command from my shell yesterday during the day (when the server is actually much more active) and the backup completed successfully. That one table took about 5 hours to back up though - I'm not sure if that is normal or not. Then last night's automated (unattended) backup completed successfully for the first time in a few days. Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ smime.p7s Description: S/MIME cryptographic signature
RE: Regarding the usage of mutex in the mysql connection threading
Hi Green, Thanks a lot for your reply. In brief if I explain the architecture it something goes like this:- - Element management Apllications uses MySQL database through DB access layer. - DB access layer provides the application some APIs needed for DB opearations. - for Each DB transactions the applications login to the database- gets free connection id from pool (local data structure maintained in application) - do the operation - logout - return the connection id to the pool. - we are going to MySQL through ODBC which identifies the transactions by there connection handles. So every parralel transactions are having separate connection id and separate handles for ODBC. Now in this kind of implementation do we need to put any of the sql statement execution / reading from result set opearation into a critical section (mutex)? Please send the reply asap. Regards, Narasimha, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
hierarchical db/depth?
hi! I'm still trying to organize an hierachical db (I saw the last article on mysql.com: http://dev.mysql.com/tech-resources/articles/hierarchical-data.hml). I began working with what the author calls adjency list model (I think the nested set model is a bit too complex for what I want to do.In addition I found an equivalent schema in an other application which is close to mineand working fine) Basically, I have a table term (term_id, name), relation (relation_id,term_id1,term_id2,type_id) and a last table to identify the relations type id, relation_type (type_id,type_name). I can retrieve all the info I need with that query: SELECT t1.term_id2 AS lev0, term0.name AS parent0, trel1.type_name AS rel1, t1.term_id1 AS lev1, term1.name AS parent1, trel2.type_name AS rel2, t2.term_id1 AS lev2, term2.name AS parent2, trel3.type_name AS rel3, t3.term_id1 AS lev3, term3.name AS parent3 FROM relation AS t1 LEFT JOIN relation_type AS trel1 USING ( type_id ) LEFT JOIN term AS term0 ON ( t1.term_id2 = term0.term_id ) LEFT JOIN relation AS t2 ON t2.term_id2 = t1.term_id1 LEFT JOIN relation_type AS trel2 USING ( type_id ) LEFT JOIN term AS term1 ON ( t2.term_id2 = term1.term_id ) LEFT JOIN relation AS t3 ON t3.term_id2 = t2.term_id1 LEFT JOIN relation_type AS trel3 USING ( type_id ) LEFT JOIN term AS term2 ON ( t3.term_id2 = term2.term_id ) LEFT JOIN term AS term3 ON t3.term_id1 = term3.term_id WHERE t1.term_id2 =1 It's just adding a new sub-part to the query for each level. This is working fine on my data sample, because I know that I have exactly 4 levels of depth. I would like to know if there is a way to have a kind of auto-extension of the query if there is for example a fifth level? Thanks for any help or tip, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding the usage of mutex in the mysql connection threading
hi , Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections Please reply asap. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: Regarding the usage of mutex in the mysql connection threading
Hi, Please advise us whether mysql/myodbc - handles different queries/write from different threads within the same connection or not? - handles different queries (or batch fetches) from multi-connections or not? Thanks, Narasimha -Original Message- From: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Sent: Tuesday, August 30, 2005 7:34 PM To: mysql@lists.mysql.com Cc: '[EMAIL PROTECTED]' Subject: RE: Regarding the usage of mutex in the mysql connection threading hi , Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections Please reply asap. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
innodb thread concurrency size
Hey guys, I'm looking for any best practices or a formula that is commonly used in setting the value for thread concurrency in the configuration file. I have 24 instances running on a sun 2900 server with 32GB or ram. Here is a sample of my configuration file. #use this line mfor mysql 4.1 old-passwords server-id = 2216 net_buffer_length=65536 net_read_timeout=120 net_write_timeout=180 key_buffer=64M max_allowed_packet=1M table_cache=2048 sort_buffer=1M record_buffer=1M myisam_sort_buffer_size=16M max_connections=2500 thread_cache=8 # Try number of CPU's*2 thread_concurrency=4 query_cache_size=256M query_cache_limit=128K #only availble in 4.1 innodb_file_per_table innodb_buffer_pool_size=500M innodb_additional_mem_pool_size=25M innodb_log_archive=0 innodb_log_files_in_group=3 innodb_log_file_size=100M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_file_io_threads=4 innodb_lock_wait_timeout=30 #innodb_flush_method=fdatasync #innodb_fast_shutdown=1 innodb_thread_concurrency=5 transaction-isolation = READ-UNCOMMITTED [mysqld140] bind-address=xxx.xxx.xxx.xxx old-passwords mysqld=/usr/local/mysql/bin/mysqld_safe pid-file=/p01/abq/mysqladmin/abq_pid basedir=/usr/local/mysql datadir=/p01/abq/mysqldata socket=/p01/abq/mysqladmin/mysql.sock port=3306 local-infile=1 user=mysql tmpdir = /tmp/abq/ log = /p01/abq/mysqllogs log-bin = /p01/abq/mysqllogs/abq-bin log-err = /p01/abq/mysqllogs/abq.err log-slow-queries = /p01/abq/mysqllogs/abq_slow_query.log innodb_file_per_table set-variable = innodb_buffer_pool_size=500M set-variable = innodb_additional_mem_pool_size=25M innodb_data_home_dir = /p01/abq/mysqldata/innodb/ innodb_data_file_path = ibdata1_abq:100M:autoextend:max:4096M #.._log_arch_dir must be the same #as .._log_group_home_dir innodb_log_group_home_dir = /p01/abq/mysqladmin/iblogs innodb_log_arch_dir = /p01/abq/mysqladmin/iblogs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linux+AMD64+MySQL.
Folks, I'm used to run MySQL (4.0) with OpenBSD (3.7). Now I've decided to give Linux a shot, to see if I could gain some serious performance. The server I'm using is a dual Opteron 246, with 2GB RAM, LSI MegaRAID 320. CentOS 4.1 x86_64 seems to be a good OS. Although I thought that the most interesting thing in running Linux+MySQL was the fact that LinuxThreads is available, the binaries are using pthreads. When I try to compile my own MySQL (pretty much the same way as the RPM is created, except that I'm trying LinuxThreads), everything goes smoothly, but the servers crashes with Signal 11 every 10, maybe 5 minutes. When I compile it with pthreads, everything is back to normal. I'm trying with MySQL 4.1.10a (I've tried the 4.0.25, 4.1.14 as well), with Centos 4.1 x86_64, Kernel 2.6.9-11.ELsmp. So, I've a few questions, maybe you guys can help: 1) pthreads is safer, but LinuxThreads is way faster? 2) What's the best io scheduler for 20GB MyISAM DB? I'm currently using cfq. 3) Does anyone have any tips on running MySQL+AMD64+Linux? Thanks a lot! Best regards, RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Collation ?
Hi, Ive noticed that my ALL my databases and tables have latin1_swedish_ci as the collation...h that wouldn't be so bad except that I didn't set it that way by default, and I don't speak swedish. Not that swedish isn't a fine language, or sweden isn't a fine country (although Ive never been there) so I hear. However, that's NOT what I want. 1.) What should the db collation be set to if I am in the USA - latin_1_bin ? 2.) and would doing so cause a problem that anyone can think of ? Thanks. I'm running 4.1.12 on OS X.4.2. Thanks. M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp and php
Hi, I have a row in myslq database called time and is just a simple timestamp column When I echo it out echo $row['time']; echo $row['content']; I get the following 2005-08-30 13:50.05 this is the text content Now I am not worried about the time but I would like to know how to (i) sort the returned rows in order (latest first) (ii) be able to extract the individual parts of the date and display them in UK format (ddmm) 30.08.2005 this is the text from 30th of August 27.08.2005 this is the text from 27th of August 27.08.2005 this is the text from 23rd of August thanks, R.
RE: Regarding the usage of mutex in the mysql connection threading
[EMAIL PROTECTED] wrote on 08/30/2005 10:03:51 AM: Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections [EMAIL PROTECTED] wrote on 08/30/2005 09:54:54 AM: In brief if I explain the architecture it something goes like this:- - Element management Apllications uses MySQL database through DB access layer. - DB access layer provides the application some APIs needed for DB opearations. - for Each DB transactions the applications login to the database- gets free connection id from pool (local data structure maintained in application) - do the operation - logout - return the connection id to the pool. - we are going to MySQL through ODBC which identifies the transactions by there connection handles. So every parralel transactions are having separate connection id and separate handles for ODBC. Now in this kind of implementation do we need to put any of the sql statement execution / reading from result set opearation into a critical section (mutex)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I will respond to both emails at once (or at least try to). First, I have to ask: Why are you trying to use ODBC? I ask because you describe critical sections and mutexes which are lower level programming concepts. If you are working at that level you have a much easier and more direct access to the database if you use the MySQL C API (Chapter 23 in the current manual). The DLLs, .h files, and .lib files should already exist on your system. You explain that each thread/process gets its own connection (without sharing) from the ODBC connection manager pool. You asked if you now needed to synchronize access so that your various connections are only in use one-at-a-time. The answer is no because you do not SHARE connections. You do not need to worry about concurrent connection usage so long as only one thread or process is using any single connection at any one time. Now, because you are using a connection pool, a minimum number of connections are created and maintained by the pool manager. Each connection has specific to it any user variables or temporary
Re: timestamp and php
2005-08-30 13:50.05 this is the text content (i) sort the returned rows in order (latest first) http://dev.mysql.com/doc/mysql/en/sorting-rows.html (ii) be able to extract the individual parts of the date and display them in UK format (ddmm) http://www.php.net/manual/en/function.strtotime.php http://www.php.net/manual/en/function.date.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding the usage of mutex in the mysql connection threading
[EMAIL PROTECTED] wrote on 08/30/2005 10:09:27 AM: Hi, Please advise us whether mysql/myodbc - handles different queries/write from different threads within the same connection or not? - handles different queries (or batch fetches) from multi-connections or not? Thanks, Narasimha Please consider this a friendly warning. This is not a help desk. This is a community-supported mailing list. We all have our regular jobs to do. Sometimes it takes DAYS for a good response. You happen to be lucky enough that I was ALREADY working on a response to the FIRST time you posted this and that I did not see this repeat before I sent my response. NEXT TIME... I will sit on my response for an entire week before if I see one more ASAP or repeated message show up from you. I am sure there are others as frustrated with your lack of consideration as I am. There is nothing time-critical about this list or how we respond to it. If you need time-critical consultation, buy some. Otherwise, please be thankful that so many experts contribute to this list freely and as their time alows. There may be as many as 5 people actually paid to respond to this list, tops. Everyone else does it just to be helpful. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Regarding the usage of mutex in the mysql connection threading
Hi Green, We are at the last stage of the project (migration from Oracle to MySQL). We are demanded to adopt the MYODBC as a customer requrement. As mutex was implemented for oracle so it is there in our code. Now we are thinking to remove that because application level we have connection id. So, please advise us in this case can we remove the mutex?. I beleive that in the ODBC and in MySQL mutex will be handled automatically, right?. Regards Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 8:03 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: RE: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 10:03:51 AM: Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections [EMAIL PROTECTED] wrote on 08/30/2005 09:54:54 AM: In brief if I explain the architecture it something goes like this:- - Element management Apllications uses MySQL database through DB access layer. - DB access layer provides the application some APIs needed for DB opearations. - for Each DB transactions the applications login to the database- gets free connection id from pool (local data structure maintained in application) - do the operation - logout - return the connection id to the pool. - we are going to MySQL through ODBC which identifies the transactions by there connection handles. So every parralel transactions are having separate connection id and separate handles for ODBC. Now in this kind of implementation do we need to put any of the sql statement execution / reading from result set opearation into a critical section (mutex)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I will respond to both emails at once (or at least try to). First, I have to ask: Why are you trying to use ODBC? I ask because you describe critical sections and mutexes which are lower level programming concepts. If you are working at that level you have a much easier and more direct access to the database if you use the MySQL C API (Chapter 23
Re: Table Collation ?
m i l e s [EMAIL PROTECTED] wrote on 30/08/2005 15:28:31: Hi, Ive noticed that my ALL my databases and tables have latin1_swedish_ci as the collation...h that wouldn't be so bad except that I didn't set it that way by default, and I don't speak swedish. Not that swedish isn't a fine language, or sweden isn't a fine country (although Ive never been there) so I hear. However, that's NOT what I want. 1.) What should the db collation be set to if I am in the USA - latin_1_bin ? 2.) and would doing so cause a problem that anyone can think of ? I think the answer is that if you are in the USA, any of the latin collations will do equally well. The will only cut in when you use characters outside the range 32-126, which you will not do in US English. MySQL defaults to Swedish, I would guess, because that handles its two largest customer groups: English and Swedish speakers. You can set the collation to latin1_bin. Chapter 10 of the manual tells you many ways to do this (command line, my.ini, per database, per table). If you choose to change it, rather than blindly changing it to bin, you should perhaps consider you real needs. For example, might not a Spanish collation serve better than a binary one? Many Americans speak Spanish, few binary. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp and php
[EMAIL PROTECTED] wrote: Hi, I have a row in mysql database called time and is just a simple timestamp column When I echo it out echo $row['time']; echo $row['content']; I get the following 2005-08-30 13:50.05 this is the text content Now I am not worried about the time but I would like to know how to (i) sort the returned rows in order (latest first) (ii) be able to extract the individual parts of the date and display them in UK format (ddmm) 30.08.2005 this is the text from 30th of August 27.08.2005 this is the text from 27th of August 27.08.2005 this is the text from 23rd of August thanks, R. Use ORDER BY to get sorted results http://dev.mysql.com/doc/mysql/en/sorting-rows.html. Use DATE_FORMAT() to get the date output you want http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html. SELECT DATE_FORMAT(time, '%e.%c.%Y') AS time, content FROM your_table WHERE where conditions as needed ORDER BY time DESC; The '%e.%c.%Y' will give 3.1.2005 for the 3rd of January. Use '%d.%m.%Y' to get 03.01.2005 instead. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regarding the usage of mutex in the mysql connection threading
I can not and will not make an absolute recommendation to keep or lose the mutex before I performed a full code review. IMHO, the final stages of a project is one of the worst times to be making this kind of decision as it could impact so much work already accomplished. If you are certain that each connection can only be used by one thread/process at a time, then you should not need to synchronize (serialize) access to any connection. Connection pooling IS NOT connection sharing. It sounds to me that the previous version of your application shared a single connection between several threads and required a transaction-level mutex to ensure proper SQL command serialization. You probably do not need that now as you are not _sharing_ a single common connection between more than one execution at a time. If you DO share a connection between two or more threads or processes, you will need to keep the mutex to keep one thread from clobbering the SQL being executed by the other. Does MySQL or ODBC handle the mutex? No, that is part of your execution environment (usually handled by the OS). Will ODBC pool connections? Yes, if you allow it to do so. MySQL keeps all connections separated from each other until the point data is committed to the database (transactional boundaries). How your application uses transactions is up to you. Under most circumstances, what one connection is doing is invisible to what another connection is doing up to the point at which one of them commits their changes to the database. Then those changes may, depending on your transaction isolation level, become instantly visible to the other connection or not. As I said, it all depends. If each thread/process establishes its own connection to the database server, you are in a situation (from the point of view of the database server) identical to what would be happening if all of your processes were connecting in from different physical machines. If you need a mutex to serialize access under that scenario, then you should keep it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/30/2005 10:56:20 AM: Hi Green, We are at the last stage of the project (migration from Oracle to MySQL). We are demanded to adopt the MYODBC as a customer requrement. As mutex was implemented for oracle so it is there in our code. Now we are thinking to remove that because application level we have connection id. So, please advise us in this case can we remove the mutex?. I beleive that in the ODBC and in MySQL mutex will be handled automatically, right?. Regards Narasimha
Some Assistance Please
Hi All, I have been using MySQL for some time in a basic and simple manner to retain prompt information and data for my multilingual websites. My new project is however a much larger matter. The site is a repository for advertisements advertising vehicles. This includes land sea and air vehicles and basically anything that carries people. My questions are: What is the optimum way to structure the database to allow for the simplest user search? Is it more efficient to store images and large documents in their respective format on disc and the URL in the database or as blob objects? A requirement is that searches will be entered in many languages including all European, Asian and middle-eastern languages and scripts. This may also be mixed languages where manufacturer name may be in the middle of say Japanese script. Further, my clients expect the site to grow over a period of three years from 4 initial languages to around 50 and service many more countries. I have defined so far a range of categories and sub categories, types, manufacturers etc. I also intend to carry advertiser data, country, language and images. Included may also be uploaded documents being detailed specifications of vehicles in PDF and other formats although they may be outside the search criteria (???) My knowledge of applying single field search (ala Google) to multiple tables is decidedly limited and I don't want to find myself in deep water due to lack of specific knowledge. If this is off topic please accept my apologies and if possible direct me to an appropriate forum. Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some Assistance Please
Greetings Rob, What is the optimum way to structure the database to allow for the simplest user search? Is it more efficient to store images and large documents in their respective format on disc and the URL in the database or as blob objects? Most of the times, especially for read-only data(=images), going for the file-system is the right thing to do. Just make sure you don't put them all in a single directory ; employ a simple hash mechanism for deciding where to store them ( many files in a directory = performance problem with most FS flavor ) A requirement is that searches will be entered in many languages including all European, Asian and middle-eastern languages and scripts. This may also be mixed languages where manufacturer name may be in the middle of say Japanese script. Further, my clients expect the site to grow over a period of three years from 4 initial languages to around 50 and service many more countries. I have defined so far a range of categories and sub categories, types, manufacturers etc. I also intend to carry advertiser data, country, language and images. Included may also be uploaded documents being detailed specifications of vehicles in PDF and other formats although they may be outside the search criteria (???) My knowledge of applying single field search (ala Google) to multiple tables is decidedly limited and I don't want to find myself in deep water due to lack of specific knowledge. There are many things to consider here. Do you need additions and updates to be instantly indexed / searchable? Is it going to be keywords only ? What about advanced options ? Those are just some of the parameters that would come into play. Personally, I wouldn't use mySQL's LIKE or regular expression matching, nor it fulltext indices. I wouldn't even use mySQL for search at all. Still, you should try both ways ( and others, such as breaking down your text into words and mapping those words into objects -- I am sorry if that sounds confusing ) and see what works for you. Good luck, Mark -- Mark Papadakis http://www.markpapadakis.com/ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A lot of instances of mysqld
Hello. Is this normal? Yes. These are actually threads of a single process. JM [EMAIL PROTECTED] wrote: Hi ALL, Is this normal? 26527 ?SW 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid 26554 ?S 23:08 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 23531 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 23753 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24545 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24801 ?S 0:04 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24806 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24834 ?S 0:03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24849 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24850 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24851 ?S 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24852 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24854 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24856 ?S 0:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24858 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24910 ?S 0:01 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24911 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24914 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24921 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24922 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock 24923 ?S 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/db.forum.com.pid.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock tia, -- 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:
Re: Linux+AMD64+MySQL.
Hello. I'm not directly answering on your questions, but this link sounds to be helpful: http://hashmysql.org/index.php?title=Opteron_HOWTO Have a look here as well: http://dev.mysql.com/doc/mysql/en/compile-and-link-options.html I'm using official binaries of MySQL in most cases, so I suggest you to do the same. RV Tec [EMAIL PROTECTED] wrote: Folks, I'm used to run MySQL (4.0) with OpenBSD (3.7). Now I've decided to give Linux a shot, to see if I could gain some serious performance. The server I'm using is a dual Opteron 246, with 2GB RAM, LSI MegaRAID 320. CentOS 4.1 x86_64 seems to be a good OS. Although I thought that the most interesting thing in running Linux+MySQL was the fact that LinuxThreads is available, the binaries are using pthreads. When I try to compile my own MySQL (pretty much the same way as the RPM is created, except that I'm trying LinuxThreads), everything goes smoothly, but the servers crashes with Signal 11 every 10, maybe 5 minutes. When I compile it with pthreads, everything is back to normal. I'm trying with MySQL 4.1.10a (I've tried the 4.0.25, 4.1.14 as well), with Centos 4.1 x86_64, Kernel 2.6.9-11.ELsmp. So, I've a few questions, maybe you guys can help: 1) pthreads is safer, but LinuxThreads is way faster? 2) What's the best io scheduler for 20GB MyISAM DB? I'm currently using cfq. 3) Does anyone have any tips on running MySQL+AMD64+Linux? Thanks a lot! Best regards, RV -- 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]
Counting results for pagination + limit?
Greetings,
We have a few queries that we use against our product database. We pull these
results, and only display 50 rows per page via our web interface (then use
next and back page links). An example query is:
select distributer.short_desc,
distributer.sku,
distributer.avail_code,
distributer.msrp,
distributer.dealer,
IF( ISNULL(distributer_classmap.description),
distributer.brand_code,
distributer_classmap.description
),
distributer.msrp - distributer.dealer as profit,
distributer.family from distributer
Left Join distributer_classmap on
distributer_classmap.code = distributer.brand_code
where avail_code = ('AA' or 'A' or 'B')
and
(distributer.brand_code = ? or distributer_classmap.description = ?)
LIMIT ?,50
What I need to do is be able to count the *total* number of results this query
generates, so I can build the offset numbers for the proper number of next
page links.
Since I am using a limit clause, if i were to count in my app how many rows
there are I would get 50 as that is what the limit is set for.
The only other way I know of to get the total results is to use COUNT. I
really am not that great with SQL, so I don't know how I would apply a count
statement to the above SQL.
I would like to keep the counting of total results, along with the LIMIT'ed
result statement into 1 query if possible.
Any hints would be greatly appreciated.
I am using MySQL 4.1.13a
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting results for pagination + limit?
Chris [EMAIL PROTECTED] wrote on 08/30/2005 01:51:34 PM:
Greetings,
We have a few queries that we use against our product database. We pull
these
results, and only display 50 rows per page via our web interface (then
use
next and back page links). An example query is:
select distributer.short_desc,
distributer.sku,
distributer.avail_code,
distributer.msrp,
distributer.dealer,
IF( ISNULL(distributer_classmap.description),
distributer.brand_code,
distributer_classmap.description
),
distributer.msrp - distributer.dealer as profit,
distributer.family from distributer
Left Join distributer_classmap on
distributer_classmap.code = distributer.brand_code
where avail_code = ('AA' or 'A' or 'B')
and
(distributer.brand_code = ? or distributer_classmap.description = ?)
LIMIT ?,50
What I need to do is be able to count the *total* number of results
this query
generates, so I can build the offset numbers for the proper number of
next
page links.
Since I am using a limit clause, if i were to count in my app how many
rows
there are I would get 50 as that is what the limit is set for.
The only other way I know of to get the total results is to use COUNT. I
really am not that great with SQL, so I don't know how I would apply a
count
statement to the above SQL.
I would like to keep the counting of total results, along with the
LIMIT'ed
result statement into 1 query if possible.
Any hints would be greatly appreciated.
I am using MySQL 4.1.13a
Thanks!
You want to check out the FOUND_ROWS() function:
http://dev.mysql.com/doc/mysql/en/information-functions.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Linux+AMD64+MySQL.
RV Tec wrote: Folks, I'm used to run MySQL (4.0) with OpenBSD (3.7). Now I've decided to give Linux a shot, to see if I could gain some serious performance. The server I'm using is a dual Opteron 246, with 2GB RAM, LSI MegaRAID 320. CentOS 4.1 x86_64 seems to be a good OS. Although I thought that the most interesting thing in running Linux+MySQL was the fact that LinuxThreads is available, the binaries are using pthreads. When I try to compile my own MySQL (pretty much the same way as the RPM is created, except that I'm trying LinuxThreads), everything goes smoothly, but the servers crashes with Signal 11 every 10, maybe 5 minutes. When I compile it with pthreads, everything is back to normal. I'm trying with MySQL 4.1.10a (I've tried the 4.0.25, 4.1.14 as well), with Centos 4.1 x86_64, Kernel 2.6.9-11.ELsmp. So, I've a few questions, maybe you guys can help: 1) pthreads is safer, but LinuxThreads is way faster? 2) What's the best io scheduler for 20GB MyISAM DB? I'm currently using cfq. 3) Does anyone have any tips on running MySQL+AMD64+Linux? Thanks a lot! Best regards, RV We fought this exact setup for some time. Some things I learned and our setup: - gcc 3.4 (we are using 3.4.3) - glibs 2.4.4 (w/ NPTL) - deadline scheduler - xfs for filesystem - 2.6 kernel - custom build of mysql 4.1 Everything else was either non-performant or unstable. Also, stay away from Fedora. We ended up switching to Gentoo and had massive performance and stability gains from the switch, but the reasons are more to do w/ gcc and glibc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stopping drop index command on table
What are the consequences of cancelling(stopping) the drop index on table command if any at all? The process is running on a 9GB table and need to stop the process (dev environment). Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Control Center works with v4.0.23 -- how about V5?
[Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center works with v4.0.23 -- how about V5?
From what I recall MysqlCC has been deprecated and has been replaced by MySQL Administrator (which does support v5). I don't know think mysqlcc supports v5. good luck, --bemansell On 8/30/05, Siegfried Heintze [EMAIL PROTECTED] wrote: [Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center works with v4.0.23 -- how about V5?
Siegfried Heintze [EMAIL PROTECTED] wrote on 08/30/2005 03:11:26 PM: [Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried I think what you are looking for is MySQL Query Browser http://www.mysql.com/products/tools/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Insert Into problem
Can anyone please tell me why this fails, I've done this type of thing before without a problem Insert Into tbl1 Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114 tbl1 has five fields all ints with fld5 being the autoincrement index. I've also tried it as, Insert Into tbl1 (fld1, fld2, fld3, fld4) Values(Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114) I think its obvious what I want but just in case, I want to duplicate all the records where fld1 matches my value and assign all the duplicate records a new value for fld1. When I do it the first way I get a Column Count doesn't match value count at row 1 error. When I do it the second way I just get a syntax error. Thanks!
Re: Insert Into problem
Drop the VALUES part in your second attempt: INSERT INTO tbl1 (fld1, fld2, fld3, fld4) SELECT 10306, fld2, fld3, fld4 FROM tbl1 WHERE fld1 = 8114 Note that you'll need to be running MySQL 4.0.14 or above to insert into the same table you're selecting from. Eamon Daly - Original Message - From: Ed Reed [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 30, 2005 3:08 PM Subject: Insert Into problem Can anyone please tell me why this fails, I've done this type of thing before without a problem Insert Into tbl1 Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114 tbl1 has five fields all ints with fld5 being the autoincrement index. I've also tried it as, Insert Into tbl1 (fld1, fld2, fld3, fld4) Values(Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114) I think its obvious what I want but just in case, I want to duplicate all the records where fld1 matches my value and assign all the duplicate records a new value for fld1. When I do it the first way I get a Column Count doesn't match value count at row 1 error. When I do it the second way I just get a syntax error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Into problem
Ed Reed [EMAIL PROTECTED] wrote on 08/30/2005 04:08:57 PM: Can anyone please tell me why this fails, I've done this type of thing before without a problem Insert Into tbl1 Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114 tbl1 has five fields all ints with fld5 being the autoincrement index. I've also tried it as, Insert Into tbl1 (fld1, fld2, fld3, fld4) Values(Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114) I think its obvious what I want but just in case, I want to duplicate all the records where fld1 matches my value and assign all the duplicate records a new value for fld1. When I do it the first way I get a Column Count doesn't match value count at row 1 error. When I do it the second way I just get a syntax error. Thanks! The correct syntax for the second method doesn't use VALUES(). I would always use this form if I were going to exclude any columns from receiving data. It's just safer and relies less on the SQL engine to correctly divine what it is you want to do. Insert Into tbl1 (fld1, fld2, fld3, fld4) Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Connections with bad DNS cause lockups
Hi, We seem to be running into a problem with our installation that we don't understand. We are running mysql-server-4.0.25 from the ports collection on a FreeBSD 5.3-RELEASE-p10 machine. Its tcpwrapper'd to only allow from our /24, and a single machine outside the /24. At times, all of a sudden the server seems to freeze. It appears that we've narrowed it down to an issue with people attacking the server that come from a site that has a bad reverse DNS setup. Has anyone else seen this, or knows how to stop it? Thanks, Tuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hierarchical db/depth?
Mel,
I would like to know if there is a way to have a kind of auto-extension
of the query if there is for example a fifth level?
SQLdoesn't have recursion, so to avoid writing literal queries for each
number of levels you need either (i) something like an edge list, an
adjacency list, or a preorder tree traversal ('nested sets' as Celko
calls them) model, or (ii) write a query generator in an app language.
PB
-
mel list_php wrote:
hi!
I'm still trying to organize an hierachical db (I saw the last article
on mysql.com:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.hml).
I began working with what the author calls adjency list model (I
think the nested set model is a bit too complex for what I want to
do.In addition I found an equivalent schema in an other application
which is close to mineand working fine)
Basically, I have a table term (term_id, name), relation
(relation_id,term_id1,term_id2,type_id) and a last table to identify
the relations type id, relation_type (type_id,type_name).
I can retrieve all the info I need with that query:
SELECT t1.term_id2 AS lev0, term0.name AS parent0, trel1.type_name AS
rel1, t1.term_id1 AS lev1, term1.name AS parent1, trel2.type_name AS
rel2, t2.term_id1 AS lev2, term2.name AS parent2, trel3.type_name AS
rel3, t3.term_id1 AS lev3, term3.name AS parent3
FROM relation AS t1
LEFT JOIN relation_type AS trel1
USING ( type_id )
LEFT JOIN term AS term0 ON ( t1.term_id2 = term0.term_id )
LEFT JOIN relation AS t2 ON t2.term_id2 = t1.term_id1
LEFT JOIN relation_type AS trel2
USING ( type_id )
LEFT JOIN term AS term1 ON ( t2.term_id2 = term1.term_id )
LEFT JOIN relation AS t3 ON t3.term_id2 = t2.term_id1
LEFT JOIN relation_type AS trel3
USING ( type_id )
LEFT JOIN term AS term2 ON ( t3.term_id2 = term2.term_id )
LEFT JOIN term AS term3 ON t3.term_id1 = term3.term_id
WHERE t1.term_id2 =1
It's just adding a new sub-part to the query for each level.
This is working fine on my data sample, because I know that I have
exactly 4 levels of depth.
I would like to know if there is a way to have a kind of
auto-extension of the query if there is for example a fifth level?
Thanks for any help or tip,
melanie
_
Be the first to hear what's new at MSN - sign up to our free
newsletters! http://www.msn.co.uk/newsletters
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.16/83 - Release Date: 8/26/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error while running mysql_install_db on Solaris 8
Please see the attached output of the bug report and an output file of the messages that came out in the screen on Sun Solaris 8 while running the mysql_install_db for MySQL version 4,1.14, 64 bit. I got the same message for 32 bit version also. I tried MySQL version 4,1.12, 64 bit and 32 bit all failed with the same message and in every case there was no log file in mysql/data directory. Please check. If you need further information please let me know. Thanks. Helal Khan Sr. Manager, SDC Two Metrotech, 8th Floor Brooklyn, NY 11201 Tel: 212.383.4000 Fax:212.383.4325 E-mail: [EMAIL PROTECTED] This message and its attachments may contain privileged and confidential information. If you are not the intended recipient(s), you are prohibited from printing, forwarding, saving or copying this email. If you have received this e-mail in error, please immediately notify the sender and delete this e-mail and its attachments from your computer. khanstation# scripts/mysql_install_db --user=mysql Installing all prepared tables ld.so.1: ./bin/mysqld: fatal: libz.so.1: open failed: No such file or directory Killed Installation of system tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ../bin/mysqld --skip-grant You can use the command line tool ../bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! khanstation# Broken Pipe khanstation# cd .. khanstation# pwd /usr/local khanstation# ls -l total 6 drwxr-xr-x 2 root other512 Aug 1 10:12 bin lrwxrwxrwx 1 root other 59 Aug 30 15:41 mysql - /usr/local/mysql-standard-4.1.14-sun-solaris2.8-sparc-64bit drwxr-xr-x 14 101 other512 Aug 30 15:46 mysql-standard-4.1.14-sun-solaris2.8-sparc-64bit khanstation# pwd /usr/local khanstation# id uid=0(root) gid=1(other) SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Super-User Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.1.14-standard (MySQL Community Edition - Standard (GPL)) C compiler: C++ compiler: Environment: machine, os, target, libraries (multiple lines) System: SunOS khanstation 5.8 Generic_108528-09 sun4u sparc SUNW,Ultra-5_10 Architecture: sun4 Some paths: /usr/bin/perl /usr/ucb/cc Compilation info: CC='cc-5.0' CFLAGS='-Xa -xstrconst -mt -D_FORTEC_ -xarch=v9' CXX='CC' CXXFLAGS='-noex -mt -D_FORTEC_ -xarch=v9' LDFLAGS='' ASFLAGS='-xarch=v9' LIBC: lrwxrwxrwx 1 root root 11 Jan 28 2004 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136720 Jun 6 2001 /lib/libc.so.1 lrwxrwxrwx 1 root root 11 Jan 28 2004 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136720 Jun 6 2001 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--with-named-z-libs=no' '--with-named-curses-libs=-lcurses' '--disable-shared' '--with-low-memory' '--with-readline' '--with-embedded-server' '--with-archive-storage-engine' '--with-innodb' 'CC=cc-5.0' 'CFLAGS=-Xa -xstrconst -mt -D_FORTEC_ -xarch=v9' 'CXXFLAGS=-noex -mt -D_FORTEC_ -xarch=v9' 'CXX=CC' Perl: This is perl, version 5.005_03 built for sun4-solaris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Re: Error while running mysql_install_db on Solaris 8
Helal Khan [EMAIL PROTECTED] wrote on 08/30/2005 04:35:48 PM: Please see the attached output of the bug report and an output file of the messages that came out in the screen on Sun Solaris 8 while running the mysql_install_db for MySQL version 4,1.14, 64 bit. I got the same message for 32 bit version also. I tried MySQL version 4,1. 12, 64 bit and 32 bit all failed with the same message and in every case there was no log file in mysql/data directory. Please check. If you need further information please let me know. Thanks. Helal Khan Sr. Manager, SDC Two Metrotech, 8th Floor Brooklyn, NY 11201 Tel: 212.383.4000 Fax:212.383.4325 E-mail: [EMAIL PROTECTED] How are you trying to install MySQL? Are you following the Official MySQL Installation Documentation ( http://dev.mysql.com/doc/mysql/en/installing.html) or something else? Please, do not attach files to your messages. Many (if not most) of the people on this list (including me) will NOT read them. I am not a Solaris expert but there are several very good ones that do monitor this list. Please repost your errors inline and do not forget to CC the list on all responses. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Counting results for pagination + limit?
On Tuesday 30 August 2005 02:30 pm, [EMAIL PROTECTED] wrote:
Chris [EMAIL PROTECTED] wrote on 08/30/2005 01:51:34 PM:
Greetings,
We have a few queries that we use against our product database. We pull
these
results, and only display 50 rows per page via our web interface (then
use
next and back page links). An example query is:
select distributer.short_desc,
distributer.sku,
distributer.avail_code,
distributer.msrp,
distributer.dealer,
IF( ISNULL(distributer_classmap.description),
distributer.brand_code,
distributer_classmap.description
),
distributer.msrp - distributer.dealer as profit,
distributer.family from distributer
Left Join distributer_classmap on
distributer_classmap.code = distributer.brand_code
where avail_code = ('AA' or 'A' or 'B')
and
(distributer.brand_code = ? or distributer_classmap.description = ?)
LIMIT ?,50
What I need to do is be able to count the *total* number of results
this query
generates, so I can build the offset numbers for the proper number of
next
page links.
Since I am using a limit clause, if i were to count in my app how many
rows
there are I would get 50 as that is what the limit is set for.
The only other way I know of to get the total results is to use COUNT. I
really am not that great with SQL, so I don't know how I would apply a
count
statement to the above SQL.
I would like to keep the counting of total results, along with the
LIMIT'ed
result statement into 1 query if possible.
Any hints would be greatly appreciated.
I am using MySQL 4.1.13a
Thanks!
You want to check out the FOUND_ROWS() function:
http://dev.mysql.com/doc/mysql/en/information-functions.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thank you, works great
-c
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error while running mysql_install_db on Solaris 8
Hi, Your error message khanstation# scripts/mysql_install_db --user=mysql Installing all prepared tables ld.so.1: ./bin/mysqld: fatal: libz.so.1: open failed: No such file or directory -- means you don't have zlib installed or you don't have it in your LD_LIBRARY_PATH. You can put in into your default path by using the crle command (man crle) and you can get this from http://www.sunfreeware.com if you don't have it installed. I notice you had --with-named-z-libs=no, try taking this out and letting ./configure find out where the z libraries are. The definition for this option is : --with-named-z-libs=ARG Use specified zlib libraries instead of those automatically found by configure. and it is probably looking for a path called no. If you need to disable it, then try using --without-named-z-libs (I may be wrong here but that is my reading of the configure --help screen) or leave it out altogether. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax From: Helal Khan [mailto:[EMAIL PROTECTED] Sent: Wednesday, 31 August 2005 6:06 AM To: mysql@lists.mysql.com Subject: Error while running mysql_install_db on Solaris 8 Please see the attached output of the bug report and an output file of the messages that came out in the screen on Sun Solaris 8 while running the mysql_install_db for MySQL version 4,1.14, 64 bit. I got the same message for 32 bit version also. I tried MySQL version 4,1.12, 64 bit and 32 bit all failed with the same message and in every case there was no log file in mysql/data directory. Please check. If you need further information please let me know. Thanks. Helal Khan Sr. Manager, SDC Two Metrotech, 8th Floor Brooklyn, NY 11201 Tel: 212.383.4000 Fax:212.383.4325 E-mail: [EMAIL PROTECTED] This message and its attachments may contain privileged and confidential information. If you are not the intended recipient(s), you are prohibited from printing, forwarding, saving or copying this email. If you have received this e-mail in error, please immediately notify the sender and delete this e-mail and its attachments from your computer.
Re: Insert Into problem
Thanks guys! [EMAIL PROTECTED] 8/30/05 1:22 PM The correct syntax for the second method doesn't use VALUES(). I would always use this form if I were going to exclude any columns from receiving data. It's just safer and relies less on the SQL engine to correctly divine what it is you want to do. Insert Into tbl1 (fld1, fld2, fld3, fld4) Select 10306, fld2, fld3, fld4 From tbl1 Where fld1=8114; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eamon Daly [EMAIL PROTECTED] 8/30/05 1:25 PM Drop the VALUES part in your second attempt: INSERT INTO tbl1 (fld1, fld2, fld3, fld4) SELECT 10306, fld2, fld3, fld4 FROM tbl1 WHERE fld1 = 8114 Note that you'll need to be running MySQL 4.0.14 or above to insert into the same table you're selecting from. Eamon Daly
Server hangs and table gets corrupted on simple subselect
Hi, I wonder if someone can help me with the following simple(?) MySQL problem. I have a table 'action' with about 180,000 web server requests records running under MySQL Ver 14.7 Distrib 4.1.12 (Fedora Core 4) mysqldesc action +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | host_ip | varchar(16) | | | || | file| varchar(255) | | | || | querystring | varchar(255) | YES | | NULL|| | timestamp | datetime | YES | | NULL|| +-+--+--+-+-++ 5 rows in set (0.00 sec) From this table, I must delete all records associated with host_ips that occur only once in the table (all unique host_ips). I have tried the following approaches; all of which hang the server and corrupt the table: Method 1: First create a 'totals' table that holds for each host_ip the number of occurrences in the 'action' table: mysql create table totals as select host_ip, count(*) as hits from action group by host_ip order by hits; Next, combine the tables in a query (a 'select' for now, but a 'delete' eventually): mysql select from action where host_ip in ( select host_ip from totals where hits = 1 ); Method 2: use an explicit join: mysql select host_ip from action, totals where action.host_ip = totals.host_ip and totals.hits = 1; Method 3: don't use the 'totals' table at all: mysql select host_ip from action group by host_id having count(*) = 1; As mentioned, none of these work and all of these hang the server and break the database. How do I do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: hierarchical db/depth?
You should see it: http://www.openwin.org/mike/presentations/hierarchy/hierarchy.html Regrads! -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 9:01 AM To: mysql@lists.mysql.com Subject: hierarchical db/depth? hi! I'm still trying to organize an hierachical db (I saw the last article on mysql.com: http://dev.mysql.com/tech-resources/articles/hierarchical-data.hml). I began working with what the author calls adjency list model (I think the nested set model is a bit too complex for what I want to do.In addition I found an equivalent schema in an other application which is close to mineand working fine) Basically, I have a table term (term_id, name), relation (relation_id,term_id1,term_id2,type_id) and a last table to identify the relations type id, relation_type (type_id,type_name). I can retrieve all the info I need with that query: SELECT t1.term_id2 AS lev0, term0.name AS parent0, trel1.type_name AS rel1, t1.term_id1 AS lev1, term1.name AS parent1, trel2.type_name AS rel2, t2.term_id1 AS lev2, term2.name AS parent2, trel3.type_name AS rel3, t3.term_id1 AS lev3, term3.name AS parent3 FROM relation AS t1 LEFT JOIN relation_type AS trel1 USING ( type_id ) LEFT JOIN term AS term0 ON ( t1.term_id2 = term0.term_id ) LEFT JOIN relation AS t2 ON t2.term_id2 = t1.term_id1 LEFT JOIN relation_type AS trel2 USING ( type_id ) LEFT JOIN term AS term1 ON ( t2.term_id2 = term1.term_id ) LEFT JOIN relation AS t3 ON t3.term_id2 = t2.term_id1 LEFT JOIN relation_type AS trel3 USING ( type_id ) LEFT JOIN term AS term2 ON ( t3.term_id2 = term2.term_id ) LEFT JOIN term AS term3 ON t3.term_id1 = term3.term_id WHERE t1.term_id2 =1 It's just adding a new sub-part to the query for each level. This is working fine on my data sample, because I know that I have exactly 4 levels of depth. I would like to know if there is a way to have a kind of auto-extension of the query if there is for example a fifth level? Thanks for any help or tip, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Control Center works with v4.0.23 -- how about V5?
That is unfortunate that it MSQLCC is deprecated. Is there a gui tool, maybe query-browser, that will let me update, insert and delete without writing SQL statements? That was a very nice feature. Thanks, Siegfried _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 1:12 PM To: Siegfried Heintze Cc: mysql@lists.mysql.com Subject: Re: MySQL Control Center works with v4.0.23 -- how about V5? Siegfried Heintze [EMAIL PROTECTED] wrote on 08/30/2005 03:11:26 PM: [Siegfried Heintze] I love MySQL Control center. I can make it work for MySQL v4.0 but not the latest (v5). It simply does not connect to the V5 Mysql server I just installed. It seems to hang on the connection. Am I doing something wrong or does MySQL Control center not support 5? Assuming it does not work with MySQL v5, is there a similar substitute that does work with V5 that will enable me to look at my data and try out SQL statements interactively? I was using the GUI program that comes with MySQL V5 (I think it is the MySQL Administrator) and that looks very nice too. However, I could not figure out how to make it view the contents of my tables. It looked like it was supposed to be able to do that from the screen shots. Thanks, Siegfried I think what you are looking for is MySQL Query Browser http://www.mysql.com/products/tools/ Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Alphabetizing within GROUPS
Hi. Thanks for any help on this. I've been beating my head over it for hours. Here's what I am trying to do: I have four tables I am joining via a unique key (Fellow_id). The results I want to group into three categories, each alphabetized within the group. Is this too much to do in a single query? I am very close, but not there yet. Here's the query: SELECT Fellowships.Fellowship_type, Fellowships.Fellowship_year, Fellowships.Fellowship_id, Fellow_contact.Fellow_1st_name, Fellow_contact.Fellow_2nd_name, Fellowships.Fellow_id, graduate_results_INSTITUTIONS.END, graduate_results_INSTITUTIONS.UNKNOWN, graduate_results_INSTITUTIONS.COMMENTS FROM Fellow_coordinator LEFT JOIN Fellowships ON Fellow_coordinator.Fellow_id = Fellowships.Fellow_id LEFT JOIN Fellow_contact ON Fellow_coordinator.Fellow_id = Fellow_contact.Fellow_id LEFT JOIN graduate_results_INSTITUTIONS ON Fellow_coordinator.Fellow_id = graduate_results_INSTITUTIONS.Fellow_id WHERE Fellow_coordinator.Coord_id = '$Coord_id' GROUP BY graduate_results_INSTITUTIONS.UNKNOWN ASC, graduate_results_INSTITUTIONS.END ASC, Fellowships.Fellowship_id I want to alphabetize each group with something like: ORDER BY Fellow_contact.Fellow_2nd_name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table in use
When i do some insert or edit operations on my BDB tables i sometimes get an error saying table in use. After this i cannot proceed further. I only have to shut down the server and restart it all over again. Why do i get this and what should be done ??
