Archive for the ‘Mysql’ Category

Use following PHP script to change the collation of all the tables of mysql database

$db = mysql_connect(‘localhost’,’Database_sername’,’Database_password’);
if(!$db) echo “Cannot connect to the database – incorrect details”;
mysql_select_db(‘Database_name’); $result=mysql_query(‘show tables’);
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query(“ALTER TABLE $value COLLATE utf8_general_ci”);
}}
echo “The collation of your database has been successfully changed!”;
?>

Please make necessary changes in the script.


If you are getting error like 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

MySQL has built-in functionality that allows you to log SQL queries to a file , You can enable the full SQL queries logs to a file or only slow running queries log.  It is easy for us to troubleshoot/ debug the sql statement if SQL queries log enable , The slow query log  is  used to find queries that take a long time to execute and are therefore candidates for optimization.

To enable you just need to add some lines to your my.cnf file, and restart. Add the following:

* To enable slow Query Log only

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

After enabling slow query, mysqld writes a statement to the slow query log file and it consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. mysqld only log after SQL statements has been executed and after all locks have been released, so log order might be different from execution order. The minimum and default values of long_query_time are 1 and 10, respectively.

* To enable full Log Query

log=/var/log/mysqldquery.log

The above will log all queries to the log file.

Note :: Don’t forgot to restart mysql service after making changes in my.cnf file.

mysql_pconnect() will maintain a persistent connection to the database. Whenever your script calls the connect to database function, it first searches already existing connections to the database and if exists it will use the same connection to connect to the database, if not it will open a new connection to the database. ie. Connection is Persistent

where as mysql_connect() function will establish a new connection whenever a connection to database needed, and after executing the script, this function disconnects the connection. ie. connection is not a persistent one.

mysql_pconnect() function is used where your site has a Heavy Traffic and where as mysql_connect() function is used when there is moderate/less traffic to your site.

After specifying all the correct details in your configuration file of your application but still you are getting following error.

Error : Your all database details is correct but the database is not connected  in any application.

Solution is:

You will face this error because of  ”sql.safe_mode”  is “On” in php.ini file. You will have to tuern it off in the main server php.ini file.

To find the location of the main php.ini file, just execute following command on shell

php -i | grep php.ini

Sometimes it happens that your database get corrupted due to many reason like it does nto get restored properly or server get rebooted while updating database etc…. and you have tried to repait it using phpmyadmin and it does shows that it has repaired but it is not.

Please follow the following steps to repair the database:

Login to server with root access

Stop mysql using following command

/etc/init.d/mysql stop

Run following command to check all teh tables in teh database

/usr/bin/myisamchk /var/lib/mysql/databasename/*.MYI

Run following command to repair all teh tables in the database

/usr/bin/myisamchk -r /var/lib/mysql/databasename/*.MYI

Then recheck again using following command

/usr/bin/myisamchk /var/lib/mysql/databasename/*.MYI

Restrart MySql service using following command

/etc/init.d/mysql restart