19 June 2013, 0:10

Hi, we suffered a systems failure and all I have is a phpMyAdmin dump of the Mahara database.

When I try to import the database through either phpMyAdmin or mysql at the command line I get the following error:

Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 12
STR: //
SQL: 
DELIMITER //
CREATE TRIGGER `update_unread_delete_trigger` AFTER DELETE ON `notification_internal_activity`
 FOR EACH ROW BEGIN
                    
                IF OLD.read = 0 THEN
                    UPDATE "usr" SET unread = unread - 1 WHERE id = OLD.usr;
                END IF;
                END
//

SQL query:

DELIMITER // CREATE TRIGGER `update_unread_delete_trigger` AFTER DELETE ON `notification_internal_activity` FOR EACH ROW BEGIN IF OLD.read = 0 THEN UPDATE "usr" SET unread = unread - 1 WHERE id = OLD.usr; END IF; END //

MySQL said:

#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 '"usr" SET unread = unread - 1 WHERE id = OLD.usr;
                END IF;
      ' at line 5

It looks like the same error that was discussed on this thread: https://mahara.org/interaction/forum/topic.php?id=4839

Unfortunately, I can't go in and dump the database from the command line as the server died and all I have is the dump made through phpMyAdmin.

Can anyone point me in the right direction to fix the broken SQL and get it imported?

20 June 2013, 12:07

Hi Richard,

You've got a general MySQL/phpMyAdmin problem rather than a Mahara-specific problem, so you might have better luck asking in a more general MySQL or phpMyAdmin forum (maybe stackexchange?).

But my guess is that phpMyAdmin exported it using the double-quote " character to escape table names, whereas MySQL normally expects the back-tick ` character.

I normally use Postgres rather than MySQL, but if I'm reading the MySQL manual correctly, you should be able to set a session variable to activate ANSI_QUOTES mode, which will make it accept double-quotes. ( http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes )

Try doing "SELECT @@SESSION.sql_mode;" to find out if you have any session-specific SQL modes currently set. Then add to the top of your dump SQL file this command: "SET SESSION sql_mode='ANSI_QUOTES';" If the first query returned anything other than an empty string, you'll need to add those other session SQL modes to the list in the second query as well.

Good luck!

Cheers,
Aaron