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.