Can’t get CREATE TABLE (Can’t open file: ‘Tory_Map.MYI’. (errno: 145))

Error Message :
/usr/local/mysql/bin/mysqldump: Can’t get CREATE TABLE for table `Tory_Map` (Can’t open file: ‘Tory_Map.MYI’. (errno: 145))

Solution :
1) Do a show table status as below:
mysql> show table status like ‘Tory_Map’\G
*************************** 1. row ***************************
Name: Tory_Map
Type: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options: NULL
Comment: Can’t open file: ‘Tory_Map.MYI’. (errno: 145)
1 row in set (0.00 sec)

2) and then do a check table as below.
mysql> check table Tory_Map;
+——————+——-+———-+——————————-+
| Table | Op | Msg_type | Msg_text |
+——————+——-+———-+——————————-+
| doc.Tory_Map | check | warning | Table is marked as crashed |
| doc.Tory_Map | check | error | Found 3670180 keys of 3670179 |
| doc.Tory_Map | check | error | Corrupt |
+——————+——-+———-+——————————-+
3 rows in set (20 min 1.65 sec)

3) If the table is marked as crashed then do a repair of that table as below.
mysql> repair table Tory_Map;

+——————+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————+——–+———-+———-+
| doc.Tory_Map | repair | status | OK |
+——————+——–+———-+———-+
1 row in set (1 min 42.77 sec)

and if you do a check table now, the result has to be ok as below.
mysql> check table Tory_Map;
+——————+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————+——-+———-+———-+
| doc.Tory_Map | check | status | OK |
+——————+——-+———-+———-+
1 row in set (6 min 3.33 sec)

Replication resumed, Apparent master shutdown, Failed reading log event

The slave server is getting this error in error log.

081102 0:30:18 [Note] Slave: connected to master ‘replicate@10.0.1.5:3310′,replication resumed in log ‘central3310.000142′ at position 789529654
081102 0:30:18 [Note] Slave: received end packet from server, apparent master shutdown:
081102 0:30:18 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log ‘central3310.000142′ position 789529654

Solution :

The problem has started when a new slave has been configured for the existing setup. The slave server-id is matching exactly with another slave server-id, so the replication is happening for some time and then stopping. Finally one server is getting crashed. Then stop one of the slaves, change the server-id and start the slave to rectify the same.

Error: data file /mysql/data/mysqld/ibdata1 is of a different size

If the Mysql is not starting and the error log consists of the below error after the configuration of a slave with the tar file of master.

081022 6:19:02 InnoDB: Warning: shutting down a not properly started
InnoDB: or created database!
081022 6:19:02 /usr/local/mysql4020/libexec/mysqld: Shutdown Complete

InnoDB: Error: data file /mysql/data/mysqld/ibdata1 is of a different size
InnoDB: 128000 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 65536 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
081022 6:19:04 Can’t init databases
081022 6:19:04 Aborting

Then change the below variable as the same in the master cnf file

innodb_data_file_path =ibdata1:2000M;ibdata2:100M:autoextend
and start the mysqld server.

Lost connection to MySQL server

This type of error may occur in the below scenarios.

1) It indicates network connectivity trouble. If the error message includes “during query” then we have to look at the query and the number of rows being sent.
Then try increasing “net_read_timeout” variable from its default value.
2) It can happen when the client is attempting the initial connection to the server. If your “connect_timeout” value is set to only a few seconds, you can increase the same for resolving this issue.
3) It can occur when using BLOB values that are larger than “max_allowed_packet” and may get “packet too large” as error, then increase the “max_allowed_packet” value to resolve the same.

show procedure status or mysqldump with routines causing mysqld restart in 5.1.40 version

When we take a mysqldump with routines or show procedure status where Db=’dbname’;

The mysqld server got an error like “Lost connection to mysqld server”, when we login into server the will be running and it got restarted.

To fix this, upgrade the db by using mysql_upgrade and it got fixed for me.

MySQL Cluster Query Execution

MySQL Cluster executes the queries depending on the below type of scan’s it does

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let’s say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you’ve controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here’s how each ones works:

1. Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
2. Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
3. Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
4. Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.

MONyog MySQL Monitor and Advisor

MONyog MySQL Monitor and Advisor helps to manage more MySQL servers, tune current MySQL servers and fix problems with their MySQL database applications before becoming serious problems.MONyog pro-actively monitors enterprise database environments and provides expert advice on how even those new to MySQL can tighten security, optimize performance and reduce downtime of their MySQL powered systems.
As a MONyog user you will know in advance if server resources will soon be running short. You can plan a hardware replacement or upgrade in comfortable time and not when it is almost too late (or just plain too late!). And the metrics about the server that MONyog gives you will help to decide what to upgrade: Faster CPU, More RAM, Faster disc systems - or maybe only remove another program from the computer where the MySQL server is running.

MONyog provides a log analysis module, a "query sniffer" and a "processlist watcher" that makes it easy to identify the statements and applications that run slow on the server. This includes various filtering and EXPLAIN options for an efficient workflow when you want to optimize your applications. To use the MONyog mail alert functionality to mail you alerts about a MySQL server you will first need to select that feature when registering a server. You can choose to receive mails, not only when there are problems but also to notify that the system is running smoothly at regular intervals. Further, you can set the minimum time interval between mails sent by MONyog to suit your needs and to avoid cluttering your mailbox. For every MySQL server choose a setting depending on how important the performance of this particular server is.

MONyog MySQL Monitor and Advisor Features
Agent-less architecture
Get up and running in less than 1 minute!
No installation required on servers
Install on a single machine and monitor any no. of servers
No hassles of upgrading agents on all server
Server Optimization
Completely new set of Advisors
Monitoring Tools for MySQL Community and Enterprise Servers
History/Trend Analysis
Quick Performance Tuning and Optimization
Analyze MySQL and OS performance data collected over a period of time
Error Log Monitoring New!
Find Problem SQL
Querying MySQL Proxy
Analyzing General Query Log
Analyzing Slow Query Log
Issuing SHOW PROCESSLIST at regular intervals
Additionally all the above reports can be exported as CSV New!
Multi-platform Support
Monitor MySQL on any platform
Available on Windows and Linux
Support for both 64-bit and 32-bit Linux
Both RPM and Non RPM Package
Security
Detect MySQL hacking attempts
Identify and fix security vulnerabilities
User Friendly GUI
AJAX interface
Simple Descriptions for all performance metrics. Helps you learn MySQL Internals
Fully customizable using java script and MONyog Object Model
Compare unlimited MySQL servers side-by-side on Real time Dashboard
Enterprise dashboard helps identify problems quickly
Monitor Replication
Get alerts when replication lags behind or if slave has stopped
Replication Log details for replication trouble shooting
Miscellaneous Features
Shows Processlist displaying information on running queries as per execution time
Inbuilt web-server
Does not force to install multiple web-servers & language runtimes like other tools
SSH Tunneling Support
Get proactive alerts via email and SNMP Traps New.

Suresh Kuna
MySQL DBA