New install of Upfront, Posts table growing to 60MB

I noticed my database backups grew at some point today form about 4MB to over 50MB.

Looking at phpMyAdmin I can see lots of unusual entries being entered in the posts_table every time I edit a page in Upfront, see attached image of phpMyAdmin for a couple of simple edits I just did as a test. That table is over 50MB on its own.

For each of those entries that have "rvsn" in the post_status column there is a whole load of random characters, starting something like the following and in all each one is about 177,801 characters long - that's a lot of database. nearly 300 rows on a new install of Luke & Sara them with about 3 three pages/posts from other test.
---------------
A tiny sample of the mysterious code:
YTo3OntzOjc6InJlZ2lvbnMiO2E6NTp7aTowO2E6MTE6e3M6MTA6InByb3BlcnRpZXMiO2E6MjA6e2k6MDthOjI6e3M6NDoibmFtZSI7czozOiJyb3ciO3M6NToidmFsdWUiO2k6MzA7fWk6MTthOjI6e3M6NDoibmFtZSI7czoxNToiYmFja2dyb3VuZF90eXBlIjtzOjU6InZhbHVlIjtzOjU6ImNvbG9yIjt9aToyO2E6Mjp7czo0OiJuYW1lIjtzOjE2OiJiYWNrZ3JvdW5kX2NvbG9yIjtzOjU6InZhbHVlIjtzOjE5OiJyZ2JhKDI1NSwyNTUsMjU1LDEpIjt9aTozO2E6Mjp7czo0OiJuYW1lIjtzOjc6InZlcnNpb24iO3M6NToidmFsdWUiO3M6NToiMS4wLjAiO31pOjQ7.......
----------------------

For each edit I do in Upfront, I get several entries like this in the database, they are huge.

I have turned off all plugins and it's made no difference.

I have run two respected database optimisers, one being "Optimize Database after Deleting Revisions" that can get rid of any old revision data, but neither plugin made a difference.

This is a new WP install and I'm building it up bit by bit on a local DesktopServer environment and up to when this problem started, all has been running well, it still is except for these large db entries which are growing at every tiny edit.

All I know is I had a couple of odd things happen around the time this started, one was I lost some edits when I pressed the backspace key and the browser went back a page (mentioned in another question and probably not related?), and the other thing is that for a while edits weren't showing when I revisited a page in Upfront to re-edit/check. That problem seemed to clear itself after logging out and then in again - both those things happened around that time so I'm thinking maybe something got corrupted but I have no clue what to do now.

