Execute complex SQL from file with PHP mysqli

Question

I have a set of SQL files with complex SQL (droping/creation tables and stored procedures with delimiters changes, droping/creation tables, altering tables etc). I try to run this files like:

$sql = file_get_contents("sql/filename.sql");
if (!$mysqli->multi_query($sql)) {
    echo("Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error);
    exit();
}
do {
    if ($res = $mysqli->store_result()) {
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());

Some files are processed normally, but on other files with delimiter changes and stored procedures creation I received an error:

Multi query failed: (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 $$

DROP PROCEDURE IF EXISTS alter_members_table $$
CREATE PROCEDUR' at line 1

If I copy content of this SQL file and execute it in phpMyAdmin - all works fine, so I am sure that SQL is correct.

What is wrong and how can I fix this?

UPDATE... One of the SQL files generated this error:

DELIMITER $$

DROP PROCEDURE IF EXISTS procedure_name $$
CREATE PROCEDURE procedure_name()
BEGIN
    IF NOT EXISTS ((
        SELECT * FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND COLUMN_NAME = 'column_name'
          AND TABLE_NAME = 'table_name'
    ))
    THEN
        ALTER TABLE `table_name`
        ADD COLUMN `column_name` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
        AFTER `other_column_name`;
    END IF;
END $$

CALL procedure_name() $$

DELIMITER ;

Why I use stored procedure here, this is a part of the plugin installation script, it must execute few SQL files to prepare the DB. I have surfed for the solution, how to create new column only if it still not exists. I have found only procedural solution.

Please see http://stackoverflow.com/questions/345637/php-multiple-sql-queries-in-one-mysql-query-statement/345712#345712 — Alex Blex, May 12 '16 at 11:18
I think it would be useful if you showed us an example of the queries that fail. Even if they are not the real code, but exibit the same code style and syntax — RiggsFolly, May 12 '16 at 11:22
Also to run many queries via multi_query each is run as a unique query, just in a set, so that would probably work coded as `DROP PROCEDURE IF EXISTS alter_members_table;` — RiggsFolly, May 12 '16 at 11:24
multi_query allows you to run multiple queries, but they are still delimited by semi-colon, so you still have the problem that if your procedure contains multiple statements, it's going to parse them separately; not as part of the procedure creation statement. — UberDoodles, May 12 '16 at 11:37
Consider moving conditional part of the installation to php. It is trivial to check if the column exists and execute the alter statement otherwise. — Alex Blex, May 12 '16 at 14:47
@AlexBlex Yes, I know how to do this with PHP. But I prefer to hold all DB connected logic in the SQL if it's possible. So I ask if I can do this. — Dmytro Zarezenko, May 12 '16 at 14:55
Well, creating a stored procedure for one-time deployment operation have questionable advantage. Any way it is known limitation, and the best thing you can do is to make a `system` call to use native mysql client to process files with *DB connected logic*. — Alex Blex, May 12 '16 at 15:14
@AlexBlex Ok, but when I execute this SQL via phpMyAdmin, all works, how phpMyAdmin do this, it's a PHP script as well? Maybe you know. — Dmytro Zarezenko, May 12 '16 at 21:48
They are using [own SQL Parser](https://github.com/phpmyadmin/sql-parser). You can use it as well, but be pragmatic considering efforts you put into it vs value it adds. Somewhy I believe you don't want to go down this rabbit hole. — Alex Blex, May 13 '16 at 09:42

score 0 · Answer 1 · answered May 12 '16 at 11:33

0

I believe the statement to change the batch delimiter is only recognized by the MySQL client. It is not a valid SQL statement when executing via other interfaces.

Therefore, I'm not sure it's possible to do what you want to achieve.

Why do you need your PHP script to create and drop stored procedures? Why can't the procedures exist permanently on the DB?

answered May 12 '16 at 11:33

UberDoodles

618
5
13

The answer is that I don't believe it is possible. Surely, the answer to a question which has no answer is that there is no answer. – UberDoodles May 12 '16 at 11:59

Execute complex SQL from file with PHP mysqli

1 Answers1