PHP Drivers for MSSQL Issues

PHP Drivers for MSSQL Issues

I wanted to setup my Mac Book Pro to access the SQL Server RDS Instance on AWS.My intial thinking was just google on how to setup PHP Drivers on Mac OSX and follow the steps.

So I thought  it shouldn’t take more than 10 – 15 minutes. But it was not that straight forward and all the links and results from google were outdated. None of the articles explanied what errors you might get and how to troubleshoot.

The following link explained how to setup mack ports and install mssql server drivers through mac ports.

http://bill.butler.net/2012/04/mssql-driver-for-osx-and-php.html

Instaled mac port through the following link.

http://www.macports.org/install.php

Once mac ports were instlled,  I ran the following command to install php5-mssql drivers through this after doing the selfupdate.

$ port selfupdate

$ port install php5-mssql

After doing this , I noticed the errors in log and versions doesn’t match.

My php.ini was loaded from /etc/php.ini

I added the Dynamic Extensions for mssql.so. You can notice the extension was older version and it wa compatible with the PHP Version I had.

So I had to uninstall php5-mssql and reinstall with php54-mssql because I had php 5.4 running in my machine. I made changes to php.ini to include the extension  and reference the latest mssql.so location,

$ sudo port uninstall php5-mssql

$ sudo port install php54-mssql

;;;;;;;;;;;;;;;;;;;;;;

; Dynamic Extensions ;

;;;;;;;;;;;;;;;;;;;;;;

;extension=/opt/local/lib/php/extensions/no-debug-non-zts-20090626/mssql.so

extension=/opt/local/lib/php54/extensions/no-debug-non-zts-20100525/mssql.so

  

I modified the freetds.conf file. Location of the file can be found using the ” tsql – C ” command.

$ tsql -C

Compile-time settings (established with the “configure” script)

Version: freetds v0.92.405

freetds.conf directory: /opt/local/etc/freetds

MS db-lib source compatibility: no

Sybase binary compatibility: no

Thread safety: yes

iconv library: yes

TDS version: 5.0

iODBC: no

unixodbc: no

SSPI “trusted” logins: no

Kerberos: no

Contents fo freetda.conf file 

# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage “man freetds.conf”.

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting ‘text size’ to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0

[KAIZENAWS]
host = sqlserver.cmph1rucllhq.us-west-2.rds.amazonaws.com
port = 1433
tds version = 7.0

I added reference to SQL Server. In the past I didn’t have tds version Number. It throwed the following error.

tsql -S KAIZENAWS -U Kaizen 

Password:

locale is “en_US.UTF-8”

locale charset is “UTF-8”

using default charset “UTF-8”

Error 20017 (severity 9):

Unexpected EOF from the server

Error 20002 (severity 9):

Adaptive Server connection failed

There was a problem connecting to the server

After adding the ” tds version = 7.0 ” on freetds.conf file, I was able to connect.  

Errors:

[Sat Apr 12 15:27:33 2014] [error] [client 127.0.0.1] PHP Warning:  mssql_connect(): Unable to connect to server: KAIZENAWS in /Users/Raju/development/Kaizen/Work/databases-aws.php on line 9 

MySQL Sock Related Errors

mysql.sock default location:

[Sat Apr 12 17:19:22 2014] [error] [client 127.0.0.1] PHP Warning:  mysqli::mysqli(): (HY000/2002): No such file or directory in /Users/Raju/development/Kaizen/classes/Database.class.php on line 33

Directive

Local Value

Master Value

mysqli.allow_local_infile

On

On

mysqli.allow_persistent

On

On

mysqli.default_host

no value

no value

mysqli.default_port

3306

3306

mysqli.default_pw

no value

no value

mysqli.default_socket

/tmp/mysql.sock

/tmp/mysql.sock

mysqli.default_user

no value

no value

mysqli.max_links

Unlimited

Unlimited

mysqli.max_persistent

Unlimited

Unlimited

mysqli.reconnect

Off

Could not connect: No such file or directory

I didn’t have the mysql.sock file on default location. It throw the error. I changed the mysqli.default_socket location to /tmp/mysql.sock on php.ini and I was able to sucessfully connect. 

