Disappearing .frm files ?
I posted a few days ago, but didn't get any responses. I'm hopeful someone has seen this, and can offer advice. We recently converted some tables from MyISAM to InnoDB because the need had arisen for transactional support. Things have been fine until recently, when the .frm files for these tables mysteriously disappeared from the mysql data directory (even though the underlying data for the tables was still intact in the separate InnoDB data file). We replaced the .frm files from our prod database (we have identical dev and prod) - and all was well again - but I am curious as to how these files could disappear? I would think if someone had errantly dropped the tables, the data would be gone also. Any advice or guidance would be greatly appreciated. Thanks, Geoff Thompson Avaion Support [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.avaion.com http://www.avaion.com/
InnoDB .frm files disappearing?
We have been using MyISAM tables with MySQL merrily for about 18 months. Recently we upgraded to MySQL 4.1, and even more recently, we converted some of our MyISAM tables (which needed transactional support) to InnoDB. After some configuration issues - the worst of which was the need to use a symbolic link to redirect the data directories for MySQL to another disk partition (our /usr/lib directory did not have sufficient space allocated to support the data stores), we got things working, and they have been working for about a month. Today, however, we lost all of the InnoDB tables in one of our databases. Upon examining the MySQL data directory, we discovered that the .frm files for these three tables were gone (although the ibdata1 file was still there). These same tables were still working fine in another of our databases (dev1 vs. dev2 - both of which share the same ibdata1 file), so we copied the .frm files from that database's data directory over, and once we did this, the tables re-appeared, complete with the correct data that had been originally loaded into these tables prior to their disappearing. So, it appears that while the data file was fine, the .frm files were somehow deleted. Anyone seen this happen before? Any insights would be very much appreciated! Regards, Geoff Thompson Avaion Support [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.avaion.com http://www.avaion.com/
RE: Alias query problem in 4.1.7?
Michael Stassen wrote: You would increase your chances of getting a useful answer if you would take the time to reformat your query to be easily readable, as I have done for you below. SELECT Product.Product, AssignedToAgent.AgentName AS AssignedTo, sum(Inquiries) AS Inquiries FROM Inquiry INNER JOIN Product ON Inquiry.ProductKey = Product.ProductKey INNER JOIN Agent AS AssignedToAgent ON Inquiry.AssignedToKey = AssignedToAgent.AgentKey INNER JOIN DateDim AS DateOpen ON Inquiry.DateOpenKey = DateOpen.DateDimKey WHERE DateOpen.Year = '2003' GROUP BY Product.Product, AssignedToAgent.AssignedTo; Thanks for the tip. You don't say, but I expect you get ERROR 1054 (42S22): Unknown column 'AssignedToAgent.AssignedTo' in 'group statement' That is correct. Table AssignedToAgent (AKA Agent) does not have a column named AssignedTo. It has a column named AgentName. AssignedTo is an alias which means AssignedToAgent.AgentName. I would expect AssignedToAgent.AgentName and AssignedTo to work in the GROUP BY. I wouldn't have expected AssignedToAgent.AssignedTo to work, but I have confirmed that it does work in 4.0.20. It appears that if I take the table alias AssignedToAgent out of the GROUP BY clause (leaving just the column alias AssignedTo), the query will then work in 4.1.7 - even though the table alias does not present a problem in 3.2.3. Any ideas why? As I said, I'm surprised it ever worked. In any case, having made AssignedTo an alias for the column in question, why not simply use that alias instead of trying to qualify it with an unnecessary table name? I will. As I mentioned earlier, I did determine that the query would run by removing the table name. I just wondered why something that did work in one release ceased working in another. I generally prefer to 'know why' vs. just changing code when I see something like this. FYI - the query is generated through a reporting engine that we have written, which is why the 'heavy-handed' use of table names that are seemingly unnecessary. Depending on the column names selected by the user in the reporting tool, the query engine sometimes builds queries with ambiguous names without the table prefix. Having said that, the alias name will be sufficiently un-ambiguous, so it will definitely work for our purposes. Since it does work in 4.0.20, and I cannot find the change documented, I've gone ahead and submitted this as a bug report http://bugs.mysql.com/bug.php?id=6818, though I would not be surprised to hear that this was intentional. Thanks - I will keep tabs on the response. Geoff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alias query problem in 4.1.7?
I have an interesting problem that I cannot find any clues to in the MySQL documentation. The following query works in 3.2.3, but does not work in 4.1.7: SELECT `Product`.`Product`, `AssignedToAgent`.`AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` AS `AssignedToAgent` ON `Inquiry`.`AssignedToKey` = `AssignedToAgent`.`AgentKey` INNER JOIN `DateDim` AS `DateOpen` ON `Inquiry`.`DateOpenKey` = `DateOpen`.`DateDimKey` WHERE `DateOpen`.`Year` = '2003' GROUP BY `Product`.`Product`, `AssignedToAgent`.`AssignedTo`; It appears that if I take the table alias AssignedToAgent out of the GROUP BY clause (leaving just the column alias AssignedTo), the query will then work in 4.1.7 - even though the table alias does not present a problem in 3.2.3. Any ideas why? Any help would be greatly appreciated. Geoff Thompson Avaion Support [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] http://www.avaion.com http://www.avaion.com/
RE: Alias query problem in 4.1.7?
Rhino: Thanks for the feedback. The query is generated by a home-grown tool, which allows the users to build ad-hoc reports. For this reason, and because there are aliases in use (in this case, two agents are joined to the underlying table), it would be possible for a similar query that would group by both AgentCreatedBy and AgentAssignedTo - which are both the same column in the underlying table, joined to by two separate foreign keys. So, that is the reason for all the 'extra' table qualifiers. I will try your suggestion of using the column name vs. the alias name in the GROUP BY clause, and see if that works. Interestingly, however, if I remove the table qualifier, and just do the group by on the aliased column, it works, as such: GROUP BY `Product`, `AssignedTo` Geoff Thompson Avaion Support [EMAIL PROTECTED] http://www.avaion.com -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 10:26 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Alias query problem in 4.1.7? Try changing your GROUP BY to use the column name of the second column in the SELECT, not the alias of the second column name, i.e. GROUP BY `Product`.`Product`, `AssignedToAgent`.`AgentName` That should work on any version of MySQL. I don't think you're allowed to use aliases in a GROUP BY, only actual column names. Then again, I am mostly a DB2 person and the MySQL dialect of SQL may allow aliases in a GROUP BY for all I know. I'm suprised that the alias worked on 3.2.3: are you sure you have reproduced the exact query that works on 3.2.3? I'm afraid I don't have either a 3.2.3 or 4.1.7 system so I can't try any of this for myself to see. By the way, did you realize that your query is substantially longer than it needs to be? You really only need to qualify column names with table names if you are doing a join of two or more tables and even then, you only need to qualify column names that occur in two or more of the tables read by the query. That would also eliminate the need for you to write aliases for some of your table names at all, further shortening the query. In your query, it appears that only the 'ProductKey' column occurs in more than one of the tables so your query could be as short as this: SELECT `Product`, `AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` ON `AssignedToKey` = `AgentKey` INNER JOIN `DateDim` ON `DateOpenKey` = `DateDimKey` WHERE `Year` = '2003' GROUP BY `Product`, `AssignedTo`; Then again, perhaps it is your shop standard to fully qualify all column names in queries; if so, you should follow your shop standard ;-) More likely, you are probably using some sort of query generating tool in which case you probably don't have a choice in the matter. Rhino - Original Message - From: Geoffrey R. Thompson [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 12:11 AM Subject: Alias query problem in 4.1.7? I have an interesting problem that I cannot find any clues to in the MySQL documentation. The following query works in 3.2.3, but does not work in 4.1.7: SELECT `Product`.`Product`, `AssignedToAgent`.`AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` AS `AssignedToAgent` ON `Inquiry`.`AssignedToKey` = `AssignedToAgent`.`AgentKey` INNER JOIN `DateDim` AS `DateOpen` ON `Inquiry`.`DateOpenKey` = `DateOpen`.`DateDimKey` WHERE `DateOpen`.`Year` = '2003' GROUP BY `Product`.`Product`, `AssignedToAgent`.`AssignedTo`; It appears that if I take the table alias AssignedToAgent out of the GROUP BY clause (leaving just the column alias AssignedTo), the query will then work in 4.1.7 - even though the table alias does not present a problem in 3.2.3. Any ideas why? Any help would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
