Older mysql client with newer server compatibility
Hi experts, I'm thinking about compatibility issues between older mysql client (say 5.0.x or 5.1.x) with newer mysql server (5.5.x or 5.6.x). After some tests I've noticed no issues and it seems to work just fine. Is anybody aware of some problems that I can expect? Cheers, Honza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Older mysql client with newer server compatibility
Am 17.09.2012 10:53, schrieb Honza Horak: I'm thinking about compatibility issues between older mysql client (say 5.0.x or 5.1.x) with newer mysql server (5.5.x or 5.6.x). After some tests I've noticed no issues and it seems to work just fine. Is anybody aware of some problems that I can expect? as long as protocols / authentications are not changed no problem above means: mysql client 4.0 can not connect to = 4.1 if the server is using old_passwords = 0 sql commands are sql commands, independent of the version signature.asc Description: OpenPGP digital signature
Re: Older mysql client with newer server compatibility
On 09/17/2012 11:08 AM, Reindl Harald wrote: Am 17.09.2012 10:53, schrieb Honza Horak: I'm thinking about compatibility issues between older mysql client (say 5.0.x or 5.1.x) with newer mysql server (5.5.x or 5.6.x). After some tests I've noticed no issues and it seems to work just fine. Is anybody aware of some problems that I can expect? as long as protocols / authentications are not changed no problem above means: mysql client 4.0 can not connect to = 4.1 if the server is using old_passwords = 0 sql commands are sql commands, independent of the version Of course, so I can specify my question a little bit: Is anybody aware of any other backward incompatible change in protocol? Honza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
function INTERVAL in view
My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: function INTERVAL in view
n 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? Looks like a bug. Report it? PB www.artfulsoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: function INTERVAL in view
INTERVAL is a keyword. This is probably the root of the hiccup. Is that your Stored Function? Or UDF? -Original Message- From: Peter Brawley [mailto:peter.braw...@earthlink.net] Sent: Monday, September 17, 2012 11:12 AM To: mysql@lists.mysql.com Subject: Re: function INTERVAL in view n 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? Looks like a bug. Report it? PB www.artfulsoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: function INTERVAL in view
-Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: September 17, 2012 3:04 PM To: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: RE: function INTERVAL in view INTERVAL is a keyword. This is probably the root of the hiccup. Is that your Stored Function? Or UDF? on 2012-09-17 12:58 PM, h...@tbbs.net wrote: My MySQL is of version 5.5.8-log. I find I cannot save a query with INTERVAL in a view: redundant round brackets are added. If the query is SELECT INTERVAL(1, 2, 3, 4) within the frm file there is the expression interval((1, 2, 3, 4)) which is wrong. What is known about this? It is also a function: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in terval as for why it does that? I have no idea. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Making myself crazy...
If the subquery-version is not too slow, live with it. If necessary, make your non-grouped SELECT a subquery and apply GROUP BY outside. Thus: SELECT ... FROM ( SELECT non-grouped... ) GROUP BY ... Could you provide that; we might be able to simplify it. Also provide SHOW CREATE TABLE for each table. How many rows in each table? (approx) -Original Message- From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Sunday, September 16, 2012 3:45 PM To: mysql@lists.mysql.com Subject: Making myself crazy... I'm having trouble figuring out how to make a query. It seems simple, but it's making me crazy right now. Please point out where my thinking is addled... I have the following (simplified) table structure: s.timelog -- s.projects -- s.profit_centres s.product.sales -- s.products -- s.profit_centres (The arrows refer to many-to-one relationships: each Timelog record refers to a single Project, which is in a single Profit Centre. Each record has an opaque ID referred to by records in the table to its left.) What I want to do is figure out productivity: sales per hour worked per Profit Centre. I can do this at a gross level -- without grouping by Profit Centres -- with a subquery: simply sum up the Lales and divide by the sum of the labour (Timelog.out - Timelog.in). But I suspect even this can be done without a subquery. But needing to do two levels of indirection has stymied me: how can I group $/hour by Profit Centre? You can see the SQL here if you wish: http://www.ecoreality.org/wiki/Template:Annual_gross_productivity _for Then click on the Source link to see the code. (You won't be able to change it without logging in.) I'm using CASE to pivot tables to separate out monetary versus barter/trade income. Thanks for any help offered! Respond with love and compassion, rather than punishment and retaliation, and an angry person will be disarmed. Trade the need to be right for a loving relationship. It is a trade you - and everyone around you - will benefit from. -- Dean Van Leuven Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Older mysql client with newer server compatibility
I'm pretty sure all is well... In one case, I think I have 4.1 client talking to 5.5 server. -Original Message- From: Honza Horak [mailto:hho...@redhat.com] Sent: Monday, September 17, 2012 2:53 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: Older mysql client with newer server compatibility On 09/17/2012 11:08 AM, Reindl Harald wrote: Am 17.09.2012 10:53, schrieb Honza Horak: I'm thinking about compatibility issues between older mysql client (say 5.0.x or 5.1.x) with newer mysql server (5.5.x or 5.6.x). After some tests I've noticed no issues and it seems to work just fine. Is anybody aware of some problems that I can expect? as long as protocols / authentications are not changed no problem above means: mysql client 4.0 can not connect to = 4.1 if the server is using old_passwords = 0 sql commands are sql commands, independent of the version Of course, so I can specify my question a little bit: Is anybody aware of any other backward incompatible change in protocol? Honza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Are Single Column Indexes are sufficient
Hi all, Currently i am doing performance level tuning of some queries that are running very slow in my slow -query log. Below are the sample of some queries the cardinality of indexes :- --- Below queries take more than 15 minutes to complete on a table scd_table of size 7 GB SELECT t0.id, t0.bean_type, t0.action_number, t0.action_xml, t0.console_url, t0.created_conf, t0.error_code, t0.error_message, t0.external_status, t0.missing_dependencies, t0.run_conf, t0.time_out, t0.tracker_uri, t0.job_type, t0.created_time, t0.external_id, t0.job_id, t0.last_modified_time, t0.nominal_time, t0.pending, t0.rerun_time, t0.sla_xml, t0.status FROM scd_table t0 WHERE (t0.job_id = '006-120613043532587-o-C') AND t0.bean_type = 'ActionItems'; select status, count(*) as cnt from scd_table where job_id = '0043189-120805203721153-o-C' and nominal_time = '2012-09-07 07:16:00' and nominal_time '2012-09-07 08:06:00' group by status; SELECT t0.id, t0.bean_type, t0.action_number, t0.action_xml, t0.console_url, t0.created_conf, t0.error_code, t0.error_message, t0.external_status, t0.missing_dependencies, t0.run_conf, t0.time_out, t0.tracker_uri, t0.job_type, t0.created_time, t0.external_id, t0.job_id, t0.last_modified_time, t0.nominal_time, t0.pending, t0.rerun_time, t0.sla_xml, t0.status FROM scd_table t0 WHERE (t0.pending 0 AND (t0.status = 'SUSPENDED' OR t0.status = 'KILLED' OR t0.status = 'RUNNING') AND t0.last_modified_time = '2012-09-07 08:08:34') AND t0.bean_type = 'ActionItems'; mysql show indexes from scd_table; +---++--+--++---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--++---+-+--++--++-+ | scd_table | 0 | PRIMARY |1 | id | A | 188908 | NULL | NULL | | BTREE | | | scd_table | 1 | I_CRD_TNS_CREATED_TIME |1 | created_time | A | 188908 | NULL | NULL | YES | BTREE | | | scd_table | 1 | I_CRD_TNS_DTYPE |1 | bean_type | A | 14 | NULL | NULL | YES | BTREE | | | scd_table | 1 | I_CRD_TNS_EXTERNAL_ID|1 | external_id| A | 188908 | NULL | NULL | YES | BTREE | | | scd_table | 1 | I_CRD_TNS_JOB_ID |1 | job_id | A | 365 | NULL | NULL | YES | BTREE | | | scd_table | 1 | I_CRD_TNS_LAST_MODIFIED_TIME |1 | last_modified_time | A | 188908 | NULL | NULL | YES | BTREE | | | scd_table | 1 | I_CRD_TNS_RERUN_TIME |1 | rerun_time | A | 14 | NULL | NULL | YES | BTREE | | | scd_table | 1 | I_CRD_TNS_STATUS |1 | status | A | 14 | NULL | NULL | YES | BTREE | | +---++--+--++---+-+--++--++-+ Whenever i explain the query it takes the index with low cardinality. Can I remove all the indexes and create only 1-2 multi column index or any other tuning that i can do for the above queries. Please let me know if any other info is reqd. ( table schema has the same columns mentioned in select clause ). Thanks
