Forums

backup throws mysql error

Permalink 1 user found helpful
I'm getting this error when i try and back up the database from the dashboard.

An unexpected error occurred.
mysql 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 ''web246-studio'' at line 1] in
EXECUTE("SHOW TABLES FROM web246-studio")

i tried putting the database name in single quotes but that didnt work. if i log into mysql using phpmyadmin, select the database and try and issue the same sql i get the same error. if i just type show tables i get a list of tables. is this a privs problem on shared hosting.
 
peteswat replied on at Permalink Reply
had to change the line in concrete\libraries\backup.php

from
$arr_tables = $db->getCol("SHOW TABLES FROM " . DB_DATABASE);

To

$arr_tables = $db->getCol("SHOW TABLES FROM `" . DB_DATABASE . "`");

should i have put my own copy in my top level/libraries ???
nteaviation replied on at Permalink Reply
nteaviation
That may be a bug in c5, not sure. I would bet the hyphen ("-") is causing the invalid synatax. MySQL is probably trying to evaluate it as a minus sign or something. MySQL is going to need the database name wrapped in a delimiter (either double qoute or perferably single quotes) to handle special characters. I imagine phpMyAdmin is automatically formatting you SQL query prior to submitting to MySQL, hence you don't get an error.
peteswat replied on at Permalink Reply
i added a single back blip around it to fix. see previous response.
thanks

Pete
nteaviation replied on at Permalink Reply
nteaviation
Yea, i saw that :) Good catch! I wonder if this should be reported as a possible bug. I wonder what other (if any) special characters will cause a problem.
ScottC replied on at Permalink Reply
ScottC
this is absolutely a bug.

any string should be escaped.

-Scott
RodneyC5 replied on at Permalink Reply
This affects both database names AND table names too - so any extra tables you have added should not have a 'dash' (-) in the table name.
RodneyC5 replied on at Permalink Reply
If you need the fix this error where the table name contains a dash, add the following (first line is added, second line already existing)

$bkuptable = "`".$bkuptable."`";
$str_backupdata .= "DROP TABLE IF EXISTS $bkuptable;\n\n";


$table = "`".$table."`";
$this->str_tablename = $table;