PHP mssql backup script runs successfully in interactive mode. Fails with PDO error when executed as script

Question

I am running a script from cron that backs up an MSSQL database to disk.

The script runs successfully when I copy, and paste the code into an interactive php shell - ie "php -a"

The script fails when it is being executed from the command line. It doesn't matter if it's run directly, or as an input file "php -f" or from cron. I can run the script as root, or an unprivileged user. The results are the same. It appears to fail because the PDO driver does not load in that context.

edit: successful interactive, and failed non-interactive tests are using the same php.ini file

Any insight would be much appreciated.

Thanks.

ERROR:

    Fatal error: Uncaught Error: Class 'PDO' not found in /usr/local/username/scripts/backupmssqldb.php:4
    Stack trace:
    #0 {main}
    thrown in /usr/local/username/scripts/backupmssqldb.php on line 4

SCRIPT:

    #!/usr/bin/php
    <?php
    $msdb = new PDO("dblib:host=host;dbname=DATABASE","username",'password');$msdb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $msdb->prepare("
    DECLARE @path VARCHAR(500)
    DECLARE @name VARCHAR(500)
    DECLARE @pathwithname VARCHAR(500)
    DECLARE @time DATETIME
    DECLARE @year VARCHAR(4)
    DECLARE @month VARCHAR(2)
    DECLARE @day VARCHAR(2)
    DECLARE @hour VARCHAR(2)
    DECLARE @minute VARCHAR(2)
    DECLARE @second VARCHAR(2)
    SET @path = 'c:\sqldata\SQLBACKUPS\'
    SELECT @time   = GETDATE()
    SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
    SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
    SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
    SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
    SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
    SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))
    SELECT @name ='FILENAE' + '_' + @year + @month + @day + @hour + @minute + @second
    SET @pathwithname = @path + @name + '.bak'
    BACKUP DATABASE [DATABASE] TO DISK = @pathwithname WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10");
    $stmt->execute();

    //$stmt = $msdb->prepare("dbcc shrinkfile (DATABASE_Log,1)");
    //$stmt->execute();
    ?>

Output from phpinfo(); in script:

what unclear is in `Class 'PDO' not found` message? You got wrong include paths or so — Marcin Orlowski, Apr 07 '19 at 19:42
@MarcinOrlowski If I specify the same php.ini file, the result is the same. Executing non-interactively fails. Executing the script interactively succeeds. I do not feel the configuration is the problem. — Skye Bowen, Apr 07 '19 at 23:01
One thing to try: include a backslash infront of PDO like so `\PDO`. Aslo see https://stackoverflow.com/questions/14282011/php-use-class-in-global-namespace. — knot22, Apr 08 '19 at 01:18
@knot22 Thanks, I tried adding the back-slash, but no success. I think this has something to do with the SQL query itself. I placed the SQL query into a stored procedure, and am able to execute the procedure successfully with the same php code. — Skye Bowen, Apr 08 '19 at 20:34
Interesting. Are you able to use the approach of calling the sproc from PHP going forward or do you have to figure out how it get it to work using exclusively a PHP script? — knot22, Apr 08 '19 at 22:40
@knot22 My particular issue is solved. Going forward I am able to call the stored procedure, and my backups run successfully. Thank you. — Skye Bowen, Apr 09 '19 at 13:44

score 0 · Answer 1 · answered Apr 08 '19 at 22:55

You might try refactoring the T-SQL to see if that helps. Here is a one liner that gives the equivalent result of the original @pathwithname:

declare @pathwithname varchar(500) = 'c:\sqldata\SQLBACKUPS\FILENAME_' + format(getdate(), 'yyyyMMddHHmmss') + '.bak';
--select @pathwithname;

score 0 · Accepted Answer · answered Apr 12 '19 at 16:16

The script executes successfully as both regular user, and root user in interactive mode. So the original post is now resolved.

Creating a stored procedure that containing the SQL code I wish to execute, and then executing that stored procedure seems to work.

The script now fails to execute from cron as regular user, but succeeds as root user. This is not pertinent to the question, but may be related, so I am including as an FYI.

PHP mssql backup script runs successfully in interactive mode. Fails with PDO error when executed as script

2 Answers2