Apostrophes causing syntax errors in the database.

[Antechinus]

I'm getting lots of these and I need them to stop, because they are causing massive corruption in my databases. This is the only thing stopping me upgrading to RC1 (the site is currently running 1.1.8 with TP 0.983).

I'm going to have to do a lot of database editing to clean things up so it's all good when I upgrade, and I am not at all keen on having to do the job more than once. Databases are really, really boring. 

What is happening is that every time a post, or thread title, or category name or whatever contains an apostrophe it is being entered in the db as \'

This is fine for php but generates a syntax error in sql. One syntax error for every apostrophe in the database. That's a lot of errors and they are causing broken links and dropped content.

I raised this some time back and it was suggested that disabling magic quotes was a good move. This was done but the problem hasn't stopped occurring. What has also happened is that with an online RC1 test site and another RC1 test site on my local host (which has never had magic quotes enabled) I am getting exactly the same errors.

This leads me to think the problem may be common. Anyone who has had the famous "You have an error in your MySQL syntax" message in the admin log is quite likely suffering from the same problem as this is what first alerted me to it.

The phpinfo for the online server can be found here. I can copy the corresponding info for the local host if it will be useful.
By the way, the online server was originally a Fantastico installation. Obviously the local wasn't.  

[karlbenson]

I don't think its that common, otherwise it would be seeing hundreds of reports of it.
Almost everytime I've seen it reported (most commonly with signatures) its been mod related.

Although double check that both gpc_magic_quotes AND magic_quotes_sybase are both off. (since the later overrides the former)

[Antechinus]

magic_quotes_gpc	Off	Off
magic_quotes_runtime	Off	Off
magic_quotes_sybase	Off	Off

It doesn't seem to affect sigs particularly either. Mods are different on the 1.1.8 and RC1 sites. I'll go through the sites and get a list of mods.

[Antechinus]

This is the list of installed mods on the 1.1.8 site:

Show Local Url Titles
DonationCoder.com SMF Search Enhancement Mod
TinyPortal 0.983
Bookmarks
Save PM as HTML
simplemp
Auto Embed Video/Audio Clips
SMFChess

Out of those some are not relevant, because the problem existed before they were installed.
The only ones that might possibly be relevant are these:

DonationCoder.com SMF Search Enhancement Mod
TinyPortal 0.983
Bookmarks
simplemp
Auto Embed Video/Audio Clips    3.0
SMFChess

[karlbenson]

Auto Embed Video/Audio Clips    3.0
Its not impossible that the above mod is the cause. try uninstalling it and see if it resolves the issue.

[Antechinus]

Ok, I can try that but it's only fair to warn you that the RC1 sites that exhibit the same problem do not have AEVC or any of its relatives installed, so even if it did solve the problem on the 1.1.8 site I would still have to solve it on the RC1 sites. One of them has PortaMx installed and the other one on my local host is vanilla RC1.

In other words, "it's not impossible that" is a long way from "this will be the cause".

ETA: Also if the AEVC mod was causing the problem then I would expect everyone who had installed that to have the same database problems.

Oh and the other thing is testing this. The only way I can reliably test this is to get a database backup and manually edit out all the syntax errors before restoring the corrected backup, then running that for 24 hours or so to see what happens. This would involve taking my site offline while doing the editing, and of course this would apply every time I need to test another proposed fix. I simply do not have the time to do this too often, so I would like to be fairly certain that whatever is proposed is going to work.

[Antechinus]

Ok regex old son, I had me a brainwave here and did some testing. 

What I've done is to set up a brand new, completely vanilla, 118 installation on a clean, empty database. I figured I could then start installing the same mods one by one until I hit the problem. As it turns out this wont be necessary because the corruption is right there from the word go. I did an immediate database backup and checked the downloaded file.

Yep. It is not a mod problem at all. Currently it seems it is either a server config problem or a problem within SMF itself. I'm not sure which and I don't have the expertise to decide.

Again, the phpinfo for the server is here.
If you want full cpanel/phpMyAdmin access I suppose I can sorta trust you with that. 

[SlammedDime]

how did you do the database backup?  From SMF's admin panel?  From phpMyAdmin?  Cpanel?

If you tried one method, try another, compare the outputs, see where they differ.

[Antechinus]

SMF admin for the first one. I'll try phpMyAdmin later today. I have some other things I need to do first.

[Antechinus]

Ok, I've done three test backups.
First with SMF admin, which makes a mess of the syntax as per the OP, etc.
Second with the cpanel backup manager, which has exactly the same problem as SMF admin.
Third was exported straight from phpMyAdmin. No problem with this one. Syntax is perfect everywhere.

However there is another problem: phpMyAdmin does not export the full messages table. It chops it in half.
So I'm stuffed either way. I can use SMF admin and get all data but with massive corruption, or I can use phpMyAdmin and get perfect syntax but lose half my content.

Neither of these solutions is exactly satisfactory.

[Aleksi "Lex" Kilpinen]

Just a thought, but if phpmyadmin works correctly but other means don't, there might be something wrong with the communication settings between MySQL and outside software...

For example, I noticed a while back that if your database is in UTF8, but your connection charset is latin1 it will create some strange situations with certain characters.
Like, if you post € it comes out as €, but if you quote it or inline edit it, it will become a simple square box.

[Antechinus]

I just updated my post. PhpMyAdmin does not work correctly either. It's fine for syntax but loses content when exporting. What this means in practice is that I currently have no way of getting a reliable backup.

ETA: Hell, I just noticed something else too. The damned TP tables are in utf-8 collation unlike the rest of the site, which is in latin1_swedish_ci like it ought to be. That's another problem to sort. 

[Aleksi "Lex" Kilpinen]

Isn't there a way to take backups in parts available via phpmyadmin?

I seem to remember an option to do that, since I have a problem in backing up as well, and have had to look at all options available. I am unable to take a full database backup without some script like Adminiature that knows how to do it in smaller parts, or otherwise my messages table comes out only partially or completely empty... ( I've managed to track down the problem so far, that I believe I am simply running out of memory doing a backup the "traditional" way. )

[Antechinus]

Bugger that for a joke. The db aint getting smaller, mate. What happens if I end up having to export the messages table in fifteen segments? Imagine trying to restore that lot.

ETA: SMF admin will handle the full db content in one export, it just wont get the syntax right.

[Aleksi "Lex" Kilpinen]

Bugger that for a joke. The db aint getting smaller, mate. What happens if I end up having to export the messages table in fifteen segments? Imagine trying to restore that lot.

I don't have to imagine

But that's where Adminiature and Bigdump came along to save _my_ day.
Adminiature can take a backup pretty much the same way Bigdump is used to restore it.

( One thing though about adminiature - if the folder or file you are trying do the backup in, is not writable Adminiature will not notice, and will flood your error log.... )

http://www.adminiature.com/

[Antechinus]

I was actually thinking of trying out MySQLDumper as apparently that will do both jobs. Getting the right access to install it and set it up might be a drama though. Sure need to sort something because the status quo is totally borked.

[青山 素子]

If you have shell access, the most reliable way to make a database backup is with mysqldump.

[Antechinus]

I don't have shell access. Anyway I should probably file a bug report for the SMF backup tool as the problem is definitely repeatable.

[青山 素子]

Yes, please report it as a bug. Also include the status of the magic_quotes variables (which are likely related to the issue) on the server along with how PHP is being run (Apache handler, CGI, FastCGI, etc).

[Antechinus]

All magic quotes stuff is turned off. Not sure on the latter. What's the best way of finding out?