My final step before posting this entry is to do a grep replace to escape all my quotes before submitting it to the database. Unacceptable.
I have a sneaking suspicion that Movable Type 3.15 is not quite there yet as I have, within the first month of blogging with it, had a Berkeley Database corruption and, after a painful port to MySQL and painful restore (requiring a third-party script), I find that my MT/MySQL install is unable to deal with quotes, both single quotation marks and double quotation marks.
Last Friday, my Movable Type install that was based on the Berkeley Database got corrupted.
I was told it was the fault of the Berkeley Database, but I know that Caucus uses flat text files and can support thousands of posts and replies.
So, I gave Movable Type the benefit of the doubt and reinstalled the software with a MySQL back end.
I am having problems that should have already been solved: Movable Type is not escaping my quotes and so the every entry with a quote is choking MySQL.
The MT code base can and should be able to adjust for issues requiring escaped characters as well as for issues that could potentially corrupt a pretty tried-and-true database solution, which Berkeley certainly is. I never had this problem with Greymatter.
I IM’d Scott Burns, who was the SA for The Meta Network and worked with me at Caucus, an Enterprise-level discussion forum tool that uses flat-file text database storage based on Berkeley and he shared with me the scope of what he was dealing with very reliably versus what I am dealing with with a simple blog:
“I seem to remember around 8000 posts, probably close to 1GB of stuff, most of it text. Brainstorms was double that last I had management control of it and it’s grown since then.”
I wrote this to the gang at Movable Type and I hope they can help because I love the application framework.
“After all of my issues with everything — I am finally ported over to MySQL — the issue always comes back to escaped quotes.
As long as all quotes are escaped, the entries go in.
Is this is something you can put into your Request for Improvement?
This should never be an issue, and that is the only thing that it is.
I will for now escape my own quoted (single ‘ and double “) and the issue does not exist in the Berkeley DB and apparently not in all MySQL install, but in mine it does.
I will follow up when I get more info…
Also, are you aware of the mt-db2sql.cgi tool?
It saved my arse!
Cheers,
Chris”
And then I asked Phillip Rhoades to give me a postmortem as to what he thought the entire issue was and why I could not shoehorn fewer than one-hundred blog entries that I had exported into a text file back into the most amazingly-quick relational database on the planet, MySQL, without choking:
“Movable Type cannot handle unescaped quotes when submitting data to a mysql server, at least not reliably. This is evidenced by its failure to submit entries with unescaped quotes on chrisabraham.com
Quotes and other entities that need to be escaped prior to insertion should be escaped automatically by the Movable Type script prior to an attempt to insert the data. This activity should be transparent to the user. Or possibly changing the insert line to take into account that it may contain unescaped quotes.
Please correct this problem.”
Oy gevalt!
Comments (2)
See all the funny \” and \’ characters up there? Well, MT is dealing with the entire thing pretty strangely so until then, I might have a funny-looking blog — please excuse the visible escape character thing.
Chris, thanks for posting about this. Do you have any good news since then? I’m encountering the same problem and would rather see this sort of thing handled automagically.
I’m also working on a plugin and am searching for a way to do this right in the MySQL query. I know they have QUOTE(…) but it doesn’t appear to work when passing in a value, only when selecting a field.