1

I am trying to run the following SQL string in PDO. I can confirm that the code does execute in MySQL directly but running it in PHP PDO it throws an error.

I understand that the DELIMITER $$ is not available on the PDO interface. And according to how to execute mysql command DELIMITER it should be fine to just leave the delimiter out of the query.

QUERY STRING:

CREATE DEFINER=CURRENT_USER TRIGGER `M5_tblMVTransactionVat_VatInsert` AFTER INSERT ON `M2_tblVatRevisions`
FOR EACH ROW BEGIN
  UPDATE M5_tblMVTransactionVat
  SET M5_tblMVTransactionVat.vatID = 1;

END

CREATE DEFINER=CURRENT_USER TRIGGER `M5_tblMVTransactionVat_VatUpdate` AFTER UPDATE ON `M2_tblVatRevisions`
FOR EACH ROW BEGIN
  UPDATE M5_tblMVTransactionVat
  SET M5_tblMVTransactionVat.vatID = 2;

END

Run via either a simple PDO::exec or PDOStatement::prepare (with PDO::ATTR_EMULATE_PREPARES = 1) causes an error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'CREATE
DEFINER=CURRENT_USER TRIGGER `M5_tblMVTransactionVat_VatUpdate` AFTER
UPD' at line 8

I have confirmed that running only one Create Trigger at a time via PDO does not cause the same error and the trigger is created successfully. .

How do I execute multiple create trigger queries on a PDO connection?

Eaton Emmerich
  • 452
  • 4
  • 17

2 Answers2

2

Don't execute multiple statements at a time.

There is no reason to do so, and it causes problems if you try.

The former Director of Engineering for MySQL once told me, there is no reason for multi_query to exist, it can only do harm (that's my paraphrasing).

It's simpler and safer to execute each CREATE TRIGGER statement in its own call to PDO::exec().

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Okay, so then if I wanted to apply a large amount of create triggers on a database via PHP PDO as an database initialization sequence, it would require me to put them into different files, or write my own delimiter/parser? – Eaton Emmerich Jun 21 '18 at 15:10
  • Yes. How do you think the `mysql` client does it? – Bill Karwin Jun 21 '18 at 15:11
  • I thought it was via the MySQL CLI, which has a builtin parser? Edit: now that you mention it I see things like DELETE VIEWS are listed as separate statements on the history. – Eaton Emmerich Jun 21 '18 at 15:14
  • The client has a very simple parser: It reads until the DELIMITER string, then it sends what it has read to the server to be executed as a single statement. Then it reads until the next DELIMITER and repeats the process. – Bill Karwin Jun 21 '18 at 15:16
  • Just seemed silly to have have to write my own one if its something so generally used. – Eaton Emmerich Jun 21 '18 at 15:17
  • 1
    Then write it as a reusable PHP package to run SQL scripts, and release it as an open-source product, so other people don't have to reinvent this wheel. So far, I've never seen one that actually worked. – Bill Karwin Jun 21 '18 at 15:20
1

You want a bunch of initialization stuff in a file? Then why bother with PHP; do

mysql ... -e "source foo.sql"

Ok, you do want PHP to run things? Then do this from php:

execcute('mysql ... -e "source foo.sql"');

But, you had better button down the hatches -- this would be a wonderful way for a hacker to wreak havoc.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • seems like this is an easier approach than writing my own client to interpret an SQL file. Thanks! It's a solution to my problem but not 'technically' a answer to my question. So I will be selecting the 'You can't' answer as the answer from bill-karwin – Eaton Emmerich Jun 22 '18 at 14:14
  • @EatonEmmerich - Many questions on this forum fail to state the _original_ problem, but only state the problem that occurred just before hitting a brick wall. (I do like, and agree with, Bill's answer.) – Rick James Jun 22 '18 at 14:54