Databases | Linux Ask!

Enable external locking in MySQL

Sep 292010

Enable external locking in MySQL

Answer:

External locking is a method that allow MySQL aware of external program(s) might also have access to the database tables as the same time, so if other programs locked the files, MySQL will wait until those files are unlocked.

This is not recommended and poor for MySQL performance, so you should turn off it unless you have multiple MySQL servers using the same database directory.

To enable it, edit the MySQL configurations, e.g. /etc/my.cnf

..
external-locking
..

Don't forget to restart MySQL to take effect.

# /sbin/service mysqld restart

Changing the default storage engine in MySQL

MySQL No Responses »

Sep 282010

Changing the default storage engine in MySQL

Answer:

The default storage of MySQL is MyISAM, but you can change to others such as InnoDB.

To change this default, edit the MySQL configurations, e.g. /etc/my.cnf

..
[mysqld]
default-storage_engine = innodb

Don't forget to restart MySQL to take effect.

# /sbin/service mysqld restart

Connect MySQL server using C

C, MySQL No Responses »

Sep 262010

Connect MySQL server using C

Answer:

Below show a code sample for how to connect to MySQL server using C programming language.

#include <stdio.h>
#include <stdlib.h>
#include "mysql.h"

MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;

void exiterr(int exitcode)
{
	fprintf( stderr, "%s\n", mysql_error(&mysql) );
	exit( exitcode );
}

int main()
{
	uint i = 0;

	if (!(mysql_connect(&mysql,"host","username","password"))) 
		exiterr(1);

	if (mysql_select_db(&mysql,"payroll"))
		exiterr(2);

	if (mysql_query(&mysql,"SELECT name,rate FROM emp_master"))
		exiterr(3);

	if (!(res = mysql_store_result(&mysql)))
		exiterr(4);

	while((row = mysql_fetch_row(res))) {
		for (i=0 ; i < mysql_num_fields(res); i++) 
			printf("%s\n",row[i]);
	}

	mysql_free_result(res);
	mysql_close(&mysql);
}

Clear screen in MySQL client

MySQL No Responses »

Sep 212010

Clear screen in MySQL client

Answer:

To clear the screen of MySQL client, like the clear command in Linux, use the following tricks:

mysql> pager clear;
PAGER set to 'clear'

mysql> select 1; -- Magic Happened here!
mysql> nopager;
PAGER set to stdout

mysql>

Calculate age in MySQL

MySQL No Responses »

Sep 092010

Calculate age in MySQL

Answer:

If you have a datetime field in MySQL, e.g. dob and you want to calculate the age, you can try the following query.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age FROM ...

Reference: http://ma.tt/2003/12/calculate-age-in-mysql/

Older Entries Newer Entries