/* Copyright GPL 2004 Mike Chiriico mmc
   Updated: Wed Apr 21 00:33:10 EDT 2004

   This should really be used with MySQL Version 4 or greater.


  Must Reads:


indent -kr -i8                                                         
#include <stdlib.h>
#include <arpa/inet.h>
#include <assert.h>
#include <errno.h>
#include <netinet/in.h>
#include <signal.h>
#include <stdio.h>
#include <string.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <sys/wait.h>
#include <unistd.h>
#include <pthread.h>
#include <mysql.h>
#include "wrapper.h"

#define SA      struct sockaddr	/* keeps the lines shorter. Note sockaddr and not sockaddr_in; it's generic */
#define MAXLINE 4096
/* Following could be derived from SOMAXCONN in <sys/socket.h>, but many
   kernels still #define it as 5, while actually supporting many more */
#define LISTENQ         1024	/* 2nd argument to listen() */
#define MAXSOCKADDR  128	/* max socket address structure size */
#define MAX_SQLSELECT 1000
#define RBUF_INCREMENT 1000
#define Q_BUF 1024
pthread_mutex_t mrx_mutex = PTHREAD_MUTEX_INITIALIZER;

FILE *fp;
char username[20], password[20], database[20];
char qbuf[Q_BUF];
int count, num_fields, i;

MYSQL mysql, *sock;

/* connection testing */
pthread_mutex_t conn_mutex = PTHREAD_MUTEX_INITIALIZER;
#define CTBLMAX 12
struct conntbl {
	int conn;
	pthread_t t;
	int FLAG;
} conntbl[CTBLMAX];
int conn_count = -1;
/* for asyc_write */
struct S_Buf {
	int conn;
	char *s;

/* normally these would go in a header */
void process_stuff(int, MYSQL *, int me);
int sockfd_to_family(int);

  mysql_real_connect wrapper. Assume didn't compile with
      ./configure --enable-thread-safe-client
MYSQL *Mysql_real_connect(MYSQL * mysql, const char *host,
			  const char *user, const char *passwd,
			  const char *db, unsigned int port,
			  const char *unix_socket,
			  unsigned long client_flag)
	MYSQL *sock;

	sock = mysql_real_connect(mysql, host, user, passwd,
				  db, port, unix_socket, client_flag);

