Mysql error, Can’t create/write to file ‘/var/tmp1/#sql_e2f_0.MYI’ (Errcode: 2)]

Sometimes you may face the following error

Can’t create/write to file ‘/var/tmp1/#sql_e2f_0.MYI’ (Errcode: 2)]

This is because of big temporary tables created while executing a query due to which the tmp folder is full and unable to write the data there. The solution is to increase the size of tmp directory or change its path.

Try the following:

# mkdir /var/lib/mysql/tmp
# chown mysql:mysql /var/lib/mysql/tmp

Add the following line into the [mysqld] section:

tmpdir = /var/lib/mysql/tmp

 

Restart the service.

# /etc/init.d/mysql restart

Enable slow query log in a cPanel/Linux server

To enable slow query log in cpanel server, you can use following steps.

Add following to following to /etc/my.cnf file on server.

# vi /etc/my.cnf

log-slow-queries=/var/lib/mysql/slow.log

After that, do the following commands to create the file with the right ownership and file permissions

# touch /var/lib/mysql/slow.log

# chmod 660 /var/lib/mysql/slow.log

# chown mysql:mysql /var/lib/mysql/slow.log

Restart the mysql server and check the logs in /var/lib/mysql/slow.log.

Mysql Error: Can’t create/write to file (Errcode: 17)

Sometimes you may face the error for a mysql driven website. All of sudden a site has started to display this error:

Can’t create/write to file ‘#sql_5a0_0.MYD’ (Errcode: 17)

Its MySQL error, MySQL is saying the it cannot write to that directory. Its most probably related file exists in that directory. The /tmp is a folder used for temporary files and folders of all users on a server. In this case you need to empty the /tmp folder.

On shell

# cd /tmp

# rm -rf *.MYD

# service mysql restart

Its done!

If you still have problem with this, better check the disk space on your server and delete unwanted files, folders and log files.

How to optimize the MySQL using scripts

To optimize the mysql, you can use either of the scripts provided below.

The first one is MysqlTuner

The MysqlTuner is a Perl script that analyzes your MySQL performance and, based on the statistics it gathers, gives recommendations which variables you should adjust in order to increase performance. That way, you can tune your my.cnf file to tease out the last bit of performance from your MySQL server and make it work more efficiently.

You can download the MySQLTuner script by

# wget http://mysqltuner.com/mysqltuner.pl

# chmod 755 mysqltuner.pl

Once you make the .pl script executable

# ./mysqltuner.pl

It will show output as below (its sample output)

>> MySQLTuner 1.0.0 – Major Hayden

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with ‘–help’ for additional options and output filtering

——– General Statistics ————————————————–

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.81-community

