Migrate to new server and get SQL error
6 posts by 3 authors in: Forums > CMS Builder
Last Post: January 26, 2015 (RSS)
By benedict - November 2, 2014
Hi guys,
We migrated a lot of cmsBuilder accounts to a new server without issue, but one account is throwing issues on the website, saying:
Warning: Cannot modify header information - headers already sent by (output started at /home/jmcpher/public_html/about-mcpherson-wines.php:32) in /home/jmcpher/public_html/cmsAdmin/lib/database_functions.php on line 634 There was an error creating the list field ''. MySQL Error: 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 'range, year DESC LIMIT 0, 999' at line 1
Any ideas what this might be in that file (/home/jmcpher/public_html/cmsAdmin/lib/database_functions.php) on line 634 that I can fix?
Cheers!
P.S. I had to change the DNS back to the old server in the mean time, so I can not show you a live example.
Hey Benedict,
It sounds like CMS Builder is trying to create an empty list field for some reason.
Could you give me a bit more detail about when this error appears? Is it when you edit a particular record in the accounts section? Or is this when you're trying to set up the CMS Builder on the new server?
Thanks!
Greg
PHP Programmer - interactivetools.com
By benedict - January 24, 2015
Sorry I haven't come back to you sooner - the client held off the server migration again until now. So I have tracked down the problem to one issue:
This is a Wine List page that shows the wines from a particular range.
The code that worked fine on the old server, but not this one is:
// load records
list($winesRecords, $winesMetaData) = getRecords(array(
'tableName' => 'wines',
'where' => "range = '". intval($rangeRecord['num']) ."'" ,
));
When I remove this code, the page loads at least, without the wine list. When I include the code, I get this (when trying to load the wine range MWC which is range #11):
MySQL Error: 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 'range = '11')' at line 3
FInal piece of info - the WInes table pulls the Ranges from the Ranges table using the GET OPTIONS FROM DATABASE option, values=num, labels=title.
Detail pages all present fine as well.
Hi Bendict,
I'm wondering if the new myslq server you're using doesn't allow the word range to be used without it being declared as a table name. Try changing your code to this:
// load records list($winesRecords, $winesMetaData) = getRecords(array(
'tableName' => 'wines',
'where' => "`range` = '". intval($rangeRecord['num']) ."'" ,
));
So I've added apostrophes around the range table name (note: you have to use the apostrophe below the esc key on the keyboard, as this is what MySQL uses for table names.)
If that doesn't work, could you add the debug SQL command to the getRecords statement:
// load records list($winesRecords, $winesMetaData) = getRecords(array(
'tableName' => 'wines',
'debugSql => true,
'where' => "`range` = '". intval($rangeRecord['num']) ."'" ,
));
Then copy the output that is created into a post so I can see exactly what MySQL statement is being created.
Cheers,
Greg
PHP Programmer - interactivetools.com
By Dave - January 26, 2015
Just for clarity, those characters are called `backticks` and you want to make sure you get this one ( ` ) which is a backtick character, not this one ( ' ) which is an apostrophe.
http://en.wikipedia.org/wiki/Grave_accent#Use_in_programming
:
interactivetools.com
By benedict - January 26, 2015
Thanks Greg,
That did the trick - as always I am indebted.
Cheers,
Benedict