Using MySQL with Cygwin

Notes

Using mysql 5.1.16 beta client libraries on cygwin

    * From: Eric Lilja <mindcooler at gmail dot com>
    * To: cygwin at cygwin dot com
    * Date: Wed, 28 Mar 2007 22:54:05 +0200
    * Subject: Using mysql 5.1.16 beta client libraries on cygwin

Hi!

As many of you may know, the binary distribution of MySQL for Windows only ships with MSVC++ libraries. They have no binary distribution for Cygwin. So what do you do if you want to develop c or c++ programs talking to a native Windows MySQL server using the cygwin tools?

Well, you can compile the client libraries yourself. Here's how I did it:
$ tar xvzf mysql-5.1.16-beta.tar.gz
$ cd mysql-5.1.16-beta
$ ./configure --prefix=/usr/local/mysql-5.1.16-beta --exec-prefix=/usr/local/mysql-5.1.16-beta --without-server --without-docs --without-man
$ make
$ make install

Keep the source directory so you can uninstall with:
$ make uninstall


Then I launched the mysql server (native windows version), created a database named dbase. This particular server doesn't require a password to login but you have to be on localhost.

Then I wrote this simple testprogram using my newly-compiled client libraries:
#include <cstdlib>
#include <iostream>
#include <sstream> /* Gives us <string> too. */

#include <mysql.h>

using namespace std;

void execute_query(MYSQL *, const string&, const string&);

int
main()
{
MYSQL m;
const char *host = "127.0.0.1"; /* "localhost" instead of 127.0.0.1 doesn't work. */
const char *user = "root";
const char *password = 0;
const char *database = "dbase";
const char *table_name = "wizard_spells";

mysql_init(&m);

if (!mysql_real_connect(&m, host, user, password, database, 3306, NULL, 0))
{
cerr << mysql_error(&m) << endl;

      return EXIT_FAILURE;
   }


cout << "Connection successful." << endl;

stringstream query;

query << "CREATE TABLE " << table_name << " (name VARCHAR(64) PRIMARY KEY, mana INT)";

execute_query(&m, query.str(), "Succesfully created new table wizard_spells.");

query.str(""); /* Empty stringstream. */

cout << query.str() << endl;

query << "INSERT INTO " << table_name << " VALUES ('solar strike', 50)";

execute_query(&m, query.str(), "Successfully inserted one row into table wizard_spells.");

mysql_close(&m);

   return EXIT_SUCCESS;
}


void
execute_query(MYSQL *m, const string& query, const string& success_string)
{
   if (mysql_query(m, query.c_str()) == 0)
   {
      cout << success_string << endl;
   }
   else
   {
      cerr << "Query failed: "<<  mysql_error(m) << endl;;
   }
}


Corresponding makefile:
CXX = g++
# Cannot use -pedantic or it will fail with:
# usr/local/mysql-5.1.16-beta/include/mysql/mysql.h:125: error: ISO C++ does not support `long long'
CXXFLAGS = -Wall -Wextra -std=c++98 -g -I /usr/local/mysql-5.1.16-beta/include/mysql -c
LDFLAGS = -L /usr/local/mysql-5.1.16-beta/lib/mysql -lmysqlclient -lz -o $(EXEC)
EXEC = cygwintest.exe
OBJECTS = cygwintest.o

all: $(OBJECTS)
	$(CXX) $^ $(LDFLAGS)


cygwintest.o: cygwintest.cpp
	$(CXX) $(CXXFLAGS) $<


clean:
	rm -f $(OBJECTS) $(EXEC) *.stackdump



Hope this helps someone. I've attached source and Makefile.

- Eric

CXX = g++
# Cannot use -pedantic or it will fail with:
# usr/local/mysql-5.1.16-beta/include/mysql/mysql.h:125: error: ISO C++ does not support `long long'
CXXFLAGS = -Wall -Wextra -std=c++98 -g -I /usr/local/mysql-5.1.16-beta/include/mysql -c
LDFLAGS = -L /usr/local/mysql-5.1.16-beta/lib/mysql -lmysqlclient -lz -o $(EXEC)
EXEC = cygwintest.exe
OBJECTS = cygwintest.o

all: $(OBJECTS)
	$(CXX) $^ $(LDFLAGS)

cygwintest.o: cygwintest.cpp
	$(CXX) $(CXXFLAGS) $<

clean:
	rm -f $(OBJECTS) $(EXEC) *.stackdump

#include <cstdlib>
#include <iostream>
#include <sstream> /* Gives us <string> too. */

#include <mysql.h>

using namespace std;

void execute_query(MYSQL *, const string&, const string&);

int
main()
{
   MYSQL m;
   const char *host = "127.0.0.1"; /* "localhost" instead of 127.0.0.1 doesn't work. */
   const char *user = "root";
   const char *password = 0;
   const char *database = "dbase";
   const char *table_name = "wizard_spells";
   
   mysql_init(&m);

   if (!mysql_real_connect(&m, host, user, password, database, 3306, NULL, 0))
   {
      cerr << mysql_error(&m) << endl;

      return EXIT_FAILURE;
   }

   cout << "Connection successful." << endl;

   stringstream query;

   query << "CREATE TABLE " << table_name << " (name VARCHAR(64) PRIMARY KEY, mana INT)";

   execute_query(&m, query.str(), "Succesfully created new table wizard_spells.");

   query.str(""); /* Empty stringstream. */

   cout << query.str() << endl;

   query << "INSERT INTO " << table_name << " VALUES ('solar strike', 50)";

   execute_query(&m, query.str(), "Successfully inserted one row into table wizard_spells.");

   mysql_close(&m);
   
   return EXIT_SUCCESS;
}

void
execute_query(MYSQL *m, const string& query, const string& success_string)
{
   if (mysql_query(m, query.c_str()) == 0)
   {
      cout << success_string << endl;
   }
   else
   {
      cerr << "Query failed: "<<  mysql_error(m) << endl;;
   }
}


--
Unsubscribe info:      http://cygwin.com/ml/#unsubscribe-simple
Problem reports:       http://cygwin.com/problems.html
Documentation:         http://cygwin.com/docs.html
FAQ:                   http://cygwin.com/faq/

Another

I recently compiled the MySQL client from source under the Cygwin environment. The installation completed without issue. I already had a MySQL server (and client) installed and configured for XP on the same machine.

I had a great deal of difficulty in getting my Cygwin mysql client to connect to mysqld-nt on Windows - mainly because of wasted hours Googling and reading post after post of people getting the same error message (see below) but not really under the same setup I was attempting, so none of their solutions worked for me - but finally I found out that the solution was simply to run the Cygwin MySQL client from the shell as so:

mysql -h 127.0.0.1 -u root -p

The "-h 127.0.0.1" is the crucial bit; apparently it might have to do with "MySQL using domain sockets for LINUX machines and named pipes on Windows machines" (don't really understand this; just paraphrasing someone else here).
The following error results if the -h option is not used as above
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/tmp/mysql.sock' (2)

Anyway (sorry for the long rant above - although it might come in useful for someone else trying to solve the same problem!), my question now is that can I do something to my Windows MySQL server configuration (say, through the my.ini) that would obviate the need of using the -h as above? It seems to be a bit of a hack... plus, I'm planning to use PHP/C++/etc. under Cygwin to talk to my Windows MySQL, and I don't know if the same problem would manifest itself in another way, so a more general solution would be in order.