Archive for the “MySQL” Category

mysql: Incorrect information in file:

Incorrect information in file /path/test.frm

This indicates that skip-innodb is enabled in the my.cnf file ..

January 30, 2010 Posted Under: MySQL   Read More

Mysql ERROR 3 (HY000): Error writing file

Error :

ERROR 3 (HY000): Error writing file ‘/tmp/MY68ie0h’ (Errcode: 28)

 

this is due to a storage issue with the partition that is holding /tmp

October 31, 2009 Posted Under: MySQL   Read More

MySQL: Persistent connections

You may encounter a max user connection error if your my.cnf file is set to a low number and you have mysql.allow_persistent = on within your php.ini file.

You have two ways to fix this .

1) use a mysql_close() statement within your code. this is not needed for non-persistent connection per the PHP manual
PHP Manual

2) disallow persistent connection by turning it off within the php.ini file.

Persistent connection keeps the last connection active with mysql which goes into sleep mode.

Other errors you may see
Too many connections

May 10, 2009 Posted Under: MySQL   Read More

MysQL: Show grants for users

mysql> show grants for ‘user’@'host’
or
select * from information_schema.user_privileges where grantee like “‘user’%”;

Tools
Maakit

May 9, 2009 Posted Under: MySQL   Read More

Mysql: drop all tables via the command line

Thanks to VIVEK GITE for this ——
Drop all tables using shell script w/o root access

Nice script to run via shell

I’ve small handy shell script that removes all tables without dropping and creating MySQL database again.

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"

# Detect paths

MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"

	echo "Drops all tables from a MySQL"
	exit 1
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"

done

Simply use above script as follows to empty a database called quiz with username tom and password jerry:

$ ./drop.table.sh tom jerry quiz

View the full script below

==============

#!/bin/bash
# A shell script to delete / drop all tables from MySQL database.
# Usage: ./script user password dbnane
# Usage: ./script user password dbnane server-ip
# Usage: ./script user password dbnane mysql.nixcraft.in
# -------------------------------------------------------------------------
# Copyright (c) 2008 nixCraft project <http://www.cyberciti.biz/fb/>

# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.

# ----------------------------------------------------------------------
# See URL for more info:
# http://www.cyberciti.biz/faq/how-do-i-empty-mysql-database/
# ---------------------------------------------------

MUSER="$1"

MPASS="$2"
MDB="$3"

MHOST="localhost"

[ "$4" != "" ] && MHOST="$4"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

# help
if [ ! $# -ge 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name} [host-name]"

	echo "Drops all tables from a MySQL"
	exit 1
fi

# make sure we can connect to server

$MYSQL -u $MUSER -p$MPASS -h $MHOST -e "use $MDB"  &>/dev/null
if [ $? -ne 0 ]

then
	echo "Error - Cannot connect to mysql server using given username, password or database does not exits!"
	exit 2
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

# make sure tables exits
if [ "$TABLES" == "" ]
then
	echo "Error - No table found in $MDB database!"

	exit 3
fi

# let us do it
for t in $TABLES

do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS -h $MHOST $MDB -e "drop table $t"

done
February 1, 2009 Posted Under: MySQL   Read More
« Older Entries