Has anyone seen anything like this before or any ideas for a fix?

    MrArtist

    I have just checked my full backups made at various stages while developing this site and it is clear that the extra data being added into the database started from the moment I installed Upfront (Luke + Sara via the WPMU DEV site plugin).

    Before that the WP site install was very basic with just a few tweaks from the original install and the WPMU DEV plugins Defender and Humming Bird added.

    It looks like I might have to scrap over a days work and start again unless someone knows what the issue is.

    All ideas gratefully accepted.

    Sajid

    Hi @ta-wpmudev,
    Hope you are doing good today

    I have one issue reported by a member in the past with this issue and that fixed it by optimizing the database with WP Optimize plugin.

    In that thread, I were unable to replicate that issue on my own test install using WAMP server on localhost.

    Here is the old thread link for reference:
    https://premium.wpmudev.org/forums/topic/upfront-luke-sara-theme-big-database#post-1007197

    Also, please bear in mind that Upfront stores most of the data in database so it will be slightly bigger than normal WordPress websites using regular themes.

    Hope that helps! Feel free to post a reply if you need further assistance
    Cheers, Sajid

    MrArtist

    Hi Sajid
    I have now tried three database optimsers including WP Optimize and nothing more can be cleaned. Pages table in the database is now 58MB with just the default WP & Luke + Sara posts and pages.

    Every time I do an Upfront edit, two or three new entries associated with that post get added to the database, increasing it in size each time dramatically.

    What does the "rvsn" mean in the post_title column - it seems to represent "revision" (perhaps while the page is being revised in upfront?) but for some reason it's not being cleared when the page edit is saved.

    I've read the older link you provided, it sounds similar but my db is not clearing. Even if it did, it seems it wouldn't fix whatever is causing the problem, and new edits would continue to accumulate excessive db entries.

    I'm really reluctant to build this site again, but it looks like I might have to. I will do some more tests with backups from before I installed Upfront, and with a new WP install to see if it happens again, but I am going to lose a lot of work so it would be great if I can find the cause and clear the current db.

    Thanks for your help so far.

    MrArtist

    I think I've partially solved what's causing this problem, or rather worked out what's actually happening with Upfront causing the huge databases.

    I'm now very sure the reason for the large Posts database table is that Upfront stores each entry for its undo buffer as a separate row in the Posts table. Every single edit to absolutely anything done in Upfront gets an extra (large) database row entry. Even just going to the Upfront interface causes one.

    Unfortunately, these entries do not clear when the page is saved or when Upfront is exited/quit, and those row entries also linger in the database for along time. I have a feeling they do get wiped from the database after a period, maybe several hours? Maybe the site needs to be active for this to happen? Perhaps it's a cron job? All in all, it makes using Upfront a problem especially when having a large database (after many edits) may need immediate backing up after all those alterations to structure, logo, design, colours, text, image placement, etc., etc.

    The main problem is that Upfront's undo/redo buffer database saves are actually very large and there can be lots of them. For the Fixer theme, each tiny edit or change of just the home page causes another 670KB of data to be added, as well as the 670KB added on just opening it in Upfront (Spirit Theme is 310K per edit and one of Luke+ Sara Theme pages is about 260K)..... You can see how this could quickly cause problems if one does many edits over lots of pages, adding tens or even hundreds of MBs to the page table in the database.

    I have now extensively tested this fact (problem) on a local server, a live server, both with a clean WP 4.4.2 install and with 3 different Upfront themes and they all do it. The problem is the undo buffer and it not being cleared quickly or easily. Perhaps Upfront needs a button to purge the database entries or it should empty the buffer after a save or quit?

    If there is an auto-clearing of Upfront's undo (revision) table entries at some point (maybe by cron?), I have not yet established how long it takes. I have been watching one test site for a few hours now and the most recent (custom) Upfront revision entries have not yet disappeared but I'm fairly sure that some of the excessive entries from yesterday have now cleared on their own.

    Can the developers of Upfront confirm my findings and perhaps confirm the time Upfront/WP/cron takes to auto-clear the entries (if at all), and is there any way to manually clear those entries ahead of whenever it might auto-clear?

    -------------------------

    By the way, most if not all WP DB optimizer plugins don't work with InnoDB tables, they only help with the (older) MyISAM types. From what I understand most databases are created as InnoDB these days. There is a WP way to optimise built in (it must be used only temporarily though), the wp-config setting "define('WP_ALLOW_REPAIR', true);" apparently can optimise both types of DB. Someone has written a small plugin for it here: https://perishablepress.com/wordpress-plugin-enable-database-tools/

    I did try some various tests at optimising the database tables but they didn't seem to get rid of the Upfront revision data as I believe they are custom revision entries and don't get noticed as junk by the various Optimisers. Perhaps when the other person reported a similar issue late last year and said an optimiser worked, actually had the database auto-clear at around the same time so mistook it for the plugin working?

    MrArtist

    I can confirm my findings of yesterday, Upfront is still storing all individual edits in large db entries and db's post table can become very large if doing lots of edits.

    I have found there IS an hourly Upfront cron job setup in WP cron manager, but the odd thing is that it doesn't seem to actually do anything until 24 hours has gone by since the edit.

    Via phpMyAdmin, I have been watching various Upfront revision table entries get automatically removed today but none before at least 24 hours since the edit time is up.

    Therefore, there seems to be something a bit wrong with the way that the hourly Upfront cron task is working. Can any Devs confirm my findings and why this might be going wrong? Is there a fix so at least there's a chance that DB sizes can get back to more sensible sizes within the cron's hour - 24+ hours can cause lots of db size issues when there are a lot of edits being done all the time.

    Many thanks.

    Sajid

    Hi @ta-wpmudev,
    Hope you are doing good today

    I am glad and appreciate all the tests you ran to find out the root cause of this issue. However, regarding the cron job not working issue, WP Cron is not 100% accurate so that scheduled can be missed easily on WordPress sites, specially in development phase when there is no enough site traffic.

    Regarding your findings, I am flagging one of our Upfront developer to take confirm and provide more details about this issue.

    They will post a reply here as soon as possible.

    Take care and have a nice day
    Cheers, Sajid

    Sajid

    Hi @ta-wpmudev,
    Hope you are doing good today

    I heard back from developer and he confirmed what you have found so far. Regarding the cron job, it does schedule the cron every hour and clear it after a day.

    He further stated that he is aware of that large DB sizes but it is necessary to keep that all data temporarily for undo/redo functionality. So, after the scheduled time it will get cleaned automatically.

    Thanks and kudos for smart move. I am sending some points your way for appreciation.

    Take care and have a nice day
    Cheers, Sajid

    MrArtist

    Hi Sajid
    Thanks for your reply.

    Can you find out why it is necessary to keep all of Upfront's temporary undo data for 24 hours before being lined up for cron removal?

    It's not as though we can go back to edit the page in Upfront at a later time (after quitting Upfront) and then undo any previous changes held in that store for that page, so it seems pointless holding on to the data and bulking out so hugely our databases.

    We really need the facility to manually clear those undo buffers as soon as possible, perhaps a "Clear Undo Cache" button somewhere.

    The potentially (very) large undo cache is really causing problems for me here as I try and upload from local dev to live staging or the real site. The Upfront cache has the potential to make the posts table 100's of MB in size and even an hour is too long to hold on to that data, let alone a day, especially when it serves no purpose after quitting Upfront.

    Using any form of database optimiser to try and clear the entries doesn't work, nor manually running the Upfront cron task.... We just have to wait 24+ hours for the cron task to be allowed to do its thing (I appreciate that the pages need to be visited before WP cron can perform its tasks)

    Ideally the undo cache should be emptied the moment I quit Upfront (or hit a 'Clear Upfront Cache' button).

    While I remember, in a related way, Upfront also needs the facility to NOT update every element on the page for every tiny thing we do. When doing something like trying to sort out menus, Upfront can be painfully slow while each entry is WYSIWYG updated. It would be cool to have a button we can activate to temporarily put WYSIWYG updates (and database undo's) on hold, to be followed by a "Commit Edits" button/action. Also, not forgetting, the strain that could be avoided on database undo entries 's and general interface responsiveness that would be avoided by each tiny thing I do being a 200KB+ entry in the database.

    And lastly, it's great that we essentially have 'unlimited' undo's in Upfront, but in the event of me wanting to go back several stages, it is so painfully slow that I rarely want to do it, maybe once or twice backwards is great manually, but would it not be great to have a selector that could drop down and maybe let us pick say 10 undo's ago, or 20, 30, etc.. You get the idea?

    Don't get me started on the media selector for already existing images in my library. A unchangeable default of 20 images showing is painful to wait for before picking the 100 option and then waiting again. Would be great to see those image previews cached and not rebuilt each time I go to enter an existing image. Also integration with things like WP-Media Folders plugin would be brilliant.

    Panos

    Hello MrArtist ,

    And thank you for all your valuable feedback

    First allow me to say that this is an enormous project, with nothing similar ever made and still is in its early days. With each update it will become better and better!

    I would strongly recommend to post your suggestions in the Features and Feedback forum so it comes under the attention of the developers. Keep in mind that the most likes it receives the better the chances to see your ideas take place in the Upfront theme!

    Kind regards,
    Panos

    MrArtist

    It's a shame to hear this is still a problem with Upfront. I've been put off using it for any other sites since because of all the issues I had particularly with the DB sizes (and migrating Upfront's encoded URLs) whilst trying to stage/update.

    I have just read the whole post again (from a year ago) and it seems clear that we still need an Upfront "Clear undo buffer/cache" button" so that Upfront can clear its 24 hour buffer of pointless content. - only then can things like migrating/copying from staging work (although I also did have problems with Upfront encoding its resource URLs in its table entries in a way that backup/staging management plugins could not convert from one domain to another).

    It would be nice to know if ALL these issues have now been sorted out. But the last comment suggests not.

    If Upfront cannot yet do these things (a year later), I can't ever see how using Upfront in any meaningful way is practicable.

    Please confirm if these things now ALWAYS work properly or if it's a long-term problem/limitation of how Upfront will forever operate.

    Sajid

    Hello MrArtist,
    Hope you had a great weekend

    As mentioned in my last reply, it is required to keep the data for undo/redo functionality. If you don't store previous data, you can not un-do changes.

    This is a very big projects and a lots of development has been done since it was first released like WooCommerce and MarketPress integration. It is a lot better and in stable state than it was released.

    However, I can understand this issue and it is something already in our developer's attention. They are work really hard on it. So we can not say never, it will definitely improve and also focus on its performance. You will see a lots of improvements in Upfront Builder soon.

    Thanks for your feedback and have a nice day

    Best Regards,
    Sajid - WPMU DEV Support

    Paul

    Thank You Sajid.
    I have to say that I rarely use the Undo function.
    I don't find it particularly flexible and therefore not very useful.
    This is only a problem for me on a couple of sites where my hosting package is rather storage restrictive and it's easy for the accumulation of data to exceed the limit. I imagaine most people wouldn't notice because their hosting package is more generous.
    Once I understood the problem a work around was easy to implement - I use php admin to delete the offending data.

      MrArtist

      Hi Sajid - I think we all understand why an undo buffer is useful/required, my point is why keep it beyond the session we are working in? Upfront should ideally clear it when we exit the page. It's not as though we can go back to a page later and undo something we did earlier. Its usefulness is only when still in the page and as Paul indicates it isn't particularly flexible (e.g. we can't easily pick 10 or 20 undos back from a menu) and so if we ever do try and use its functionality it's incredibly slow and unwieldy.

      At the same time it massively increases the database size and after prolonged sessions working on a site it makes the database so big (day after day, each time the site is worked on) it becomes a problem for all sorts of reasons - apart from the space it uses up, the particular problems I started getting was with backup, migration and/or staging with various server timeout failures from plugins and service abilities.

      There seems little point in the undo buffers hanging around way beyond the current editing session, let alone for it to sill be there 24 hours later (or more sometimes as I found). Upfront needs a way of immediately clearing or limiting those to a certain number of undos.

      There is also still the so far unaddressed problem of how Upfront stores all its URL data in an encoded way that makes migration and staging fail/impossible. All the well known plugins and methods I tried couldn't properly find and adjust the Upfront stored URLs because it seems Upfront has it's encoded method and so I was getting sites that got all mixed up with where they thought they were pointing for certain resources.

      I understand Upfront is a massive project and these details can take time to iron out, but skimming back to my comments in this post from a year ago (Wed Mar 30 2016, 1:20:13 PM), I see I make exactly the same points and from what I understand nothing has yet changed/improved.

      All in all, I still don't know if I can use or trust Upfront to cope with anything more than working on a simple live site. Again, are the undo buffer problems and the URL migration issues just something that will always be an issue because that's just how Upfront works and will always have to work because it's how the core functioning was designed and it's too late to change its working methods?