[!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM

——– Storage Engine Statistics ——————————————-

[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[–] Data in MyISAM tables: 374M (Tables: 3987)

[–] Data in InnoDB tables: 3M (Tables: 98)

[–] Data in MEMORY tables: 0B (Tables: 2)

[!!] Total fragmented tables: 201

——– Performance Metrics ————————————————-

[–] Up for: 14d 0h 24m 52s (12M q [10.043 qps], 491K conn, TX: 3B, RX: 1B)

[–] Reads / Writes: 86% / 14%

[–] Total buffers: 442.0M global + 12.3M per thread (100 max threads)

[OK] Maximum possible memory usage: 1.6G (41% of installed RAM)

[OK] Slow queries: 0% (91/12M)

[!!] Highest connection usage: 100% (101/100)

[OK] Key buffer size / total MyISAM indexes: 384.0M/111.0M

[OK] Key buffer hit rate: 100.0% (3B cached / 1M reads)

[OK] Query cache efficiency: 75.8% (7M cached / 9M selects)

[!!] Query cache prunes per day: 33741

[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 1M sorts)

[!!] Joins performed without indexes: 53627

[!!] Temporary tables created on disk: 35% (587K on disk / 1M total)

[OK] Thread cache hit rate: 99% (2K created / 491K connections)

[!!] Table cache hit rate: 1% (512 open / 38K opened)

[OK] Open file limit used: 24% (1K/4K)

[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

[OK] InnoDB data size / buffer pool: 3.7M/8.0M

——– Recommendations —————————————————–

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Enable the slow query log to troubleshoot bad queries

Reduce or eliminate persistent connections to reduce connection usage

Adjust your join queries to always utilize indexes

When making adjustments, make tmp_table_size/max_heap_table_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

max_connections (> 100)

wait_timeout (< 28800)

interactive_timeout (< 28800) query_cache_size (> 32M)

join_buffer_size (> 128.0K, or always use indexes with joins)

tmp_table_size (> 32M)

max_heap_table_size (> 16M)

table_cache (> 512)

Check the “Recommendations” and edit the my.cnf accordingly.

You can use another script as below.

Its MySQL Performance Tuning Primer Script which can be downloadd from http://www.day32.com/MySQL/

# wget http://www.day32.com/MySQL/tuning-primer.sh

# chmod 755 tuning-primer.sh

# ./tuning-primer.sh

It will provide sample output as below.

— MYSQL PERFORMANCE TUNING PRIMER —

– By: Matthew Montgomery –

MySQL Version 5.0.81-community i686

Uptime = 30 days 0 hrs 20 min 55 sec

Avg. qps = 10

Total Questions = 12160398

Threads Connected = 3

Server has been running for over 65hrs.

It should be safe to follow these recommendations

To find out more information on how each of these

runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html

for info about MySQL’s Enterprise Monitoring and Advisory Service

SLOW QUERIES

The slow query log is NOT enabled.

Current long_query_time = 10 sec.

You have 90 out of 12160419 that take longer than 10 sec. to complete

Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG

The binary update log is NOT enabled.

You will not be able to do point in time recovery

See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS

Current thread_cache_size = 8

Current threads_cached = 5

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine

MAX CONNECTIONS

Current max_connections = 100

Current threads_connected = 3

Historic max_used_connections = 101

The number of used connections is 101% of the configured maximum.

You should raise max_connections

MEMORY USAGE

Max Memory Ever Allocated : 1.63 G

Configured Max Per-thread Buffers : 1.20 G

Configured Max Global Buffers : 426 M

Configured Max Memory Limit : 1.61 G

Physical Memory : 3.96 G

Max memory limit seem to be within acceptable norms

KEY BUFFER

Current MyISAM index space = 108 M

Current key_buffer_size = 384 M

Key cache miss rate is 1 : 3157

Key buffer free ratio = 85 %

Your key_buffer_size seems to be fine

QUERY CACHE

Query cache is enabled

Current query_cache_size = 32 M

Current query_cache_used = 18 M

Current query_cache_limit = 1 M

Current Query cache Memory fill ratio = 59.10 %

Current query_cache_min_res_unit = 4 K

MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS

Current sort_buffer_size = 2 M

Current read_rnd_buffer_size = 8 M

Sort buffer seems to be fine

JOINS

Current join_buffer_size = 132.00 K

You have had 53627 queries where a join could not use an index properly

You should enable “log-queries-not-using-indexes”

Then look for non indexed joins in the slow query log.

If you are unable to optimize your queries you may want to increase your

join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when

ANY joins not using indexes are found.

OPEN FILES LIMIT

Current open_files_limit = 4096 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine

TABLE CACHE

Current table_cache value = 512 tables

You have a total of 4104 tables

You have 512 open tables.

Current table_cache hit rate is 1%, while 100% of your table cache is in use

You should probably increase your table_cache

TEMP TABLES

Current max_heap_table_size = 16 M

Current tmp_table_size = 32 M

Of 1081722 temp tables, 35% were created on disk

Effective in-memory tmp_table_size is limited to max_heap_table_size.

Perhaps you should increase your tmp_table_size and/or max_heap_table_size

to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your

ratio of on disk temp tables.

TABLE SCANS

Current read_buffer_size = 2 M

Current table scan ratio = 1537 : 1

read_buffer_size seems to be fine

TABLE LOCKING

Current Lock Wait ratio = 1 : 1545

You may benefit from selective use of InnoDB.

If you have long running SELECT’s against MyISAM tables and perform

frequent updates consider setting ‘low_priority_updates=1′

If you have a high concurrency of inserts.

no mysql database size shown in cpanel

You may see the mysql database size is zero in cPanel >> Mysql Databases, though the databases contains tables and data. In order to include the size of the databases while displaying disk usage in cPanel/WHM, use either of the following steps:

1) SSH to your server as root and edit the cpanel.config file

# vi /var/cpanel/cpanel.config

Search for

disk_usage_include_sqldbs=0

and change to

disk_usage_include_sqldbs=1

If the parameter is not present, add it. Save the file and execute the following command:

# /scripts/update_db_cache

OR You may use follwing option in WHM

2) Login to the WHM, goto Tweak Settings >> ‘SQL’ section and enable the following option:

When displaying disk usage in cpanel/WHM include Postgresql and MySQL.

You are done.

Log files in a cPanel server

To better troubleshoot an issue in a cPanel server its good go know the various log files, following are some log files which will help in resolution.

General Logs :

cPanel/WHM Initial Installation Errors:
Location : /var/log/cpanel*install*
Description : These log files contain cPanel installation logs & should be referenced first for any issues resulting from new cPanel installations..

Cpanel/WHM Service Status Logs:
Location : /var/log/chkservd.log
Description :The service monitoring demon (chkservd) logs all service checks here. Failed service are represented with a [-] and active services are represented by [+].

Cpanel/WHM Accounting Logs:
Location : /var/cpanel/accounting.log
Description : Contains a list of accounting functions performed through WHM, including account removal and creation..

cPanel/WHM Specific Requests and Errors:

cPanel error logs:
Location : /usr/local/cpanel/logs/error_log
Description : cPanel logs any error it incurs here. This should be checked when you encounter errors or strange behavior in cPanel/WHM…

cPanel License Error Logs:
Location : /usr/local/cpanel/logs/license_log
Description : All license update attempts are logged here. If you run into any errors related to license when logging in, check here.

Stats Daemon Logs:
Location : /usr/local/cpanel/logs/stats_log
Description : The stats daemon (cpanellogd) logs the output from all stats generators (Awstats, Webalizer, Analog) here.

Client Information, Requested URL Logs:
Location : /usr/local/cpanel/logs/access_log
Description : General information related to access cPanel requests is logged here.

cPanel/WHM Update Logs:
Location : /var/cpanel/updatelogs/update-[TIMESTAMP].log
Description : Contains all output from each cPanel update [upcp]. It’s named with the timestamp at which the upcp process was initiated..

Bandwidth Logs:
Location : /var/cpanel/bandwidth
Description : Files contain a list of the bandwidth history for each account. Each named after their respective user.

Tailwatchd [New]:
Location : /usr/local/cpanel/logs/tailwatchd_log
Description : Logs for daemon configuired under tailwatchd ie. cPBandwd, Eximstats, Antirelayd.

Apache Logs:

General Error and Auditing Logs:
Location : /usr/local/apache/logs/error_log
Description : All exceptions caught by httpd along with standard error output from CGI applications are logged here..
The first place you should look when httpd crashes or you incur errors when accessing website.

Apache SuExec Logs:
Location : /usr/local/apache/logs/suexec_log
Description : Auditing information reported by suexec each time a CGI application is executed. Useful for debugging internal server errors, with no relevant information being reported to the Apache error_log, check here for potential suexec policy violations…

Domain Access Logs:
Location : /usr/local/apache/domlogs/domain.com
Description : General access log file for each domain configured with cPanel.

Apache Access Logs:
Location : /usr/local/apache/logs/access_log
Description : Complete web server access log records all requests processed by the server.

Exim :

Message Reception and Delivery:
Location : /var/log/exim_mainlog or /var/log/exim/mainlog
Description : Receives an entry every time a message is received or delivered.

Exim ACLs/Policies based RejectLog :
Location : /var/log/exim_rejectlog
Description : An entry is written to this log every time a message is rejected based on either ACLs or other policies eg: aliases configured to :fail

Unexpected or Fatal Errors:
Location : /var/log/exim_paniclog
Description : Logs any entries exim doesn’t know how to handle. It’s generally a really bad thing when log entries are being written here, and they should be properly investigated..

IMAP/POP/SpamAssassin General Logging and Errors:
Location : /var/log/maillog & /var/log/messages
Description : The IMAP, POP, and SpamAssassin services all log here. This includes all general logging information (login attempts, transactions, spam scoring), along with fatal errors.

FTP:

FTP Logins and General Errors:
Location : /var/log/messages
Description : General information and login attempts are logged here..

FTP Transactions logging:
Location : /var/log/xferlog or /var/log/messages
Description : Is a symbolic link in most cases to /usr/local/apache/domlogs/ftpxferlog, which contains a history of the transactions made by FTP users…

MySQL:

MySQL General Information and Errors :
Location : /var/lib/mysql/$(hostname).err
Description : This path could vary, but is generally located in /var/lib/mysql. Could also be located at /var/log/mysqld.log

Security:

Authentication attempts:
Location : /var/log/secure
Description : Logs all daemons which requires PAM Authentication.

Tracking all Bad Logins and Logouts:
Location : /var/log/btmp
Description : Log of all attempted bad logins to the system. Accessed via the lastb command..

Tracking all Logins and Logouts:
Location : /var/log/wtmp
Description : The wtmp file records all logins and logouts.

Last Logins:
Location : /var/log/lastlog
Description : Database times of previous user logins. The lastlog file is a database which contains info on the last login of each user.

WebDav or WebDisk Log :
Location : /usr/local/cpanel/logs/cpdavd_error_log
Description : The cpdavd daemon is “WebDav” (better known as “WebDisk”) which was introduced in cPanel 11 to allow users to mount their home directory on their personal computer, always having access to the files and content…

Cphulkd Logs:
Location : /usr/local/cpanel/logs/cphulkd_errors.log
Description : cPHulk Brute Force Protection prevents malicious forces from trying to access your server’s services by guessing the login password for that service….
It blacklists IPs that it thinks are trying to run a brute force attack.

Failure Logging:
Location : /var/log/faillog
Description : Faillog formats the contents of the failure log from /var/log/faillog database. It also can be used for maintains failure counters and limits. Run faillog without arguments display only list of user faillog records who have ever had a login failure.

Startup/Boot, Kernel & Hardware error messages :
Location : /var/log/dmesg
Description : dmesg is a “window” into the kernels ring-buffer. It’s a message buffer of the kernel. The content of this file is referred to by the dmesg command. It shows bootlog and the hardware errors..

Tomcat:

General Startup, Shutdown & Error Logs:
Location : /usr/local/jakarta/tomcat/logs/catalina.err and /usr/local/jakarta/tomcat/logs/catalina.out
Description : Logs for Tomcat and all tomcat based applications…

Mysql database backup and restore

If you’re storing anything in MySQL databases that you do not want to lose, it is very important to make regular backups of your data to protect it from loss. It is always good to have backup copy of everything. This tutorial will show you easy steps to backup and restore the data in your MySQL database.

Creating A Backup

The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with .sql format which are used to restore the database in case of any database loss.

If you want to back up a single database, you can create the dump and send the output into a file by

# mysqldump database_name > database_name.sql

Multiple databases can be backed up at the same time

# mysqldump –databases database_one database_two > two_databases.sql

It is also simple to back up all of the databases on a server

# mysqldump –all-databases > all_databases.sql

Restoring a Backup

Since the dump files are just .sql files, you can restore the database backup by

# mysql database_name < database_name.sql

If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this

# mysql –one-database database_name < all_databases.sql

Here is the proper syntax if there is password for mysql for root user.

# mysqldump –opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

[uname] Your database username

[pass] The password for your database (note there is no space between -p and the password)

[dbname] The name of your database

[backupfile.sql] the filename for your database backup

[–opt] The mysqldump option.

For example, to backup a database named ‘database_name’ with the username ‘root’ and with no password to a file database_name.sql ,

# mysqldump -u root -p database_name > database_name.sql

This command will backup the ‘database_name’ database into a file called database_name.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only “tableone” and “tabletwo” tables from the ‘database_name’ database use the command below. Each table name has to be separated by space.

# mysqldump -u root -p database_name tableone tabletwo > tables.sql

Sometimes it is necessary to back up more than one database at once. In this case you can use the –database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

# mysqldump -u root -p –databases database_name database_name2 > databases_backup.sql

If you want to back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.

# mysqldump -u root -p –all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

–add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

–no-data: Dumps only the database structure, not the contents.

–add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is too big, its good to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

# mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below

# gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the database_name database into database_name.sql file. To re-create the database_name database, you can use following commands

Create an empty database on the serverLoad the file using the mysql command

# mysql -u [uname] -p[pass] [database_name] < [database_name.sql]

To restore compressed backup files you can do the following

# gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you want to restore a database that already exists, you’ll have to use mysqlimport command. The syntax for mysqlimport is as below

# mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Backing up database by using PHPMYADMIN

phpMyAdmin is the most popular web-based interface to MySQL and is usually preinstalled in most shared hosting accounts. Through it, you can perform any action on the database including backups and restoration. You can use phpMyAdmin to backup/restore your database if it is relatively small ie upto 8 to 10 MB. For bigger databases, you can use above method.

To back up your database, open your phpMyAdmin page in your web browser and select your database from the dropdown on the left. From the main database page, click on Export tab on the top button bar.

1. Select the database you’d like to back up from the column on the left side of phpMyAdmin.

2. Click on Export tab on the top button bar.

3. Select the tables from the list that you would like to backup. If you want to backup the entire database, check Select All.

4. Select Structure and data from the bullet list.

5. Check the Add ‘drop table box if you are moving the database to a new location and don’t want to merge the old table with an existing one.

6. Click the Save as file box.

7. Use the Save as file zipped if you want to compress the backup before downloading it from the server.

8. Click the Go button, when prompted save the file to your local computer.

Restoring via PHPMYADMIN

1. From the column on the left select the database that you want to restore to. If one doesn’t exist you must first create it.

2. Click on Import from the top set of tabs.

3. Click on the Browse button next to Location of the textfile near the bottom.

4. Browse to the local backup and click Open.

5. If you have the local backup in a non-text file format, e.g. you selected save as file: zipped when you backed up the database, you’ll have to unzip the file on your local computer before you can select it during this step.

6. Click the Go button.

7. Done…Your SQL-query has been executed successfully.

How To check if innodb enabled or not

How to check if innodb is enabled or not?

[[email protected] ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 343316 to server version: 4.1.22-standard
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> SHOW VARIABLES LIKE ‘have_innodb’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_innodb   | YES   |
+—————+——-+
1 row in set (0.00 sec)
mysql>
It will show provided output.

Mysql error : Can’t create new tempfile: ‘*.TMD file .

If you are getting “Can’t create new tempfile: ‘tablesname.TMD file

“ error while repairing corrupted database tables please try use following command to fix it.

Solution is

myisamchk -r -f tables.MYI

How to repair a corrupted mysql database table

How do you repair a corrupt MySQL table?

Error: Table ‘tbl_ name’ doesn’t exist databasename_tablesname.frm can’t open
If you get either of the following errors, it usually means that no table exists in the current database with the given name:
Table ‘tbl_name’ doesn’t exist
Can’t find file: ‘tbl_name’ (errno: 2)

1 ) Check the permission and ownership of database i.e. it should be same below

drwx—— 2 mysql mysql cpanelusername_dbname.

2) If the permission are corrects but same error occurs then it seems that your database table may be corrupts then there are following way to repair the DB

a) Go to whm >>SQL Services >> Repair a Database >> select database name and click Repair Database.

b) Go to cpanel >> mysql section MySQL Account Maintenance >> search database then click on Repair.

C) You can repair it though shell when mysqld server is running

i) login in mysql to that particular user by using following command

mysql>mysql –u databaseusername –p databasename

ii) select particular database

mysql> use databasename;

iii) Check whether database table is corrupted or not if following command output shows null value then it should be corrupts otherwise it is fine

mysql>show table status like ‘table name’\G; Or

mysql>check table tablename ;

iv)If it is corrupts then use the following command to repair that particular database table.

mysql>repair table tablename;

D] You can repair it though shell when mysqld server is not running

Repairing MyISAM mySQL Tables/Databases:

# cd /var/lib/mysql/DBNAME

# myisamchk tablename.MYI

Repairing ISAM mySQL Tables/Databases:

# cd /var/lib/mysql/DBNAME

isamchk tablename.MYI

where

-c –> check database is corrupted or not

-r –> recorver

-o –> optimise the database