Recent Topics

Ultra long posts and the importance of database backups

Started by on Jan 31, 2007 – Contents updated: Jan 31, 2007

Jan 31, 2007 01:58    

This is not a problem, but I got scared for 20 minutes.

Back in august 2005 I wrote and ultra long blog post. This was a traslation I made during several days, separated between them for weeks. At some point when I was editing my master piece translation, I realized that the post was being cut, and no matter what I wrote, nothing would be saved after a given amount of characters.

I thought this could be a limitation on the maximum size of any given post. Search here and there I found that the 'post_content' field in the 'evo_posts' table was type 'TEXT' and this kind if field has a maximum size of something (65k characters, if I'm not wrong). I was editing past that limit, and when the post was fed into the DB the content was cut to fit that size.

Alright, I said, maybe if I change the type to something bigger, the problem would be solved. So I read mysql reference manuals and determined that a type MEDIUMTEXT would do the trick. I backed up y database, did the change, and voila, I could post ultra long posts.

Problem was when I upgraded B2 to the next version. The update script would again set the 'post_content' field to type TEXT. So that time, I went to my ultra long post, and it was cut. I panicked, thinking I would have to write the missing text all over again. But I rememered I had a DB backup from before the last upgrade. So I restore the particular post and everything fine.

The next upgrade, I forgot the issue with the 'post_content' and had to repeat, restoring the particular ultra long post from a previous backup.

To my surprise, today someone leaves a comment in that post and I go there to read it and, what the hell! The post is cut again. Again, I panicked, and rush to look for my last DB backup, but before doing that I click the edit link to see what was happening. Bigger surprise! The post content was complete inside the backoffice editor, but outside, in the blog it was displayed cut.

So, OK, I say, now I don't understand nothing at all.

I go the phpMyadmin and check that in the DB, the post is complete. So I say "these guys changed somethin inside the get_content() function or something of the sort, because the problem is in the trip between the DB and the blog display". Thus, I venture into b2evo source code, but I don't find anything. Not any clue of any kind.

I was already preparing for a post in the forum crying for help, when two neurones made contact in my brain and made me remember there is a link, an obscure and unexplainable link, in the backoffice "Tools" tab, which says something like 'purge cache of pre-rendered items' or something to that effect. So I click there and then go again to my ultra long post, product of months of work, and it was there! complete in all its majesty!

So I understood a little bit of black magic. What I think it happened was that nobody had seen this post for a long time, from sometime after being cut to be made fit into a 'TEXT' field, and before it was restored by me to its full size. So this post was sitting there in the 'cache of pre-rendered itmes', cut and incomplete, but in the database it was full and complete. After I purged the cache, B2evo had no option but to suck it from the DB and display it complete.

And that is how I understood what a cache of pre-rendered items is for: to make me panick and make me write and ultra long post in the forum, letting everybody know of my 'discovery'.

So, anyway I ask, this cache of pre-rendered items is never refreshed? Is there a way to call a post manually directly from the DB and not from the cache? If the cache is not stored in the DB to speed things up, where is it stored? I' sure the answers to all my questions are in the source, but I did't want to let pass a post without questions. Never mind.

MORALEJA: have your backups always ready for any emergency.

Jan 31, 2007 10:21

Damn, you're as long winded as Stk is :|

There's a possibility that the field type/size will be changed in the near future ;)

¥

Jan 31, 2007 12:58

I just wanted to let everybody know, just in case somebody stumbled upon the same problem.

¿Would the change in data type affect the DB performance? I think I have only one out of more than 1400 posts that is sooo long. Bu I don't know wat are the internals of MySQL.

Jan 31, 2007 17:25

You mean there might be even more long winded bloggers out there ? 8| ...... damn, and Stk thought he was unique ;)

It's actually a shame you didn't post this a couple of days ago, it would have saved him tearing his hair out when he upgraded and hit the same problem ;)

I don't know if it'd have any performance impact, but I'm pretty sure it's one of the things the devs will take into account before they consider implementing any changes

¥

Feb 03, 2007 20:22

Version 2.0 has MEDIUMTEXTs by default now.

Also, the cache is refreshed when you edit a post in the backoffice.

It is not refreshed when you restore a particular post directly in the database. And that's normal. :p

Mar 20, 2007 02:28

I'm not all certain what TEXT and MEDIUMTEXT means in this case. However, I'd like to increase the number of characters each post can hold in 1.9.2. I discovered tonight that some large posts were missing information at the end. It shows up in the preview, but when I actually issue the post, they are gone.

Mar 20, 2007 10:16

In the MySQL database there are several possibilities for storing text. Here they are:
char (255), varchar (255), tinytext (255), text (65535), mediumtext (16777215), longtext (429496295), blob (65535) (binary large object), mediumblob (16777215) , tinyblob (255), longblob (429496295)
The size is in bits and tells the database how much space it should reserve for a particular item.

Mar 20, 2007 10:51

Where in the b2evolution sql database can I adjust those settings?

Mar 20, 2007 11:35

If you must know: you can access your database through your host's cpanel or phpMyAdmin or equivalent tool.
The posts are stored in evo_posts -> post_content.

You can run into serious problems if you change the database. Not only am I not sure if this works, you may loose your existing content and you'll probably run into trouble when you upgrade B2e to a new version (2.x). You'd better wait till 2.0 arrives.
So don't try this with a serious blog and be sure to make a backup before you start.


Form is loading...

Build your own website! – This forum is powered by b2evolution CMS, a complete engine for your website.