MySQL PDO import schema fails with no error

Question

Here is my code:

$dbh = new PDO('mysql:host=localhost;dbname=diffsrc', 'root', '******', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);


$path = 'C:/dumps/test.sql';

$sql = file_get_contents($path);

$dbh->query($sql);
echo 'OK';

and the dump (test.sql) is:

-- phpMyAdmin SQL Dump
-- version 4.1.13
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 21, 2014 at 09:25 AM
-- Server version: 5.5.23
-- PHP Version: 5.4.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `diffdst`
--

-- --------------------------------------------------------

--
-- Table structure for table `dfgxfcghfdxgh`
--

DROP TABLE IF EXISTS `dfgxfcghfdxgh`;
CREATE TABLE IF NOT EXISTS `dfgxfcghfdxgh` (
  `asdf` int(11) NOT NULL,
  `asdffg` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Triggers `dfgxfcghfdxgh`
--
DROP TRIGGER IF EXISTS `zzz`;
DELIMITER //
CREATE TRIGGER `zzz` BEFORE INSERT ON `dfgxfcghfdxgh`
 FOR EACH ROW begin
INSERT INTO zz (z1,z2)

      values (1, 2);
end
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `zz`
--

DROP TABLE IF EXISTS `zz`;
CREATE TABLE IF NOT EXISTS `zz` (
  `z1` int(11) NOT NULL,
  `z2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

And the problem is when I execute that PHP script, I see 'OK'. So no exceptions. But when I take a look into created DB: no trigger and no table zz. looks like execution stops on trigger.

Even if I put in this dump any other errors, such as wrong keywords etc. It just silently fails without exc eption or other error explanation. If I ran simple sql such as: select * from none - exception apears

You can explicitly tell PDO to throw exceptions when it fails `$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);`. I tested the SQL Dump and that worked OK for me. — Alex.Ritna, May 21 '14 at 07:03
Doing some searching / reading, threads like [this one](http://stackoverflow.com/questions/147821/loading-sql-files-from-within-php) sound like it's a limitation in the mysql extensions, and you'll need to execute the statements individually. The second answer on that thread has a potential solution for you. — Alex.Ritna, May 21 '14 at 07:05
@Alex.Ritna OP is already setting the error mode in the PDO constructor — Phil, May 21 '14 at 07:06
@Alex.Ritna it is already done by [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ] — Subdigger, May 21 '14 at 07:12
Well there is just a problem with pdo, when you run multiple queries at once and there is an error in one of them (but not first one). Here you can get some info about solving issue: http://stackoverflow.com/questions/23247553/how-can-i-get-an-error-when-running-multiple-queries-with-pdo — MSadura, May 21 '14 at 07:14
@Phil Yeah I noticed that as soon as I reloaded this page to read the comment replies. My mistake. At least you have your answer now though! — Alex.Ritna, May 21 '14 at 07:37
so the best solution is here http://stackoverflow.com/questions/4027769/running-mysql-sql-files-in-php/4028289#4028289 — Subdigger, May 21 '14 at 08:21

MySQL PDO import schema fails with no error

0 Answers0