Re: Group by question
On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
re: mysqlcheck 5.5 -a -o
Hi! Noel == Noel Butler noel.but...@ausics.net writes: Noel In all previous versions we were able to run -Aao --auto-repair Noel Anyone know WHY, in 5.5, -a and -o , must be run separately, this Noel is kind of silly I think, requiring two passes now. I checked the 5.0 code and even there you could not run 'a' and 'o' separately. mysqlcheck always used the later option. However as optimize for most engines that supports it does an automatic analyze, you can solve your problem by just removing the 'a' option above. Regards, Monty PS: Have you tried out MariaDB yet? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 He meant the execution order, please use the agregation function as suggested. On 11-01-17 05:03, Jørn Dahl-Stamnes wrote: On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.14 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNNHQIAAoJENgwSj9ZOOwrR9UH/RDLojUwXYB1a+fcS0kuwzFW AZ/VdwknydB5ENkt7+MmWgHdVcPwrChE+nY2bpRI9LYp8ehUNwaeC2uV/ToWXFti ZTnVdnFDanHy20UOF3a1X7UXW89/zTy/B06X7NP1NqmIGnAahPK6VBuIx1OP/oGZ +es+m9BIYnuc8JzfRo5YSQuydfWIJ87ygrkodhM/C2VPBWDMwpEX/wuxgW/x+ukM RXKaxHrHOrWc1hWLFp3P+QI+J7VNP1fh6Rxw1Q91latJkY4I3hbN9nEsXeHlD2l2 f5ZNn4LwPPNC++XpGaDqQmA0W2Sua9lHUhtVsxsJt5kIigAwNJ5GnaayY/p9apk= =7MLg -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Rewrite SQL to stop table scan
snip SELECT COUNT(*) AS num FROM alerts WHERE stamp DATE_SUB(NOW(), interval 300 second) AND devid=244; With this query, MySQL will run DATE_SUB() once, and then use the index on stamp (which I assume you have) to narrow down the result set. [JS] I don't understand how an index on a timestamp would help. Theoretically, each record could have a unique value for the timestamp; so the index would have an entry for each record. Would MySQL really use that in preference to, or in combination with, an index on devid? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Steve That did it! Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
re: mysqlcheck 5.5 -a -o
On Mon, 2011-01-17 at 14:50 +0200, Michael Widenius wrote: Hi! Noel == Noel Butler noel.but...@ausics.net writes: Noel In all previous versions we were able to run -Aao --auto-repair Noel Anyone know WHY, in 5.5, -a and -o , must be run separately, this Noel is kind of silly I think, requiring two passes now. I checked the 5.0 code and even there you could not run 'a' and 'o' separately. mysqlcheck always used the later option. Ahhh, so all that time it was silently ignoring the -a ? But now borks instead of ignores, my bad :) However as optimize for most engines that supports it does an automatic analyze, you can solve your problem by just removing the 'a' option above. Thanks Monty Regards, Monty PS: Have you tried out MariaDB yet? I regrettably confess, not yet signature.asc Description: This is a digitally signed message part
ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13)
Hi, I run the following command. But I got the following error. I'm not sure what causes the problem. I have seen the same issue before, but it disappeared even I didn't take any action. Could anybody let me know how to fix the problem? mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A mysql select geneName as symbol, name as refSeq, chrom, strand, txStart, txEnd from refFlat group by refSeq having count(*)=1; ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13) -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Rewrite SQL to stop table scan
On 1/17/11 9:52 AM, Jerry Schwartz wrote: [JS] I don't understand how an index on a timestamp would help. Theoretically, each record could have a unique value for the timestamp; so the index would have an entry for each record. Would MySQL really use that in preference to, or in combination with, an index on devid? You are correct. The ideal index would be across (devid, stamp). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13)
May be db files are not as MySQL user. Check owner ship for the files of the table. It should be mysql user ownership. On Tue, Jan 18, 2011 at 6:36 AM, Peng Yu pengyu...@gmail.com wrote: Hi, I run the following command. But I got the following error. I'm not sure what causes the problem. I have seen the same issue before, but it disappeared even I didn't take any action. Could anybody let me know how to fix the problem? mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A mysql select geneName as symbol, name as refSeq, chrom, strand, txStart, txEnd from refFlat group by refSeq having count(*)=1; ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13) -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Re: ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13)
Make sure the following things: - /tmp folder is having 1777 permissions - mysql folder is having the ownership from which it is running. Refer /etc/my.cnf - Make sure you have space on your MySQL partition Regards Sushant Chawla On Tue, Jan 18, 2011 at 10:51 AM, Yogesh Kore yogeshk...@gmail.com wrote: May be db files are not as MySQL user. Check owner ship for the files of the table. It should be mysql user ownership. On Tue, Jan 18, 2011 at 6:36 AM, Peng Yu pengyu...@gmail.com wrote: Hi, I run the following command. But I got the following error. I'm not sure what causes the problem. I have seen the same issue before, but it disappeared even I didn't take any action. Could anybody let me know how to fix the problem? mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A mysql select geneName as symbol, name as refSeq, chrom, strand, txStart, txEnd from refFlat group by refSeq having count(*)=1; ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13) -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Innodb table space questions
Folks : two questions: 1. can Innodb create per database table space , not per table table space? 2. can we store table on specific tablespace like Oracle or DB2 when creating table? Many thanks.
Re: Innodb table space questions
reply inline On Mon, Jan 17, 2011 at 9:30 PM, Angela liu yyll2...@yahoo.com wrote: Folks : two questions: 1. can Innodb create per database table space , not per table table space? No. The only available options are creating a global tablespace which can be many files or a file per table. 2. can we store table on specific tablespace like Oracle or DB2 when creating table? You can only choose to store a table in it's own tablespace or in the global one. Many thanks. -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb table space questions
Thanks first, I checked MySQL 5.1 manual, looks like , 'create tablespace' is ok only with NDB and NDBCLUSTER, NOT INNODB And I did not find 'create tablespace' in MySQL 5.5 manual:( looks like MySQL5.5 does not offer 'create tablespace' anymore. --- On Mon, 1/17/11, Eric Bergen eric.ber...@gmail.com wrote: From: Eric Bergen eric.ber...@gmail.com Subject: Re: Innodb table space questions To: Angela liu yyll2...@yahoo.com Cc: mysql@lists.mysql.com Date: Monday, January 17, 2011, 10:09 PM reply inline On Mon, Jan 17, 2011 at 9:30 PM, Angela liu yyll2...@yahoo.com wrote: Folks : two questions: 1. can Innodb create per database table space , not per table table space? No. The only available options are creating a global tablespace which can be many files or a file per table. 2. can we store table on specific tablespace like Oracle or DB2 when creating table? You can only choose to store a table in it's own tablespace or in the global one. Many thanks. -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net
