RE: Query Help
[snip] For the life of me I cannot remember how to make a query like this and what it is called. I know it is fairly basic though. Table 1 Product_id Product_Name Table 2 Category_id, Category_name Table 3 Product_id, Category_id Each product can have one or more categories. So I want a result that has Product A one category other category Product B other category [/snip] Can you give us an example of how you would like the output to be? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
More Tools to Work with MySQL Databases Provided by dbForge Studio!
Devart Email: i...@devart.com Web: http://www.devart.com FOR IMMEDIATE RELEASE CONTACT INFORMATION: Julia Samarska jul...@devart.com 12-Jul-10 More Tools to Work with MySQL Databases Provided by dbForge Studio! With dbForge Studio for MySQL, Devart continues its initiative to produce efficient database experiences for all the people in MySQL world. Devart today releases dbForge Studio for MySQL, v4.50 - cutting-edge administration tool and development environment for professional working with MySQL databases. With dbForge Studio, Devart continues its initiative to produce efficient database experiences for all the people in MySQL world. New features in dbForge Studio for MySQL, v4.50 include: a.. More freedom for backing up schemas Schema Export wizard was totally redesigned to Database Backup to enable users to back up schemas in automatic mode using Windows task scheduler, save backup options for future use, view automatically complied log file. Besides, old backup files are automatically removed based on date or quantity. a.. New tool for database developers - Query Profiler dbForge Studio offers results of internal MySQL tools like SHOW PROFILE and EXPLAIN in a convenient and clear GUI. Besides, you get STATUS variables for the required query automatically calculated. Additional benefits: a.. Plan of the query displayed in the tree view for easy review b.. Profiling history that can be saved for further analysis c.. Capability to compare profiling results in two clicks d.. Capability to print profiling results e.. Data comparison and synchronization of any databases Diverse testing and close interaction with database developers, admins and casual users resulted in thoughtful redesign and enhancement of Data Comparison tool. Now it compares and synchronizes database of any length with significant performance improvement. To customize comparison and synchronization, the users can use new options, change synchronization direction in one click, and quickly filter tables in comparison results. An additional benefit is generating accurate comparison reports in HTML and Excel formats. a.. Advanced query building Now Query Builder, a powerful tool for visual query creating, is tailored for creating complex conditions with several clicks. The new power is based on optimized performance of the Selection tab in the expression editor, visual addition of subqueries to any part of the main query, new Wrap to Subquery option to wrap tables into a subquery, optimized navigation in the editor, particularly between subqueries and other features. a.. Quick generating template SQL scripts for database objects Thanks to this new functionality, you can save your time while working with database objects. For example, you can quickly generate template SQL scripts CREATE, DROP, SELECT, INSERT, UPDATE or DELETE scripts for tables. This option is available in the context menu of Database Explorer and called 'Generate Script As'. a.. Improved schema comparison a.. Extended capabilities of Schema Comparison wizard b.. New comparison options to ignore some table options, DEFINER and SQL SECURITY expressions, default values for columns Price and Availability The fully-functional 30-day trial version of dbForge Studio for MySQL 4.50 is available for download at www.devart.com/dbforge/mysql/studio/download.html. The license price starts at $49.95. For more information about dbForge Studio for MySQL, visit the product's web site - www.devart.com/dbforge/mysql/studio/. About Devart Devart is a software development company with 11 years of experience on the software market and over 20 thousands of devoted users. We specialize in providing comprehensive development and management tools as well as native connectivity solutions for the most popular databases, including Oracle, SQL Server, MySQL, PostgreSQL, InterBase, Firebird, and SQLite. For additional information about Devart, visit www.devart.com/company/. # # # EVALUATION COPY AVAILABLE ON REQUEST
Creating a Data Dictionary
Hi;
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value pairs
into one data and create an enum like that, then parse them later. I'm just
wondering if there's a more elegant way to do this.
TIA,
Victor
Re: Creating a Data Dictionary
No such thing in SQL
On Mon, Jul 12, 2010 at 10:39 AM, Victor Subervi
victorsube...@gmail.com wrote:
Hi;
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value pairs
into one data and create an enum like that, then parse them later. I'm just
wondering if there's a more elegant way to do this.
TIA,
Victor
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Creating a Data Dictionary
[snip]
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value
pairs
into one data and create an enum like that, then parse them later. I'm
just
wondering if there's a more elegant way to do this.
[/snip]
There is an enumerated type
http://dev.mysql.com/doc/refman/5.1/en/enum.html and there are data
dictionaries
http://dev.mysql.com/tech-resources/articles/mysql-datadictionary.html
but neither are really what you want.
Actually what you're describing the reason that we have databases in the
first place - the ability to have data in one column ('1') related to
data in another column ('one') in a record. The most elegant way of
using a database is to use it as it was designed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: table structure problem
On 7/8/2010 11:29 PM, Miguel Vaz wrote: Hi, I am having some uncertainty while designing the following structure: I have two sets of data: * arqueology sites (can be natural): id name description id_category id_period x y * natural sites (can be arqueological also - bear with me -, so there will be duplicate records in the above table and this): id name description altitude x y and i would like to put these two sites in the same data set and maybe add a new table called site types to categorize each record (maybe a relation table to allow many to many): how can i go about doing it? is this solution decent enough: * sites (generic): id_site name description x y * site_natural id id_site altitude * site_arqueology id id_site id_category id_period But i seem to be missing something. How can i have this in a way that its easy to list only arqueology sites for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated. You have done a nice job at normalizing. All of you sites are tagged and identified in one table then specific differentiating details are stored in separate tables. When you ask about listing only the arqueology sites that's just a simple query: SELECT * from site_arqueology inner join sites on sites.id_site = site_arqueology.id_site I can't see where your problem is. Can you provide some additional details? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
finding exact query being run
Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing sleep Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo
Re: Creating a Data Dictionary
A data dictionary, as the term is generally used, is what is contained
in INFORMATION_SCHEMA. It is meta data describing the types and names
and structure of the data within a given domain.
Python's concept of a dictionary is what other language call a hash
(perl), an associative array (awk,php) or a Map (Java/JVM). None of
them implement 'data dictionaries' as the term is used.
- md
On Mon, Jul 12, 2010 at 11:24 AM, Jay Blanchard jblanch...@pocket.com wrote:
[snip]
Perhaps I have a conflict of terms here, but my googling mysql data
dictionary turned up material that didn't seem to correspond with my
problem. In python I can create dictionaries:
my_dict = {'1': 'one', '2': 'two'}
Now, I would like to create the equivalent of an enum in which I could
utilize data like that. Of course, I could lump the whole key-value
pairs
into one data and create an enum like that, then parse them later. I'm
just
wondering if there's a more elegant way to do this.
[/snip]
There is an enumerated type
http://dev.mysql.com/doc/refman/5.1/en/enum.html and there are data
dictionaries
http://dev.mysql.com/tech-resources/articles/mysql-datadictionary.html
but neither are really what you want.
Actually what you're describing the reason that we have databases in the
first place - the ability to have data in one column ('1') related to
data in another column ('one') in a record. The most elegant way of
using a database is to use it as it was designed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: finding exact query being run
SHOW FULL RPOCESSLIST will show you the full text of the running queries. - md On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards machi...@rdc.co.zawrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing “sleep” Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] -- - michael dykman - mdyk...@gmail.com May the Source be with you.
RE: finding exact query being run
I had a look and all of these just say sleep as the command. Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: 12 July 2010 5:43 PM To: Machiel Richards Cc: mysql@lists.mysql.com Subject: Re: finding exact query being run SHOW FULL RPOCESSLIST will show you the full text of the running queries. - md On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing sleep Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting Error! Filename not specified. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: finding exact query being run
Am I correct in assuming that your application does connection pooling? If so, then these long lasting, idle threads are normal. If there is no connection pooling, then this might be a symptom of a problem with your application server not letting go of processes properly. - md On Mon, Jul 12, 2010 at 11:44 AM, Machiel Richards machi...@rdc.co.zawrote: I had a look and all of these just say sleep as the command. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Michael Dykman [mailto:mdyk...@gmail.com] *Sent:* 12 July 2010 5:43 PM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: finding exact query being run SHOW FULL RPOCESSLIST will show you the full text of the running queries. - md On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing “sleep” Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting *Error! Filename not specified.* -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
RE: finding exact query being run
mmm... I am not sure about the connection pooling... Fairly new to the site. What I do know is that they run a couple of web servers that connect to the MySQL database and are currently experiencing some performance and connection problems. There are a lot of processes that have been running for more than 25000 seconds and all of them seem to be coming from the web server / host. I am trying to find out whether this can be the cause of the problem and if o if we can simply kill these processes. Machiel Richards herbalife_logo From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: 12 July 2010 5:47 PM To: Machiel Richards Cc: mysql@lists.mysql.com Subject: Re: finding exact query being run Am I correct in assuming that your application does connection pooling? If so, then these long lasting, idle threads are normal. If there is no connection pooling, then this might be a symptom of a problem with your application server not letting go of processes properly. - md On Mon, Jul 12, 2010 at 11:44 AM, Machiel Richards machi...@rdc.co.za wrote: I had a look and all of these just say sleep as the command. Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: 12 July 2010 5:43 PM To: Machiel Richards Cc: mysql@lists.mysql.com Subject: Re: finding exact query being run SHOW FULL RPOCESSLIST will show you the full text of the running queries. - md On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing sleep Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting Error! Filename not specified. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: table structure problem
Hi, Shawn, Thanks for replying. What i meant is that i would also like to create a table with site types, where i would have a listing of possible sites, like arqueology, natural, etc. and maybe use it to redirect the queries instead of having to hardcode the table name when i need to list a specific type of site. :-) site_types id_site_type name this table would have: 1 arqueology 2 natural 3 generic ... how could i use this to be able to query dynamically? Maybe use the field id_site_type in the table sites as a link? Thanks. Pag PS - Shawn, forgive the duplicate email. I replied only to you first, instead of all. On Mon, Jul 12, 2010 at 4:37 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 7/8/2010 11:29 PM, Miguel Vaz wrote: Hi, I am having some uncertainty while designing the following structure: I have two sets of data: * arqueology sites (can be natural): id name description id_category id_period x y * natural sites (can be arqueological also - bear with me -, so there will be duplicate records in the above table and this): id name description altitude x y and i would like to put these two sites in the same data set and maybe add a new table called site types to categorize each record (maybe a relation table to allow many to many): how can i go about doing it? is this solution decent enough: * sites (generic): id_site name description x y * site_natural id id_site altitude * site_arqueology id id_site id_category id_period But i seem to be missing something. How can i have this in a way that its easy to list only arqueology sites for example. I feel the solution is simple enough, even for me, but its eluding me. Any help in the right direction would be very appreciated. You have done a nice job at normalizing. All of you sites are tagged and identified in one table then specific differentiating details are stored in separate tables. When you ask about listing only the arqueology sites that's just a simple query: SELECT * from site_arqueology inner join sites on sites.id_site = site_arqueology.id_site I can't see where your problem is. Can you provide some additional details? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: finding exact query being run
What are they running on those web servers? PHP? if so, which is of the several PHP interfaces to MySQL are they using? (Zend Enterprise does some smart connection pooling). Java-based applications tend to use connection polling more often than not. You should find out to determine if those sleeping connections are a good sign or a bad one. On Mon, Jul 12, 2010 at 11:51 AM, machiel.richards machiel.richa...@gmail.com wrote: mmm... I am not sure about the connection pooling... Fairly new to the site. What I do know is that they run a couple of web servers that connect to the MySQL database and are currently experiencing some performance and connection problems. There are a lot of processes that have been running for more than 25000 seconds and all of them seem to be coming from the web server / host. I am trying to find out whether this can be the cause of the problem and if o if we can simply kill these processes. *Machiel Richards* [image: herbalife_logo] *From:* Michael Dykman [mailto:mdyk...@gmail.com] *Sent:* 12 July 2010 5:47 PM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: finding exact query being run Am I correct in assuming that your application does connection pooling? If so, then these long lasting, idle threads are normal. If there is no connection pooling, then this might be a symptom of a problem with your application server not letting go of processes properly. - md On Mon, Jul 12, 2010 at 11:44 AM, Machiel Richards machi...@rdc.co.za wrote: I had a look and all of these just say sleep as the command. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Michael Dykman [mailto:mdyk...@gmail.com] *Sent:* 12 July 2010 5:43 PM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: finding exact query being run SHOW FULL RPOCESSLIST will show you the full text of the running queries. - md On Mon, Jul 12, 2010 at 11:39 AM, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing “sleep” Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting *Error! Filename not specified.* -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: Query Help
Table 1 Product_id | Product_Name 1| Product A 2| Product B 3| Product C Table 2 Category_id | Category_Name 1 | Admin 2 | Marketing 3 | Support 4 | IT Table 3 Product_id | Category_id 1| 1 1| 3 2| 2 3| 3 3| 4 Result would look like Product A, Admin, Support Product B, Marketing Product C, Support, IT I believe this is a one to many using an index table? Blessed Be Phillip If you try to protect idiots from themselves, even if you succeed, you just wind up filling the world with idiots. - - Doug Casey On Mon, Jul 12, 2010 at 5:27 AM, Jay Blanchard jblanch...@pocket.comwrote: [snip] For the life of me I cannot remember how to make a query like this and what it is called. I know it is fairly basic though. Table 1 Product_id Product_Name Table 2 Category_id, Category_name Table 3 Product_id, Category_id Each product can have one or more categories. So I want a result that has Product A one category other category Product B other category [/snip] Can you give us an example of how you would like the output to be?
Re: finding exact query being run
If you have lots of sleeping processes the chances are you have a poorly written app that is not closing its database connections. Those sleeping processes are just connections waiting for another command from the application or whatever else initiated them. The sleeping processes will not show up in the slow query log because they are not slow queries and you cannot see the sql they are running because they aren't running any. If you need to prevent these building up (and you do because they will eat away at system resources) you can add a wait_timeout option to your my.cnf / my.ini file but the best way to control these is ensure your app closes its connections when it is finished with them and do not use persistent connections unless absolutely necessary Regards John John Daisley SQL Server 2005/2008 Database Administrator Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk - On 12 July 2010 16:39, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing “sleep” Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: finding exact query being run
In my.cnf, check wait_timeout value , default is 4800. Make it to wait_timeout = 60 Thus, the connections will automatically be closed after waiting for 60 seconds. On Mon, Jul 12, 2010 at 11:51 PM, John Daisley daisleyj...@googlemail.comwrote: If you have lots of sleeping processes the chances are you have a poorly written app that is not closing its database connections. Those sleeping processes are just connections waiting for another command from the application or whatever else initiated them. The sleeping processes will not show up in the slow query log because they are not slow queries and you cannot see the sql they are running because they aren't running any. If you need to prevent these building up (and you do because they will eat away at system resources) you can add a wait_timeout option to your my.cnf / my.ini file but the best way to control these is ensure your app closes its connections when it is finished with them and do not use persistent connections unless absolutely necessary Regards John John Daisley SQL Server 2005/2008 Database Administrator Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk - On 12 July 2010 16:39, Machiel Richards machi...@rdc.co.za wrote: Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing “sleep” Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: finding exact query being run
get mytop _ From: Machiel Richards [mailto:machi...@rdc.co.za] Sent: Monday, July 12, 2010 8:40 AM To: mysql@lists.mysql.com Subject: finding exact query being run Hi All I am trying to find out how to see the exact query being run. When running show processlit, I get a lot of processes that have been running for a VERY long time. I a trying to find out exactly what query it is that is being run , any ideas? I tried the slow query log but it is not showing up in there. All the queries are showing sleep Not sure if this means anything Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo
