Re. Cannot find table from the internal data dictionary of InnoDB though the .frm
Dear Sir, I am using Mysql 5 in windows system. I have formatted my system and copy the data directory before formatting the system. After formatting the system I have installed Mysql5 and resorted the data directory with the older one. When I browsing the database then innodb table not shown. I have checked the log file then following error shows. I have tried to recover using .frm file but it doesn't work. 040101 1:11:52040101 1:11:52 [ERROR] Cannot find table atc/circle from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html how you can resolve the problem. Pls help Prakash Makwana 303-304, Saumya Building, 5 Kalpana Society, Navarangpura, Ahmedabad - 380 009 Gujarat, INDIA. PH :- +91-79-26448181,30006547 M :- +91-9374021782 Url :- www.krishtechnologies.net
Re: MySQL University session on March 12: MySQL and ZFS
On Mon, Mar 9, 2009 at 9:10 AM, Stefan Hinz stefan.h...@sun.com wrote: MySQL and ZFS http://forge.mysql.com/wiki/MySQL_and_ZFS This Thursday (March 12th, 14:00 UTC), Martin MC Brown will give a MySQL University session on MySQL and ZFS. MC works on the MySQL Documentation Team and has been involved with quite a few Solaris things, for example porting MySQL to openSolaris. MC has recently given MySQL University talks on developing MySQL on Solaris and about using DTrace with MySQL. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown March 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 BST / 15:00 CET / 17:00 MDT (Moscow) How to Use Charsets and Collations Properly Susanne Ebrecht A quick reminder, in the U.S. we're in daylight savings time now, so those times are 7AM PDT / 8AM MDT / 9AM CDT / 10AM EDT. Ultimately though, you can't go wrong if you base it on 14:00 UTC :-) -- Regards... Todd All truth passes through three stages. First, it is ridiculed. Second, it is violently opposed. Third, it is accepted as being self-evident. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
quick question on mysql memory usage
Hi, My understanding is that the memory utilization of mysql can be calculated roughly using the formula like: (All global memory related server variables + max_connections * session memory related server variables) As I noticed that most global variables like key_buffer_size, innodb_buffer_szie will not return back the memory to OS since they are using (mt)malloc/free, My question is: will mysql return the memory to the OS after closing some connections? Such as, when one connection executing a query which located sort buffer, and created tempory table, after complete the query or close the query, will the memory used by this query release to OS? Thanks in advance for your info. Regards, jenny
Re: Select query locks tables in Innodb
Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since that narrows down the data set better. You can run SHOW INDEX FROM journal_entry_master to see the distribution of your data in the index. The cardinality column will indicate the uniqueness of your data. The higher the number, the more unique values. Brent 2009/3/4 Carl c...@etrak-plus.com: Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone
Re: Select query locks tables in Innodb
The nice thing about InnnoDB is that it won't have to access the data portion of the file if it doesn't have to. So if all the information you are retrieving is contained in an index, it only accesses the index to get the information it needs. The data portion is never access, and thus never locked. Something like this is probably going on. All the information it needs for the 100,000 records is contained in the index, the the data portion is never accessed until it needs to retrieve the 60,000 records. That's a simplistic overview of what could be going on. But it sounds like your issue has been resolved. Interesting, your temp1 attached file shows mysql switched from using the org_date index to the organization index. Brent Baisley 2009/3/12 Carl c...@etrak-plus.com: Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since that
Re: Query from HPM book to limit rows scanned doesn't appear to work as described
Baron Schwartz wrote: Hi! On Wed, Mar 11, 2009 at 8:20 PM, David M. Karr davidmichaelk...@gmail.com wrote: Using 5.0.67-0ubuntu6 on Ubuntu 8.10. I'm going through the High Performance MySQL book. I was reading section 4.4.1.8, titled MIN() and MAX(). The point of this is that MySQL doesn't optimize MIN()/MAX() very well, but it showed a supposed workaround for this. The first sample query was: SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE'; As described, this does a table scan, looking at 200 rows. The alternative was this: SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENELOPE' LIMIT 1; Which supposedly would not do a full table scan, and it seems logical. The explain output for this is the following (tabs replaced with colon): id:select_type:table:type:possible_keys:key:key_len:ref:rows:Extra 1:SIMPLE:actor:ALL:null:null:null:null:200:Using where This explain output is identical to the output for the previous query, so this workaround didn't appear to help any. But EXPLAIN is only a prediction. If you look at the changes in the Handler status variables, you'll see the second one reads fewer rows. Ok, I think I get it. I first changed both of my queries to add sql_no_cache because without that, the Handler_read_rnd_next variable was zero in both cases. Before running each query, I ran flush status, then the query, then show session status like 'Handler%'. The first one had a value of 207 for Handler_read_rnd_next and the second one had a value of 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can I force a View to use a particular index when the view is executed in a Select?
I have a view which is a 3 table join on a compound index. I have two indexes: Index1: Product_Code, Store_Id, Date_Sold and Index2: Date_Sold,Store_Id,Product_Code If I execute a select like: select * from MyView where product_code=123; it returns the results in 3 seconds from 15 million rows because it uses index 1. But if I execute select * from MyView where Date_Sold=2009-03-10; for some reason it is still trying to use Index1 and it takes forever to return the results because it is doing a full table scan to join the 3 tables. Is there a way to force the view to use Index2? Unfortunately select * from MyView use index(Index2) where Date_Sold=2009-03-10; throws an error. I think the only way around it is to create another view and put the Use Index(Index2) into the view itself and then call the appropriate view depending on the Where clause of the Select statement. Does this make sense? Or is there a better way? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
Do I jsut need to monitor better and manually add it should the log and pos change? -Bryan On Wed, Mar 11, 2009 at 7:05 PM, Baron Schwartz ba...@xaprb.com wrote: That's deprecated too :-) On Wed, Mar 11, 2009 at 9:17 PM, Cui Shijun rancp...@gmail.com wrote: hi, #2. try adding the information of master into my.cnf then restart the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
When you start up replication the data should be recorded in the master.info in your data dir. [r...@someserver ~]# cat /var/lib/mysqldata/master.info 14 MASTERHOST-bin.01 MASTERPOS 192.168.0.2 REPLICATION_USER REPLICATION_PASSWORD 3306 60 0 I am not familiar with any setups where the master.info would not be created, and I am too lazy too look for a setting that would lead to such an silly/annoying state. On Thu, Mar 12, 2009 at 10:03 AM, Bryan Irvine sparcta...@gmail.com wrote: Do I jsut need to monitor better and manually add it should the log and pos change? -Bryan On Wed, Mar 11, 2009 at 7:05 PM, Baron Schwartz ba...@xaprb.com wrote: That's deprecated too :-) On Wed, Mar 11, 2009 at 9:17 PM, Cui Shijun rancp...@gmail.com wrote: hi, #2. try adding the information of master into my.cnf then restart the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.com wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
The information is there and the replica can connect on a reboot but only if that information has not changed correct? e.g. I reboot the slave, and on the master just run reset master; the bin.01 could change to bin.02 and the pos change from 98 to 15443 or some such. If I do that do I then need to re-run the set master to statements when the slave comes back up? or can that be handled automatically? -Bryan On Thu, Mar 12, 2009 at 10:26 AM, Rob Wultsch wult...@gmail.com wrote: When you start up replication the data should be recorded in the master.info in your data dir. [r...@someserver ~]# cat /var/lib/mysqldata/master.info 14 MASTERHOST-bin.01 MASTERPOS 192.168.0.2 REPLICATION_USER REPLICATION_PASSWORD 3306 60 0 I am not familiar with any setups where the master.info would not be created, and I am too lazy too look for a setting that would lead to such an silly/annoying state. On Thu, Mar 12, 2009 at 10:03 AM, Bryan Irvine sparcta...@gmail.com wrote: Do I jsut need to monitor better and manually add it should the log and pos change? -Bryan On Wed, Mar 11, 2009 at 7:05 PM, Baron Schwartz ba...@xaprb.com wrote: That's deprecated too :-) On Wed, Mar 11, 2009 at 9:17 PM, Cui Shijun rancp...@gmail.com wrote: hi, #2. try adding the information of master into my.cnf then restart the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.com wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
:S Hi Bryan Actually changing the slave setup after a reboot is a very bad idea, you will have inconsistent data on your slave because every transaction made from when you rebooted the server to when you reset the position will not be copied from the master. Thats the point of the position information, that MySQL can know what data has been replicated and what data has not been. My understanding is that you should never have to reset the binlog position info after initial setup unless you are reinitialising your slave with a new copy of the data from the master because the slave broke for whatever reason. I have several DBs copied to slaves and have never had to reset anything following a reboot of the slave (or master for that matter). If you are finding replication stops after a reboot then I think you have a serious problem, but don't know what that might be. Post some more info and someone on the list can probably help, thanks Andy. Quoting Bryan Irvine sparcta...@gmail.com: The information is there and the replica can connect on a reboot but only if that information has not changed correct? e.g. I reboot the slave, and on the master just run reset master; the bin.01 could change to bin.02 and the pos change from 98 to 15443 or some such. If I do that do I then need to re-run the set master to statements when the slave comes back up? or can that be handled automatically? -Bryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
BTW there is a list specially dedicated to replication configurations: http://lists.mysql.com/replication -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query from HPM book to limit rows scanned doesn't appear to work as described
But EXPLAIN is only a prediction. If you look at the changes in the Handler status variables, you'll see the second one reads fewer rows. Ok, I think I get it. I first changed both of my queries to add sql_no_cache because without that, the Handler_read_rnd_next variable was zero in both cases. Before running each query, I ran flush status, then the query, then show session status like 'Handler%'. The first one had a value of 207 for Handler_read_rnd_next and the second one had a value of 1. Yes, that's it exactly. The LIMIT stops the work as soon as you find the first row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
If you are finding replication stops after a reboot then I think you have a serious problem, but don't know what that might be. Post some more info and someone on the list can probably help, just a 'skip-slave-start' in my.cnf does the trick. 2 Is there a way to get a slave to automatically re-connect as the slave without having to know the masters binlog and position? Each time it reboots I have to manually add it back in with the change master to ... command. if you do NOT have 'skip-slave-start' in the my.cnf replication starts automatically, what is the output of SHOW SLAVE STATUS\G after a reboot? if you do not have an empty set and SQL and IO thread are NOT running you probably have 'skip-slave-start' in the config file. Claudio
