0

I am starting to use Codeception for a large existing website.

I want to use a dump file to restore the database. Our database makes use of procedures, triggers, functions and views. On cleanup, codeception only removes tables and triggers.

However, I get errors when it tries to create Triggers and Functions (and possibly the same errors when creating procedures and view, I haven't got that far)

This is the error message:

[Codeception\Exception\Module] (Exception in Db) 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 DELIMITER // CREATE TRIGGER players_weekly_delete AFTER DELETE ON players_wee at line 1

I have created a user for the test database with all privileges, and it also has the global SUPER privilege.

When I use the same dump and run it in HeidiSQL it works perfectly.

Does anyone have an idea why I get this error when trying to populate the database with the dump?

Or alternatively, does anyone know a way to make codeception remove only tables, and leave the triggers in place?

vivekpansara
  • 895
  • 1
  • 6
  • 14
sunomad
  • 1,693
  • 18
  • 23
  • See http://stackoverflow.com/questions/10259504/delimiters-in-mysql -- `DELIMITER` is _not_ a MySQL keyword, but rather a function of the CLI MySQL client (and some other clients who implement it the same way). – Michael Berkowski Jun 02 '15 at 13:37
  • I don't know how Codeception works, but you may be able to create the trigger directly without needing to worry about the delimiter. – Michael Berkowski Jun 02 '15 at 13:38
  • Of course, HeidiSQL added the DELIMITER when creating the dump. Would you suggest removing the DELIMITER after creating the dump, or look for a way to dump the database with some tool that not adds a DELIMITER? – sunomad Jun 02 '15 at 14:09
  • Remove the DELIMITER statements from the dump and try it out with Codeception. Does HeidiSQL have an option to omit those? – Michael Berkowski Jun 02 '15 at 14:21
  • No, HeidiSQL does not have that option. I will try to remove them, and I'll let you know about the result tomorrow. Thanks! – sunomad Jun 02 '15 at 14:30
  • It looks like removing the DELIMITER statements does not work. Here is the error I get: Syntax error or access violation: 1064 Y ou have an error in your SQL syntax; check the manual that corresponds to y our MySQL server version for the right syntax to use near '' at line 4 SQL query being executed: CREATE DEFINER=`oulala-su`@`%` FUNCTION `getAdminPageLink`(`pageID` INT(11) , `adminUserID` INT(11), `title` VARCHAR(255), `key` VARCHAR(255), `value` TEXT, `method` VARCHAR(20) ) RETURNS text CHARSET latin1 DETERMINISTIC BEGIN DECLARE url TEXT – sunomad Jun 03 '15 at 08:49

0 Answers0