SMF Development > Fixed or Bogus Bugs

Syntax errors from SMF database backup.

(1/9) > >>

Antechinus:
As per this topic: http://www.simplemachines.org/community/index.php?topic=303006.0

The phpinfo.php for the server is located here.

All magic_quotes variables are turned off.

The account I am using is resticted to my domain and does not have root access.
Php is running as a module, whatever that means and if it makes any difference.

Problem: when backing up from SMF admin any apostrophes are being saved as \' instead of the correct version (for SQL) which is ''
This causes a syntax error for each and every apostrophe in the database, with consequent problems with broken links and dropped content if restoring the backup.
This applies to both 1.1.x and SMF 2 RC1 sites. I have tested it on both.

ETA: I can zip up some examples of backups and post them in the team boards if anyone wants to take a look at them for comparison. Full access to phpMyAdmin, etc is available too of course.

SleePy:
They shouldn't be saved as \'.
SMF is storing in the file as single quotes.

Is it like this?

--- Quote ---'\'Text\''

--- End quote ---

Can I see a sample set of the backup?

Antechinus:
Sure. Would you like a complete from one of the test sites? Very small. Only about 45 kb unzipped.

ETA: Here's an example from the calendar holidays table.


--- Code: ---# Dumping data in `smf_calendar_holidays`
#

INSERT INTO `smf_calendar_holidays`
(`ID_HOLIDAY`, `eventDate`, `title`)
VALUES (1, '0004-01-01', 'New Year\'s'),
(2, '0004-12-25', 'Christmas'),
(3, '0004-02-14', 'Valentine\'s Day'),
(4, '0004-03-17', 'St. Patrick\'s Day'),
(5, '0004-04-01', 'April Fools'),
(6, '0004-04-22', 'Earth Day'),
(7, '0004-10-24', 'United Nations Day'),
(8, '0004-10-31', 'Halloween'),
(9, '2004-05-09', 'Mother\'s Day'),
(10, '2005-05-08', 'Mother\'s Day'),
(11, '2006-05-14', 'Mother\'s Day'),
(12, '2007-05-13', 'Mother\'s Day'),
(13, '2008-05-11', 'Mother\'s Day'),
(14, '2009-05-10', 'Mother\'s Day'),
(15, '2010-05-09', 'Mother\'s Day'),
(16, '2011-05-08', 'Mother\'s Day'),
(17, '2012-05-13', 'Mother\'s Day'),
(18, '2013-05-12', 'Mother\'s Day'),
(19, '2014-05-11', 'Mother\'s Day'),
(20, '2015-05-10', 'Mother\'s Day'),
--- End code ---

SleePy:
I don't see what is wrong there?
That is how they should be saved. When we want to use a single apostrophe, and we have opened the string with a single apostrophe, we have to escape it with a \. The same apples for the double apostrophe.

Antechinus:
Well I can tell you for a fact that when such a backup is opened in Notepad++ the syntax highlighting shows an error for every  \'  and I can also tell you that when such a backup is restored there is missing content and broken links all over the place. So to my mind this indicates quite a problem.

On the other hand when I get a backup with the apostrophes saved in a different format (either from my local host or by exporting a table directly from phpMyAdmin) then the syntax highlighting is correct and restoring does not result in dropped content.

What this means is that even if you think \' is the correct syntax my server happens to disagree with you, and so does the bloke who programmed Notepad++.

ETA: I'll give you two different examples of syntax that does work.

First, this:


--- Code: ----- Dumping data for table `smf_calendar_holidays`
--

INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(1, '0004-01-01', 'New Year''s');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(2, '0004-12-25', 'Christmas');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(3, '0004-02-14', 'Valentine''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(4, '0004-03-17', 'St. Patrick''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(5, '0004-04-01', 'April Fools');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(6, '0004-04-22', 'Earth Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(7, '0004-10-24', 'United Nations Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(8, '0004-10-31', 'Halloween');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(9, '2004-05-09', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(10, '2005-05-08', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(11, '2006-05-14', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(12, '2007-05-13', 'Mother''s Day');
INSERT INTO `smf_calendar_holidays` (`ID_HOLIDAY`, `eventDate`, `title`) VALUES(13, '2008-05-11', 'Mother''s Day');
--- End code ---

Note that the apostrophes are saved as ''. This is actually two singe apostrophes, not one double. This works for both syntax highlighting in Notepad++ and for restoration of content without any dropped sections, etc.

The other syntax which works is this:


--- Code: ---# Dumping data in `smf_calendar_holidays`
#

INSERT INTO `smf_calendar_holidays`
(`ID_HOLIDAY`, `eventDate`, `title`)
VALUES (1, '0004-01-01', 'New Year's'),
(2, '0004-12-25', 'Christmas'),
(3, '0004-02-14', 'Valentine's Day'),
(4, '0004-03-17', 'St. Patrick's Day'),
(5, '0004-04-01', 'April Fools'),
(6, '0004-04-22', 'Earth Day'),
(7, '0004-10-24', 'United Nations Day'),
(8, '0004-10-31', 'Halloween'),
(9, '2004-05-09', 'Mother's Day'),
(10, '2005-05-08', 'Mother's Day'),
(11, '2006-05-14', 'Mother's Day'),
(12, '2007-05-13', 'Mother's Day'),
(13, '2008-05-11', 'Mother's Day'),
(14, '2009-05-10', 'Mother's Day'),
(15, '2010-05-09', 'Mother's Day'),
(16, '2011-05-08', 'Mother's Day'),
(17, '2012-05-13', 'Mother's Day'),
(18, '2013-05-12', 'Mother's Day'),
(19, '2014-05-11', 'Mother's Day'),
(20, '2015-05-10', 'Mother's Day'),
(21, '2016-05-08', 'Mother's Day'),
--- End code ---

Again, this is fine for both highlighting in Notepad++ and for error-free restoration of content. Of course I'm currently running ISO not UTF-8.

Anyway the point is that the current way that both SMF and the cpanel SQL manager want to save apostrophes does not work in practice. It's that simple. The only way I can get an uncorrupted table exported from my online server is to export straight from phpMyAdmin, and when I do that apostrophes are not saved as \'

When I export from my local host using SMF admin the backups are fine, and once again the apostrophes are not saved as \'

This has to be a server configuration problem. Something in the way my online server is set up is causing both SMF admin and the cpanel SQL manager to bugger the syntax in backups. 

Navigation

[0] Message Index

[#] Next page

Go to full version