Category Archives: Database

install DBD::mysql problem

I was working on installing Bugzilla and ran into an error when installing DBD::MySQL module:
usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm

If you run into this error, run:
export LANG=C
and then do the following:
perl Makefile.PL
make
make install

(ignore all the warning messages, during make and make install).

MySQL: Migrating Slave to New Master

Consider the following scenario:
Master’s IP address has been changed and the slave needs to be reconfigured for the New IP address.

How do we go about doing it ? Two ways.
One of them a bit hackish but the most
simple:

– Shut down slave MySQL server.
– Find master.info file, store current one in the backup and edit it
replacing the host name with new host name.
– Start slave MySQL server.

Second one is more complex but using only SQL commands:

– Run SLAVE STOP to stop slave thread;
– Run SHOW SLAVE STATUS and record the position in the master binary log
slave is currently located
– use CHANGE MASTER TO to point slave to the same position on the
different hostname
– Use SLAVE START to start slave back again.

We cannot change the IP that easily. We need to do things so complex since MySQL assumes we are
replicating from new slave if you’re changing host name and resets all
other parameters.

Configuring MySQL replication

Steps for configuring MySQL replication:

We have two boxes www1 and www2

Configuring the master (www1):
1. Edit /etc/my.cnf
2. Make sure that the following lines are present in the [mysqld] section:
log-bin
server-id=##
where ## is a unique number. Lets use 1 here.
3. run: mysql -u root -p
4. at mysql prompt, run the following commands:
mysql> GRANT FILE ON *.* TO repl@”%” IDENTIFIED BY ‘replicate’;
Here, the username which the slave would use to connect to master is repl and password is replicate;
mysql> FLUSH TABLES WITH READ LOCK;
This flushes all the tables and blocks write queries.
5. We now take a snapshot of the master server(www1).
a) tar -zcvf master_data.tar.gz /usr/local/mysql/var/
b) copy this to www2 ( say in /usr/local/src/ on www2)
6. on www1, run: mysql -u root -p
mysql > SHOW MASTER STATUS;
This will show the status of the master, and would look like the following:
+—————–+———-+————–+——————+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+—————–+———-+————–+——————+
| www1-bin.051 | 702 | | |
+—————–+———-+————–+——————+
Take a note of the values of “File” and “Position”
mysql > UNLOCK TABLES;
This re-enables the write activity

configuring the slave(www2)
1. Edit /etc/my.cnf
2. Make sure that the following lines are present in the [mysqld] section:
server-id=2
3. untar the file master.tar.gz in /usr/local/mysql/var/ (take a backup of the existing files and delete them)
4. restart mysql
5. run: mysql -u root -p
mysql > CHANGE MASTER TO MASTER_HOST=’www1′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’replicate’,
MASTER_LOG_FILE=’www1-bin.051′,
MASTER_LOG_POS=702;
mysql > START SLAVE;

Replication should be running now.

Making a PHP Site on Linux Work with a Microsoft SQL Server Database

Making a PHP Site on Linux Work with a Microsoft SQL Server Database
by David Perrin

A recent project at my current employer called for creating a new web frontend to an existing MSSQL database. My boss, having created a sophisticated MySQL and PHP-driven black diamond web site in PHP was enthused about the prospects of further web development with PHP. He suggested trying to get PHP on a Linux box to connect to a MSSQL database server.

An attempt at this task made months before ended in frustration. This time, after nibbling away at the task for a couple of days on a standard Red Hat system, success was had. Here’s how.

Similar to Windows, one method of connecting to a MSSQL database is through an ODBC DSN (open database connection, data source name). The ODBC DSN specifies a MSSQL driver to make the connection to the database. Recent versions of Red Hat include the utility for creating the ODBC DSN, but not the driver.

The driver chosen and discussed in this article is a FreeTDS driver. TDS (tabular datastream) is a protocol used by Sybase and MSSQL. This driver enables the Linux machine to connect to the MSSQL server.

Once the driver is installed, you can configure an ODBC connection on your Linux machine to use the driver, which then allows a connection to MSSQL. Start by downloading and saving the FreeTDS driver.

[root@localhost]# lynx
http://ibiblio.org/pub/Linux/ALPHA/freetds/freetds-0.60.tgz

Next, uncompress, configure and make the FreeTDS driver.

[root@localhost]# tar xvfz freetds-0.60.tgz
[root@localhost]# cd freetds-0.60
[root@localhost]# ./configure –with-tdsver=7.0 –with-unixodbc

su to root if you are not already root.

[root@localhost]# make
[root@localhost]# make install
[root@localhost]# make clean

Now test the ability of FreeTDS to connect to your MSSQL server:

[root@localhost]# /usr/local/bin/tsql -S -U

Password:

With luck, you’ll see the following prompt

1>

Then, use Ctrl-C to exit.

If the tsql command doesn’t return the 1> prompt, verify that you can get to your MSSQL server with

[root@localhost]# telnet 1433

If you’re able to telnet to port 1433, try opening the Microsoft Query Analyzer. Use the login combination you tried above to verify that a user name and password combination exists for your SQL server.

Once you can get the 1> prompt from tsql, we can configure the TDS driver and make the ODBC connection.

[root@localhost]# cd /usr/local/etc

From /usr/local/etc/, edit freetds.conf. At the end of this file, add an entry something like this:

[TDS]
host = mssql.serverhost.com
port = 1433
tds version = 7.0

Set Up the ODBC Data Source
Red Hat comes with a graphic interface tool called ODBCConfig. We’ll use it to set up our DSN.

From KDE, select K -> System -> ODBCConfig

From GNOME, select G -> Programs -> System -> ODBCConfig

Click on the Drivers Tab and click Add. The window should contain the following data:

Name: TDS
Description: v0.60 with Protocol v7.0
Driver: /usr/local/lib/libtdsodbc.so
Setup: /usr/lib/libtdsS.so
FileUsage: 1

The rest can be blank. Click the check-mark in the upper left-hand corner. Select the TDS driver and click OK.

Name: MSSQLServer
Description: TDS MSSQL (description isn’t important)
Servername: mssql.serverhost.com
UID: sa
PWD:
Port: 1433

Then click the System DSN tab and select Add. Test out the unixODBC connection with:

[root@localhost]# isql -v MSSQLServer username password
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> use Northwind
0 rows affected
SQL> SELECT TOP 1 CategoryName from Categories
+——————————-+
| CategoryName |
+——————————-+
| Beverages |
+——————————-+
1 rows affected
SQL> quit

Now to test it using PHP, put this page in a web-viewable directory and try to view it from the browser.

— begin odbctest.php—
// connect to DSN MSSQL with a user and password
$connect = odbc_connect(“MSSQLServer”, “username”, “password”) or die
(“couldn’t connect”);

odbc_exec($connect, “use Northwind”);

$result = odbc_exec($connect, “SELECT CompanyName, ContactName ” .
“FROM Suppliers”);

while(odbc_fetch_row($result)){
print(odbc_result($result, “CompanyName”) .
‘ ‘ . odbc_result($result, “ContactName”) . “
\n”);
}

odbc_free_result($result);
odbc_close($connect);

— end odbctest.php —

Should a SQL statement contain an error, PHP will return a cryptic, incomprehensible warning message via the driver. In debugging, it has been helpful to echo out the offending queries.

The web application that instigated this setup is now in its third month of production and is performing quite well.