Another Issue I had a while ago in Amazon AMI Instance. 

I have spent more than 3 hours trying to figure out why website can’t connect to SQL Server on the other hand connecting from localhost was successful. 

The problem was I made changes to odbc.ini file at /etc/ location but php.ini had different location /etc/php.d/odbc.ini.

Install three things through yum package manager 

php-mssql / freetds / freetds-devel 

Make changes to freetds.conf and odbc.ini files.

Changes I made:

[KAIZENAWS]

        host = RDSSQLNAME.CMPH1RUCLLHQ.US-WEST.RDS.AMAZONAWS.COM

        port = 1433

PHP Driver for MSSQL

yum install php-mssql

yum install freetds

yum info freetds

yum info php-mssql

yum info freetds-devel

sudo vi freetds.conf 

 sudo vi odbc.ini

Changes I made to the file Location of the file

: /etc/odbc.ini

[KAIZEN] 

host = RDSSQL.US-WEST-2.RDS.AMAZONAWS.COM 

port = 1433

Loaded Configuration :

/etc/php.d/odbc.ini

Freetds location : /etc/freetds.conf [root@ip-10-255-21-186 etc]# sudo vi freetds.conf

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $

#

# This file is installed by FreeTDS if no file by the same

# name is found in the installation directory.

#

# For information about the layout of this file and its settings,

# see the freetds.conf manpage “man freetds.conf”.

# Global settings are overridden by those in a database

# server specific section

[global]

        # TDS protocol version

;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes

        # (setting this to /tmp is insecure on a multi-user system)

;       dump file = /tmp/freetds.log

;       debug flags = 0xffff

        # Command and connection timeouts

;       timeout = 10

;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client

        # is trying to allocate a huge buffer for a TEXT field.

        # Try setting ‘text size’ to a more reasonable limit

        text size = 64512

# A typical Sybase server

[egServer50]

        host = symachine.domain.com

        port = 5000

        tds version = 5.0

# A typical Microsoft server

[egServer70]

        host = ntmachine.domain.com

        port = 1433

        tds version = 7.0

[KAIZENAWS]

        host = KAIZENSQL.CMPH1RUCLLHQ.US-WEST-2.RDS.AMAZONAWS.COM

        port = 1433

2014/02/21 03:13:46 [error] 14228#0: *812 FastCGI sent in stderr: “PHP message: PHP Fatal error:  Call to undefined function mssql_connect() in /usr/share/nginx/www/kaizen/Work/Environments/databases-aws.php on line 45” while reading response header from upstream, client: 97.64.176.4, server: i90runner.com, request: “GET /Work/Environments/databases-aws.php HTTP/1.1”, upstream: “fastcgi://127.0.0.1:9000”, host: “i90runner.com”

Configuration File (php.ini) Path/etc

Loaded Configuration File/etc/php.ini

Scan this dir for additional .ini files/etc/php.d

Additional .ini files parsed/etc/php.d/curl.ini, /etc/php.d/dom.ini, /etc/php.d/fileinfo.ini, /etc/php.d/gd.ini, /etc/php.d/imap.ini, /etc/php.d/json.ini, /etc/php.d/mbstring.ini, /etc/php.d/mysql.ini, /etc/php.d/mysqli.ini, /etc/php.d/odbc.ini, /etc/php.d/pdo.ini, /etc/php.d/pdo_mysql.ini, /etc/php.d/pdo_odbc.ini, /etc/php.d/pdo_sqlite.ini, /etc/php.d/phar.ini, /etc/php.d/posix.ini, /etc/php.d/soap.ini, /etc/php.d/sqlite3.ini, /etc/php.d/sysvmsg.ini, /etc/php.d/sysvsem.ini, /etc/php.d/sysvshm.ini, /etc/php.d/wddx.ini, /etc/php.d/xmlreader.ini, /etc/php.d/xmlwriter.ini, /etc/php.d/xsl.ini, /etc/php.d/zip.ini 

It should’ve been pretty straight forward driver install it took lot longer because of misleading and out of date informations and trying to troubleshoot errors one after another. Hope this helps.

One comment on “PHP Drivers for MSSQL Issues

Leave a Reply

Your email address will not be published. Required fields are marked *

*