Forums | Mahara Community
Error importing SQL from phpMyAdmin backup
19 June 2013, 12:10 AM
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:
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 //
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 //
#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;
' 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 PM
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.
A post by Deleted user was deleted