Re: Missing database file names
In the last episode (Jun 02), Jesse F. Hughes said: After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? table definition files are the .frm files; MISAM compressed data files are .MYI files. Unfortunately, you're missing the .MYD files in that list, which don't have a header (sometimes 'file' thinks they're dbase format). You may be in luck, though. The filenames are the inode numbers of the original files, and it looks like they're in sequential order, so the tables were probably created all at once, one after the other. File #15901616 (if it exists) is probably the .MYD file that corresponds to #15901614 (.frm) and #15901615 (.MYI). The numerical order of the files should match the order the mythtv setup script creates its tables. If they aren't in order for some reason, then you can rename the groups of files into a.frm, a.MYI, a.MYD, b.frm, b.MYI, b.MYD, etc, then run show create table a etc to determine the table layout and match them up to known mythtv tables. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Slow when using sub-query
The short answer is that the optimizer is amazingly stupid about subqueries, and it assumes that they are dependent even when they're not - that subquery gets executed for every row in your main query. The fastest way to do this, would probably be to run your subquery, have your code assemble the appropriate IN clause, and then run your main query. The long answer is that there's a rather good Advanced Tuning course that addresses all this and more, as does Baron C° 's excellent MySQL book. On Wed, Jun 2, 2010 at 10:05 PM, Jerry Schwartz je...@gii.co.jp wrote: I've heard that sub-queries aren't well-optimized, but this case seems ridiculous. First, a little setup: SELECT pub_id FROM pub WHERE pub_code = 'GD' INTO @P; === Inner Query by Itself === us-gii SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1; +--+ | prod_pub_prod_id | +--+ | NULL | | GDAE0106ICR | | GDME0002TR | | GDME0023IAR | | GDME0059IAR | +--+ 5 rows in set (0.05 sec) us-gii EXPLAIN - SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1 - \G *** 1. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 1543 Extra: Using where; Using temporary; Using filesort === Outer Query without Inner Query === us-gii SELECT prod_num FROM prod - WHERE pub_id = @P - AND prod_pub_prod_id IN - ( - NULL, - 'GDAE0106ICR', - 'GDME0002TR', - 'GDME0023IAR', - 'GDME0059IAR' - ) - ; +--+ | prod_num | +--+ |83298 | |85092 | |88728 | |97231 | |97235 | |98368 | | 107693 | | 112461 | +--+ 8 rows in set (0.01 sec) us-gii EXPLAIN - SELECT prod_num FROM prod - WHERE pub_id = @P - AND prod_pub_prod_id IN - ( - NULL, - 'GDAE0106ICR', - 'GDME0002TR', - 'GDME0023IAR', - 'GDME0059IAR' - ) - \G *** 1. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: prod_pub_prod_id,pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 1543 Extra: Using where === Outer Query with Sub-query === us-gii EXPLAIN - SELECT prod_num FROM prod - WHERE pub_id = @P - AND prod_pub_prod_id IN - (SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1) - \G *** 1. row *** id: 1 select_type: PRIMARY table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 1543 Extra: Using where *** 2. row *** id: 2 select_type: DEPENDENT SUBQUERY table: prod type: index possible_keys: pub_id,pub_id_2 key: prod_pub_prod_id key_len: 768 ref: NULL rows: 72 Extra: Using where; Using filesort I don't know how long the Outer Query with Sub-query would take: I killed it after several minutes. I'm guessing that it has to do with the fact that the inner query is dependent, but why is that happening? === Rewritten as Join === us-gii SELECT prod_num FROM prod JOIN - (SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1) AS x - ON prod.prod_pub_prod_id = x.prod_pub_prod_id - WHERE prod.pub_id = @P - AND prod.prod_discont = 0; +--+ | prod_num | +--+ |98368 | | 107693 | |83298 | |85092 | |88728 | |97231 | |97235 | | 112461 | +--+ 8 rows in set (0.05 sec) us-gii EXPLAIN - SELECT prod_num FROM prod JOIN - (SELECT prod_pub_prod_id FROM prod - WHERE pub_id = @P - AND prod_discont = 0 - GROUP BY prod_pub_prod_id - HAVING COUNT(*) 1) AS x - ON prod.prod_pub_prod_id = x.prod_pub_prod_id - WHERE prod.pub_id = @P - AND prod.prod_discont = 0 - \G *** 1. row *** id: 1 select_type: PRIMARY table: derived2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 Extra: *** 2. row *** id: 1
RE: Slow when using sub-query
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, June 03, 2010 6:52 AM To: je...@gii.co.jp Cc: mysql@lists.mysql.com Subject: Re: Slow when using sub-query The short answer is that the optimizer is amazingly stupid about subqueries, and it assumes that they are dependent even when they're not - that subquery gets executed for every row in your main query. [JS] That's what I figured was happening -- either that, or my CPU was on strike for higher voltages. The fastest way to do this, would probably be to run your subquery, have your code assemble the appropriate IN clause, and then run your main query. [JS] A lot of what I do is one-off things, and I usually wind up using the CLI. Rewriting the whole business as a JOIN to a derived table worked very well. The long answer is that there's a rather good Advanced Tuning course that addresses all this and more, as does Baron C° 's excellent MySQL book. [JS] No doubt. Our database is so small, and my duties are so diverse, that tuning only gets my attention when a problem interferes with other people's productivity. Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help needed on query on multiple tables
Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help needed on query on multiple tables
How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help needed on query on multiple tables
Thanks! That did it perfectly! Michael On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Help needed on query on multiple tables
I am glad that I was able to help someone finally :) There may be other ways to do this, but that was what first came to mind. I would maybe run an explain on that query to ensure that it is using indexes. Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:55 AM To: Steven Staples Cc: 'MySql' Subject: Re: Help needed on query on multiple tables Thanks! That did it perfectly! Michael On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Geting current user pasword.
Hello, I need to get the user and password from the current session. I found the user() function, wich gets the username, is there anything like that to get the password ? Thx Guillermo __ Información de ESET NOD32 Antivirus, versión de la base de firmas de virus 5170 (20100603) __ ESET NOD32 Antivirus ha comprobado este mensaje. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Geting current user pasword.
In the last episode (Jun 03), Guillermo said: Hello, I need to get the user and password from the current session. I found the user() function, wich gets the username, is there anything like that to get the password ? Nope. I don't think the server even sees the password during authentication. It's all done with hashed versions. You'll probably have to cache the original password used to make the connection, if you need to use it again later. http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Geting current user pasword.
It goes strongly against best practices to store user passwords in the clear. If you are storing any personal information at all, information as trivial as the association as a name and an email address, there are privacy regulations which prohibit the practice. For some trivial app, with user buy-in, it *can* be done but there is rarely a good reason for it in my experience. - michael dykman On Thu, Jun 3, 2010 at 1:12 PM, Guillermo srguiller...@yahoo.com.ar wrote: Hello, I need to get the user and password from the current session. I found the user() function, wich gets the username, is there anything like that to get the password ? Thx Guillermo __ Información de ESET NOD32 Antivirus, versión de la base de firmas de virus 5170 (20100603) __ ESET NOD32 Antivirus ha comprobado este mensaje. http://www.eset.com -- 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: Geting current user pasword.
You mean the mysql password of the user? No, you can't get that, even the server can't get it, at least not the clear-text version. When you create/change your password an encrypted version is stored in the mysql.user table along with the hostname. If you have SELECT permission on that table, you can get the encrypted version. You could use that for some sort of separate authentication by encrypting a user-supplied password and comparing the 2 encrypted strings but you can't get the actual password. In other words, if the password is 'cat' you only see '*FD98809C60DD51B6B380DB17B72F9A9E2FE1928F in mysql.user. You could have code that checked a user-supplied password, like: if password(@pwd) = '*FD98809C60DD51B6B380DB17B72F9A9E2FE1928F' but that's it. Jim On Thu, Jun 3, 2010 at 12:12 PM, Guillermo srguiller...@yahoo.com.ar wrote: Hello, I need to get the user and password from the current session. I found the user() function, wich gets the username, is there anything like that to get the password ? Thx Guillermo __ Información de ESET NOD32 Antivirus, versión de la base de firmas de virus 5170 (20100603) __ ESET NOD32 Antivirus ha comprobado este mensaje. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Missing database file names
Dan Nelson dnel...@allantgroup.com writes: In the last episode (Jun 02), Jesse F. Hughes said: After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? table definition files are the .frm files; MISAM compressed data files are .MYI files. Unfortunately, you're missing the .MYD files in that list, which don't have a header (sometimes 'file' thinks they're dbase format). You may be in luck, though. The filenames are the inode numbers of the original files, and it looks like they're in sequential order, so the tables were probably created all at once, one after the other. File #15901616 (if it exists) is probably the .MYD file that corresponds to #15901614 (.frm) and #15901615 (.MYI). The numerical order of the files should match the order the mythtv setup script creates its tables. If they aren't in order for some reason, then you can rename the groups of files into a.frm, a.MYI, a.MYD, b.frm, b.MYI, b.MYD, etc, then run show create table a etc to determine the table layout and match them up to known mythtv tables. Brilliant! I'll look into it as soon as I can. And I'll be sure to complain if it doesn't work. Thanks. -- [I want to] stand at the pinnacle of human achievement with no one else in all of history even close, no human being having faced what I have--and survived. Because when all is said and done, make no mistake, the simple truth is, I am better. --James S. Harris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
replication of RENAME TABLE d1.t TO d2.t
i've a need to change the name of a database and haven't done this before in our live server. while the tables are myisam, i'm not inclined to rename the dirname of d1's datafiles because i'd rather not interrupt service for other databases and i'd prefer if the renaming would replicate. can i do it like this: in the application, kick users of d1 out create database d2 foreach t in d1: RENAME TABLE d1.t TO d2.t tell user to use d2 ? tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: replication of RENAME TABLE d1.t TO d2.t
Other way around. Assuming the aplcation is expecting your table to be named 'db.table1' and your replacement is newdb.table1 you can do the following. create a database for hold archives CREATE olddb; then RENAME TABLE db.table1 to olddb.table1, -- back up the current table newdb.table1 to db.table1 -- rename the new table as the old one You can have multiple oldtable TO newtable section, comma-separated. MySQL guarantees that this is an atomic action which is to say that, for all intents and purposed, all the table renames within a single statement occur simultaneously or not at all. I have swapped out quite larger database on the fly in production like this with no reported problems. - michael On Thu, Jun 3, 2010 at 5:01 PM, Tom Worster f...@thefsb.org wrote: i've a need to change the name of a database and haven't done this before in our live server. while the tables are myisam, i'm not inclined to rename the dirname of d1's datafiles because i'd rather not interrupt service for other databases and i'd prefer if the renaming would replicate. can i do it like this: in the application, kick users of d1 out create database d2 foreach t in d1: RENAME TABLE d1.t TO d2.t tell user to use d2 ? tom -- 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: Missing database file names
Dan Nelson dnel...@allantgroup.com writes: In the last episode (Jun 02), Jesse F. Hughes said: After a recent hard drive kerfluffle and the results of fsck, I'm left with a slew of jumbled database files. The file command can tell me the file types, like so: #15901614: MySQL table definition file Version 10 #15901615: MySQL MISAM compressed data file Version 1 #15901617: MySQL table definition file Version 10 #15901618: MySQL MISAM compressed data file Version 1 #15901620: MySQL table definition file Version 10 #15901621: MySQL MISAM compressed data file Version 1 These files are, I'm pretty sure, from my mythtv database. I'm rebuilding my box and it would be nice if I can keep the mythtv database the same as before, but how can I tell which file is which? table definition files are the .frm files; MISAM compressed data files are .MYI files. Unfortunately, you're missing the .MYD files in that list, which don't have a header (sometimes 'file' thinks they're dbase format). You may be in luck, though. The filenames are the inode numbers of the original files, and it looks like they're in sequential order, so the tables were probably created all at once, one after the other. File #15901616 (if it exists) is probably the .MYD file that corresponds to #15901614 (.frm) and #15901615 (.MYI). The numerical order of the files should match the order the mythtv setup script creates its tables. If they aren't in order for some reason, then you can rename the groups of files into a.frm, a.MYI, a.MYD, b.frm, b.MYI, b.MYD, etc, then run show create table a etc to determine the table layout and match them up to known mythtv tables. Ouch. I've found the time to look into the problem a little more closely. I do have a lot of DBase3 files listed, with varying number of records. Unfortunately, they *aren't* in sequential order with the .MYI and .frm files. I probably used a MySQL dump file to create them, so I suppose that the previous files were unlinked and new ones created. Except that the dbase files I see have *lower* inode numbers than the .MYIs and .frms! Is there some way to match the .MYI to its corresponding .MYD? I have only 33 to 47 .MYD files (judging from the output of file and their contiguity), but 74 .MYI files. Thanks. -- Jesse F. Hughes This post marks the end of an era in the world of mathematics. -- James S. Harris and the demise of Galois theory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
