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.

blog.dt.org

• http://blog.dt.org/index.php/2009/04/perl-dbi-and-dbdmysql-on-cygwin-connecting-to-a-native-windows-build-of-mysql-on-a-windows-2003-ami-within-amazon-ec2/

Perl DBI and DBD::mysql on Cygwin — Connecting to a Native Windows Build of MySQL on a Windows 2003 AMI Within Amazon EC2

In my ongoing project involving Amazon’s EC2 service, I had a frustrating problem to solve this past weekend. I have an EC2 instance running Windows 2003, and on that instance I have a native Windows version of MySQL 5 and Cygwin. I wanted to use the mysqlhotcopy Perl script from the Cygwin command line against the Windows-native MySQL instance. Once again, I would have expected this to be a simple job with a simple solution, but in the end it turned into an extensive hacking session. Here is a quick roadmap of what I did.

My initial thought was that this should just work: MySQL and its scripts should not care if they are running in native Windows mode or in Cygwin, and mysqlhotcopy is just a Perl script that should run fine in either Cygwin or Windows…wrong! The native Windows version of MySQL does not ship with the mysqlhotcopy script, probably because that script uses Perl and DBI and there is no guarantee that Perl/DBI will be available on Windows. So I grabbed the mysqlhotcopy script from a UNIX box and attempted to run it via Cygwin. I got this Perl error saying that the DBI module was not found:

Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/5.10/i686-cygwin /usr/lib/perl5/5.10 /usr/lib/perl5/site_perl/5.10/i686-cygwin /usr/lib/perl5/site_perl/5.10 /usr/lib/perl5/vendor_perl/5.10/i686-cygwin /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/site_perl/5.8 /usr/lib/perl5/vendor_perl/5.8 .) at ./mysqlhotcopy line 8.
BEGIN failed--compilation aborted at ./mysqlhotcopy line 8.

So I guess I just need to get DBI installed for Perl and we should be good to go…right? Perl modules can be installed on Cygwin using cpan, so I ran:

cpan DBI

This command completed without errors. Let’s try the mysqlhotcopy script again…it runs without errors and prints out the usage page. Progress! So now let’s test it out with a real call to take a hot copy of the database:

mysqlhotcopy -u <username> -p <password> <database> <backup directory>

This command gives me the following error, complaining about DBD::mysql (the MySQL driver used by DBI to actually connect to MySQL):

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/5.10/i686-cygwin /usr/lib/perl5/5.10 /usr/lib/perl5/site_perl/5.10/i686-cygwin /usr/lib/perl5/site_perl/5.10 /usr/lib/perl5/vendor_perl/5.10/i686-cygwin /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/site_perl/5.8 /usr/lib/perl5/vendor_perl/5.8 .) at (eval 9) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.  at ./mysqlhotcopy line 182

So we just need to install the DBD::mysql module and we should be good to go, right?. I ran the following command:

cpan DBD::mysql

This command failed with a build error:

Can't exec "mysql_config": No such file or directory at Makefile.PL line 76.

The DBD::mysql module is compiled locally, using the mysql_config script to find the location of the local MySQL installation. But the native Windows version of MySQL does not contain the mysql_config script. Ugh. I tried copying this file over from a UNIX box, but the output from the script (which is just configuration info for the MySQL installation and the settings in my.ini) looked a little screwy. So I guess I need to figure out what mysql_config is used for within the mysqlhotcopy script.

After some digging, it appears that the crux of the problem is that the MySQL client libraries are not available in the native Windows MySQL installation, and these libraries are required to build DBD::mysql. So if we can figure out a way to get these libraries to work in Cygwin, then we should have a working solution. Luckily, I found a note in the DBD::mysql readme file that pointed me in the right direction. Here is what I ultimately did:

0) Download and unzip the MySQL source code (I grabbed mysql 5.1.34).

1) Build the MySQL client libraries (without the server) via:
./configure --without-server --prefix=/usr/local/mysql-5.1.34
make

The build halts with an error for the file sys/ttypdefaults.h (not found), so I copied that file from /usr/include/sys/ttydefaults.h on a UNIX box into /usr/include/sys within Cygwin. Running make again completes the build after this file is in place. There is little of consequence in this file, so I am hoping that copying it from a UNIX box into Cygwin won’t have any serious side effects.

2) Once the MySQL build has finally completed (and this takes a while), run a manual build of the cpan download of DBD::mysql in the .cpan cache directory, using parameters for the location of the MySQL client libraries (which eliminates the need for mysql_config to be used to find them):

cd ~/.cpan/build/DBD-mysql-4.011-ynTTNR
perl Makefile.PL --libs="-L/usr/local/mysql-5.1.34/lib/mysql -lmysqlclient -lz" --cflags=-I/usr/local/mysql-5.1.34/include/mysql --testhost=127.0.0.1make
make install

So now we are ready to try mysqlhotcopy again. The MySQL client build installed a copy of mysqlhotcopy in /usr/local/mysql-5.1.34/bin, so let’s use that one instead of the one that was copied in from a UNIX box. Here’s the command:

/usr/bin/mysql-5.1.34/bin/mysqlhotcopy -u <username> -p <password> <database> <backup directory>

Still no joy; now we get this error:

DBI connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','<database>',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock'(2) at /usr/local/mysql-5.1.34/bin/mysqlhotcopy line 177

This looks to me like DBI (using DBD::mysql) is trying to connect to a UNIX socket on the local machine instead of using TCP. Given that we’re on Windows, it will probably be a pain in the neck to figure out how to get the native Windows version of MySQL to listen on a local UNIX socket. Luckily, I’ve spent some time looking at the Perl code in mysqlhotcopy and it turns out that if you specify an IP address via the -h command, then this will override the use of the UNIX socket and will force DBI to use TCP to connect to MySQL. So let’s try the localhost loopback address (127.0.0.1) to see if that works:

/usr/bin/mysql-5.1.34/bin/mysqlhotcopy -h '127.0.0.1' -u <username> -p <password> <database> <backup directory>

Success! The command runs to completion without errors, and I can verify that the backup has taken place.

Hope this helps.