SQL error when running command from php file

Question

I need to check if a column exists before trying to add it if not present. Following information found in this post: mysql ALTER TABLE if column not exists

I added the following to my zen cart php file

$db->Execute("DROP PROCEDURE IF EXISTS `gdpr_accept`;
              DELIMITER //
              CREATE PROCEDURE `gdpr_accept`()
              BEGIN
              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
              ALTER TABLE " . TABLE_CUSTOMERS . " ADD `gdpr_accept` TINYINT(1) NOT NULL DEFAULT '0' AFTER `COWOA_account`;
              END //
              DELIMITER ;
              CALL `gdpr_accept`();
              DROP PROCEDURE `gdpr_accept`;");

However, I get the following error logged

[05-May-2018 19:37:02 Europe/Paris] PHP Fatal error: 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 'DELIMITER // CREATE PROCEDURE gdpr_accept() BEGIN ' at line 2 :: DROP PROCEDURE IF EXISTS gdpr_accept; DELIMITER // CREATE PROCEDURE gdpr_accept() BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; ALTER TABLE customers ADD gdpr_accept TINYINT(1) NOT NULL DEFAULT '0' AFTER COWOA_account; END // DELIMITER ; CALL gdpr_accept(); DROP PROCEDURE gdpr_accept; ==> (as called by) /Applications/MAMP/htdocs/gdpr/stOrm-PTO-fluSh/includes/init_includes/init_reconsent_popup_setup.php on line 72

However, when I run the same command in phpMyAdmin, after confirming that i want to "DROP PROCEDURE IF EXISTS gdpr_accept" it runs perfectly.

Note: If i attempt to split up the query, it will fail at

$db->Execute("DELIMITER //");

with this error: 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 'DELIMITER //' at line 1

Is there a reason why this SQL command can't be done via php, and is there a way round it?

Why is this a stored procedure? Normally things like this are stored in a database migration system. — tadman, May 05 '18 at 17:47
Is this relevant? Some versions of php / PDO don't allow more than one SQL statement in each Execute operation. https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — O. Jones, May 05 '18 at 17:49
It's part of an auto installation script for a zen cart module. We need to confirm the field doesn't already exist in the table when the installer runs. — Steve Price, May 05 '18 at 17:49

score 1 · Accepted Answer · answered May 05 '18 at 18:09

DELIMITER is a builtin command in the mysql client, it is not a statement that the MySQL server recognizes. You can't run a DELIMITER statement using PHP.

But you don't need to use DELIMITER. That's only to help the mysql client tell where your CREATE PROCEDURE statement ends, because a procedure usually contains semicolon characters, and otherwise it would be ambiguous which semicolon was part of the procedure body versus the end of the procedure definition.

You should run one statement at a time from PHP. Then it's not ambiguous.

$db->Execute("DROP PROCEDURE IF EXISTS `gdpr_accept`");
$db->Execute("CREATE PROCEDURE `gdpr_accept`()
              BEGIN
              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
              ALTER TABLE " . TABLE_CUSTOMERS . " ADD `gdpr_accept` TINYINT(1) NOT NULL DEFAULT '0' AFTER `COWOA_account`;
              END");
$db->Execute("CALL `gdpr_accept`()");
$db->Execute("DROP PROCEDURE `gdpr_accept`;");

By the way, there's no reason you need a procedure for this task, since you just drop the procedure when you're done anyway. It would be much simpler to just run the ALTER TABLE directly:

$db->Execute("ALTER TABLE " . TABLE_CUSTOMERS . 
    " ADD `gdpr_accept` TINYINT(1) NOT NULL DEFAULT '0' AFTER `COWOA_account`");

I see a lot of questions on Stack Overflow from people who seem to think it's a good idea to use stored procedures in MySQL. While stored procedures are common in Oracle and Microsoft SQL Server, they're more trouble than they're worth in MySQL. I avoid using stored procedures in MySQL.

" It would be much simpler to just run the ALTER TABLE directly:" But surely that would throw an error if the column `gdpr_accept`already exists? — Steve Price, May 05 '18 at 18:17
Yes. Catch the error and disregard it (if it's an error 1060 duplicate column name). Errors are not necessarily a bad thing, but you have to handle them appropriately. — Bill Karwin, May 05 '18 at 18:18

SQL error when running command from php file

1 Answers1