	if (!sock) {
		fprintf(stderr, "sock error in mysql_real_connect\n");

	return sock;


int sql_select(char *s, int sockfd, MYSQL * sock)
	/* Call realloc .... can't send in separate chunks.
	   write is atomic, but, problems will happen
	   between writes.

	char bbuf[MAXLINE + 50];
	char *rbuf = NULL;
	size_t cur_limit = 0, new_limit;
	MYSQL_RES *res;
	MYSQL_FIELD *fields;

	rbuf = (char *) malloc(RBUF_INCREMENT * sizeof(char));
	if (rbuf == NULL) {
			"Out of memory: sql_select (initial init)\n");
		return -1;
	cur_limit = RBUF_INCREMENT;
	/* initial tag for each row */

	snprintf(rbuf, RBUF_INCREMENT, " ");

	snprintf(qbuf, Q_BUF, "%s", s);
	sock =
	    Mysql_real_connect(&mysql, NULL, username, password, database,
			       0, NULL, 0);

	if (mysql_query(sock, qbuf)) {
		fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));
	if (!(res = mysql_store_result(sock))) {
		fprintf(stderr, "Couldn't get result from %s\n",
	printf("number of fields: %d\n", mysql_num_fields(res));
	num_fields = mysql_num_fields(res);
	fields = mysql_fetch_fields(res);
	while ((row = mysql_fetch_row(res))) {
		for (i = 0; i < num_fields; i++) {
			snprintf(bbuf, MAXLINE, "%s|", row[i]);
			if (strlen(bbuf)+strlen(rbuf) >= cur_limit -1 ) {
				new_limit = cur_limit + RBUF_INCREMENT;
				char *tmp =
					    new_limit * sizeof(char));
				if (tmp == NULL) {
						"Out of memory in sql_select\n");
					return 1;
				} else {
					rbuf = tmp;
					cur_limit = new_limit;
			//                                v------------------------v
			// You can't do this: 	snprintf(rbuf, cur_limit, "%s%s", rbuf, bbuf); 
                        strncat(rbuf,bbuf,cur_limit -1);

		strncat(rbuf,"\n",cur_limit - 1);

	//write(sockfd, rbuf, strlen(rbuf));
        fprintf(stderr,"strlen(rbuf)= %d = %d *1024 +  strlen(rbuf) 1024= %d ",strlen(rbuf),strlen(rbuf)/1024, strlen(rbuf) %  1024);
        /* May want to break the packets up */
        int packet_size=1024;
        int tot_char= strlen(rbuf);
        int num_sends=tot_char/packet_size;
        int pck_extra= tot_char % 1024;
        int i;
        char *tmp;
	for( i=0; i< num_sends; ++i){

        fprintf(stderr,"\n\nSend stats:\n      Total bytes: %d (pck size: %d, of this size: %d) last: %d \n\n",tot_char,packet_size,num_sends,pck_extra);

	fprintf(stderr, "ok out of select %d\n", cur_limit);
	return 0;


static void *asyc_write(void *arg)
	int conn;
	char *s;

	conn = ((struct S_Buf *) arg)->conn;
	s = ((struct S_Buf *) arg)->s;

	/* fprintf(stderr, "asyc_write s.len=%d ->%s<-\n", strlen(s), s); */
	write(conn, s, strlen(s));

	return (NULL);

void process_stuff(int sockfd, MYSQL * sock, int me)
	ssize_t n;
	char line[MAXLINE + 1];
	char bbuf[MAXLINE + 50];
	int i;
	struct S_Buf *s_b;
	pthread_t t;

	while (1) {		/* there is a problem here that we may not read everything at once */
		if ((n = read(sockfd, line, MAXLINE)) == 0) {
				"return in process_stuff ... read == 0\n");
			pthread_t tmp = pthread_self();
			for (i = 0; i < CTBLMAX; ++i) {
				if (conntbl[i].t == tmp) {
					conntbl[i].FLAG = 0;

		if (line[n - 1] == '\n')
			fprintf(stderr, "it is a slash n\n");
		if (line[n - 1] == '\r')
			fprintf(stderr, "it is a slash r\n");
		if (n > 2)
				"n=%d last[n-2]=%d last[n-1]=%d  last[n]=%d  ",
				n, line[n - 2], line[n - 1], line[n]);
		if (n == 2)
			fprintf(stderr, "n=%d last[n-1]=%d  last[n]=%d  ",
				n, line[n - 1], line[n]);
		if (n == 1)
			fprintf(stderr, "n=%d  last[n]=%d  ", n, line[n]);
		/* sleep(1); */

		line[n] = '\0';
		fprintf(stderr, "client(%d) sent: bytes(%d)", me, n);
		if (strncmp(line, "WEATHER|", 8) == 0) {
			fprintf(stderr, " Starting the mysql query\n");
			/* begin mysql */
			    ("select * from weather order by timeEnter desc limit 500 ",
			     sockfd, sock);
		} else if (strncmp(line, "ECHO|", 5) == 0) {
			fprintf(stderr, "Starting echo\n");
			snprintf(bbuf, MAXLINE + 50, "%s:SERVER ECHO: ",
			write(sockfd, bbuf, strlen(bbuf));
		} else if (strncmp(line, "STAT|", 5) == 0) {
			for (i = 0; i < CTBLMAX; ++i) {
				if (conntbl[i].FLAG == 1) {
					snprintf(bbuf, MAXLINE, "%d ", i);
					write(sockfd, bbuf, strlen(bbuf));

			fprintf(stderr, "Starting STAT\n");
			snprintf(bbuf, MAXLINE, "Connections: %d",
			write(sockfd, bbuf, strlen(bbuf));
		} else if (strncmp(line, "WRITE|", 5) == 0) {

			fprintf(stderr, "Starting WRITE\n");
			snprintf(bbuf, MAXLINE, "%s ", line);
			s_b =
			    (struct S_Buf *) malloc(sizeof(struct S_Buf));
			s_b->s =
			    (char *) malloc(sizeof(char) * sizeof(bbuf));
			strcpy(s_b->s, bbuf);

			/* At this point only setup for checking.
                           This works with (0-4) connections.
			if (line[6] == '0' && conntbl[0].FLAG != 0 ) {	/* make sure you change these numbers */
				s_b->conn = conntbl[0].conn;
				Pthread_create(&t, NULL, &asyc_write, s_b);
			if (line[6] == '1' && conntbl[1].FLAG != 0) {
				s_b->conn = conntbl[1].conn;
				Pthread_create(&t, NULL, &asyc_write, s_b);
			if (line[6] == '2' && conntbl[2].FLAG != 0) {
				s_b->conn = conntbl[2].conn;
				Pthread_create(&t, NULL, &asyc_write, s_b);
			if (line[6] == '3' && conntbl[3].FLAG != 0 ) {
				s_b->conn = conntbl[3].conn;
				Pthread_create(&t, NULL, &asyc_write, s_b);
			if (line[6] == '4' && conntbl[4].FLAG != 0 ) {
				s_b->conn = conntbl[4].conn;
				Pthread_create(&t, NULL, &asyc_write, s_b);

		} else {
			write(sockfd, "HELLO FR SERVER: ", strlen("HELLO FR SERVER: "));	/* the test client will block on read  */
		}		/* Also Microsoft will terminate on Null */


int sockfd_to_family(int sockfd)
	struct sockaddr_in sa;
	socklen_t len;
	len = sizeof(sa);
	if (getpeername(sockfd, (struct sockaddr *) &sa, &len) < 0)
		return -1;
	fprintf(stderr, "client: %s: %d\n", inet_ntoa(sa.sin_addr),
		(int) ntohs(sa.sin_port));
	return (sa.sin_port);

static void *do_init(void *arg)
	int connfd;
	int me;
	MYSQL *sock = NULL;

	connfd = *((int *) arg);

	conntbl[conn_count].conn = connfd;
	conntbl[conn_count].t = pthread_self();
	conntbl[conn_count].FLAG = 1;
	me = conn_count;

	/* needs to be called in thread handler */
	process_stuff(connfd, sock, me);
	return (NULL);

int main(void)
	int listenfd, *connfdptr;
	socklen_t clilen;
	pthread_t t;
	struct sockaddr_in cliaddr, servaddr;
	printf("server_thread_mysql version 0.2\n");

	int i;
	for (i = 0; i < CTBLMAX; ++i) {
		conntbl[i].conn = 0;
		conntbl[i].t = 0;
		conntbl[i].FLAG = 0;

	fp = fopen("mysql.cfg", "r");
	if (fp == NULL) {
			"See README! \nmysql.cfg does not exist\n");
		fprintf(stderr, "Create mysql.cfg from mysql.cfg.old\n");
			"mysql.cfg must contain username password database\n");
		fprintf(stderr, "as follows:\n\n");
			"username bob password secretpass78 database test\n\n\n");
		return (1);
	fscanf(fp, "username %s password %s database %s", username,
	       password, database);

	/* called in main progam..seems to be required */

	listenfd = socket(AF_INET, SOCK_STREAM, 0);
	bzero(&servaddr, sizeof(servaddr));
	servaddr.sin_family = AF_INET;
	servaddr.sin_addr.s_addr = htonl(INADDR_ANY);
	servaddr.sin_port = htons(10000);
	bind(listenfd, (SA *) & servaddr, sizeof(servaddr));
	listen(listenfd, LISTENQ);

	while (1) {
		clilen = sizeof(cliaddr);
		connfdptr = (int *) malloc(sizeof(int));	/* free is called in do_init */
		*connfdptr = accept(listenfd, (SA *) & cliaddr, &clilen);
		Pthread_create(&t, NULL, &do_init, connfdptr);



Linux System Admin Tips: There are over 200 Linux tips and tricks in this article. That is over 100 pages covering everything from NTP, setting up 2 IP address on one NIC, sharing directories among several users, putting running jobs in the background, find out who is doing what on your system by examining open sockets and the ps command, how to watch a file, how to prevent even root from deleting a file, tape commands, setting up cron jobs, using rsync, using screen conveniently with emacs, how to kill every process for a user, security tips and a lot more. These tip grow weekly. The above link will download the text version for easy grep searching. There is also an html version here.

Breaking Firewalls with OpenSSH and PuTTY: If the system administrator deliberately filters out all traffic except port 22 (ssh), to a single server, it is very likely that you can still gain access other computers behind the firewall. This article shows how remote Linux and Windows users can gain access to firewalled samba, mail, and http servers. In essence, it shows how openSSH and Putty can be used as a VPN solution for your home or workplace.

MySQL Tips and Tricks: Find out who is doing what in MySQL and how to kill the process, create binary log files, connect, create and select with Perl and Java, remove duplicates in a table with the index command, rollback and how to apply, merging several tables into one, updating foreign keys, monitor port 3306 with the tcpdump command, creating a C API, complex selects, and much more.

Create a Live Linux CD - BusyBox and OpenSSH Included: These steps will show you how to create a functioning Linux system, with the latest 2.6 kernel compiled from source, and how to integrate the BusyBox utilities including the installation of DHCP. Plus, how to compile in the OpenSSH package on this CD based system. On system boot-up a filesystem will be created and the contents from the CD will be uncompressed and completely loaded into RAM -- the CD could be removed at this point for boot-up on a second computer. The remaining functioning system will have full ssh capabilities. You can take over any PC assuming, of course, you have configured the kernel with the appropriate drivers and the PC can boot from a CD. This tutorial steps you through the whole processes.

SQLite Tutorial : This article explores the power and simplicity of sqlite3, first by starting with common commands and triggers, then the attach statement with the union operation is introduced in a way that allows multiple tables, in separate databases, to be combined as one virtual table, without the overhead of copying or moving data. Next, the simple sign function and the amazingly powerful trick of using this function in SQL select statements to solve complex queries with a single pass through the data is demonstrated, after making a brief mathematical case for how the sign function defines the absolute value and IF conditions.

The Lemon Parser Tutorial: This article explains how to build grammars and programs using the lemon parser, which is faster than yacc. And, unlike yacc, it is thread safe.

How to Compile the 2.6 kernel for Red Hat 9 and 8.0 and get Fedora Updates: This is a step by step tutorial on how to compile the 2.6 kernel from source.

Virtual Filesystem: Building A Linux Filesystem From An Ordinary File. You can take a disk file, format it as ext2, ext3, or reiser filesystem and then mount it, just like a physical drive. Yes, it then possible to read and write files to this newly mounted device. You can also copy the complete filesystem, since it is just a file, to another computer. If security is an issue, read on. This article will show you how to encrypt the filesystem, and mount it with ACL (Access Control Lists), which give you rights beyond the traditional read (r) write (w) and execute (x) for the 3 user groups file, owner and other.

Working With Time: What? There are 61 seconds in a minute? We can go back in time? We still tell time by the sun?

Chirico img Mike Chirico, a father of triplets (all girls) lives outside of Philadelphia, PA, USA. He has worked with Linux since 1996, has a Masters in Computer Science and Mathematics from Villanova University, and has worked in computer-related jobs from Wall Street to the University of Pennsylvania. His hero is Paul Erdos, a brilliant number theorist who was known for his open collaboration with others.

Mike's notes page is souptonuts. For open source consulting needs, please send an email to All consulting work must include a donation to Logo Logo